我们尝试将包含多个插入语句的SQL文件作为单个查询运行,但是当任何语句包含错误时,似乎回滚失败.

sql_mode = STRICT_ALL_TABLES

default-storage-engine = innodb

Python代码：

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

预期结果：“抛出异常”和“回滚”两次打印.

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

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

是什么赋予了？我们是否真的必须解析SQL以拆分语句(包含所有需要的转义和引用处理)以在多次执行中运行它们？