All or Nothing in Python

An order changes several rows: the order itself, one row per item, and the stock of every product it buys. If an item fails halfway, the shop must not keep the first half. An order has to be all or nothing.

The database gives you this with a transaction. Python's sqlite3 opens one by itself before your first change, and every change waits in it until db.commit() saves them all at once. Its opposite, db.rollback(), throws them all away. Here is the idea on a money transfer between two accounts:

@app.post("/transfer")
def transfer():
    data = request.get_json()
    db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (data["amount"], data["from"]))
    db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (data["amount"], data["to"]))
    sender = db.execute("SELECT balance FROM accounts WHERE id = ?", (data["from"],)).fetchone()
    if sender["balance"] < 0:
        db.rollback()
        return jsonify({"error": "Not enough money"}), 409
    db.commit()
    return jsonify({"ok": True})

Both updates are saved, or neither: money never leaves one account without reaching the other. 409 Conflict is the status for this: the request is fine in itself, but it clashes with the state of the data. The rollback comes before the return, since nothing after a return runs.

Run in this order on a fresh shop, your POST /orders must give these answers:

POST /orders  {"customer_id": 5, "items": [{"product_id": 3, "quantity": 1}, {"product_id": 1, "quantity": 2}]}
    →  201  {"id": 8}
GET /products/3  →  200  {"id": 3, "name": "Lamp", "price": 29, "category": "home", "stock": 0}
POST /orders  {"customer_id": 4, "items": [{"product_id": 4, "quantity": 10}, {"product_id": 3, "quantity": 1}]}
    →  409  {"error": "Not enough stock", "product_id": 3}
GET /orders/9/items  →  404  {"error": "Order not found"}
GET /products/4  →  200  {"id": 4, "name": "Pen", "price": 2, "category": "office", "stock": 120}
POST /orders  {"customer_id": 2, "items": [{"product_id": 7, "quantity": 1}, {"product_id": 99, "quantity": 1}]}
    →  404  {"error": "Product not found", "product_id": 99}
POST /orders  {"customer_id": 4, "items": [{"product_id": 4, "quantity": 10}]}
    →  201  {"id": 9}

The second order fails on the lamp, so its pens are not saved either: there is no order 9, the pens' stock is still 120, and the next good order takes id 9.

Your task: in POST /orders, take each item's quantity off its product's stock. Answer 409 when there isn't enough stock and 404 when the product doesn't exist, and roll back before both answers, so a failed order leaves nothing behind.

Assignment
Follow the Coding Tutorial to update the stock, check it before each item, and roll back every order that can't be completed.

Hint
Both checks go right below the line that reads product, before anything is written for that item. The None check comes first: when there is no product, product["stock"] crashes.