How to run multiple queries in sqlalchemy as atomic operation?

Question

My current solution involves using the execute() method on sqlalchemy each time when I want to run multiple queries. for eg.

connection.connect().execute("query1")
connection.connect().execute("query2")
connection.connect().execute("query3")

This will execute the queries one by one, however it is possible that "query2" execution failed but the "query1" would have ran. how to revert back to original state in case any intermediary queries fail. Like if "query1" and "query2" ran but "query3" failed, then how do I make it rollback to original state

You want to operate within a transaction. See: https://docs.sqlalchemy.org/en/14/core/connections.html#using-transactions — SuperShoot, Apr 16 '21 at 09:50

score 1 · Answer 1 · answered Apr 17 '21 at 01:54

As mentioned in the comments, the desired behavior can be achieved by placing your queries inside a transaction.

Read What is a database transaction? for more information. Here is a quote from the most voted answer in that question to help you get started:

A transaction is a unit of work that you want to treat as "a whole." It has to either happen in full or not at all.

See Using Transactions for details on how to implement this. Your code should look something like this:

# starts a transaction
with engine.begin() as connection:
    connection.execute("query1")
    connection.execute("query2")
    connection.execute("query3")

You may need to use `connection.commit()` or `connection.rollback()` (if it fails) at the bottom of the context manager — technogeek1995, Aug 07 '23 at 18:50

How to run multiple queries in sqlalchemy as atomic operation?

1 Answers1