How to convert SQL query results into a python dictionary

Question

Im having difficuty converting my results from a query into a python dictionary. Each dictionary is supposed to represent one student, the keys are the column name and the values are the corresponding values from the query, so far this is what I've come up with:

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

But when i print it , it returns in correct information and everything is out of order, and it only displays one record :

{'StudentLastName': Jane, StudentNum: 'Smith  ', StudentFirst Name: '1612'}

Answer 1

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:

import itertools

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

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