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: stackoverflow.com/questions/2180226/… — Gandi
– Gandi, Commented Sep 1, 2011 at 9:06
Maybe you need dictionary cursor?cursor = conn.cursor (MySQLdb.cursors.DictCursor) — varela
– varela, Commented Sep 1, 2011 at 9: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
– agf, Commented Sep 1, 2011 at 9:36

Cornea Valentin · Accepted Answer · 2022-07-25 13:21:57Z

52

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, 2022 at 13:21

Cornea Valentin

64510 silver badges14 bronze badges

answered Jul 12, 2019 at 9:59

DaWe

1,7231 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The EasyLearn Academy Over a year ago

it is working with mysql.connector library.

Marco Frag Delle Monache Over a year ago

looks like it doesn't even need the foreach!

varela · Accepted Answer · 2011-09-01 09:08:40Z

39

Please use dictionary cursor:

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

answered Sep 1, 2011 at 9:08

varela

1,3311 gold badge10 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related