Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #78509 > unrolled thread

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

Back to article view | Back to comp.lang.python

Contents

  Using namedtuple with sqlite, surely it can do better than the example cl@isbd.net - 2014-09-30 22:32 +0100
    Re: Using namedtuple with sqlite, surely it can do better than the example Peter Otten <__peter__@web.de> - 2014-10-01 01:06 +0200
    Re: Using namedtuple with sqlite, surely it can do better than the example Mark Lawrence <breamoreboy@yahoo.co.uk> - 2014-10-01 00:23 +0100

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

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.

-- 
Chris Green
·

[toc] | [next] | [standalone]

#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.

[toc] | [prev] | [next] | [standalone]

#78515

On 30/09/2014 22:32, 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.
>

I'm not sure what you're trying to achieve but I've found it easy to use 
the sqlite Row object see 
https://docs.python.org/3/library/sqlite3.html#row-objects

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web