Geek asks and answers

Pyscopg2 select NULL values

How to use string replacement in Psycopg2 to handle both NULL and non-NULL values?

For instance:

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 similar to SELECT * FROM table WHERE col = 1;, and the second a functional equivalentSELECT * FROM table WHERE col IS NULL;

Is there a trick? I have many columns that can be NULL or have a value, so dynamically building SQL is rather cumbersome.

+3
source share
2 answers

Each time you do not know if there will be a parameter NULL, you can use the following template:

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

where <parameter>is your parameter.

, , dict. :

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

, :

SELECT COUNT(*) FROM table WHERE col = Null;
SELECT COUNT(*) FROM table WHERE col::TEXT = '';
SELECT COUNT(*) FROM table WHERE col IN (Null::TEXT);
SELECT COUNT(*) FROM table WHERE col IN (VALUES (NULL::TEXT));

kludgy, , :

if record['api'] is not None:
    sql += 'api = %(api)s'
else:
    sql += 'api IS NULL'
0

More articles:


All Articles