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

Question

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.

did you know Python string functions and string formatting ? ie "and ".join(["col_a = '{col_a}'", "col_b = '{col_b}'"])and ["{name} = {{{name}}}" for name in ["col_a", "col_b"]] — furas
– furas, Commented Sep 2, 2020 at 5:10
first you should keep columns on list col[0], col[1] or in dict col["a"], col["b"] - and then you can use loops. — furas
– furas, Commented Sep 2, 2020 at 5:14

furas · Accepted Answer · 2020-09-02 05:25:49Z

1

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

answered Sep 2, 2020 at 5:25

furas

149k12 gold badges121 silver badges171 bronze badges

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

4 Comments

scott martin Over a year ago

thanks for the output. That helped.. Got one question on this. When I run the SQL cursor.execute(query) it fails. I believe it is because for col_b in the where clause it expect col_b = 'A'.. How could I alter this to pass it in the right format. Thanks..

scott martin Over a year ago

figured it out. Thanks for your help!!

furas Over a year ago

f"{key} = '{val}'"

furas Over a year ago

BTW: I used word where in code because in similar way you can use it also for order by, group by, etc.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related