Documentation Home
MySQL Connector/Python Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb
EPUB - 137.6Kb
HTML Download (TGZ) - 85.2Kb
HTML Download (Zip) - 103.1Kb


10.6.8 cursor.MySQLCursorPrepared Class

The MySQLCursorPrepared class inherits from MySQLCursor. This class is available as of Connector/Python 1.1.0.

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


User Comments
Sign Up Login You must be logged in to post a comment.