如何在调用 SQLite 的 python 中通过名称引用列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/576933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I reference columns by their names in python calling SQLite?
提问by Ben
I have some code which I've been using to query MySQL, and I'm hoping to use it with SQLite. My real hope is that this will not involve making too many changes to the code. Unfortunately, the following code doesn't work with SQLite:
我有一些用于查询 MySQL 的代码，我希望将它与 SQLite 一起使用。我真正希望这不会涉及对代码进行太多更改。不幸的是，以下代码不适用于 SQLite：
cursor.execute(query)
rows = cursor.fetchall()
data = []
for row in rows
data.append(row["column_name"])
This gives the following error:
这给出了以下错误：
TypeError: tuple indices must be integers
Whereas if I change the reference to use a column number, it works fine:
而如果我更改引用以使用列号，则它可以正常工作：
data.append(row[1])
Can I execute the query in such a way that I can reference columns by their names?
我可以以可以通过名称引用列的方式执行查询吗？
采纳答案by kquinn
I'm not sure if this is the best approach, but here's what I typically do to retrieve a record set using a DB-API 2 compliant module:
我不确定这是否是最好的方法，但这是我通常使用 DB-API 2 兼容模块检索记录集的方法：
cursor.execute("""SELECT foo, bar, baz, quux FROM table WHERE id = %s;""",
(interesting_record_id,))
for foo, bar, baz, quux in cursor.fetchall():
frobnicate(foo + bar, baz * quux)
The query formatting method is one of the DB-API standards, but happens to be the preferred method for Psycopg2; other DB-API adapters might suggest a different convention which will be fine.
查询格式化方法是 DB-API 标准之一，但恰好是 Psycopg2 的首选方法；其他 DB-API 适配器可能会建议一个不同的约定，这很好。
Writing queries like this, where implicit tuple unpacking is used to work with the result set, has typically been more effective for me than trying to worry about matching Python variable names to SQL column names (which I usually only use to drop prefixes, and then only if I'm working with a subset of the column names such that the prefixes don't help to clarify things anymore), and is muchbetter than remembering numerical column IDs.
编写这样的查询，其中隐式元组解包用于处理结果集，对我来说通常比试图担心将 Python 变量名称与 SQL 列名称匹配（我通常只用于删除前缀，然后只有当我同列名这样的前缀不有助于澄清事情了）的一个子集工作，并且是多比记住数值列ID更好。
This style also helps you avoid SELECT * FROM table..., which is just a maintenance disaster for anything but the simplest tables and queries.
这种风格还可以帮助您避免SELECT * FROM table...，这对于除了最简单的表和查询之外的任何东西来说都是一场维护灾难。
So, not exactly the answer you were asking for, but possibly enlightening nonetheless.
因此，这不完全是您要的答案，但仍然可能具有启发性。
回答by dotancohen
In the five years since the question was asked and then answered, a very simple solution has arisen. Any new code can simply wrap the connection object with a row factory. Code example:
在这个问题被提出和回答之后的五年里，出现了一个非常简单的解决方案。任何新代码都可以简单地用行工厂包装连接对象。代码示例：
import sqlite3
conn = sqlite3.connect('./someFile')
conn.row_factory = sqlite3.Row // Here's the magic!
cursor = conn.execute("SELECT name, age FROM someTable")
for row in cursor:
print(row['name'])
Here are some fine docs. Enjoy!
这里有一些很好的文档。享受！
回答by Richard Levasseur
To access columns by name, use the row_factoryattribute of the Connection instance. It lets you set a function that takes the arguments cursorand row, and return whatever you'd like. There's a few builtin to pysqlite, namely sqlite3.Row, which does what you've asked.
要按名称访问列，请使用row_factoryConnection 实例的属性。它允许您设置一个函数，该函数接受参数cursorandrow并返回您想要的任何内容。pysqlite 有一些内置功能，即sqlite3.Row，它可以满足您的要求。
回答by chaimp
This can be done by adding a single line after the "connect" statment:
这可以通过在“connect”语句后添加一行来完成：
conn.row_factory = sqlite3.Row
Check the documentation here: http://docs.python.org/library/sqlite3.html#accessing-columns-by-name-instead-of-by-index
检查此处的文档：http: //docs.python.org/library/sqlite3.html#accessing-columns-by-name-instead-of-by-index
回答by srh snl
kushal's answer to this forumworks fine:
kushal 对这个论坛的回答工作正常：
Use a DictCursor:
使用字典光标：
import MySQLdb.cursors
.
.
.
cursor = db.cursor (MySQLdb.cursors.DictCursor)
cursor.execute (query)
rows = cursor.fetchall ()
for row in rows:
print row['employee_id']
Please take note that the column name is case sensitive.
请注意，列名区分大小写。
回答by Christian Witts
The SQLite API supports cursor.description so you can easily do it like this
SQLite API 支持 cursor.description 所以你可以很容易地这样做
headers = {}
for record in cursor.fetchall():
if not headers:
headers = dict((desc[0], idx) for idx,desc in cursor.description))
data.append(record[headers['column_name']])
A little long winded but gets the job done. I noticed they even have it in the factory.py file under dict_factory.
有点啰嗦，但可以完成工作。我注意到他们甚至在 dict_factory 下的 factory.py 文件中有它。
回答by svenema
Use the cursor description, like so:
使用光标描述，如下所示：
rows = c.fetchall()
for row in rows:
for col_i, col in enumerate(row):
print("Attribute: {0:30} Value: {1}".format(c.description[col_i][0],col))
