如何从sqlite查询中获取字典? [英] How can I get dict from sqlite query?

问题描述

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

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

With iteration I get lists coresponding to the rows.

for row in res:
    print row

我可以获取列的名称

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

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

But is there some function or setting to get dictionaries instead of list?

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

还是我必须自己做?

推荐答案

您可以使用

You could use row_factory, as in the example in the docs:

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"]

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

or follow the advice that's given right after this example in the docs:

如果返回元组还不够 并且您想要基于名称的访问权限 列，您应该考虑设置 row_factory进行高度优化 sqlite3.Row类型.行同时提供 基于索引且不区分大小写 基于名称的列访问 几乎没有内存开销.它会 可能比你自己的好 基于字典的自定义方法或 甚至是基于db_row的解决方案.

If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting row_factory to the highly-optimized sqlite3.Row type. Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.

这篇关于如何从sqlite查询中获取字典?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！