sqlite3.OperationalError:没有这样的列 - 但我不是要列? [英] sqlite3.OperationalError: no such column - but I'm not asking for a column?

问题描述

所以，我正在尝试使用 sqlite3，但在运行 SELECT 查询时似乎出现了问题，我对它不太熟悉，所以我想知道问题出在哪里:

So, I'm trying to use sqlite3 and there seems to be a problem when I run a SELECT query, I'm not too familiar with it so I was wondering where the problem is:

def show_items():
var = cursor.execute("SELECT Cost FROM Items WHERE ID = A01")
for row in cursor.fetchall():
    print(row)

当我运行这个(希望要求 ID = A01 的成本值)时，我收到错误:

When I run this (hopefully asking for a cost value where the ID = A01), I get the error:

sqlite3.OperationalError: no such column: A01

虽然我不是要求它在 A01 列中查找，但我是要求它在成本"列中查找?

Though I wasn't asking for it to look in column A01, I was asking for it to look in column 'Cost'?

推荐答案

如果要在列中查找字符串值，必须将其包裹在 ' 中，否则会被解释作为列名:

If you're looking for a string value in a column, you have to wrap it in ', otherwise it will be interpreted as a column name:

var = cursor.execute("SELECT Cost FROM Items WHERE ID = 'A01'")

---

2021-02-10 更新:

鉴于此问答受到如此多的关注，我认为值得编辑以让您的读者了解准备好的陈述.

Since this Q&A gets so much attention, I think it's worth editing to let you readers know about prepared statements.

它们不仅会帮助您避免 SQL 注入，在某些情况下甚至可能会加快您的查询速度，您将不再需要担心那些单引号引起的问题，因为 DB 库会为您处理这些问题.

Not only will they help you avoid SQL injections, they might in some cases even speed up your queries and you will no longer have to worry about those single quotes around stings, as the DB library will take care of it for you.

假设我们有上面的查询，并且我们的值 A01 存储在变量 value 中.

Let's assume we have the query above, and our value A01 is stored in a variable value.

你可以写:

 var = cursor.execute("SELECT Cost FROM Items WHERE ID = '{}'".format( value ))

作为一个准备语句，它看起来像这样:

And as a prepares statement it will look like this:

var = cursor.execute("SELECT Cost FROM Items WHERE ID = ?", (value,))

请注意，cursor.execute() 方法接受第二个参数，该参数必须是序列(可以是元组或列表).由于我们只有一个值，您可能会错过 (value,) 中的 , ，它可以有效地将单个值转换为元组.

Notice that the cursor.execute() method accepts a second parameter, that must be a sequence (could be a tuple or a list). Since we have only a single value, you might miss the , in (value,) that will effectively turn the single value into a tuple.

如果您想使用列表而不是元组，查询将如下所示:

If you want to use a list instead of a tuple the query would look like this:

var = cursor.execute("SELECT Cost FROM Items WHERE ID = ?", [value])

处理多个值时，只需确保 ? 的数量与序列中的值数量匹配:

When working with multiple values, just make sure the numer of ? and the number of values in your sequence match up:

cursor.execute("SELECT * FROM students WHERE ID=? AND name=? AND age=?", (123, "Steve", 17))

你也可以使用命名风格的参数，而不是元组或列表，你使用字典作为参数:

You could also use named-style parameters, where instead of a tuple or list, you use a dictionary as parameter:

d = { "name": "Steve", "age": 17, "id": 123 }
cursor.execute("SELECT * FROM students WHERE ID = :id AND name = :name AND age = :age", d)

这篇关于sqlite3.OperationalError:没有这样的列 - 但我不是要列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！