MySQL Connector/Python Developer Guide

10.6.8 cursor.MySQLCursorPrepared Class

The MySQLCursorPrepared class inherits from MySQLCursor.

Note

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:

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:

A cursor instantiated from the MySQLCursorPrepared class works like this:

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 ...
  1. The %s within the statement is a parameter marker. Do not put quote marks around parameter markers.

  2. 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.

  3. 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")), ))