如何在调用 SQLite 的 python 中通过名称引用列? [英] How can I reference columns by their names in python calling SQLite?

问题描述

我有一些代码用于查询 MySQL，我希望将它与 SQLite 一起使用.我真正希望这不会涉及对代码进行太多更改.不幸的是，以下代码不适用于 SQLite:

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:

cursor.execute(query)

rows = cursor.fetchall()

data = []
for row in rows
  data.append(row["column_name"])

这会产生以下错误:

 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?

推荐答案

我不确定这是否是最佳方法，但这是我使用 DB-API 2 兼容模块检索记录集的通常做法:

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:

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)

查询格式化方法是DB-API标准之一，但恰好是Psycopg2的首选方法；其他 DB-API 适配器可能会建议一个不同的约定，这会很好.

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.

像这样编写查询，其中隐式元组解包用于处理结果集，对我来说通常比​​试图担心将 Python 变量名称与 SQL 列名称匹配(我通常只用于删除前缀)更有效，然后仅当我使用列名的子集时，前缀不再有助于澄清事情)，并且比记住数字列 ID 好.

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 much better than remembering numerical column IDs.

这种样式还可以帮助您避免 SELECT * FROM table...，这对于除最简单的表和查询之外的任何内容来说都是一场维护灾难.

This style also helps you avoid SELECT * FROM table..., which is just a maintenance disaster for anything but the simplest tables and queries.

所以，这不完全是您要的答案，但仍然可能具有启发性.

So, not exactly the answer you were asking for, but possibly enlightening nonetheless.

这篇关于如何在调用 SQLite 的 python 中通过名称引用列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！