MySQL Connector/Python Developer Guide
The MySQLCursorPrepared
class inherits from
MySQLCursor
.
This class is available as of Connector/Python 1.1.0. The C extension supports it as of Connector/Python 8.0.17.
In MySQL, there are two ways to execute a prepared statement:
Use the binary client/server protocol to send and receive
data. To repeatedly execute the same statement with
different data for different executions, this is more
efficient than using PREPARE
and EXECUTE
. For information
about the binary protocol, see
C API Prepared Statements.
In Connector/Python, there are two ways to create a cursor that enables
execution of prepared statements using the binary protocol. In
both cases, the cursor()
method of the
connection object returns a
MySQLCursorPrepared
object:
The simpler syntax uses a prepared=True
argument to the cursor()
method. This
syntax is available as of Connector/Python 1.1.2.
import mysql.connector cnx = mysql.connector.connect(database='employees') cursor = cnx.cursor(prepared=True)
Alternatively, create an instance of the
MySQLCursorPrepared
class using the
cursor_class
argument to the
cursor()
method. This syntax is available
as of Connector/Python 1.1.0.
import mysql.connector from mysql.connector.cursor import MySQLCursorPrepared cnx = mysql.connector.connect(database='employees') cursor = cnx.cursor(cursor_class=MySQLCursorPrepared)
A cursor instantiated from the
MySQLCursorPrepared
class works like this:
The first time you pass a statement to the cursor's
execute()
method, it prepares the
statement. For subsequent invocations of
execute()
, the preparation phase is
skipped if the statement is the same.
The execute()
method takes an optional
second argument containing a list of data values to
associate with parameter markers in the statement. If the
list argument is present, there must be one value per
parameter marker.
Example:
cursor = cnx.cursor(prepared=True) stmt = "SELECT fullname FROM employees WHERE id = %s" # (1) cursor.execute(stmt, (5,)) # (2) # ... fetch data ... cursor.execute(stmt, (10,)) # (3) # ... fetch data ...
The %s
within the statement is a
parameter marker. Do not put quote marks around parameter
markers.
For the first call to the execute()
method, the cursor prepares the statement. If data is given
in the same call, it also executes the statement and you
should fetch the data.
For subsequent execute()
calls that pass
the same SQL statement, the cursor skips the preparation
phase.
Prepared statements executed with
MySQLCursorPrepared
can use the
format
(%s
) or
qmark
(?
) parameterization
style. This differs from nonprepared statements executed with
MySQLCursor
, which can use the
format
or pyformat
parameterization style.
To use multiple prepared statements simultaneously, instantiate
multiple cursors from the MySQLCursorPrepared
class.
The MySQL client/server protocol has an option to send prepared
statement parameters via the
COM_STMT_SEND_LONG_DATA
command. To use this
from Connector/Python scripts, send the parameter in question using the
IOBase
interface. Example:
from io import IOBase ... cur = cnx.cursor(prepared=True) cur.execute("SELECT (%s)", (io.BytesIO(bytes("A", "latin1")), ))