将结果行映射到python sqlite中的namedtuple [英] Mapping result rows to namedtuple in python sqlite

问题描述

我正在使用 sqlite3 的 python api，我有一个用于存储语言的小表，其中包含 id、name 和 creation_date 字段.我正在尝试将原始查询结果映射到文档推荐的 namedtuple 中，这样我就可以以更易读的方式管理行，所以这是我的 namedtuple.

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())

此代码不起作用，因为 fetchone() 返回一个元组而不是一个包含一个元组的列表，所以 map 函数尝试为每个元组字段创建三个 namedtuples 一个.

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

我的第二种方法是使用 fetchall() 虽然我只想要一个记录.我可以设置数据库中具有 unique 约束的 name 字段，以保证只有一个结果.

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

另一种方法是使用 fetchall()[0] 没有 unique 约束来保证一个结果.

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

我的问题是处理这个问题的最佳和常用方法是什么，我应该始终使用fetchall来维护一个通用接口并让数据库管理唯一性逻辑吗?还是应该像方法 1 一样明确创建一个列表?有没有更简单的方法来完成这个任务?

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?

推荐答案

有一个更简单的方法！Sqlite3 为用户提供了一种定义"行工厂".这些行工厂接受游标和元组行，并且可以返回它想要的任何类型的对象.

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.

一旦你设置了行工厂

con.row_factory = my_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())

收益

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

<小时>

有关如何定义命名元组工厂的另一个示例，请参阅这篇文章.

顺便说一下，如果你设置了

By the way, if you set

conn.row_factory = sqlite3.Row

然后行作为字典返回，其键是表的列名.因此，您可以使用内置的 sqlite3.Row 行工厂，并使用 row['creation_date'].

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

这篇关于将结果行映射到python sqlite中的namedtuple的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！