pyscopg2 select NULL values

Question

How do I use string substitution in Psycopg2 to handle both NULL and non-NULL values?

For example:

sql = 'SELECT * FROM table WHERE col = %s;'
possible_params = [1, None]

for x in possible_params:
    print cur.mogrify(sql,(x,))

I need the first query to look like SELECT * FROM table WHERE col = 1; And the second to be the functional equivalent of SELECT * FROM table WHERE col IS NULL;

Is there a trick? I have many columns that may be NULL or have a value so building the SQL dynamically is fairly cumbersome.

Answer 1

Every time you don't know if a parameter will be NULL you can use the following pattern:

SELECT * FROM table WHERE col = <parameter> OR (col IS NULL AND <parameter> IS NULL)

where <parameter> is your parameter.

But note that I am using the same parameter two times in a row, so you need to switch to the the dict format. The full code is:

sql = 'SELECT * FROM table WHERE col = %(p)s OR (col IS NULL AND %(p)s IS NULL)'
possible_params = [{"p":1}, {"p":None}]

for x in possible_params:
    print cur.mogrify(sql,(x,))