How to check if value is in a list or if the list is empty?

Question

I'm using psycopg2 to access a PostgreSQL database through Python 3, and I'm attempting to make a query where I want to select all users whose name are in a list, if the list is not empty. If the provided list is empty, I want to ignore the condition, i.e. select all users regardless of their name.

I've already tried the following three calls:

# Using list
cursor.execute(
    "SELECT age FROM user WHERE %(names) = '{}' OR user.name IN %(names)s",
    {'names': []},
)

# Using tuple
cursor.execute(
    "SELECT age FROM user WHERE %(names) = () OR user.name IN %(names)s",
    {'names': ()},
)

# Using both list and tuple
cursor.execute(
    "SELECT age FROM user WHERE %(names_l) = '{}' OR user.name IN %(names_t)s",
    {'names_l': [], 'names_t': ()},
)

But they all raise an invalid syntax error from one point or another:

# Using list
psycopg2.ProgrammingError: syntax error at or near "'{}'"
LINE 17:         user.name IN '{}'

# Using tuple
psycopg2.ProgrammingError: syntax error at or near ")"
LINE 16:         () == ()

# Using both list and tuple
psycopg2.ProgrammingError: syntax error at or near ")"
LINE 17:         user.name IN ()

Clodoaldo Neto · Accepted Answer · 2017-01-26T12:54:07.390

For optional parameters you want a SQL where clause like:

where column = :parameter or :parameter is null

With the above when the parameter is null all rows will be returned otherwise only those meeting the condition.

Psycopg adapts a Python list to a Postgresql array. To check if any of the Postgresql array values is equal to a certain value:

where column = any (array[value1, value2])

To get a Python None, which is adapted to a Postgresql null, from an empty Python list:

parameter = [] or None

Passing a dictionary to the cursor.execute method avoids parameter repetition in the parameters argument:

names = ['John','Mary']

query = """
    select age
    from user
    where user.name = any (%(names)s) or %(names)s is null
"""
print (cursor.mogrify(query, {'names': names or None}).decode('utf8'))
#cursor.execute(query, {'names': names or None})

Output:

select age
from user
where user.name = any (ARRAY['John', 'Mary']) or ARRAY['John', 'Mary'] is null

When the list is empty:

select age
from user
where user.name = any (NULL) or NULL is null

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

I feel really silly now, didn't realize I could use SQL's `NULL` even though I did know it exists and all... But this worked great, thanks for the help! :) — Markus Meskanen, Jan 26 '17 at 16:34

How to check if value is in a list or if the list is empty?

1 Answers1