Python MySql Connector prepared statement and dictionary class

Question

How do I make my cursor a prepared statement as well as a dictionary. I have the following.

 cursor(cursor_class = MySQLCursorPrepared)
 prints <class 'mysql.connector.cursor.MySQLCursorPrepared'>
 cursor(dictionary = True)
 prints <class 'mysql.connector.cursor.MySQLCursorDict'>

So I am overwriting the prepared statement with the dictionary. I am using the mysql connector for python3.

I have tried

 cursor(prepared=True,dictionary=True)

That errors with

Cursor not available with given criteria: dictionary, prepared

Interestingly

cursor(cursor_class = MySQLCursorPrepared,dictionary = True)

I get no errors but the data not of dictionary type.

Answer 1

I have the same problem. Prepared and Dictionary don't work together.

I reported bug: https://bugs.mysql.com/bug.php?id=92700

Now we wait ;)

Answer 2

Zipping cursor.column_names with the row values is a good workaround:

cursor = connection.cursor(prepared=True)
query = 'SELECT foo, bar FROM some_table WHERE a = %s AND b = %s'
a = 1
b = 42
cursor.execute(query, [a, b])
rows = cursor.fetchall()
for row in rows:
    result = dict(zip(cursor.column_names, row))
    print(result['foo'], result['bar'])

See https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-column-names.html for documentation on MySQLCursor.column_names.