Insert Many at Once

A supplier sends the shop a list of new products. One POST /products per product costs one request, one query and one commit each, and the commit is the slow part: it waits until the disk has really stored the data. A batch sends the whole list in one request, and the shop saves it with one commit.

executemany: a database connection can run one SQL statement once for each item of a list, all in a single call. Each item is a tuple, with its values in the order of the ? marks. Here it adds two lines to order 4 at once, 2 mugs and 1 lamp:

items = [(4, 1, 2), (4, 3, 1)]
db.executemany("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)", items)
db.commit()

All or nothing: if one row is refused, like a product with no price in a NOT NULL column, the database raises sqlite3.IntegrityError when it reaches that row. The rows before it are inserted but not saved yet: they wait in the open transaction, and db.rollback() throws them all away. The client can fix its list and send it again without making doubles.

GET /products  →  200  [{"id": 1, "name": "Mug", "price": 12}, …, {"id": 8, "name": "Gift card", "price": 50}]
POST /products/batch  [{"name": "Cup", "price": 8}, {"name": "Bowl", "price": 11}, {"name": "Vase", "price": 18}]
    →  201  {"added": 3}
GET /debug/queries  →  200  {"queries": 2}   (1 for the first GET /products, 1 for the whole batch)
GET /products  →  200  [… the 8 products …, {"id": 9, "name": "Cup", "price": 8}, {"id": 10, "name": "Bowl", …}, {"id": 11, "name": "Vase", …}]
POST /products/batch  [{"name": "Tray", "price": 6}, {"name": "Clock"}]
    →  400  {"error": "Each product needs a name and a price"}
GET /products/12  →  404  {"error": "Product not found"}

Your task: write a wrapper run_many(sql, rows) that counts one query and calls db.executemany. Then add POST /products/batch: turn the JSON list into (name, price) tuples, insert them all with one run_many call, roll back and answer 400 if a row is refused, and otherwise commit once, clear the cache and answer {"added": …} with 201.

Assignment
Follow the Coding Tutorial to add run_many and the POST /products/batch route.

Hint
Read each value with item.get("name") and item.get("price"): a missing price becomes None, and the database refuses it for you. Put db.commit() after the try/except, so it runs only when every row went in.