sqlite3.OperationalError: no such column – but I’m not asking for a column?

by

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)

More Related Contents:

  1. Select Query To Get Last 9 Months Data in SQLite3
  2. sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied
  3. No module named _sqlite3
  4. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  5. Force Python to forego native sqlite3 and use the (installed) latest sqlite3 version
  6. Parameter substitution for a SQLite “IN” clause
  7. Python SQLite parameter substitution with wildcards in LIKE
  8. How can I get dict from sqlite query?
  9. Sqlite3, OperationalError: unable to open database file
  10. Using a WHERE ___ IN ___ statement
  11. parameter unsupported when inserting int in sqlite
  12. How to read datetime back from sqlite as a datetime instead of string in Python?
  13. Python sqlite3 string variable in execute
  14. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  15. SQLite parameter substitution and quotes
  16. Python SQLite: database is locked
  17. Bulk insert huge data into SQLite using Python
  18. How to discover table properties from SQLAlchemy mapped object
  19. String similarity with Python + Sqlite (Levenshtein distance / edit distance)
  20. Auto Increment on Composite Primary Key – Sqlite3 + Python
  21. Is there a way to get a list of column names in sqlite?
  22. sqlite3.OperationalError: unable to open database file
  23. Being that string substitution is frowned upon with forming SQL queries, how do you assign the table name dynamically?
  24. How to upgrade sqlite 3.8.2 to >= 3.8.3
  25. creating a database outside the application context
  26. Why do you need to create a cursor when querying a sqlite database?
  27. How to open and convert sqlite database to pandas dataframe
  28. Passing SQLite variables in Python
  29. Python sqlite3 parameterized drop table
  30. Python sqlite3.OperationalError: no such table:

Leave a Comment