Hi all, Based on some comments here and in the SqlAlchemy IRC chat room, I've updated my decorator to make some changes. Here is the updated version:
class ScopedSession(object): SESSIONMAKER = None # this is the single sessionmaker instance def __init__(self, engine, auto_commit=True): assert engine is not None, "Must pass a valid engine parameter" self._auto_commit = auto_commit if ScopedSession.SESSIONMAKER is None: ScopedSession.SESSIONMAKER = scoped_session(sessionmaker(expire_on_commit=True, bind=engine)) def __call__(self, func): @functools.wraps(func) def wrapper(*args, **kwargs): db_session = ScopedSession.SESSIONMAKER() try: results = func(db_session, *args, **kwargs) db_session.commit() # should we rollback for safety? if not self._auto_commit: db_session.rollback() except: db_session.rollback() raise finally: # release the session back to the connection pool db_session.close() return results return wrapper This version changes the code to a class based decorator so that I can create and use a single sessionmaker. It also calls the close() method of the session at the end of the decorator to release the session back to the connection pool. In response to using SqlAlchemy in a thread (in Twisted), I also write Pylons applications which use SqlAlchemy as the database backend. Since every request is a thread in Pylons, SqlAlchemy runs in those threads without a problem, so I'm not sure I see the problem running it in Twisted. The threads.deferToThread(...) call is nothing fancy in Twisted, it gets a thread from a thread pool, runs the passed function in that thread, and returns a deferred that fires in the main Twisted thread when the thread ends. This code looks like this: @defer.inlineCallbacks def update(data): # this function does some db work @ScopedSession(engine=engine) def process(session): # this method will be run in a thread and is passed a session by the decorator results = session.query(<use data parameter since it's in scope>) return results # call the internal process function in a thread try: results = yield threads.deferToThread(process) except Exception, e: # do something with exceptions defer.returnValue(results) Using @defer.inlineCallbacks makes calling asynchronous code easier to write in Twisted than using the traditional callback model (in my opinion). Defining the process() method internally gives it access to the parameters within the scope it's defined within and allows me to simplify it's parameters down to the session received from the @ScopedSession decorator. process() returns the SqlAlchemy results normally and is received into results when the yield returns from the thread (deferred fires). In the above example the session is commited and closed by the @ScopedSession decorator because I didn't override the default auto_commite=True parameter. Any comments, criticisms or suggestions are welcome, and again, thanks in advance! Doug -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.