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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…