I get NotImplementedError when trying to do a prepared statement with mysql python connector

Question

I want to use prepared statements to insert data into a MySQL DB (version 5.7) using python, but I keep getting a NotImplementedError. I'm following the documentation here: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html

Using Python 2.7 and version 8.0.11 of mysql-connector-python library:

pip show mysql-connector-python
---
Metadata-Version: 2.1
Name: mysql-connector-python
Version: 8.0.11
Summary: MySQL driver written in Python
Home-page: http://dev.mysql.com/doc/connector-python/en/index.html

This is a cleaned version (no specific hostname, username, password, columns, or tables) of the python script I'm running:

import mysql.connector
from mysql.connector.cursor import MySQLCursorPrepared

connection = mysql.connector.connect(user=username, password=password,
                                      host='sql_server_host',
                                      database='dbname')
print('Connected! getting cursor')
cursor = connection.cursor(cursor_class=MySQLCursorPrepared)
select = "SELECT * FROM table_name WHERE column1 = ?"
param = 'param1'
print('Executing statement')
cursor.execute(select, (param,))
rows = cursor.fetchall()
for row in rows:
    value = row.column1
print('value: '+ value)

I get this error when I run this:

Traceback (most recent call last):
  File "test.py", line 18, in <module>
    cursor.execute(select, (param,))
  File "/home/user/.local/lib/python2.7/site-packages/mysql/connector/cursor.py", line 1186, in execute
    self._prepared = self._connection.cmd_stmt_prepare(operation)
  File "/home/user/.local/lib/python2.7/site-packages/mysql/connector/abstracts.py", line 969, in cmd_stmt_prepare
    raise NotImplementedError
NotImplementedError

Macnamara

Answer 1

CEXT will be enabled by default if you have it, and prepared statements are not supported in CEXT at the time of writing.

You can disable the use of CEXT when you connect by adding the keyword argument use_pure=True as follows:

connection = mysql.connector.connect(user=username, password=password,
                                     host='sql_server_host',
                                     database='dbname',
                                     use_pure=True)

Support for prepared statements in CEXT will be included in the upcoming mysql-connector-python 8.0.17 release (according to the MySQL bug report). So once that is available, upgrade to at least 8.0.17 to solve this without needing use_pure=True.

Clinton

Thanks! What an obscure thing. They really should mention it in their documentation on the prepared statements page. – Macnamara