pythonsqlsqlitewhere-clausesqlite3-python

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

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?

Solution

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