NULL vs None in Sqlite3, Python

noneandnull.py

dbpath = "/.../test.db"
>>> import sqlite3
>>> #Create the database.
>>> connection = sqlite3.connect(dbpath)
>>> cursor = connection.cursor()
>>> cursor.execute("CREATE TABLE Test (testcolumn TEXT);")
<sqlite3.Cursor object at 0x111952ce0>
>>> cursor.execute("INSERT INTO Test VALUES(NULL);")
<sqlite3.Cursor object at 0x111952ce0>

>>> #We now have exactly one entry in our table. Verify this with SELECT:
>>> cursor.execute("SELECT * FROM Test WHERE testcolumn IS NULL;")
<sqlite3.Cursor object at 0x111952ce0>
>>> 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,))
<sqlite3.Cursor object at 0x111952ce0>

>>> #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;")
<sqlite3.Cursor object at 0x111952ce0>
>>> 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 "<pyshell#8>", line 1, in <module>
    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,))
<sqlite3.Cursor object at 0x111952ce0>
>>> 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 "<pyshell#20>", line 2, in <module>
    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,))
<sqlite3.Cursor object at 0x111952ce0>
>>> for item in cursor:
	print(item)

	
(None,)
(None,)

>>> #Commit changes (if you want to).
>>> connection.commit()
>>> #Close the database.
>>> connection.close()