python - sqlite3.OperationalError: no such column - but I'm not asking for a column?

Question

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)

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

sqlite3.OperationalError: no such column: A01

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

Answer 1

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'")

---

Update 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.

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.

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

You could write:

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

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)