python mysql.connector DictCursor?

Question

In Python mysqldb I could declare a cursor as a dictionary cursor like this:

cursor = db.cursor(MySQLdb.cursors.DictCursor) 

This would enable me to reference columns in the cursor loop by name like this:

for row in cursor:   # Using the cursor as iterator 
    city = row["city"]
    state = row["state"]

Is it possible to create a dictionary cursor using this MySQL connector? http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

Their example only returns a tuple.

I imagine the creators of MySQL would eventually do this for us?

Answer 1

According to this article it is available by passing in 'dictionary=True' to the cursor constructor: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

so I tried:

cnx = mysql.connector.connect(database='bananas')
cursor = cnx.cursor(dictionary=True)

and got: TypeError: cursor() got an unexpected keyword argument 'dictionary'

and I tried:

cnx = mysql.connector.connect(database='bananas')
cursor = cnx.cursor(named_tuple=True)

and got: TypeError: cursor() got an unexpected keyword argument 'named_tuple'

and I tried this one too: cursor = MySQLCursorDict(cnx)

but to no avail. Clearly I'm on the wrong version here and I suspect we just have to be patient as the document at http://downloads.mysql.com/docs/connector-python-relnotes-en.a4.pdf suggests these new features are in alpha phase at point of writing.

Answer 2

This example works:

cnx = mysql.connector.connect(database='world')
cursor = cnx.cursor(dictionary=True)
cursor.execute("SELECT * FROM country WHERE Continent = 'Europe'")

print("Countries in Europe:")
for row in cursor:
    print("* {Name}".format(Name=row['Name']

Keep in mind that in this example, 'Name' is specific to the column name of the database being referenced.

Also, if you want to use stored procedures, do this instead:

cursor.callproc(stored_procedure_name, args)
result = []
for recordset in cursor.stored_results():
    for row in recordset:
        result.append(dict(zip(recordset.column_names,row)))

where stored_procedure_name is the name of the stored procedure to use and args is the list of arguments for that stored procedure (leave this field empty like [] if no arguments to pass in).

This is an example from the MySQL documentation found here: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

Answer 3

Using Python 3.6.2 and MySQLdb version 1.3.10, I got this to work with:

import MySQLdb
import MySQLdb.cursors

...

conn = MySQLdb.connect(host='...', 
                       <connection info>, 
                       cursorclass=MySQLdb.cursors.DictCursor)

try:
    with conn.cursor() as cursor:
        query = '<SQL>'
        data = cursor.fetchall()
        for record in data:
            ... record['<field name>'] ...

finally:
    conn.close()

I'm using PyCharm, and simply dug into the MySQLdb modules connections.py and cursors.py.

Answer 4

I had the same problem with the default cursor returning tuples with no column names.

The answer is here:

Getting error while using MySQLdb.cursors.DictCursor in MYSQL_CURSORCLASS

app.config["MYSQL_CURSORCLASS"] = "DictCursor"