Applying dynamic filters to a SQL query using Python conditional expressions

Question

I wrote this piece of code to eliminate the need for multiple if then statements based on the filters from the incoming REST request.

If the request has category in it, then the query has to be filtered by category, the same for product_location. I wrote a single statement instead of having to check for the filters and have multiple queries.

If the product_category is equal to All Category, then a filter need not be applied, etc.

query = \
 'select AVG(stock_date - order_date) as c,product_category from data_table_data where stock_date between :s1 and :s2 ' \
 + (('and product_category = :category ' if category
 != 'All Category' else '')) + (('and product_location = :loc '
 if store_location != 'All Stores' else '')) \
 + 'group by product_category'
data = engine.execute(text(query), s1=startDate, s2=endDate,
 category=category, loc=store_location).fetchall()

Answer 1

The code in the post seems fine. I think it would be easier to read if you kept the lines to 79 columns or less, put each conditional in its own statement, and made the field names match the query parameter names, like this:

query = """
 SELECT AVG(stock_date - order_date) AS c, product_category
 FROM data_table_data
 WHERE stock_date BETWEEN :stock_date_start AND :stock_date_end
"""
if category != 'All Category':
 query += "AND product_category = :product_category "
if store_location != 'All Stores':
 query += "AND product_location = :product_location "
query += "GROUP BY product_category"
params = dict(stock_date_start=startDate,
 stock_date_end=endDate,
 product_category=category,
 product_location=store_location)
data = engine.execute(text(query), **params).fetchall()

Alternatively, you could push the conditional down into the SQL, and then the query would be constant:

QUERY = """
 SELECT AVG(stock_date - order_date) AS c, product_category
 FROM data_table_data
 WHERE stock_date BETWEEN :s1 AND :s2
 AND product_category IN ('All Category', :product_category)
 AND product_location IN ('All Stores', :product_location)
 GROUP BY product_category
"""

• HackToHell: Thanks, have separated out the conditional filters as you suggested :)