Mapping result rows to namedtuple in python sqlite

问题:

I am playing a bit with the python api for sqlite3, i have a little table for store languages with an id, name and creation_date fields. I am trying to map the raw query results into a namedtuple as the docs recommend, it that way i can manage rows in a more readable way, so here is my namedtuple.

LanguageRecord = namedtuple('LanguageRecord', 'id, name, creation_date')

The code that the docs suggest for the mapping is as follows:

for language in map(LanguageRecord._make, c.fetchall()):
  # do something with languages

This is fine when i want to return a collection of languages but in this case i want just to retrieve one language:

c.execute('SELECT * FROM language WHERE name=?', (name,))

So my first attempt it was something like this:

language = map(LanguageRecord._make, c.fetchone())

This code doesn't works because fetchone() returns a tuple instead a list with one tuple, so the map function tries to create three namedtuples one for each tuple field thought.

My first approach to solve this was to explicitly create a list and append to it the tuple result, something like:

languages = []
languages.append(c.fetchone())
for language in map(LanguageRecord._make, languages):
  # do something with language

My second approach was to use fetchall() although i just want one record. I can set the name field with a unique constrain in the database in order to garantize just one result.

for language in map(LanguageRecord._make, c.fetchall()):
  # do something with languages

Another approach could be use fetchall()[0] without the unique constrain to garantize just one result.

My question is which is the best and common way to deal with this problem, should i use always fetchall to maintain a common interface and let the database manage the uniqueness logic? or should i create a list explicitly as in approach 1? Is there a more easy way to accomplish this task?

回答1:

There is a much easier way! Sqlite3 provides a way for the user to define "row factories". These row factories take the cursor and the tuple row and can return whatever type of object it wants.

Once you set the row factory with

con.row_factory = my_row_factory

then rows returned by the cursor will be the result of my_row_factory applied to the tuple-row. For example,

import sqlite3
import collections

LanguageRecord = collections.namedtuple('LanguageRecord', 'id name creation_date')
def namedtuple_factory(cursor, row):
    return LanguageRecord(*row)

con = sqlite3.connect(":memory:")
con.row_factory = namedtuple_factory
cur = con.cursor()
cur.execute("select 1,2,3")
print(cur.fetchone())

yields

LanguageRecord(id=1, name=2, creation_date=3)

---

For another example of how to define a namedtuple factory, see this post.

---

By the way, if you set

conn.row_factory = sqlite3.Row

then rows are returned as dicts, whose keys are the table's column names. Thus, instead of accessing parts of the namedtuple with things like row.creation_date you could just use the builtin sqlite3.Row row factory and access the equivalent with row['creation_date'].

回答2:

An improved row_factory is actually this, which can be reused for all sorts of queries:

from collections import namedtuple

def namedtuple_factory(cursor, row):
    """Returns sqlite rows as named tuples."""
    fields = [col[0] for col in cursor.description]
    Row = namedtuple("Row", fields)
    return Row(*row)

conn = sqlite3.connect(":memory:")
conn.row_factory = namedtuple_factory
cur = con.cursor()

回答3:

I think better to use for language in map(LanguageRecord._make, c.fetchall()[:1]): Because it can cause IndexError with fetchall()[0].

If you need one result and there is already "WHERE" in query. As I understand query should return one row. Early optimization is evil. :)