How do I revert / undo a `session.execute()` statement in SQLAlchemy

Question

If I would like to undo a previous update statement executed with session.execute(my_update_sql) statement on a SQLAlchemy session, how do I go about it?

I am using SQLAlchemy, Zope in a Pyramid web application

Answer 1

Provided that you use a "real" DBMS with transactions, you should be able to roll back the current transaction, just as you would do with other operations involving session:

session.execute('DELETE FROM users')  # feel the thrill!
session.rollback()

A typical setup of SQLAlchemy with Pyramid involves ZopeTransactionExtension, which integrates session management with Pyramid's request-response cycle. In this case, to roll back the transaction you'll need to do that using ZTE's transaction manager:

import transaction
transaction.rollback()

Note that rolling back the transaction will undo ALL changes made in the transaction, not only your session.execute() statement. If you only want to "undo" just one statement you can try using SQLAlchemy's nested transactions, support for which depends on the DBMS you're using,