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

• Use the PREPARE and EXECUTE statements.

• 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 Statement Interface.

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

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