MySQLdb.cursor.execute can't run multiple queries
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.
I think you need to pass multi=True
to execute
when using multiple statements, see http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html
Update: This applies to the mysql.connector
module, not MySQLdb
used in this case.
Apparently there is no way to do this in MySQLdb
(aka. MySQL-python
), so we ended up just communicate
ing the data to subprocess.Popen([mysql, ...], stdin=subprocess.PIPE)
and checking the returncode
.