Execute multiple independent statements in SQLAlchemy Core?

Question

I'm using SQLAlchemy Core to run a few independent statements. The statements are to separate tables and unrelated. Because of that I can't use the standard table.insert() with multiple dictionaries of params passed in. Right now, I'm doing this:

sql_conn.execute(query1)
sql_conn.execute(query2)

Is there any way I can run these in one shot instead of needing two back-and-forths to the db? I'm on MySQL 5.7 and Python 2.7.11.

Answer 1

Sounds like you want a Transaction:

with engine.connect() as sql_conn:
    with sql_conn.begin():
        sql_conn.execute(query1)
        sql_conn.execute(query2)

There is an implicit sql_conn.commit() above (when using the context manager) which commits the changes to the database in one trip. If you want to do it explicitly, it's done like this:

from sqlalchemy import create_engine
engine = create_engine("postgresql://scott:tiger@localhost/test")
connection = engine.connect()
trans = connection.begin()
connection.execute(text("insert into x (a, b) values (1, 2)"))
trans.commit()

https://docs.sqlalchemy.org/en/14/core/connections.html#basic-usage

While this is mostly geared towards creating real database transactions, it has a useful side effect for your use case, where it will maintain a "virtual" transaction in SQLAlchemy, see this link for more info:

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#session-level-vs-engine-level-transaction-control

The Session tracks the state of a single “virtual” transaction at a time, using an object called SessionTransaction. This object then makes use of the underlying Engine or engines to which the Session object is bound in order to start real connection-level transactions using the Connection object as needed.

This “virtual” transaction is created automatically when needed, or can alternatively be started using the Session.begin() method. To as great a degree as possible, Python context manager use is supported both at the level of creating Session objects as well as to maintain the scope of the SessionTransaction.

The above describes the ORM functionality but the link shows that it has parity with the Core functionality.