Python psycopg2 dynamic where clause with optional AND based on conditional

Question

This probably isn't a new question but I have not been able to find a decent solution after googling and digging through SO for a while. I want to have a conditional AND added to my WHERE clause based on if a variable is set or not.

I could put in a ternary into the string format but that does not seem like a good solution.

def run_query(start_date, end_date, value):
    query = """
    SELECT * FROM table WHERE value = %(value)s {date_clause}""".format(date_clause = "AND start_date >= %(start_date)s AND end_date <= %(end_date)s" if start_date is not None and end_date is not Null else "")

cursor.execute(query, {"start_date": None, "end_date": None, "value": value})

klin · Accepted Answer · 2018-12-22 22:35:42Z

1

You can use the Postgres function COALESCE()

query = """
SELECT * 
FROM my_table 
WHERE column = %(value)s 
AND otherColumn = COALESCE(%(otherValue)s, '')

"""

When otherValue is None then will be converted to Postgres NULL and COALESCE() return an empty string.

answered Dec 22, 2018 at 22:35

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Justin Hamade Over a year ago

thanks forgot about COALESCE I don't think that would work if my conditional check is dates though, not sure if an empty string would work. Edited my post to show what I ended up doing. I am looking into pyscopg2 sql.SQL composition as well initd.org/psycopg/docs/sql.html#module-psycopg2.sql

mjuopperi Over a year ago

Be aware that this can be extremely slow when you have more data: stackoverflow.com/questions/6426436/…

Collectives™ on Stack Overflow

Python psycopg2 dynamic where clause with optional AND based on conditional

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related