Can I execute query via sqlalchemy without transaction

Question

I am trying to execute a stored procedure on Mysql database with sqlalchemy.

It runs fine from the shell but throws this error:

OperationalError: (MySQLdb._exceptions.OperationalError) (1568, "Transaction characteristics can't be changed while a transaction is in progress")

The reason as it seems is that SQLAlchemy runs query within a transaction. And the transaction within the stored procedure is conflicting with it. Below is sqlalchemy log:

2019-07-24 15:20:28,888 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-07-24 15:20:28,888 INFO sqlalchemy.engine.base.Engine ()
2019-07-24 15:20:28,900 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-07-24 15:20:28,900 INFO sqlalchemy.engine.base.Engine ()
2019-07-24 15:20:28,910 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-07-24 15:20:28,910 INFO sqlalchemy.engine.base.Engine ()
2019-07-24 15:20:28,916 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-07-24 15:20:28,917 INFO sqlalchemy.engine.base.Engine ()
2019-07-24 15:20:28,923 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-07-24 15:20:28,923 INFO sqlalchemy.engine.base.Engine ()
2019-07-24 15:20:28,928 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019-07-24 15:20:28,928 INFO sqlalchemy.engine.base.Engine ()
2019-07-24 15:20:28,938 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-07-24 15:20:28,938 INFO sqlalchemy.engine.base.Engine CALL my_stored_procedure(params);
2019-07-24 15:20:28,938 INFO sqlalchemy.engine.base.Engine ()

What I want to know is if I can run the query from sqlalchemy without transaction. Or is there any other way to solve the problem. I tried changing the isolation level of the stored procedure but that caused table lock issues.

Answer 1

SQLAlchemy always tries to execute queries inside transactions. However, one can easly end up a transaction by executing COMMIT statement.

First, you need a connection. Then emit a COMMIT using that connection, which will end up newly started transaction.

Here is a sample code that tries to create new database, which will throw an error when running inside transaction. I am using postgres, but same logic using MySQL is applicable. Trying to create new database inside transaction:

from sqlalchemy import create_engine

# replace URL with your MySQL instance
db_url = "postgresql://postgres:secure_pass@localhost:5432/template1"
engine = create_engine(db_url)

connection = engine.connect()

# running this query in transaction throws an error
result = connection.execute("CREATE DATABASE temp_db")

Will throw an error:

ERROR: CREATE DATABASE cannot run inside a transaction block

Now, adding COMMIT will end up a transaction started by sqlalchemy:

from sqlalchemy import create_engine

# replace url with your MySQL instance
db_url = "postgresql://postgres:secure_pass@localhost:5432/template1"
engine = create_engine(db_url)

connection = engine.connect()

# commiting will end a transaction
connection.execute("COMMIT")

# now this query runs fine
result = connection.execute("CREATE DATABASE temp_db")

And no errors are raised.