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)?

I guess it's a duplicate of this one: http://stackoverflow.com/questions/2180226/python-use-mysqldb-to-import-a-mysql-table-as-a-dictionary — Gandi, Sep 01 '11 at 09:06
Maybe you need dictionary cursor?cursor = conn.cursor (MySQLdb.cursors.DictCursor) — varela, Sep 01 '11 at 09:07
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.__ — agf, Sep 01 '11 at 09:36

score 45 · Answer 1 · edited Jul 25 '22 at 13:21

45

For me, this worked:

cursor = conn.cursor(dictionary=True)

Detailed example:

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

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

edited Jul 25 '22 at 13:21

Cornea Valentin

471
6
12

answered Jul 12 '19 at 09:59

DaWe

1,422
16
26

1

it is working with mysql.connector library. – The EasyLearn Academy Feb 11 '22 at 02:20
looks like it doesn't even need the `foreach`! – Marco Frag Delle Monache Feb 01 '23 at 09:04

score 37 · Accepted Answer · answered Sep 01 '11 at 09:08

37

Please use dictionary cursor:

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

answered Sep 01 '11 at 09:08

varela

1,281
1
10
16

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

2 Answers2

Linked