Python SQL Select with possible NULL values

Use : "Select * from testtable where (a = ? or a is null) and (b=? or b is null) "

This will select cases where a exactly matches the supplied value and will include the null values in the column - if that is what you want.

You can always use the ternary operator to switch '=' for 'IS':

("=","IS")[var is None]

Would return "IS" if var is None and "=" otherwise.

It's not very elegant to do this in one line though, but just for demonstrating:

query = "SELECT * FROM testTable WHERE a %s %s" % ( ("=","IS")[a is None], str(a) )

If you're feeling adventurous, you could also check out SQLAlchemy. It provides amongst a lot of other things an SQL construction toolkit that automatically converts comparisons to None into IS NULL operations.

First and foremost, I would strongly caution you against using SELECT * FROM tbl WHERE (col = :x OR col IS NULL) as this will most likely disqualify your query from indexing (use a query profiler). SET ANSI_NULLS is also one of those things that may not be supported in your particular database type.

A better solution (or at least a more universal solution) is, if your SQL dialect supports Coalesce, to write your query like this:

SELECT * FROM tbl WHERE col = COALESCE(:x, col)

Since col = col will always evaluate to true, passing in NULL for :x won't damage your query, and should allow for a more efficient query plan. This also has the advantage that it works from within a stored procedure, where you may not have the liberty of dynamically building a query string.

The following was tested on python sqlite3 by now, however it should work in other DB types since it quite general. The approach is the same to @MoshiBin answer with some additions:

Here is the form using cursor.execute() regular syntax, so the null variables is not supported while using this form:

import sqlite3
conn = sqlite3.connect('mydbfile.db')
c = conn.cursor()
c.execute('SELECT * FROM TestTable WHERE colname = ?', (a, ))

In order to support null variables you may replace the 4th line to:

request = 'SELECT * FROM TestTable WHERE colname %s' % ('= ' + str(a) if a else 'IS NULL')
c.execute(request)

Besides that if the variable is in text type, you also need to include a quotes:

request = "SELECT * FROM TestTable WHERE colname %s" % ("= '" + a + "'" if a else 'IS NULL')

Finaly if a variable can contain a single quotes itself, you also need to escape it by doubling:

request = "SELECT * FROM TestTable WHERE colname %s" % ("= '" + a.replace("'", "''") + "'" if a else 'IS NULL')

Edit:
Lately I have found two other approaches that also can be used in this case and uses regular cursor.execute() syntax, however I did't test this ones by now:

c.execute('SELECT * FROM TestTable WHERE colname = :1 OR (:1 IS NULL AND colname IS NULL)', {'1': a})

(Thx to @BillKarwin answer)

or, using the CASE expression:

c.execute('SELECT * FROM TestTable WHERE CASE :1 WHEN NOT NULL THEN colname = :1 ELSE colname IS NULL END', {'1': a})

It worked on my end.

with mysqlcon.cursor() as cursor:
    sql = "SELECT * \
            FROM `books` \
            WHERE `date` is NULL"
    cursor.execute(sql)
    books = cursor.fetchall()

    return books