如何从Python中的SQLite查询返回支持dict和点表示法的对象?

hmae6n7t  于 2023-10-23  发布在  SQLite
关注(0)|答案(1)|浏览(148)

下面是一个sqlite3查询的代码。我希望能够使用index、dict和点表示法来访问行属性。使用sqlite3.Row row_factory允许索引和dict访问,但不允许点表示法。

import sqlite3
conn = sqlite3.connect(':memory:')
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")
cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 1.2),
        ('And Now for Something Completely Different', 1971, 2.3)
""")
conn.commit()
results = conn.execute("select * from movie;")
for r in results:
    print(r[0], r['title'], r.title)
    # ---> `r.title` AttributeError: 'sqlite3.Row' object has no attribute 'title'

有一个点表示法row_factory存在,但我不知道如何合并这两个。这不支持dict表示法,

from collections import namedtuple

def namedtuple_factory(cursor, row):
    """Returns sqlite rows as named tuples."""
    fields = [col[0] for col in cursor.description]
    Row = namedtuple("Row", fields)
    return Row(*row)
ycggw6v2

ycggw6v21#

你可以先把它转换成一个dict,然后通过dict dunder方法设置点表示法访问。

class dotdict(dict):
    """dot.notation access to dictionary attributes"""
    __getattr__ = dict.get
    __setattr__ = dict.__setitem__
    __delattr__ = dict.__delitem__

def row_factory(cursor, data):
    keys = [col[0] for col in cursor.description]
    d = {k: v for k, v in zip(keys, data)}
    return dotdict(d)

相关问题