PL/Python. How to use None type at where clouse for integer?

So, I have a query the same as below:

query = plpy.prepare(
    "SELECT count(id) FROM contracts WHERE dependent_id = $1",  
    ["integer"])

params = [dependent_id]

plpy.execute(query, params)

If my field dependent_id isn't None, it works well. But, if I have a None at this field, query is getting work incorrect. Because = isn't the same as IS when you compare with NULL

I tried to use if-else expression to correct my query string, but when query contains IS, I catch the syntax exception.

After I'd logged my query with IS keyword (when my field is None), I executed it in a PSQL shell, and it had worked well.

So, why IS doesn't work at the plpythonu stored procedure, but still works at the native SQL query?

UPDATE:

I had a discussion with DBA at my work, and he thinks it's bug in plpythonu with type conversion. Because plpy.execute rising exception when we trying to use IS with integer type.

After you've declared field as integer and used IS in query at prepare step, you will get syntax exception on execute step.

Workaround is query string with obvious indication your None field as IS NULL or IS NOT NULL

P.S. Sorry for my English.

Solution

You may try to prepare two separate SQL statements to deal with Python's dynamic typing:

query = plpy.prepare("SELECT count(id) FROM contracts WHERE dependent_id = $1",  ["integer"]) if dependant_id is not None else plpy.prepare("SELECT count(id) FROM contracts WHERE dependent_id IS NULL",  [])

if dependant_id is None:
   plpy.execute(query, [])
else:
   plpy.execute(query, [dependant_id])