Filters in SQL

Searching by name was one optional filter. A shop needs more: ?max_price=10, ?category=office, or several at once, joined with &. A request can send any mix of them, so no single fixed query fits: you build the query piece by piece.

Keep two lists side by side. conditions holds the pieces of the WHERE, each with a ?, and params holds the values for those ?, in the same order. The visitor's text only ever goes into params, so the query stays safe. At the end, " AND ".join(...) glues the pieces with AND between them:

pieces = ["year >= ?", "author = ?"]
print(" AND ".join(pieces))   # year >= ? AND author = ?
print(" AND ".join([]))       # an empty string

Read max_price with type=int to get a number, and test every filter with is not None: a maximum price of 0 is a real filter, but 0 counts as false in an if.

GET /products?max_price=10                  →  200  [{"id": 2, "name": "Notebook", …}, {"id": 4, "name": "Pen", …}, {"id": 7, "name": "Stapler", …}]
GET /products?category=home                 →  200  [{"id": 3, "name": "Lamp", …}, {"id": 5, "name": "Plant", …}]
GET /products?category=office&max_price=8   →  200  [{"id": 2, "name": "Notebook", …}, {"id": 4, "name": "Pen", …}]
GET /products?name=Teapot&category=kitchen  →  200  [{"id": 6, "name": "Teapot", "price": 22, "category": "kitchen", "stock": 4}]
GET /products?name=Lamp&max_price=20        →  200  []
GET /products?max_price=0                   →  200  []
GET /products?category=x' OR '1'='1         →  200  []
GET /products                               →  200  [… all 8 products …]

Your task: next to name, let GET /products take the optional filters max_price (products costing at most that much) and category. Build the WHERE from the filters the request really sends, with a ? placeholder for each value.

Assignment
Follow the Coding Tutorial to read the new filters, collect the conditions and their values, and run the query you built.

Hint
Add a condition and its value in the same if, so the two lists stay in step. Add " WHERE " only when the list of conditions isn't empty.