python - 如何从 sqlite 查询中获取 dict？

Question

db = sqlite.connect("test.sqlite")
res = db.execute("select * from table")

通过迭代，我得到与行相对应的列表。

for row in res:
    print row

我可以得到列的名称

col_name_list = [tuple[0] for tuple in res.description]

但是是否有一些功能或设置可以获取字典而不是列表？

{'col1': 'value', 'col2': 'value'}

还是我自己做？

Answer 1

您可以使用 row_factory ，如文档中的示例:

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]

或遵循文档中此示例之后给出的建议:

If returning a tuple doesn’t sufficeand you want name-based access tocolumns, you should consider settingrow_factory to the highly-optimizedsqlite3.Row type. Row provides bothindex-based and case-insensitivename-based access to columns withalmost no memory overhead. It willprobably be better than your owncustom dictionary-based approach oreven a db_row based solution.

这是第二种解决方案的代码:

con = sqlite3.connect(…)
con.row_factory = sqlite3.Row   #   add this row
cursor = con.cursor()