[Python] Can I execute query via sqlalchemy without transaction

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.

Continue reading...