What is the best way to control autocommit behaviour with SQLAlchemy 2+?

Question

We have a generic class that we use for opening DB connections in SQL Alchemy. The engine is then returned to the calling code. The generic class is below. It has been simplified for this question.

class Database(db_name, autocommit=True):
    get_parameters()
    connection_string = (conf.connection_string + f"?autocommit={str(autocommit).lower()}")
    engine = create_engine(connection_string, echo=conf.echo)
    return engine

This will result in a connection string that looks something like mysql+pymysql://<dummy_username>:<dummy_password>@<db_server>:3306/<db_schema>?autocommit=true

The calling code then calls this function to retrieve the engine and uses the engine to create the session, etc, as shown in the code below

engine = Database(Databases.GUDRUN, autocommit=False)
session = session_maker(bind=engine.engine)
with session() as session:
    try:
        code here
        session.commit()
    except Exception as e:
        session.rollback()

This worked and allowed us to set the autocommit value to True by default but then allow a user to override the autocommit value if you needed to include transactions for a particular use case.

However, now when using SQL Alchemy 2.0.23 we noticed that setting the autocommit=False was no longer working. Regardless of the autocommit value passed into the connection string within the Database class, the database committed each command when entered rather than waiting for the session.commit() function.

It turns out that if we exclude the autocommit completely from the connection string (i.e. mysql+pymysql://<dummy_username>:<dummy_password>@<db_server>:3306/<db_schema> the connection would then wait for the session.commit() before committing.

I can see that the ?autocommit option within the connection string has been deprecated, so I understand why it is no longer working. But it looks like if I include autocommit at all in the connection string, regardless of the value if acts as if the auto commit has been set to true.

But what is the best way now to handle autocommit? We want it set up so that the default option will be autocommit=True and the developer must make a conscious decision to have a transaction within the code to avoid the autocommit = true.

Answer 1

We want it set up so that the default option will be autocommit=True and the developer must make a conscious decision to have a transaction within the code

In that case, specify isolation_level="AUTOCOMMIT" when you create the engine, then use .execution_options() when you want to bypass autocommit:

import sqlalchemy as sa

engine = sa.create_engine(
    "mysql+pymysql://scott:tiger@localhost:3307/mydb",
    isolation_level="AUTOCOMMIT",
    echo=True,
)

tbl = sa.Table("so77502001", sa.MetaData(), autoload_with=engine)

# typical usage: `with engine.begin()`
with engine.begin() as conn:
    conn.execute(sa.insert(tbl), {"txt": "autocommit me"})
"""
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[generated in 0.00042s] {'txt': 'autocommit me'}
COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode
"""

# we get the same results using `with engine.connect()`
with engine.connect() as conn:
    conn.execute(sa.insert(tbl), {"txt": "autocommit me, too"})
"""
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.09482s ago] {'txt': 'autocommit me, too'}
ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to autocommit mode
"""

# "turn off" autocommit for this action
with engine.connect() as conn, conn.execution_options(
    isolation_level="READ COMMITTED"
):
    conn.execute(sa.insert(tbl), {"txt": "explicitly commit me"})
    conn.commit()
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.1856s ago] {'txt': 'explicitly commit me'}
COMMIT
"""

# same thing but with rollback
with engine.connect() as conn, conn.execution_options(
    isolation_level="READ COMMITTED"
):
    conn.execute(sa.insert(tbl), {"txt": "don't commit me"})
    conn.rollback()
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.2973s ago] {'txt': "don't commit me"}
ROLLBACK
"""

We can do the same thing with a Session:

with Session(engine) as sess:
    sess.add(Thing(txt="autocommit me"))
    sess.flush()
"""
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[generated in 0.00046s] {'txt': 'autocommit me'}
ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to autocommit mode
"""

with Session(engine) as sess, sess.connection(
    execution_options={"isolation_level": "READ COMMITTED"}
):
    sess.add(Thing(txt="commit required"))
    sess.commit()  # automatically flushes
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.09807s ago] {'txt': 'commit required'}
COMMIT
"""

with Session(engine) as sess, sess.connection(
    execution_options={"isolation_level": "READ COMMITTED"}
):
    sess.add(Thing(txt="no commit -> not persisted"))
    sess.flush()
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.1569s ago] {'txt': 'no commit -> not persisted'}
ROLLBACK
"""