How can I return an object that supports dict and dot-notation from SQLite queries in Python?

I have the following code for a sqlite3 query. I want to be able to use index, dict, and dot-notation to access row attributes. Using the sqlite3.Row row_factory allows index and dict access, but not dot-notation.

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'

There is a dot-notation row_factory that exists, but I can't figure out how to merge the two. This doesn't support dict notation,

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)

Solution

You can covert it into a dict first, then set dot-notation access via dict dunder methods.

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)