how to set autocommit = 1 in a sqlalchemy.engine.Connection

Question

In sqlalchemy, I make the connection:

 conn = engine.connect()

I found this will set autocommit = 0 in my mysqld log. Now I want to set autocommit = 1 because I do not want to query in a transaction.

Is there a way to do this?

Answer 1

From The SQLAlchemy documentation: Understanding autocommit

conn = engine.connect()
conn.execute("INSERT INTO users VALUES (1, 'john')")  # autocommits

The “autocommit” feature is only in effect when no Transaction has otherwise been declared. This means the feature is not generally used with the ORM, as the Session object by default always maintains an ongoing Transaction.

Full control of the “autocommit” behavior is available using the generative Connection.execution_options() method provided on Connection, Engine, Executable, using the “autocommit” flag which will turn on or off the autocommit for the selected scope. For example, a text() construct representing a stored procedure that commits might use it so that a SELECT statement will issue a COMMIT:

engine.execute(text("SELECT my_mutating_procedure()").execution_options(autocommit=True))

Answer 2

What is your dialect for mysql connection?

You can set the autocommit to True to solve the problem, like this mysql+mysqldb://user:password@host:port/db?charset=foo&autocommit=true

Answer 3

You can use this:

from sqlalchemy.sql import text

engine = create_engine(host, user, password, dbname)
engine.execute(text(sql).execution_options(autocommit=True))

Answer 4

In case you're configuring sqlalchemy for a python application using flask / django, you can create the engine like this:

# Configure the SqlAlchemy part of the app instance
app.config['SQLALCHEMY_DATABASE_URI'] = conn_url


session_options = {
    'autocommit': True
}

# Create the SqlAlchemy db instance
db = SQLAlchemy(app, session_options=session_options)

Answer 5

I might be little late here, but for fox who is using sqlalchemy >= 2.0.*, above solution might not work as it did not work for me.

So, I went through the official documentation, and below solution worked for me.

from sqlalchemy import create_engine
db_engine = create_engine(database_uri, isolation_level="AUTOCOMMIT")

Above code works if you want to set autocommit at the engine level. But if you want use autocommit for a particular query then you can use below -

with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
    with connection.begin():
        connection.execute("<statement>")

Official Documentation

Answer 6

This can be done using the autocommit option in the execution_option() method:

engine.execute("UPDATE table SET field1 = 'test'").execution_options(autocommit=True))

This information is available within the documentation on Autocommit