CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
Vivek gupta
Vivek gupta

Reputation: 15

How to run multiple queries in sqlalchemy as atomic operation?

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

Upvotes: 0

Views: 6608

Answers (1)

user2876375
user2876375

Reputation: 339

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

Upvotes: 1

Related Questions