Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 377
PythonEnthusiast Avatar asked Mar 12 '15 21:03

PythonEnthusiast


People also ask

Does Fetchall return tuple?

fetchall() Method. The method fetches all (or all remaining) rows of a query result set and returns a list of tuples.

What is use of Fetchall () function in Python?

Fetchall(): Fetchall() is a method that fetches all the remaining tuples from the last executed statement from a table (returns a list of tuples). The method only returns the first row from the defined table and If there are no tuples then it returns an empty list in the output.

How many records will be returned by Fetchmany () method?

Cursor's fetchmany() method returns the number of rows specified by size argument. the default value is 1. If the specified size is 100, then it returns 100 rows.

What is Fetchall and Fetchone?

Fetchall( ) Fetches one row from the resultset. It will return None if there are no more rows in the resultset. Fetches all the rows from the resultset.


3 Answers

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)
like image 192
Wuriyanto Avatar answered Oct 31 '22 16:10

Wuriyanto


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()]
like image 39
alecxe Avatar answered Oct 31 '22 16:10

alecxe


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

like image 34
Jonno_FTW Avatar answered Oct 31 '22 17:10

Jonno_FTW