1

I'm working on a database using sqlite3 for Python that stores addresses. I understand that you use ? as a placeholder and you give the execute method a tuple or list of values to fill the placeholders. I am also aware that None should correspond to sqlite's NULL value.

However, if I try to SELECT from the table like so, even though this entry does exist in the table, it does not fetch any values:

cursor.execute('SELECT * FROM test WHERE (address, apartment, city, state) = (?, ?, ?, ?);',
               ('123 Front St', None, 'Springfield', 'WA'))

However, if the apartment is not a NULL value, and I give it a regular old string for the apartment, it will fetch it just fine.

What am I doing wrong?

inappropriately optimized
  • 100
  • 8

1 Answers1

2

You could rewrite query to be NULL safe:

-- ISO-standard
SELECT * 
FROM test 
WHERE address IS NOT DISTINCT FROM ?
  AND apartment IS NOT DISTINCT FROM ?
  AND city IS NOT DISTINCT FROM ?
  AND state US NOT DISTINCT FROM ?;

-- SQLite
SELECT * 
FROM test 
WHERE (address = ? OR ? IS NULL)
  AND (apartment = ? OR ? IS NULL)
  ...

In SQL world col = NULL yields Unknown which is different than true. Related: what is “=null” and “ IS NULL

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • This produces a `sqlite3.OperationalError: near "DISTINCT": syntax error`. But I see that I have to use `col IS NULL` instead of `=`. Looks like I'll just have to generate one query or the other depending on whether the apartment is empty. – inappropriately optimized Jun 25 '19 at 22:02
  • 1
    Thanks for the edit, the sqlite version works correctly. However, I'll probably still decide to dynamically generate the query. Having to duplicate each of the placeholder values in the tuple seems clunkier to my brain than having just one query or another. – inappropriately optimized Jun 25 '19 at 22:11