Get SQLite Keywords in Python 3.15

Column names do not always come from the code that builds the query. They can arrive from a CSV header, a JSON payload, or a form that lets users define their own fields. Some of those names are reserved words in SQL, and using one as an identifier breaks the statement.

The following code builds a CREATE TABLE statement from a list of column names, one of which is a reserved word:

import sqlite3

columns = ['id', 'name', 'order']

con = sqlite3.connect(':memory:')
# sqlite3.OperationalError: near "order": syntax error
con.execute(f'CREATE TABLE items ({", ".join(columns)})')

SQLite reads order as a keyword, not a column name, so sqlite3.OperationalError is raised.

Before Python 3.15, there was no way to ask the sqlite3 module which words are reserved. The usual workaround was to copy a list of keywords from the SQLite documentation into the project and keep it in sync with the SQLite version bundled with Python.

Since Python 3.15, the sqlite3 module provides the SQLITE_KEYWORDS constant, which holds the keywords recognized by the SQLite library that Python was built against.

import sqlite3


def quote(name):
    if name.upper() in sqlite3.SQLITE_KEYWORDS:
        return f'"{name}"'
    return name


columns = ['id', 'name', 'order']

con = sqlite3.connect(':memory:')
con.execute(f'CREATE TABLE items ({", ".join(quote(c) for c in columns)})')

print(len(sqlite3.SQLITE_KEYWORDS))  # 147
print(sqlite3.SQLITE_KEYWORDS[:5])  # ('REINDEX', 'INDEXED', 'INDEX', 'DESC', 'ESCAPE')

The quote function leaves id and name untouched and returns "order" for the reserved word. Wrapping a name in double quotes tells SQLite to treat it as an identifier instead of a keyword, so the statement becomes valid and the table is created. The keywords are stored in uppercase, so upper converts the column name before the comparison.

SQLITE_KEYWORDS is a tuple, and the keywords appear in no particular order. The number of keywords comes from the SQLite version that Python was compiled with and can differ between installations.