Force Python-SQLite3 to report non-existing columns in WHERE clause

Question

This is my code:

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE schools (
    county TEXT,
    statustype TEXT
)
''')

cursor.execute('''
INSERT INTO schools VALUES ('some_county', 'some_status')
''')


cursor.execute('''
SELECT COUNT(*) FROM schools 
WHERE county = 'Alpine' 
  AND statustype IN ('Active', 'Closed') 
  AND "School Type" = 'District Community Day Schools'
''')

result = cursor.fetchone()[0]
print(f"Count result: {result}")

conn.close()

Note that there is intentionally no 'School Type' column in the database schema in this example.

The result is 0.

Is possible to change some settings of SQLite3 or of the database in order to get an error about non-existing column instead?

Dale K · Accepted Answer · 2025-06-23 09:06:44Z

2

SQLite has a quirk when it comes to delimiting identifiers. In ANSI SQL double quoting an column name does what you expect i.e. delimits the column name. However in SQLite you get the following behaviour (from the docs)

... in an effort to be compatible with MySQL 3.x (which was one of the most widely used RDBMSes when SQLite was first being designed) SQLite will also interpret a double-quotes string as string literal if it does not match any valid identifier. This misfeature means that a misspelled double-quoted identifier will be interpreted as a string literal, rather than generating an error. It also lures developers who are new to the SQL language into the bad habit of using double-quoted string literals when they really need to learn to use the correct single-quoted string literal form.

Therefore, because the column doesn't exist, you end up comparing strings to strings e.g. "School Type" = 'District Community Day Schools' which is always false.

Therefore you need to delimit the column name using either square brackets ([]) or backticks (``).

e.g. you should use [column name here] or `column name here` after which you'll get an error as such:

sqlite3.OperationalError: no such column:

Of course in general its actually better to not use spaces in the first place (eg. school_type).

Note: The same documentation goes on to say:

As of SQLite 3.29.0 (2019-07-10) the use of double-quoted string literals can be disabled at run-time...

Which would seem to be a good practice in order to avoid this sort of confusion.

edited Jun 23 at 9:06

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 23 at 8:05

Kenzo Staelens

7238 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Kenzo Staelens Jun 23 at 8:15

weird, because this query

SELECT COUNT(*) FROM schools WHERE county = 'Alpine' AND statustype IN ('Active', 'Closed') AND "District Community Day Schools" = "District Community Day Schools"

does in fact return a result (given an entry in the table with country = alpine, status = active) `

Stanislav Kikot Jun 23 at 8:16

using ` School Type` gives the required error message

Kenzo Staelens Jun 23 at 8:17

@DaleK not in python IDLE :/

Kenzo Staelens Jun 23 at 8:21

@DaleK also apparently works in python's sqlite3 module ¯\_(ツ)_/¯

Kenzo Staelens Jun 23 at 8:24

@DaleK idle basically just default python shell docs.python.org/3/library/idle.html ; no comment on the dialects though; also as Stanislav mentioned import sqlite3 ...

|

Collectives™ on Stack Overflow

Force Python-SQLite3 to report non-existing columns in WHERE clause

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related