Using a `with` context manager in SQLAlchemy Core how do I know if a transaction rolled back?

Question

The SQLAlchemy CORE doc for transactions suggests using a with context manager as follows:

# runs a transaction
with engine.begin() as connection:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')

or

with connection.begin() as trans:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')

Either way, how do I know if a transaction was executed and committed, or if it was rolled back?

Answer 1

If it doesn't raise, it committed. If you look at the docs, you'll note that the with-statement is more or less equivalent to:

connection = engine.connect()
trans = connection.begin()
try:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')
    trans.commit()
except:
    trans.rollback()
    raise

Regarding the comment: a with-statement is not, necessarily, a replacement for try/except in case you need exception handling – such as when you want to know if the transaction rolled back or not.

If you have to perform additional cleanup or for example logging when a transaction rolls back, you'll still have to wrap the with-statement in a try/except, but you can be sure that the transaction has been dealt with before control passes from the block governed by the with-statement:

try:
    with ...:
        ...

except ...:
    # rolled back

else:
    # committed

You could also opt to reraise the error so that other parts can also handle their cleanup. Of course for example logging could be handled by another context manager as well:

from contextlib import contextmanager

@contextmanager
def logger(log, error_msg="Oh woe!"):
    try:
        yield

    except:
        log.exception(error_msg)
        raise

...

with logger(log), connection.begin():
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')

In this case as you've noted in the comment a try/except has been eliminated, or hidden, by using a with-statement, in the spirit of PEP 343:

This PEP adds a new statement "with" to the Python language to make it possible to factor out standard uses of try/finally statements.