Mapping result rows to namedtuple in python sqlite

Question

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…

Accepted Answer

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']`.

Mapping result rows to namedtuple in python sqlite

Solution

Problem