4

After executing a query statement on a MySQL database connection, I perform:

rows = cursor.fetchall()

This gives an array of arrays. I'd like to have an array of dictionaries, where each dictionary takes its keys from the requested column names of my table and associates the values from the table.

How do I do this?

Ken
  • 30,811
  • 34
  • 116
  • 155
  • 2
    it gives a list of tuples. Arrays are not involved. Also there are no *maps*. Those are dicts; – nosklo Dec 24 '11 at 10:35

1 Answers1

15

Well, you forgot to mention which mysql library you're using.

  • If using oursql (which I recommend, it is certainly the best one), use oursql's DictCursor. Example:

    conn = oursql.connect(...)
curs = conn.cursor(oursql.DictCursor)

  • If using MySQLdb (why?) Use MySQLdb's DictCursor. Example:

    conn = MySQLdb.connect(..., cursorclass=MySQLdb.cursors.DictCursor) 
curs = conn.cursor()

Doing that will give you a cursor that returns dicts for each row. Remember to not have duplicate rownames in your query.

nosklo
  • 217,122
  • 57
  • 293
  • 297
  • 1
    Just found this too, so instant +1 and accept. Sample construction: `cursor = db.cursor(MySQLdb.cursors.DictCursor)` – Ken Dec 24 '11 at 10:33
  • 10
    `MySQLdb.connect(..., cursorclass=MySQLdb.cursors.DictCursor)` does not work since MySQLdb does not have a cursors attribute until _after_ the connection is made. SK9s version `cursor = db.cursor(MySQLdb.cursors.DictCursor)` works. Tested on Ubuntu 10.4 with system MySQLdb. – jeorgen Nov 21 '12 at 01:10
  • @jeorgen as you can see in the official docs http://mysql-python.sourceforge.net/MySQLdb.html#mysqldb you can pass a `cursorclass` keyword parameter to the `connect()` call. It works here. If yours is failing it might be for some other reason. Please ask a separate question. – nosklo Nov 23 '12 at 11:44
  • Hi noskla I am not asking, just observing that it did not work as advertised on my Ubuntu as a caveat for others. Not important, it works for me the other way :-) – jeorgen Nov 23 '12 at 15:23