How to convert SQL query results into a python dictionary

You can use cursor.description to get the column names and "zip" the list of column names with every returned row producing as a result a list of dictionaries:

desc = cursor.description
column_names = [col[0] for col in desc]
data = [dict(zip(column_names, row))  
        for row in cursor.fetchall()]

Note: use itertools.izip in place of zip() on Python2.

At this time you probably solved your problem, but anyway:

If you're using mysql I think this is what you need:

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

As per definition: A MySQLCursorDict cursor returns each row as a dictionary. The keys for each dictionary object are the column names of the MySQL result.

So you must only set crs = cnx.cursor(dictionary=True)

Hope it helps

Maybe this can help: http://codetrace.blogspot.com/2010/05/convert-query-result-to-dictionary-like.html

query_result = [ dict(line) for line in [zip([ column[0] for column in crs.description], row) for row in crs.fetchall()] ]

print query_result

I hope this will help...

def all_students():
    qu= "select * from students"
    students = crs.execute(qu)
    students_data = []
    for row in students:
        student= {"StudentNum":row[0], "StudentLastName":row[2], "StudentFirst Name":row[3}
         students_data.append(student)

    final_data["students_info"] = students_data
    return final_data

the answer with the most votes is for python2, here is the answer for python3:

import itertools

desc = cursor.description
column_names = [col[0] for col in desc]
data = [dict(zip(column_names, row))  
        for row in cursor.fetchall()]

To answer the original question, you can try the following (there is no need to loop through crs, you can use crs.fetchall() directly, given that the table content fits into your memory):

result_list_of_dict = [{'StudentNum': col1, 'StudentLastName': col2, 'StudentFirst Name': col3} for (col1, col2, col3) in crs.fetchall()]

The resulted result_list_of_dict is a list of dictionary, each element (dictionary) will have 3 keys as you specified ('StudentNum', 'StudentLastName' and 'StudentFirst Name') with the associated values extracted from the SQL table.

To extend:

Basically, cursor.fetchall() returns a list of tuples so as long as you list all values that are supposedly returned, you can modify the front part however you like. For example, the following code would work for a table of 5 columns which you would like to extract only the first 3:

result_dict = {col1:(col2, col3) for (col1, col2, col3, col4, col5) in cursor.fetchall()}

The resulted result_dict in this case would be a dictionary instead.

The answer is to use the RealDictCursor cursor factory from the psycopg cursor.

Here's an example:

import psycopg2 # this could also be psycopg
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect(
#provide the needed details to connect
)

cursor = conn.cursor(cursor_factory=RealDictCursor)
query = """
         SELECT 
             id, weight, date_created
         FROM my_table
         ORDER BY date_created ASC
        """
cursor.excecute(query)

rows = cursor.fetchall()

# rows
[RealDictRow({'id': 1234, 'weight': 10000.0, 'date_created': datetime.datetime(2023, 6, 9, 18, 39, 51, 877113, tzinfo=datetime.timezone(datetime.timedelta(seconds=10800)))})]

If you expect your query to return a single result and you want all of the values in a dictionary by their column name you can use:

def querySingle(sql, args):
    try:
        cursor = cnx.cursor()
        cursor.execute(sql, args)
        for row in cursor:
            return dict((column[0],row[index]) for index, column in enumerate(cursor.description))
        return {}
    finally:
        cursor.close()

For example querySingle("SELECT * FROM animals WHERE id=%s", [1]) might return:

{
    "id": 1,
    "name": "Bob",
    "type": "Alligator"
}

How it works

cursor.description returns an array of all the columns, of which each entry is an array of which the first value is the name of the column. Using enumerate on that gives each column along with its index. Then it is just a matter of creating the dictionary with the name from column[0] along with the data from row[index]. Returning in the for loop ensures that only the first row is processed.

json_data = json.loads(json.dumps(row, ensure_ascii=False, indent=None))

result = db_pool.execute(query, return_dict=True)

If you are coming from Harvard's cs50 course, a SELECT query returns a list of where each row is represented by a Python dict where the keys correspond to the column name and the values are from the table.

You don't need to convert it.