Rollback the transaction if any query fails

Question

I'm trying to execute multiple update SQL queries in a list. And if any of the query fails at any point, I want to rollback all previous query updations.

import sqlalchemy as sa
from sqlalchemy.orm import Session

engine = sa.create_engine(r'mssql+pyodbc://<IP>\SQLEXPRESS/<db>?driver=SQL+Server+Native+Client+11.0'
queries = ['query1', 'query2', 'query3', '......'] #Query List
# ToDo: if any query execution from the list fails, rollback all the previous query execution

Please help. I already tried multiple answers from SO. But couldn't solve it.

Answer 1

You can use a context manager:

with Session(engine) as sess, sess.begin():
    # sess.execute() one or more queries

The context manager will commit the changes if there were no errors, otherwise it will do a rollback.