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: docs.sqlalchemy.org/en/14/core/… — SuperShoot
– SuperShoot, Commented Apr 16, 2021 at 9:50

user2876375 · Accepted Answer · 2021-04-17 01:54:56Z

1

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")

answered Apr 17, 2021 at 1:54

user2876375

3392 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

technogeek1995 Over a year ago

You may need to use connection.commit() or connection.rollback() (if it fails) at the bottom of the context manager

Collectives™ on Stack Overflow

How to run multiple queries in sqlalchemy as atomic operation?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related