Python MySQLdb.cursor.execute 不能运行多个查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20518677/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQLdb.cursor.execute can't run multiple queries
提问by l0b0
We're trying to run SQL files containing multiple insert statements as a single query, but it seems rollbackfails when any of the statements contain an error.
我们试图将包含多个插入语句的 SQL 文件作为单个查询运行，但是rollback当任何语句包含错误时，它似乎都失败了。
MySQLd configuration:
MySQLd 配置：
sql_mode = STRICT_ALL_TABLES
default-storage-engine = innodb
Python code:
蟒蛇代码：
from contextlib import closing
import MySQLdb
database_connection = MySQLdb.connect(host="127.0.0.1", user="root")
with closing(database_connection.cursor()) as cursor:
database_connection.begin()
cursor.execute('DROP DATABASE IF EXISTS db_name')
cursor.execute('CREATE DATABASE db_name')
cursor.execute('USE db_name')
cursor.execute('CREATE TABLE table_name(first_field INTEGER)')
with closing(database_connection.cursor()) as cursor:
try:
database_connection.begin()
cursor.execute('USE db_name')
cursor.execute('INSERT INTO table_name VALUES (1)')
cursor.execute('INSERT INTO table_name VALUES ("non-integer value")')
database_connection.commit()
except Exception as error:
print("Exception thrown: {0}".format(error))
database_connection.rollback()
print("Rolled back")
with closing(database_connection.cursor()) as cursor:
try:
database_connection.begin()
cursor.execute('USE db_name')
cursor.execute('INSERT INTO table_name VALUES (1); INSERT INTO table_name VALUES ("non-integer value")')
database_connection.commit()
except:
print("Exception thrown: {0}".format(error))
database_connection.rollback()
print("Rolled back")
Expected result: "Exception thrown" and "Rolled back" printed twice.
预期结果：“抛出异常”和“回滚”打印两次。
Actual result with MySQL-python 1.2.4:
MySQL-python 1.2.4 的实际结果：
Exception thrown: (1366, "Incorrect integer value: 'non-integer value' for column 'first_field' at row 1")
Rolled back
Exception thrown: (1366, "Incorrect integer value: 'non-integer value' for column 'first_field' at row 1")
Traceback (most recent call last):
File "test.py", line 30, in <module>
print("Rolled back")
File ".../python-2.7/lib/python2.7/contextlib.py", line 154, in __exit__
self.thing.close()
File ".../virtualenv-python-2.7/lib/python2.7/site-packages/MySQLdb/cursors.py", line 100, in close
while self.nextset(): pass
File ".../virtualenv-python-2.7/lib/python2.7/site-packages/MySQLdb/cursors.py", line 132, in nextset
nr = db.next_result()
_mysql_exceptions.OperationalError: (1366, "Incorrect integer value: 'non-integer value' for column 'first_field' at row 1")
What gives? Do we really have to parse the SQL to split up statements (with all the escape and quote handling that entails) to run them in multiple executes?
是什么赋予了？我们真的必须解析 SQL 来拆分语句（包括所有需要的转义和引号处理）以在多个executes 中运行它们吗？
采纳答案by l0b0
Apparently there is no way to do this in MySQLdb(aka. MySQL-python), so we ended up just communicateing the data to subprocess.Popen([mysql, ...], stdin=subprocess.PIPE)and checking the returncode.
显然没有办法在MySQLdb（又名。MySQL-python）中做到这一点，所以我们最终只是communicate将数据放入subprocess.Popen([mysql, ...], stdin=subprocess.PIPE)并检查returncode.
回答by Johannes
I think you need to pass multi=Trueto executewhen using multiple statements, see http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html
我认为您在使用多个语句时需要传递multi=True给execute，请参阅http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html
Update:This applies to the mysql.connectormodule, not MySQLdbused in this case.
更新：这适用于mysql.connector模块，MySQLdb在这种情况下不使用。
回答by Martijn Pieters
Like all Python DB-API 2.0 implementations, the cursor.execute()method is designed take only onestatement, because it makes guarantees about the state of the cursor afterward.
与所有Python DB-API 2.0 实现一样，该cursor.execute()方法设计为仅使用一条语句，因为它可以保证之后游标的状态。
Use the cursor.executemany()methodinstead. Do note that, as per the DB-API 2.0 specification:
请改用该cursor.executemany()方法。请注意，根据 DB-API 2.0 规范：
Use of this method for an operation which produces one or more result sets constitutes undefined behavior, and the implementation is permitted (but not required) to raise an exception when it detects that a result set has been created by an invocation of the operation.
将此方法用于产生一个或多个结果集的操作构成未定义的行为，并且允许（但不是必需）实现在检测到已通过调用操作创建结果集时引发异常。
Using this for multiple INSERTstatements should be just fine:
将此用于多个INSERT语句应该没问题：
cursor.executemany('INSERT INTO table_name VALUES (%s)',
[(1,), ("non-integer value",)]
)
If you need to execute a series of disparate statements like from a script, then for most cases you can just split the statements on ;and feed each statement to cursor.execute()separately.
如果您需要从脚本中执行一系列不同的语句，那么在大多数情况下，您只需拆分语句;并分别提供每个语句即可cursor.execute()。
回答by morganwahl
Using the mysqlprogram via Popen will definitely work, but if you want to just use an existing connection (and cursor), the sqlparsepackage has a splitfunction that will split into statements. I'm not sure what the compatiblity is like, but I have a script that does:
mysql通过 Popen使用该程序肯定会起作用，但是如果您只想使用现有的连接（和游标），则该sqlparse包具有一个split将拆分为语句的函数。我不确定兼容性是什么样的，但我有一个脚本可以：
with open('file.sql', 'rb') as f:
for statement in sqlparse.split(f.read()):
if not statement:
continue
cur.execute(statement)
It's only ever fed DROP TABLE and CREATE TABLE statements, but works for me.
它只提供 DROP TABLE 和 CREATE TABLE 语句，但对我有用。
回答by DavidSM
Tried the multi=Truemethod, but ended up splitting the file by semi and looping through. Obviously not going to work if you have escaped semis, but seemed like the best method for me.
尝试了该multi=True方法，但最终以半分割和循环方式分割文件。如果你已经逃脱了半决赛，显然不会起作用，但对我来说似乎是最好的方法。
with connection.cursor() as cursor:
for statement in script.split(';'):
if len(statement) > 0:
cursor.execute(statement + ';')
回答by abhishek
use below line item to execute statement :
使用下面的行项目来执行语句：
for _ in cursor.execute(query, multi=True): pass
for _ in cursor.execute(query, multi=True): pass
