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?
SET AUTOCOMMIT sets the autocommit behavior of the current database session. By default, embedded SQL programs are not in autocommit mode, so COMMIT needs to be issued explicitly when desired. This command can change the session to autocommit mode, where each individual statement is committed implicitly.
By default, connection to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it.
The create_engine() method of sqlalchemy library takes in the connection URL and returns a sqlalchemy engine that references both a Dialect and a Pool, which together interpret the DBAPI's module functions as well as the behavior of the database.
When autocommit is set on, a commit occurs automatically after every statement, except PREPARE and DESCRIBE. If autocommit is on and a cursor is opened, the DBMS does not issue a commit until the CLOSE cursor statement is executed, because cursors are logically a single statement.
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)
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With