MySQLdb.cursor.execute无法运行多个查询 [英] MySQLdb.cursor.execute can't run multiple queries

问题描述

我们正在尝试将包含多个插入语句的SQL文件作为一个查询运行，但是当任何一条语句包含错误时，rollback似乎都会失败.

We're trying to run SQL files containing multiple insert statements as a single query, but it seems rollback fails when any of the statements contain an error.

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

预期结果:抛出异常"和回滚"打印两次.

Expected result: "Exception thrown" and "Rolled back" printed twice.

MySQL-python 1.2.4的实际结果:

Actual result with 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")

有什么作用?我们真的需要解析SQL来拆分语句(包含所有转义和引号处理)，以便在多个execute s中运行它们吗?

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?

推荐答案

显然，在

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.cursor.execute无法运行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！