Hi All,
Let me preface this with: "I know that long running transactions in a
web app are a worse idea than that trying to forge steel in a mould
made of butter".
With that out of the way; I have inherited a django web app that
connects to many databases (this isn't starting well). These databases
also have an auditing system which necessitates a lock being acquired
before changes are made and then this lock being committed when
changes are complete and have been validated by an extra operator.
This app was initially a gui app (Oracle Forms) and is being ported.
The way this was done (before I arrived) was to patch django's
internals so that a logged in user has a raw cx_Oracle connecton which
is used for their interaction with the db. This connection is created
when they log in the first time. They acquire a lock (by calling some
PL/SQL) and then all the changes they make are kept inside a
transaction on this connection. When they wish to confirm the changes
the transaction is committed and they log out.
The django ORM wasn't sufficient for some parts of the database (in
particular the view changes part of the app) and some of the newer
functionality. So we now have an added problem in that sqlalchemy
needs to be able to use the open cx_Oracle connection (attached to the
user) to do its stuff.
I am now in the position where I have a piece of django middleware the
adds an SQLAlchemy session to the request on the way in (also creating
a nested_transaction) which is used for the duration of the request.
The nested transaction is then committed if it's found to be dirty on
the way out.
This scenario wasn't working due to QueuePool errors after a few
requests (I guess this mean that I am missing calling Session.close
somewhere?). Secondly I've tried to call close_all on the session in
the process_response method. Somewhere between the these two extremes
stuff isn't getting written to the db.
I suspect there is some combination of session, pool, engine and
connection that will make this scenario work, and would really
appreciate any pointers. Here's some of the code:
class SATransactionMiddleware(TransactionMiddleware):
"""
Custom Transaction middleware.
"""
def process_request(self, request):
"""Create an SQLAlchemy Session and attach it to the
request"""
try:
# This is zope stuff which is fetching the user's
connection and wrapping an
# engine and a session around it
ses = ISQLAlchemySession(request)
ses.begin_nested()
setattr(request, REQUEST_SESSION_ATTRIBUTE_NAME, ses)
except RequestSessionAdaptaionError:
# There may not be a logged in user, or it might be an
AnonymousUser
pass
def process_exception(self, request, exception):
"""If a session has been set up then rollback if it's
nested"""
ses = getattr(request, REQUEST_SESSION_ATTRIBUTE_NAME, None)
if ses and ses.transaction.nested:
ses.rollback()
ses.close_all()
def process_response(self, request, response):
"""If a session has been set up then commit and expunge if
it's nested"""
#import ipdb; ipdb.set_trace()
ses = getattr(request, REQUEST_SESSION_ATTRIBUTE_NAME, None)
if ses and ses.transaction.nested:
import ipdb; ipdb.set_trace()
if ses.dirty or ses.new or ses.deleted:
import ipdb; ipdb.set_trace()
ses.commit()
ses.close_all()
return response
The ISQLAlchemySession bit in the process_request method is basically
calling create_session on a single instance of the class below (it
checks for the presence of a session on the request first).
class SessionFactory(object):
def __init__(self):
self.sessions = {} # This will contain Session *classes*
def create_session(self, connection=None):
"""Create a Session wrapped around this connection"""
if not connection:
connection = django_connection.connection
if connection in self.sessions:
return self.sessions[connection]() # Instantiation
con_string = 'oracle://%s/%...@%s' % (connection.username,
connection.password,
connection.dsn)
engine = create_engine(con_string, creator=lambda :
connection, echo=True)
Session = sessionmaker(bind=engine, autocommit=False,
autoflush=False)
self.sessions[connection] = Session
return Session() # Instantiation
_session_factory = SessionFactory()
If anyone could explain the right way to do this, slowly and in words
of one syllable (and ignoring the fact that the whole approach smells
a bit) I would be eternally grateful!
Best,
Ben
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
