Exposes SQLAlchemy's sessions and transactions as context managers (so they will be managed automatically inside blocks), and also provides a transaction decorator, which wraps an entire function in a transaction

db_utils.py

"""Utilities for managing database sessions."""
from __future__ import with_statement

import contextlib
import functools

@contextlib.contextmanager
def temp_session(session_cls, **kwargs):
    """Quick and dirty context manager that provides a temporary Session object
       to the nested block. The session is always closed at the end of the block.
       
       This is useful if only SELECTs and the like are being done; anything involving
       INSERTs, UPDATEs etc should use transactional_session."""
    session = session_cls(**kwargs)
    yield session

@contextlib.contextmanager
def transactional_session(session_cls, nested=True, **kwargs):
    """Context manager which provides transaction management for the nested
       block. A transaction is started when the block is entered, and then either
       committed if the block exits without incident, or rolled back if an error
       is raised.
       
       Nested (SAVEPOINT) transactions are enabled by default, unless nested=False is
       passed, meaning that this context manager can be nested within another and the
       transactions treated as independent units-of-work from the perspective of the nested
       blocks. If the error is handled further up the chain, the outer transactions will
       still be committed, while the inner ones will be rolled-back independently."""
    session = session_cls(**kwargs)
    session.begin(nested=nested)
    try:
        yield session
    except:
        # Roll back if the nested block raised an error
        session.rollback()
        raise
    else:
        # Commit if it didn't (so flow ran off the end of the try block)
        session.commit()

def in_transaction(**session_kwargs):
    """Decorator which wraps the decorated function in a transactional session. If the
       function completes successfully, the transaction is committed. If not, the transaction
       is rolled back."""
    def outer_wrapper(func):
        @functools.wraps(func)
        def wrapper(*args, **kwargs):
            with transactional_session(**session_kwargs):
                return func(*args, **kwargs)
        return wrapper
    return outer_wrapper

This relies on the ref-counting nature of CPython instead of explicitly closing the sessions?

How do you mean? The sessions are explicitly closed… lines 35 and 39

I believe you should also call .close() after commit at 39 and after the yield at 15.

Yes, I think you're right actually. Looking at our code (which is a web app), there is a Session.close() that is performed at the end of each request (ie. on the class, not its instances) to clean this stuff up. I don't see any reason it isn't here, though… unless this would affect nested use? Been a long time since I wrote this stuff, so a little hazy on the details now.

Actually I believe the sessions are closed as they should be right now if you're using CPython because of the reference counting. Once no one is using session it is cleaned up and closed. The problem might arise if CPython changes garbage collector or if you use this in PyPy or Jython...

ubershmekel, the sessions will be cleaned up according to the whims of the GC... you really should call session.close() directly (though I think obeattie is correct that the class-level close will do).

ajs is right ("explicit is better than implicit")

I can't seem to make sense of the @in_transaction decorator...

Using transactional_session, I can do:

with transactional_session(session_maker) as session:
    thing = session.query(cls).filter(...)
    session.add(thing)

And the object will be added to the session and the session will be committed on exit from the context manager.

How do objects get added to the session when using @in_transaction, as it does not use as <var>, making the created session seemingly inaccessible? What am I missing?