Using Python’s DictCursor in MySQL to Return a Dict with Keys

Share Post

The default MySQLdb library in Python only returns an array of values in the order you retrieve them. Here is an example:

import MySQLdb

database = MySQLdb.connect(host = "localhost", user = "jim", passwd = "notmypassword", db = "company")
c = database.cursor()

c.execute("SELECT name, position, age FROM employees")
employee = c.fetchone()

print employee[0]
#Jim Biggs
print employee[1]
#Programmer
print employee[2]
#35

While that does the job, being able to call the fields using employee[‘name’], employee[‘position’] and employee[‘age’] makes a lot more sense. Here is how to use Python’s MySQLdb’s cursor library to return a dictionary instead:

import MySQLdb
import MySQLdb.cursors

database = MySQLdb.connect(host = "localhost", user = "jim", passwd = "notmypassword", db = "company", cursorclass=MySQLdb.cursors.DictCursor)
c = database.cursor()

c.execute("SELECT name, position, age FROM employees")
employee = c.fetchone()

print employee['name']
#Jim Biggs
print employee['position']
#Programmer
print employee['age']
#35

Note the import of MySQLdb.cursors. Without it, you will get this error:
AttributeError: ‘module’ object has no attribute ‘cursors’

I also modified the MySQLdb.connect on line 3 adding the argument cursorclass=MySQLdb.cursors.DictCursor.

The standard DictCursor documentation is pretty bad and examples are almost non-existant, so hopefully this will help someone out. It took me a while to figure this out after I started using MySQL with Python. These examples are using Python 2.7.

Written by Jim Biggs

Speak and be heard

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>