Dynamically search for null in sqlite select query using python

Question

I'm new to python and I want to do a similar query to this one:

_c.execute('select * from cases where bi = ? and age = ? and 
            shape = ? and margin = ? and density = ?',(obj['bi'], 
            obj['age'], obj['margin'], obj['density']))

When some of the parameters are None, for example obj['bi'] = None, the query searches for the row when bi = 'None'. But I want it to search for the row when: 'bi is NULL'

A possible solution is to verify the values of the parameters one by one in a sequence of if-elses. For example:

query = 'select * from cases where'

if obj['bi'] is None:
   query += ' bi is null'
else:
   query += ' bi = ' + str(obj['bi']) + ' and '
    ...
# do the same if-else for the other parameters
    ...

_c.execute(query)

But, it doesn't seem to me as the best solution. The question is, what is the best solution to the given problem and how to avoid SQL injections.

(CASE WHEN ?1 = 'None' THEN bi IS NULL ELSE bi = ?1 END) AND ...? — Shawn
– Shawn, Commented Nov 22, 2018 at 20:15
It's the first bound parameter. ?2 is the second, and so on. sqlite.org/lang_expr.html#varparam — Shawn
– Shawn, Commented Nov 22, 2018 at 20:20
@Shawn 3 years of using SQLite and never once encountered that. Thanks. I suggest you answer the question if you think you can solve the problem; it's not a trivial-answer-in-commments type of problem — roganjosh
– roganjosh, Commented Nov 22, 2018 at 20:22

Shawn · Accepted Answer · 2018-11-22 21:10:31Z

4

Okay, after firing up a python REPL and playing around with it a bit, it's simpler than I thought. The Python sqlite bindings turn a Python None into a SQL NULL, not into a string 'None' like it sounded like from your question. In SQL, = doesn't match NULL values, but IS will. So...

Given a table foo looking like:

a     | b
--------------
NULL  | 1
Dog   | 2

Doing:

c = conn.cursor()
c.execute('SELECT * FROM foo WHERE a IS ?', (None,))
print(c.fetchone())

will return the (NULL, 1) row, and

c.execute('SELECT * FROM foo WHERE a IS ?', ('Dog',))
print(c.fetchone())

will return the ('Dog', 2) row.

In other words, use IS not = in your query.

answered Nov 22, 2018 at 21:10

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically search for null in sqlite select query using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related