I am writing a web-app using Pyramid and in a couple of views I need to do
a join across databases. I have two separate models in my web-app and
initialize SQlalchemy for both separately like so
Database - users:
DBSession_users =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base2 = declarative_base()
DBSession_users.configure(bind=engine)
Base2.metadata.bind = engine
Database - master:
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
DBSession.configure(bind=engine)
Base.metadata.bind = engine
I ensure that appropriate engines are passed to both the models. The models
work fine in isolation.
For a view I need to join a table in users DB with the result set generated
from master DB. The result is generated on user-input so the filter
criteria are generated on the fly (In SQLAlchemy). Therefore, I can't use
hard-coded SQL (Which anyway appears to be bad idea). So I decided to get
the result of DBSession query and store it in a temporary table in users.
Here's the code that I wrote for this
metadata = MetaData()
temp1 = Table("temp1",metadata,
Column('id',Integer,primary_key=True,autoincrement=True),
Column('ip',mysql.MSInteger(unsigned=True)),
Column('total_count',Integer),
Column('distinct_count',Integer),
prefixes=['TEMPORARY'],
)
class TempTableUser(object):
def __init__(self,ip,tot_count,distinct_count):
self.id=None
self.ip=ip
self.total_count = tot_count
self.distinct_count = distinct_count
mapper(TempTableUser,temp1)
temp1.create(Base2.metadata.bind)
Then for the result set (which was obtained from
session.query().filter(...).all(), I copied each entry into the table
session_users.add(TempTableUser(int(entry.ip_inet),int(entry.total),int(entry.count_unique)))
Here session_users=DBSEssion_users()
Then I could join TempTableUsers to other tables and get the result.
The problem is that DBSession_users and DBSession are initialized at App
level and so do not close till app is closed (Web server shut-down). So the
TempTable persists across requests and grows. I decided to drop the table
everytime BEFORE creating it. The code becomes
temp1.drop(Base2.metadata.bind,True)
temp1.create(Base2.metadata.bind)
Now the program works fine.
But does this create a potential race-condition? Since temporary table is
apparently shared between different requests, can it happen that in case of
two near-simultaneous requests, the second request might drop the table
before first request has actually populated/used it?
Is there any other/better way of doing what I need to do? I am using
pyramid_tm and for transaction management and SQLAlchemy 0.7.8
Thanks
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/aEjzxO9MCDAJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
