MySQL fetchall() - how to get data inside a dict rather than inside a tuple

Question

I've a table having columns as id, col1, col2, col3. I executed the query in this way.

cur.execute(query)
row = cur.fetchall()

Can I get the data of row inside a dict ,ie I want to pass this result as a result to an api.

cur.fetchall() will yield me result in the following format ((id,col1,col2,col3),(id,col1,col2,col3),(id,col1,col2,col3))

Can I get result in

[
   {id:value,col1:value,col2:value,col3:value},
   {id:value,col1:value,col2:value,col3:value},
   {id:value,col1:value,col2:value,col3:value}
]

I know this concept that I can loop around fetchall(), and get values using dictionary concept, ie

rows = cursor.fetchall()
for row in rows:
   id = row['id']
   col1 = row['col1']
   and so on...

Can I pass rows as a dictionary??

Solution 1

You need to "zip" the resulting values with cursor.description:

columns = [col[0] for col in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]

Solution 2

If you are using mysql-connector library to connect to the database, the easy way is to set dictionary to True inside cursor function:

db = connector.connect(**config)

cursor = db.cursor(dictionary=True)

Solution 3

The easiest way to do it is to use the DictCursor:

import MySQLdb
...
cursor = db.cursor(MySQLdb.cursors.DictCursor)

You can also set this for all cursors generated by db by passing the cursor_class parameter to connect

Documentation for the different types of cursors is here: https://mysqlclient.readthedocs.io/user_guide.html#using-and-extending

Solution 4

I've modified a little alecxe's answer to zip each row inside cursor loop. Might be helpful in case you have a large table, so you won't hold all rows in memory.

cursor = db.execute_sql("select * from something")
columns = [col[0] for col in cursor.description]
for row in cursor.fetchall():
    row = dict(zip(columns, row))
    print(row)

View more solutions

Comments

[PythonEnthusiast]

This is same like looping around fetchall() and creating a dict, which is time-consuming. Is there some direct solution?

[alecxe]

@user1162512 I am afraid there is nothing built-in. I've been using this approach for a while whenever I needed. As an alternative approach, you can use an ORM which would hide how it does it, but would give you model objects with a "dot" access to the fields..

[alecxe]

@user1162512 btw, from what I recall, Django uses the same approach to match the model fields with results of the query.

[PythonEnthusiast]

I've been an avid user of Django and knew this feature in Django, but didnt knew how it works, hence wanted to know the same for raw MySQL. Thanks a lot :)

[alecxe]

@user1162512 yeah, and there is also information_schema.columns used, see github.com/django/django/blob/….

[caram]

TypeError: cursor() got an unexpected keyword argument 'dictionary'

[caram]

Excellent, this should be the accepted answer IMHO.

[Spacha]

@caram As you figured out already, that's because you are using MySQLdb connector, this solution is for mysql-connector.