如何将SQL查询结果转换为python字典

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:

我有difficuty将我的结果从查询转换为python字典。每个字典应该代表一个学生，键是列名，值是查询中的相应值，到目前为止，这是我提出的：

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'}

2 个解决方案

#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:

您可以使用cursor.description获取列名称，并使用每个返回的行“压缩”列名列表，从而生成字典列表：

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

#2

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

这时你可能解决了你的问题，但无论如何：

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

如果您使用的是mysql，我认为这就是您所需要的：

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.

根据定义：MySQLCursorDict游标将每行作为字典返回。每个字典对象的键是MySQL结果的列名。

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

所以你只能设置crs = cnx.cursor（dictionary = True）

Hope it helps

希望能帮助到你

#1