-
-
Notifications
You must be signed in to change notification settings - Fork 30.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
namedtuple row factory for sqlite3 #57508
Comments
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: (except with smarter caching on the named tuples) |
+1 |
Definitely! |
Hi, Here is an implementation using lru_cache to prevent regeneration of the named tuple each time. Cheers, |
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) |
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. |
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. |
Nick, Thanks for the tip. I have removed the star unpacking. |
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. :) |
...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. |
I'd like to see this in 3.5 as I often use sqlite so what needs doing here? |
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()"
10000 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()"
10000 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()"
10000 loops, best of 3: 92.1 usec per loop It would be easier to add __getattr__ to sqlite3.Row. |
Serhiy, 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) |
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() (bpo-14373) 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. |
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. |
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 for(speed test) range: 10000 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: for(speed test) range: 10000 This i can work with, tiny compromise in speed with better usage. |
See also bpo-39170 |
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 |
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. |
FTR, here's a benchmark using @serhiy-storchaka's approach w/LRU cache: Lib/sqlite3/dbapi2.py patchdiff --git a/Lib/sqlite3/dbapi2.py b/Lib/sqlite3/dbapi2.py
index 3b6d2f7ba2..216895c53a 100644
--- a/Lib/sqlite3/dbapi2.py
+++ b/Lib/sqlite3/dbapi2.py
@@ -89,6 +89,19 @@ def convert_timestamp(val):
del(register_adapters_and_converters)
+
+import functools
+@functools.lru_cache(maxsize=128)
+def _make_class(fields):
+ return collections.namedtuple("Row", fields)
+
+def NamedTupleRow(cursor, row):
+ """Return a namedtuple row factory for connection objects."""
+ fields = tuple([c[0] for c in cursor.description])
+ cls = _make_class(fields)
+ return cls._make(row)
+
+
def __getattr__(name):
if name in _deprecated_names:
from warnings import warn bench-row.pyimport pyperf
from textwrap import dedent
SETUP_DEF=dedent("""
import sqlite3
cx = sqlite3.connect(":memory:")
""")
SETUP_ROW=dedent("""
import sqlite3
cx = sqlite3.connect(":memory:")
cx.row_factory = sqlite3.Row
""")
SETUP_NAMED=dedent("""
import sqlite3
cx = sqlite3.connect(":memory:")
cx.row_factory = sqlite3.NamedTupleRow
""")
STMT=dedent("""
cx.execute("select 1 as 'a', 2 as 'b'").fetchall()
""")
runner = pyperf.Runner()
runner.timeit(name="default", stmt=STMT, setup=SETUP_DEF)
runner.timeit(name="row", stmt=STMT, setup=SETUP_ROW)
runner.timeit(name="named", stmt=STMT, setup=SETUP_NAMED)
Following up Serhiy's suggestion to add a
|
These years, I would prefer a dataclass conversion rather than a named tuple. |
I'm curious why? IMO, named tuples are a better fit. A dataclass factory benchmark
Footnotes
|
I don’t like using named tuples after all! I prefer having attribute access only, it’s more explicit than tuple positions. |
More importantly; is it worth it to pursue this issue further? Is the performance acceptable? |
I think that named tuples still make sense for a row factory. But I am the wrong person to evaluate performance numbers. |
An alternative approach is simply to add attribute access to sqlite3.Row. |
FYI, I've added a namedtuple row factory as an example in the proposed "row factory how-to" in gh-99507. Perhaps it is suffictient to keep this as an example/recipe in the docs instead of adding it as a new feature. |
The sqlite3 docs now include a "row factory how-to" that includes a recipe for a simple In the light of the docs update, I now suggest to close this feature request. |
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
bugs.python.org fields:
The text was updated successfully, but these errors were encountered: