CODEWITHSUNDEEP
pythonsqlpython-3.xpostgresqlpsycopg2
Teacher Mik
Teacher Mik

Reputation: 176

Python & Psycopg2 | Dynamic Query with varying WHERE clauses

I am trying to create a function that executes a query based on the arguments that are passed to it.

def sql_query(filter1, filter2, filter3):
    with ConnectionPool() as cursor:
        cursor.execute('''SELECT * FROM table 
        WHERE filter1 = %s AND filter2 =%s AND filter3 = %s;'''
        cursor.fetchall()

But whenever either of the filters is None, then I do not want that parameter to be part of the SQL query.

For example, if ONLY filter 1 is used then I would want the query to become:

def sql_query(filter1, filter2, filter3):
    with ConnectionPool() as cursor:
        cursor.execute('''SELECT * FROM table 
        WHERE filter1 = %s;'''
        cursor.fetchall()

How to do this such that the query is generated dynamically?

Upvotes: 4

Views: 2378

Answers (1)

Parfait
Parfait

Reputation: 107652

Consider COALESCE as Python's None translates to SQL's NULL. Below method now uses optional args that default to None:

def sql_query(filter1=None, filter2=None, filter3=None):
    with ConnectionPool() as cursor:
        sql = '''SELECT * FROM table 
                 WHERE filter1 = COALESCE(%s, filter1) 
                   AND filter2 = COALESCE(%s, filter2) 
                   AND filter3 = COALESCE(%s, filter3);
              '''
        cursor.execute(sql, (filter1, filter2, filter3))
        cursor.fetchall()

Upvotes: 8

Related Questions