Path: csiph.com!usenet.pasdenom.info!goblin2!goblin.stu.neva.ru!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Peter Otten <__peter__@web.de>
Subject: Re: Using namedtuple with sqlite, surely it can do better than the example
Date: Wed, 01 Oct 2014 01:06:51 +0200
Organization: None
References: <n3krfb-mo1.ln1@chris.zbmc.eu>
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/4.13.3
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.14499.1412118425.18130.python-list@python.org>
Lines: 61
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:78513

cl@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.