Python, MySQL and SELECT output to dictionary with column names for keys

Question

I have a MySQL table on which I'm executing SELECT statements within Python.

Is there anything out of the Python MySQLdb API that will, via the cursor, output an array of dictionaries whose keys are the column names (and values are those in the returned rows)?

Solution 1

Please use dictionary cursor:

cursor = conn.cursor (MySQLdb.cursors.DictCursor)

Solution 2

For me, this worked:

cursor = conn.cursor(dictionary=True)

---

Detailed example:

import mysql.connector # pip install mysql-connector-python

mydb = mysql.connector.connect(host="localhost", user="user", passwd="pass", database="dbname")
cursor = conn.cursor(dictionary=True)
sql = "SELECT * FROM `table` WHERE 1"
mycursor.execute(sql)
rows = mycursor.fetchall()
for row in rows:
    row["col"]

Comments

[Gandi]

I guess it's a duplicate of this one: stackoverflow.com/questions/2180226/…

[varela]

Maybe you need dictionary cursor?cursor = conn.cursor (MySQLdb.cursors.DictCursor)

[Ken]

If you want, make this link an answer which I'll accept.

[agf]

If you don't have enough reputation to vote to close, flag the answer, select "other", and just put in "duplicate" and the URL. Don't post an answer that is just a link to a duplicate.

[The EasyLearn Academy]

it is working with mysql.connector library.