避免MySQLdb的“命令不同步"的方法；您不能立即运行此命令" (2014)例外 [英] Ways to avoid MySQLdb's "Commands out of sync; you can't run this command now" (2014) exception

问题描述

以下代码(使用python 2.6.6和MySQLdb 1.2.2)导致命令不同步；您不能立即运行此命令 MySQLdb异常:

Following code, using python 2.6.6 and MySQLdb 1.2.2 causes Commands out of sync; you can't run this command now MySQLdb exception:

import MySQLdb

conn = MySQLdb.connect( db="test", user="root" )
cursor = conn.cursor( MySQLdb.cursors.DictCursor )

cursor.execute( "BEGIN; CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT ); COMMIT;" )
cursor.execute( "BEGIN; CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT ); COMMIT;" )

在执行第二个查询期间引发异常.正如我所读到的，该异常通常是由于MySQL的C API实现的局限性导致的，不允许执行并发查询.

The exception is raised during execution of the second query. As I read, the exception is generally caused by limitations of MySQL's C API implementation, which disallow concurrent query execution.

如果我在上述两个查询之间重新创建了游标对象，则该问题已解决，但不幸的是，该解决方案对我而言似乎并不完美.对于数据库连接和查询执行，我有一个非常简单的介绍，并且不希望在每次查询执行后都不要重新创建游标，因为它将(据我所知)会提交当前事务并可能产生其他副作用.

If I recreate cursor object between above two queries, the problem is worked around but unfortunatelly the solution doesn't seem perfect to me. I have a very simple abstration over database connection and query execution and would prefer not to recreate the cursor after each query execution as it will (as far as I understand it) commit the current transaction and potentially have other side effects.

因此，我的问题是:避免此异常的其他方法有哪些?如何准备游标对象以执行下一个查询?也许Python DB API期望有某种方法，当使用其他数据库接口时该方法相对中性，并且在MySQLdb的情况下可以解决该问题?

Therefore, my question is: What are other ways of avoiding this exception? How to prepare the cursor object for execution of next query? Maybe there is some method expected by the Python DB API, which would be relatively neutral when using other database interfaces and will work around the problem in case of MySQLdb?

提前感谢您的时间和帮助:)

Thanks in advance for your time and help :)

发布问题后，我开始通读Python DB API规范，以了解游标破坏的副作用(我不太确定事务提交是否足够:))，然后发现以下替代解决方法:

After I posted the question, I started to read through the Python DB API spec to read about side effects of cursor destruction (I am not so sure about transaction commit anymore :)) and I found following, alternative work around:

cursor.execute( "BEGIN; CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT ); COMMIT;" )
while cursor.nextset() is not None: pass
cursor.execute( "BEGIN; CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT );

问题是我不知道它是做什么的(它两次返回1，然后返回None).我应该深入研究这个方向吗?我的意思是，我应该了解这些集合的概念以找到解决问题的方法吗?

The problem is that I don't know what does it do (it returns 1 two times and None after that). Should I dig into this direction? I mean, should I understand concept of these sets to find solution to my problem?

推荐答案

DB-API尝试自行处理事务，在第一个命令上启动事务，并具有自己的API调用来提交它，因此:

DB-API tries to handle transactions on its own, starting a transaction on the first command and having its own API call to commit it, so:

cursor.execute( "CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT )" )
cursor.commit()
cursor.execute( "CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT )" )
cursor.commit()

在我看来，这是Python DB-API的一个严重的，明显的设计错误，这使得在事务之外执行命令并对事务进行适当控制成为了一个严重的麻烦.使用SQLite的BEGIN EXCLUSIVE TRANSACTION之类的东西.就像没有真正数据库经验的人被允许设计API一样.

In my opinion, this is a serious, glaring design error of Python's DB-API, making it a serious hassle to execute commands outside of transactions and to have proper control over transactions, eg. to use things like SQLite's BEGIN EXCLUSIVE TRANSACTION. It's as if someone with no real database experience was allowed to design the API...

这篇关于避免MySQLdb的“命令不同步"的方法；您不能立即运行此命令" (2014)例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！