使用Python的MySQL未读结果 [英] MySQL Unread Result with Python

问题描述

我使用mysql.connector进行SQL操作. 我有一个简短的脚本，该脚本使用cursor.execute(...)在光标上执行以下操作(字符串):

I use mysql.connector to do SQL operations. I have a short scripts which executes the following operations (strings) on the cursor with cursor.execute(...):

"use {}".format(db)

"show tables"

command = """
ALTER TABLE Object DROP PRIMARY KEY;
ALTER TABLE Object ADD `id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE Object ADD INDEX (`uid`);"""

脚本在多个数据库db上进行迭代.

The script iterates over several databases db.

问题是，在某些时候我收到发现未读结果"错误.看来，当我运行脚本时，在某个时候我的mydb"会返回一个结果(cursor._have_result = True)，而我没有想到这一点.奇怪的是，如果我重新运行完整的脚本，它将运行更长的时间，并且会有更多的数据库在以后给出相同的错误.

The problem is that at some point I get an "Unread result found" error. It seems when I run the script, at some point "use mydb" returns a result (cursor._have_result=True), when I didn't expect one. The weird thing is that if I rerun the full script it runs a little longer with more databases giving the same error later.

您能提出解决或调查此问题的方法吗?我有什么办法可以防止未读结果"?

Can you suggest a way to solve or investigate this problem? Is there something I can do to prevent "unread results"?

PS:当我重新运行脚本时，对于已经完成的数据库，ALTER命令失败.不确定是否会引起问题.

PS: When I rerun the script the ALTER commands fails for the databases which are already done. Not sure if that causes problems.

推荐答案

使用MySQL Connector/Python，当您在其他地方使用连接对象而不读取结果时，可能会发生未读结果.这不是一个可以解决的问题.您可以使用 buffered 选项立即读取结果.

Using MySQL Connector/Python, the Unread results found might happen when you use the connection object in different places without reading the result. It's not something one can go around. You can use the buffered option to read result immediately.

如评论中所述，最好拆分语句并分别执行.

As mentioned in the comments, it's best to split the statements and execute them separately.

如果要执行多个语句，则需要使用 multi = True 选项，用于MySQLCursor.execute()方法(自Connector/Python v1.0.4起).实际上，如果您不使用 multi 选项并发送多个语句，则会引发InterfaceError. (我也怀疑这里有一个错误.)

If you want to execute multiple statements, you'll need to use the multi=True option for the MySQLCursor.execute() method (since Connector/Python v1.0.4). Actually, if you don't use the multi option and send multiple statements, an InterfaceError will raise. (I do suspect a bug here as well..)

其他说明:

• 代替执行USE命令更改数据库，您可以

• Instead of executing the USE-command to change databases, you can MySQLConnection.database property.

• You best group the changes into one ALTER TABLE statement, like this:

ALTER TABLE t1 DROP PRIMARY KEY，添加ID INT非空AUTO_INCREMENT KEY FIRST，添加INDEX(c1)

ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT KEY FIRST, ADD INDEX(c1)

这篇关于使用Python的MySQL未读结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！