Python SQL Select with possible NULL values

Question

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

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

Answer 2

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.