NULL vs None in sqlite3 for Python

The sqlite3 library in Python accepts Python data types for data insertion. This table in the docs shows the type conversion for data when transferred between SQLite and Python.

NULL vs None

SQLite automatically accepts NULL (and null) since it is a reserved keyword in. It cannot, however, be accepted as input via parameter substitution. We build a database and explain the distinctions in the following gist:

dbpath = "/.../test.db"
>>> import sqlite3
>>> connection = sqlite3.connect(dbpath)
>>> cursor = connection.cursor()
>>> cursor.execute("CREATE TABLE Test (testcolumn TEXT);")

>>> cursor.execute("INSERT INTO Test VALUES(NULL);")


>>> #We now have exactly one entry in our table. Verify this with SELECT:
>>> cursor.execute("SELECT * FROM Test WHERE testcolumn IS NULL;")

>>> for item in cursor:
print(item)


(None,)
>>> #One entry. Note that it is returned as None -- in a "Python context".
>>> #Hence NULL (in SQL-world) is handled as None in Python-world.


>>> #To insert None, you must use parameter substitution since there is no None
>>> #in SQL.
>>> cursor.execute("INSERT INTO Test VALUES(?);", (None,))


>>> #None was substituted by NULL and will be listed as NULL within the database.
>>> #We show this with a query which returns 3 NULLs (in Python-world, they are Nones).
>>> cursor.execute("SELECT * FROM Test WHERE testcolumn IS NULL;")

>>> for item in cursor:
print(item)


(None,)
(None,)


>>> #It follows that since there is no NULL in Python, you cannot insert
>>> #NULL via a substituted value.
>>> cursor.execute("INSERT INTO Test VALUES(?);", (NULL,))
Traceback (most recent call last):
File "", line 1, in
cursor.execute("INSERT INTO Test VALUES(?);", (NULL,))
NameError: name 'NULL' is not defined
>>> #Unless, of course, you defined the variable like this:
>>> #NULL = None
>>> #Then parameter substitution will work.

>>> #So when used with parameter substition, Python's None always becomes
>>> #SQL's NULL
>>> cursor.execute("SELECT * FROM Test WHERE testcolumn IS ?;", (None,))

>>> for item in cursor:
print(item)


(None,)
(None,)


>>> #More evidence - we cannot say "SELECT... WHERE testcolumn IS None;"
>>> cursor.execute("SELECT * FROM Test WHERE testcolumn IS None;")
Traceback (most recent call last):
File "", line 2, in
cursor.execute("SELECT * FROM Test WHERE testcolumn IS None;")
sqlite3.OperationalError: no such column: None

>>> #Again, we have to substitute None:
>>> cursor.execute("SELECT * FROM Test WHERE testcolumn IS ?;", (None,))

>>> for item in cursor:
print(item)


(None,)
(None,)