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?

Did you ever get a good answer for this. If not, I'm about to write off Python3 and mySQL as not nearly ready for prime time. Seriously, F them for putting out this crap after 6+ years of Pythin 3. — user949300, Nov 25 '14 at 00:36
See jpatokal's comment below... You need Python/Connector v2.0.0+ to get MySQLCursorDict. You can check your version with mysql.connector.__version__. — panofish, Nov 25 '14 at 05:42
You should use the mysql.connector for Python. Then you can just use this example: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html . I also added a user comment at the end of that link that talks about how to implement this using stored procedures. — Blairg23, Dec 15 '14 at 19:50

score 36 · Answer 1 · edited May 08 '17 at 18:16

36

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.

edited May 08 '17 at 18:16

blues

4,547
3
23
39

answered Aug 05 '14 at 20:50

J1MF0X

689
1
6
4

great update... I figured it was only a matter of time. – panofish Aug 06 '14 at 04:15
4

You need Python/Connector v2.0.0+ to get MySQLCursorDict. You can check your version with `mysql.connector.__version__`. – lambshaanxy Oct 01 '14 at 07:18
Brilliant! I just updated to v2 and my custom MySQLCursorDict cursor_class stopped working by producing a list instead of a dict. This fixed my problem. Replacing cursor_class= with dictionary=True works in v2. – Karl M.W. Oct 25 '14 at 16:19
2

It's not the important problem, but it looks like you've got extra quotes in your code (e.g., it should be `.connect(database='bananas')`). – Joshua Taylor Dec 11 '14 at 20:25

panofish · Accepted Answer · 2015-04-15T15:21:17.577

A possible solution involves subclassing the MySQLCursor class like this:

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if row:
            return dict(zip(self.column_names, row))
        return None

db = mysql.connector.connect(user='root', database='test')

cursor = db.cursor(cursor_class=MySQLCursorDict)

Now the _row_to_python() method returns a dictionary instead of a tuple.

I found this on the mysql forum, and I believe it was posted by the mysql developers themselves. I hope they add it to the mysql connector package some day.

I tested this and it does work.

UPDATE: As mentioned below by Karl M.W... this subclass is no longer needed in v2 of the mysql.connector. The mysql.connector has been updated and now you can use the following option to enable a dictionary cursor.

cursor = db.cursor(dictionary=True)

I always used to use this method, but as of v2 you can use the native dict support: cursor = db.cursor(dictionary=True) — Karl M.W., Oct 25 '14 at 16:21
Yea, this still comes in handy since most distros still use v1.1 :/ — Klemen Tusar, Aug 18 '15 at 20:39

score 13 · Answer 3 · answered Dec 15 '14 at 19:55

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

Thank you! Took me forever to find this solution. The cursor ignores dictionary=True for callproc. — Eric Z Beard, Mar 09 '17 at 23:09

score 9 · Answer 4 · answered Sep 18 '17 at 17:56

9

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.

answered Sep 18 '17 at 17:56

KenH

500
5
4

This should be the chosen answer given the OP's question. [MySQLdb](http://mysql-python.sourceforge.net/MySQLdb.html) and [mySQL connector](https://dev.mysql.com/doc/connector-python/en/) are two different Python modules. – Caitlin Quintero Weaver Jan 10 '18 at 19:36
Seemed to work for me too. This post and a little or this one https://ianhowson.com/blog/a-quick-guide-to-using-mysql-in-python/ and everything working as expected for me. – Bitcoin Murderous Maniac Apr 12 '18 at 15:59

score 0 · Answer 5 · answered Feb 24 '22 at 21:18

0

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"

answered Feb 24 '22 at 21:18

Keith Macdonald

41
2

python mysql.connector DictCursor?

5 Answers5

Linked