Category Archives: Python

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

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]
print employee[2]

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']
print employee['age']

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.

Reading and Writing CSV Files with Python DictReader and DictWriter

Python ScriptingPython can be an extremely powerful tool for reading and writing csv files. Here I’ll demonstrate an example of each using the csv DictWriter function.

Sample File: (test.csv)

From here, I will import the csv file into a dictionary array using the csv DictReader function in the python console.

import csv
test_file = 'test.csv'
csv_file = csv.DictReader(open(test_file, 'rb'), delimiter=',', quotechar='"')

You can now parse through the data as a normal array.

for line in csv_file:
    print line['age']


Writing an array to a csv file with DictWriter

Here is an example of using DictWriter to write a csv file. The main difference is that the fieldnames have to be entered as a separate array. The fieldnames have to match all of the keys in the dictionary array but can be ordered differently.

test_array = []
test_array.append({'fruit': 'apple', 'quantity': 5, 'color': 'red'});
test_array.append({'fruit': 'pear', 'quantity': 8, 'color': 'green'});
test_array.append({'fruit': 'banana', 'quantity': 3, 'color': 'yellow'});
test_array.append({'fruit': 'orange', 'quantity': 11, 'color': 'orange'});
fieldnames = ['fruit', 'quantity', 'color']
test_file = open('test2.csv','wb')
csvwriter = csv.DictWriter(test_file, delimiter=',', fieldnames=fieldnames)
csvwriter.writerow(dict((fn,fn) for fn in fieldnames))
for row in test_array:

The writeheader() function was added to DictWriter in 2.7 and gives a cleaner way to write the first row of the csv file. You could replace line 9 with it, but I would advise against it to maintain backward compatibility.

Results of test2.csv after using the csv DictWriter: