将pyodbc游标结果输出为python字典 [英] Output pyodbc cursor results as python dictionary

问题描述

如何将 pyodbc 游标输出(来自 .fetchone、.fetchmany 或 .fetchall)序列化为 Python 字典?

我正在使用 Bottlepy 并且需要返回 dict 以便它可以将其作为 JSON 返回.

解决方案

如果你不知道提前列，使用 Cursor.description 以构建列名列表和 zip 与每一行生成一个字典列表.示例假设建立了连接和查询:

<预><代码>>>>游标 = connection.cursor().execute(sql)>>>columns = [column[0] for column in cursor.description]>>>打印(列)['姓名'，'创建日期']>>>结果 = []>>>对于 cursor.fetchall() 中的行:... results.append(dict(zip(columns, row)))...>>>打印(结果)[{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'},{'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'},{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'},{'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}]

How do I serialize pyodbc cursor output (from .fetchone, .fetchmany or .fetchall) as a Python dictionary?

I'm using bottlepy and need to return dict so it can return it as JSON.

解决方案

If you don't know columns ahead of time, use Cursor.description to build a list of column names and zip with each row to produce a list of dictionaries. Example assumes connection and query are built:

>>> cursor = connection.cursor().execute(sql)
>>> columns = [column[0] for column in cursor.description]
>>> print(columns)
['name', 'create_date']
>>> results = []
>>> for row in cursor.fetchall():
...     results.append(dict(zip(columns, row)))
...
>>> print(results)
[{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'},   
 {'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'},
 {'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'},     
 {'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}]

这篇关于将pyodbc游标结果输出为python字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！