presto - Python SQL formatting -- omitting one where clause based on conditions

Question

I would need to write a SQL question based on conditions: in Condition 1:

SELECT
    *
FROM
    table_1
WHERE
    col_1 IS NULL
    AND col_2 IS NOT NULL

and in Condition 2:

SELECT
    *
FROM
    table_1
WHERE
    col_1 IS NULL

How would I be able to achieve this easily in Python? I know I can do filters later on but that's not super efficient as it should be.

question from:https://stackoverflow.com/questions/65901348/python-sql-formatting-omitting-one-where-clause-based-on-conditions

Answer 1

The solution used in many tools: Make initial query with dummy TRUE WHERE clause, then depending on conditions can be concatenated with additional filters like this (simplified code):

query = 'select * from table where 1 = 1' # WHERE with dummy TRUE condition
                                          # it can be WHERE TRUE

condition1 = True; # if both conditions are False, query will be without filters
condition2 = True;

filter1='Col1 is not null'
filter2='Col2 is not null'

if condition1:
    query = query+' and '+ filter1

if condition2:
    query = query+' and '+ filter2

print(query)

Result:

select * from table where 1 = 1 and Col1 is not null and Col2 is not null

More elegant solution using pypika - python query builder. You can build the whole query including table, fields, where filters and joins:

from pypika import Query, Table, Field

table = Table('table')

q = Query.from_(table).select(Field('col1'), Field('col2'))                                          

condition1 = True;
condition2 = True;


if condition1:
    q = q.where(
    table.col1.notnull()
)

if condition2:
    q = q.where(
    table.col2.notnull()
)

print(q)

Result:

SELECT "col1","col2" FROM "table" WHERE NOT "col1" IS NULL AND NOT "col2" IS NULL