我可以通过sqlalchemy执行查询而无需事务 [英] Can I execute query via sqlalchemy without transaction

问题描述

我正在尝试使用sqlalchemy在Mysql数据库上执行存储过程.

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

它在shell上运行良好，但抛出此错误:

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")

看起来原因是SQLAlchemy在事务中运行查询.并且存储过程中的事务与此冲突.下面是sqlalchemy日志:

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 ()

我想知道的是，是否可以在不进行事务的情况下从sqlalchemy运行查询.还是有其他方法可以解决问题.我尝试更改存储过程的隔离级别，但这导致了表锁定问题.

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.

推荐答案

SQLAlchemy始终尝试在事务内部执行查询.但是，只需执行 COMMIT ，就可以轻松结束交易.a>声明.

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

首先，您需要一个连接.然后使用该连接发出 COMMIT ，这将结束新启动的事务.

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

这是尝试创建新数据库的示例代码，在事务内部运行时将引发错误.我正在使用postgres，但是使用MySQL的相同逻辑也是适用的.尝试在事务内创建新数据库:

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")

会抛出错误:

错误:CREATE DATABASE无法在事务块内运行

现在，添加 COMMIT 将结束由sqlalchemy启动的事务:

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.

这篇关于我可以通过sqlalchemy执行查询而无需事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！