pyscopg2 select NULL values

postgresql, postgresql-9.2, psycopg2, python

Solution

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

Problem

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.

Original source