CODEWITHSUNDEEP
pythonsqlnull
JamieH
JamieH

Reputation: 4859

Python SQL Select with possible NULL values

I'm new to python and have hit a problem with an SQL query I'm trying to perform.

I am creating an SQL SELECT statement that is populated with values from an array as follows:

ret = conn.execute('SELECT * FROM TestTable WHERE a = ? b = ? c = ?', *values)

This works ok where I have real values in the values array. However in some cases an individual entry in values may be set to None. The query then fails because the "= NULL" test does not work since the test should be IS NULL.

Is there an easy way around this?

Upvotes: 4

Views: 15698

Answers (7)

avantdev
avantdev

Reputation: 2724

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

Upvotes: 0

tschesseket
tschesseket

Reputation: 411

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})

Upvotes: 0

Michael Hays
Michael Hays

Reputation: 6908

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.

Upvotes: 0

John Hunter
John Hunter

Reputation: 4142

If you are using SQL Server then as long as you set ANSI_NULLS off for the session '= null' comparison will work.

SET ANSI_NULLS

Upvotes: 4

MoshiBin
MoshiBin

Reputation: 3196

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) )

Upvotes: 2

Ants Aasma
Ants Aasma

Reputation: 54882

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.

Upvotes: 0

blispr
blispr

Reputation: 912

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.

Upvotes: 3

Related Questions