我们尝试将包含多个插入语句的SQL文件作为单个查询运行,但是rollback
当任何语句包含错误时,它似乎都失败了.
MySQLd配置:
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, inprint("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以拆分语句(包含所有需要的转义和引用处理)以在多个execute
s中运行它们?