Python SQL Select with possible NULL values

Question

Hi,

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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