如何将SQL查询结果转换成python字典
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28755505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert SQL query results into a python dictionary
提问by Lynn
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:
我很难将我的结果从查询转换为 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'}
回答by alecxe
You can use cursor.descriptionto 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()]
Note: use zip()in place of itertools.izip()on Python3.
注意：在 Python3 上使用zip()代替itertools.izip()。
回答by pablo
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
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
希望能帮助到你
回答by Anuja
Maybe this can help: http://codetrace.blogspot.com/2010/05/convert-query-result-to-dictionary-like.html
也许这可以帮助：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
回答by Lien Chu
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):
要回答原来的问题，你可以尝试以下方法（不需要循环crs，crs.fetchall()直接使用，只要表格内容适合你的记忆）：
result_list_of_dict = [{'StudentNum': col1, 'StudentLastName': col2, 'StudentFirst Name': col3} for (col1, col2, col3) in crs.fetchall()]
result_list_of_dict = [{'StudentNum': col1, 'StudentLastName': col2, 'StudentFirst Name': col3} for (col1, col2, col3) in crs.fetchall()]
The resulted result_list_of_dictis 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.
结果result_list_of_dict是一个字典列表，每个元素（字典）将具有您指定的 3 个键（'StudentNum'、'StudentLastName' 和 'StudentFirst Name'）以及从 SQL 表中提取的关联值。
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:
基本上，cursor.fetchall()返回一个元组列表，因此只要您列出所有应该返回的值，您就可以随意修改前面的部分。例如，以下代码适用于您只想提取前 3 列的 5 列表：
result_dict = {col1:(col2, col3) for (col1, col2, col3, col4, col5) in cursor.fetchall()}
result_dict = {col1:(col2, col3) for (col1, col2, col3, col4, col5) in cursor.fetchall()}
The resulted result_dictin this case would be a dictionary instead.
所得result_dict在这种情况下将是一个字典来代替。
回答by bonifacio_kid
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
