Using namedtuple with sqlite, surely it can do better than the example

[Peter Otten]

cl at isbd.net wrote:

> In the namedtuple documentation there's an example:-
> 
>     EmployeeRecord = namedtuple('EmployeeRecord', 'name, age, title,
>     department, paygrade')
> 
>     import sqlite3
>     conn = sqlite3.connect('/companydata')
>     cursor = conn.cursor()
>     cursor.execute('SELECT name, age, title, department, paygrade FROM
>     employees') for emp in map(EmployeeRecord._make, cursor.fetchall()):
>         print emp.name, emp.title
> 
> (I've deleted the csv bit)
> 
> Surely having to match up the "name, age, title, department,
> paygrade" between the tuple and the database can be automated, the
> example is rather pointless otherwise.  At the very least one should
> use the same variable instance for both, but it should be possible to
> get the tuple names from the database.

It's an example meant to convey the basic idea -- you can add bells and 
whistles in your actual code. Something like

#!/usr/bin/env python3
import sqlite3
import functools
from collections import namedtuple

def make_namedtuple_row_factory():
    last_pair = (None, None) # description, rowtype

    @functools.lru_cache(maxsize=500)
    def make_rowtype(description):
        return namedtuple("Row", 
            [column[0] for column in description])._make

    def make_namedtuple_is_make(cursor, row):
        nonlocal last_pair

        this_description = cursor.description
        last_description, last_rowtype = last_pair
        if last_description is this_description:
            return last_rowtype(row)
        this_rowtype = make_rowtype(this_description)
        last_pair = this_description, this_rowtype
        return this_rowtype(row)

    return make_namedtuple_is_make

db = sqlite3.connect("companydata")
cursor = db.cursor()
cursor.row_factory = make_namedtuple_row_factory()
for employee in cursor.execute(
        'SELECT name, age, title, department, paygrade FROM employees'):
    print(employee.name, employee.title)

might be easy to use and have acceptable performance, but most of the code 
distracts from the namedtuple usage.