Sqlalchemy - rollback when exception

Question

I need to rollback a transaction in core.event 'handle_error' (catch 'KeyboardInterrupt'), but the parameter in this event is ExceptionContext, how do this?

Answer 1

I usually have this kind of pattern when working with sqlalchemy:

session = get_the_session_one_way_or_another()

try:
    # do something with the session
except:                   # * see comment below
    session.rollback()
    raise
else:
    session.commit()

To make things easier to use, it is useful to have this as a context manager:

@contextmanager
def get_session():
    session = get_the_session_one_way_or_another()

    try:
        yield session
    except:
        session.rollback()
        raise
    else:
        session.commit()

And then:

with get_session() as session:
    # do something with the session

If an exception is raised within the block, the transaction will be rolled back by the context manager.

---

^{*There is an empty except: which catches literally everything. That is usually not what you want, but here the exception is always re-raised, so it's fine.}