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

Answer 1

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)

Answer 2

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()]

Answer 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