Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 executes?

like image 259
l0b0 Avatar asked Dec 11 '13 12:12

l0b0


People also ask

Can a cursor execute multiple queries?

Executing multiple queries with execute() By default, it is set to False . If set to True , allows execute() to execute multiple queries separated by semicolons. When called with multi=True , the execute() method returns an iterator which can be used to access the result set produced by the queries.

What does cursor Fetchall () method do?

fetchall() Method. The method fetches all (or all remaining) rows of a query result set and returns a list of tuples. If no more rows are available, it returns an empty list.

What is Pymysql cursor?

class pymysql.cursors. Cursor (connection) This is the object used to interact with the database. Do not create an instance of a Cursor yourself.


1 Answers

Like all Python DB-API 2.0 implementations, the cursor.execute() method is designed take only one statement, because it makes guarantees about the state of the cursor afterward.

Use the cursor.executemany() method instead. Do note that, as per the DB-API 2.0 specification:

Use of this method for an operation which produces one or more result sets constitutes undefined behavior, and the implementation is permitted (but not required) to raise an exception when it detects that a result set has been created by an invocation of the operation.

Using this for multiple INSERT statements should be just fine:

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

If you need to execute a series of disparate statements like from a script, then for most cases you can just split the statements on ; and feed each statement to cursor.execute() separately.

like image 114
Martijn Pieters Avatar answered Oct 18 '22 17:10

Martijn Pieters