Python – Running a SQL in a loop based on column value

I am trying to build a SQL query that runs based on certain conditions (if a column is filled run Query 1 else Query 2 and so on) as shown below:

if len(col_a) > 0 and len(col_b) > 0: ## Both columns have value
    dwh_cursor.execute(f"""select * from table where col_a = '{col_a}' and col_b = '{col_b}'""")

elif len(col_a) > 0 and len(col_b) < 1: ## Only col_a has value
    dwh_cursor.execute(f"""select * from table where col_a = '{col_a}'""")

elif len(col_a) < 1 and len(col_b) > 0: ## Only col_b has value
    dwh_cursor.execute(f"""select * from table where col_b = '{col_b}'""")

I am trying to build something like the above but the number of fields as variable (fields like col_a and col_b) are 6 in number. Is there a way I can perform this in a loop based on the length of field, instead of having to manually have each condition explicitly mentioned as I have done above.

Answer

You should keep values on list or in dictionary and then you can use for-loop to filter empty elements.

Later you can use string functions and for-loop to convert to col = val and join() to connect these elements using word AND, etc.

where = {
    'col_a': '',   # empty so it will skip it
    'col_b': 'A',
    'col_c': '1',
}

where_filtered = {key:val for key, val in where.items() if val}

print('where filtered:', where_filtered)

where_parts = [f'{key} = {val}' for key, val in where_filtered.items()]

print('where parts:', where_parts)

where_query = ' and '.join(where_parts)

print('where query:', where_query)

query = 'SELECT * FROM table'
if where_query:
    query += ' WHERE ' + where_query
    
print('query:', query)
    

Results:

where filtered: {'col_b': 'A', 'col_c': '1'}
where parts: ['col_b = A', 'col_c = 1']
where query: col_b = A and col_c = 1
query: SELECT * FROM table WHERE col_b = A and col_c = 1