Project: Customers in Python

Time to put the module together. The shop wants a small API for its customers. GET /customers lists them, and GET /customers/<id> shows one of them, with how many orders they placed and how much they spent. Your query helper does the talking to the database.

Numbers worked out from tables: the order count and the money spent are not stored anywhere. Every request works them out from orders joined with order_items, with COUNT and SUM, so they are always up to date. Watch for two traps. A JOIN gives one row per item, so count orders with COUNT(DISTINCT ...). SUM of no rows is NULL, so wrap it in COALESCE, which gives the first of its values that isn't NULL. For example, here is a route for the stock of one category:

@app.get("/categories/<name>/stock")
def get_category_stock(name):
    sql = "SELECT COUNT(*) AS products, COALESCE(SUM(stock), 0) AS in_stock FROM products WHERE category = ?"
    totals = query(sql, (name,), one=True)
    return jsonify(totals)

A category with no products gets "in_stock": 0, not null. Notice the variable name sql. In a function that calls your helper, a variable named query would hide the helper, and query(...) would fail. Your two routes must answer:

GET /customers     →  200  [{"id": 1, "name": "Ana Pop", "city": "Cluj"}, {"id": 2, "name": "Ben Ortiz", "city": "Madrid"}, …, {"id": 5, "name": "Eva Rossi", "city": "Rome"}]
GET /customers/1   →  200  {"id": 1, "name": "Ana Pop", "email": "ana@example.com", "city": "Cluj", "joined": "2026-01-10", "orders": 2, "spent": 75}
GET /customers/2   →  200  {"id": 2, "name": "Ben Ortiz", …, "orders": 1, "spent": 29}
GET /customers/3   →  200  {"id": 3, "name": "Chloe Ng", …, "orders": 2, "spent": 89}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 0, "spent": 0}
GET /customers/99  →  404  {"error": "Customer not found"}
POST /orders {"customer_id": 5, "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 3}]}  →  201  {"id": 8}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 1, "spent": 30}

Your task: at the end of the file, add two routes. GET /customers answers the id, name and city of every customer. GET /customers/<int:customer_id> answers the whole customer plus orders and spent (cancelled orders don't count), or a 404 when no customer has that id.

Assignment
Follow the Coding Tutorial to build the two customer routes on top of your query helper.

Hint
Read the customer with one=True and check for None first. The totals also come back as one row, because COUNT and SUM turn all the rows into one. Copy its "orders" and "spent" into the customer's dictionary before you return it.