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??
fetchall() Method. The method fetches all (or all remaining) rows of a query result set and returns a list of tuples.
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.
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.
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.
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)
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()]
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With