CODEWITHSUNDEEP
pythonsqlitenullisnull
Chickenmarkus
Chickenmarkus

Reputation: 1161

Using SQLite parameter substitution for values as well as NULL

I would like to find the rows which fulfill certain conditions with their values. The values and, hence, the parameters for filtering can be a real number or NULL.

As an example, consider the following database:

import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.executescript("CREATE TABLE tab (value INTEGER); INSERT INTO tab VALUES (NULL), (42);")

Now, I am looking for a parameter substitution variant which is able to handle NULL as well as non-NULL values.

print(c.execute("SELECT * FROM tab WHERE value=?", (42,)).fetchall())
--> [(42,)]
print(c.execute("SELECT * FROM tab WHERE value=?", (None,)).fetchall())
--> []

As can be seen, the latter one does not work. Do I have to check the values for None for all parameters I use for filtering in order to use the IS NULL syntax?

print(c.execute("SELECT * FROM tab WHERE value IS NULL").fetchall())
--> [(None,)]

Upvotes: 1

Views: 945

Answers (1)

CL.
CL.

Reputation: 180070

In SQLite, the IS operator also handles non-NULL values:

c.execute("SELECT * FROM tab WHERE value IS ?", (anything,))

Upvotes: 3

Related Questions