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.
this helped me a lot, many thanks
Thanks Jim helpfull tip at right moment 🙂
Well I’ll tell you, it helped me out! Totally beats just using the standard cursor and having to map junk. Thanks!
Great!!! Thank you very much. You are awsome. 🙂