MySQLdb.cursor.execute не может запускать несколько запросов

Мы пытаемся запустить 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, 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, чтобы разделить операторы (со всеми обработками escape и quote, которые влекут), чтобы запустить их в нескольких execute s?

Ответ 1

По-видимому, нет способа сделать это в MySQLdb (aka. MySQL-python), поэтому мы закончили просто communicate данных до subprocess.Popen([mysql, ...], stdin=subprocess.PIPE) и проверки returncode.

Ответ 2

Как и во всех реализациях Python DB-API 2.0, метод cursor.execute() разработан с использованием только одного оператора, потому что он дает гарантии о состоянии курсора впоследствии.

cursor.executemany() используйте метод cursor.executemany(). Обратите внимание, что в соответствии со спецификацией DB-API 2.0:

Использование этого метода для операции, которая создает один или несколько наборов результатов, представляет собой неопределенное поведение, и реализации разрешается (но не требуется) вызывать исключение, когда он обнаруживает, что набор результатов был создан при вызове операции.

Использование этого для нескольких операторов INSERT должно быть просто прекрасно:

cursor.executemany('INSERT INTO table_name VALUES (%s)',
    [(1,), ("non-integer value",)]
)

Если вам нужно выполнить ряд разрозненных операторов, например, из скрипта, то в большинстве случаев вы можете просто разделить операторы ; и cursor.execute() каждый оператор к cursor.execute() отдельно.

Ответ 3

Я думаю, вам нужно передать multi=True в execute при использовании нескольких операторов, см. http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

Обновление: Это относится к модулю mysql.connector, а не к MySQLdb, используемому в этом случае.

Ответ 4

Попробовал метод multi=True, но в итоге разделил файл на полу и перебрал. Очевидно, что не сработает, если вы избежали полуфинала, но мне показалось, что это лучший способ.

with connection.cursor() as cursor:
    for statement in script.split(';'):
        if len(statement) > 0:
             cursor.execute(statement + ';')

Ответ 5

Использование программы mysql через Popen, безусловно, будет работать, но если вы хотите просто использовать существующее соединение (и курсор), пакет sqlparse имеет функцию split, которая будет разделяться на операторы. Я не уверен, что такое совместимость, но у меня есть script, который делает:

with open('file.sql', 'rb') as f:
    for statement in sqlparse.split(f.read()):
        if not statement:
            continue
        cur.execute(statement)

Он только когда-либо подавал команды DROP TABLE и CREATE TABLE, но работает для меня.

https://pypi.python.org/pypi/sqlparse

Ответ 6

используйте следующую позицию для выполнения оператора:

для _ в cursor.execute(query, multi = True): проход