[issue13299] namedtuple row factory for sqlite3

[Zaur Shibzukhov]

Zaur Shibzukhov  added the comment:

Instead of using cache, maybe better to use mutable default argument?

For example:

def make_row_factory(cls_factory, **kw):
def row_factory(cursor, row, cls=[None]):
rf = cls[0]
if rf is None:
fields = [col[0] for col in cursor.description]
cls[0] = cls_factory("Row", fields, **kw)
return cls[0](*row)
return rf(*row)
return row_factory

namedtuple_row_factory = make_row_factory(namedtuple)

Seem it should add less overhead.

--
nosy: +intellimath

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Raymond Hettinger]

Raymond Hettinger  added the comment:

FWIW, namedtuple speed improved considerably since these posts were made.  When 
I last checked, their lookup speed was about the same as a dict lookup.  

See: https://docs.python.org/3/whatsnew/3.9.html#optimizations

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Erlend E. Aasland]

Erlend E. Aasland  added the comment:

See also bpo-39170

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Erlend Egeberg Aasland]

Change by Erlend Egeberg Aasland :


--
nosy: +erlendaasland

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Mark Lawrence]

Change by Mark Lawrence :


--
nosy:  -BreamoreBoy

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[YoSTEALTH]

YoSTEALTH added the comment:

note: sqlite_namedtuplerow.patch _cache method conflicts with attached database 
with say common table.column name like id

Using namedtuple method over sqlite3.Row was a terrible idea for me. I thought 
namedtuple is like tuple so should be faster then dict! wrong. I wasted 2 days 
change my work to namedtuple and back to sqlite3.Row, the speed difference on 
my working project was:

namedtuple 0.035s/result
sqlite3.Rows 0.0019s/result

for(speed test) range: 1
namedtuple 17.3s
sqlite3.Rows 0.4s

My solution was to use sqlite3.Row (for speed) but to get named like usage by 
convert dict keys() with setattr names:

class dict2named(dict):
def __init__(self, *args, **kwargs):
super(dict2named, self).__init__(*args, **kwargs)
self.__dict__ = self

Usage:

for i in con.execute('SELECT * FROM table'):
yield dict2named(i)

Now i can use:

print(i.title)

and handy dict methods for dash column names:

print(i['my-title'])
print(i.get('my-title', 'boo'))

Now working project speed:
sqlite3.Rows 0.0020s/result

for(speed test) range: 1
sqlite3.Rows 0.8s with dict2named converting

This i can work with, tiny compromise in speed with better usage.

--
nosy: +YoSTEALTH

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Serhiy Storchaka]

Serhiy Storchaka added the comment:

Here is faster implementation.

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:'); 
con.row_factory = sqlite3.NamedTupleRow; con.execute('create table t (a, b)') 
-s for i in range(100): con.execute('insert into t values (1, 2)') -- 
con.execute('select * from t').fetchall()
100 loops, best of 3: 2.74 msec per loop

But it is still 3 times slower than sqlite3.Row.

--
Added file: http://bugs.python.org/file37673/sqlite_namedtuplerow.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Gerhard Häring]

Changes by Gerhard Häring g...@ghaering.de:


--
assignee:  - ghaering

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Daniel Lenski]

Daniel Lenski added the comment:

Serhiy,
52 usec/loop doesn't seem like much overhead. This is not 52 usec per row 
fetched, but just 52 usec per cursor.execute(). An example where 1 row is 
fetched for each cursor would show this more clearly.

The advantage of namedtuple is that it's a very well-known interface to most 
Python programmers. Other db-api modules have taken a similar approach; 
psycopg2 has a dict-like cursor similar to Row, but has added NameTupleCursor 
in recent versions. 
(http://initd.org/psycopg/docs/extras.html#namedtuple-cursor)

--
nosy: +dlenski

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Serhiy Storchaka]

Serhiy Storchaka added the comment:

Yes, above microbenchmarks measure the time of execute() + the time of fetching 
one row. Here is more precise microbenchmarks.

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:'); 
con.execute('create table t (a, b)') -s for i in range(100): 
con.execute('insert into t values (1, 2)') -- con.execute('select * from 
t').fetchall()
1000 loops, best of 3: 624 usec per loop

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:'); 
con.row_factory = sqlite3.Row; con.execute('create table t (a, b)') -s for i 
in range(100): con.execute('insert into t values (1, 2)') -- 
con.execute('select * from t').fetchall()
1000 loops, best of 3: 915 usec per loop

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:'); 
con.row_factory = sqlite3.NamedTupleRow; con.execute('create table t (a, b)') 
-s for i in range(100): con.execute('insert into t values (1, 2)') -- 
con.execute('select * from t').fetchall()
100 loops, best of 3: 6.21 msec per loop

Here sqlite3.Row is about 1.5 times slower than tuple, but 
sqlite3.NamedTupleRow is about 7 times slower than sqlite3.Row.

With C implementation of lru_cache() (issue14373) the result is much better:

100 loops, best of 3: 3.16 msec per loop

And it will be even more faster (up to 1.7x) when add to the Cursor class a 
method which returns a tuple of field names.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Mark Lawrence]

Mark Lawrence added the comment:

I'd like to see this in 3.5 as I often use sqlite so what needs doing here?

--
nosy: +BreamoreBoy
versions: +Python 3.5 -Python 3.4

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Serhiy Storchaka]

Serhiy Storchaka added the comment:

There is significant overhead. Microbenchmark results:

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:')  
con.execute('select 1 as a, 2 as b').fetchall()
1 loops, best of 3: 35.8 usec per loop

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:'); 
con.row_factory = sqlite3.Row  con.execute('select 1 as a, 2 as 
b').fetchall()
1 loops, best of 3: 37.3 usec per loop

$ ./python -m timeit -s import sqlite3; con = sqlite3.connect(':memory:'); 
con.row_factory = sqlite3.NamedTupleRow  con.execute('select 1 as a, 2 as 
b').fetchall()
1 loops, best of 3: 92.1 usec per loop

It would be easier to add __getattr__ to sqlite3.Row.

--
nosy: +serhiy.storchaka

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Glenn Langford]

Changes by Glenn Langford glenn.langf...@gmail.com:


--
nosy:  -glangford

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Glenn Langford]

Glenn Langford added the comment:

In abstract, I like the namedtuple interface for sqlite3 as well. One caution 
is that the approach suggested at

http://peter-hoffmann.com/2010/python-sqlite-namedtuple-factory.html 

can have a dramatic impact on performance. For one DB-intensive application, I 
experienced 20+ seconds run time with the row factory (under 3.4), versus sub 
second without (identified with cProfile). Many thousands of calls to 
namedtuple_factory were not good. :)

--
nosy: +glangford

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Glenn Langford]

Glenn Langford added the comment:

...if I understand the proposed caching scheme, then repeated executions of the 
query

SELECT a,b,c FROM table

would result in cache hits, since the column names remain the same. I'm 
guessing this would resolve the performance problem in the app I saw, but it 
would be good to verify that performance is broadly similar with/without named 
tuples.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Ezio Melotti]

Changes by Ezio Melotti ezio.melo...@gmail.com:


--
stage: needs patch - patch review
versions: +Python 3.4 -Python 3.3

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Raymond Hettinger]

Raymond Hettinger added the comment:

Caching based on the cursor going to be problematic because a single cursor can 
be used multiple times with different descriptions:

   c = conn.cursor()
   c.execute('select symbol from stocks')
   print c.description
   c.execute('select price from stocks')
   print c.description # same cursor, different layout, needs a new named 
tuple

It might make more sense to cache the namedtuple() factory itself:

   sql_namedtuple = lru_cache(maxsize=20)(namedtuple)

Also, the example in the docs is too lengthy and indirect.  Cut-out the step 
for creating an populating the database -- just assume db created in the 
example at the top of the page:

   For example::

 conn.row_factory = sqlite3.NamedTupleRow
 c = conn.cursor()
 for record in c.execute('select * from stocks'):
print record

Row(date='2006-01-05', trans='BUY', symbol='RHAT', qty=100.0, price=35.14)
Row(date='2006-01-05', trans='BUY', symbol='RHAT', qty=100, price=35.14)
Row(date='2006-03-28', trans='BUY', symbol='IBM', qty=1000, price-45.0)

No need to go into a further lesson on how to use named tuples.


Also, the patch uses star-unpacking:  _namedtuple_row(cursor)(*row)

Instead, it should use _make:  _namedtuple_row(cursor)._make(row)


(u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0)

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Russell Sim]

Russell Sim added the comment:

Raymond, Thanks for the comprehensive feedback! It's fantastic!  I have updated 
the patch with most of you feedback... but there was one part that I couldn't 
follow entirely.  I am now using the _make method but I have had to use star 
unpacking to allow the method to be cached, lru_cache won't allow a key to be a 
list because they aren't hash-able.

--
Added file: http://bugs.python.org/file26945/issue_13299.1.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Nick Coghlan]

Nick Coghlan added the comment:

You should be able to just use tuple(col[0] for col in cursor.description) 
instead of the current list comprehension in order to make the argument 
hashable.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Russell Sim]

Russell Sim added the comment:

Nick, Thanks for the tip.  I have removed the star unpacking.

--
Added file: http://bugs.python.org/file26946/issue_13299.2.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Russell Sim]

Russell Sim added the comment:

Hi,

Here is an implementation using lru_cache to prevent regeneration of the named 
tuple each time.

Cheers,
Russell

--
keywords: +patch
nosy: +Russell.Sim
Added file: http://bugs.python.org/file26909/issue_13299.patch

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Petri Lehtinen]

Changes by Petri Lehtinen pe...@digip.org:


--
nosy: +petri.lehtinen

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Éric Araujo]

Éric Araujo mer...@netwok.org added the comment:

 collections.namedtuple provides a much nicer interface than sqlite3.Row
Definitely!

--
nosy: +eric.araujo

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Raymond Hettinger]

Raymond Hettinger raymond.hettin...@gmail.com added the comment:

+1

--
nosy: +rhettinger

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Nick Coghlan]

New submission from Nick Coghlan ncogh...@gmail.com:

Currently, sqlite3 allows rows to be easily returned as ordinary tuples 
(default) or sqlite3.Row objects (which allow dict-style access).

collections.namedtuple provides a much nicer interface than sqlite3.Row for 
accessing ordered data which uses valid Python identifiers for field names, and 
can also tolerate field names which are *not* valid identifiers.

It would be convenient if sqlite3 provided a row factory along the lines of the 
one posted here:
http://peter-hoffmann.com/2010/python-sqlite-namedtuple-factory.html

(except with smarter caching on the named tuples)

--
messages: 146670
nosy: ncoghlan
priority: normal
severity: normal
stage: needs patch
status: open
title: namedtuple row factory for sqlite3
type: feature request
versions: Python 3.3

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue13299] namedtuple row factory for sqlite3

[Ned Deily]

Changes by Ned Deily n...@acm.org:


--
nosy: +ghaering

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue13299
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com