l0b0 l0b0 - 29 days ago 6
MySQL Question

MySQLdb.cursor.execute can't run multiple queries

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 configuration:

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:

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
execute
s?

Answer

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.