A Small Query Helper in Python

Look at your routes. Almost every one runs db.execute(...), takes .fetchall() or .fetchone(), and turns the rows into dictionaries with dict(row). Every route types the same moves again. A helper is a small function that makes those moves once, so each route gets its rows in one line.

Default values: a parameter can have a default, written with = in the def line. A call can leave it out, or give it by name. For example, here is a helper for error answers:

def error(message, status=400):
    return jsonify({"error": message}), status

error("Price must be a number")        # status keeps its default, 400
error("Order not found", status=404)   # status given by name

Your helper, query(sql, params=(), one=False), runs the SQL and answers a list of dictionaries. With one=True it answers only the first dictionary, or None when no row matched, as fetchone() did. In this lesson the check also counts how many times each request calls your helper (/check/calls), and it calls the helper itself (/check/helper):

GET /products?category=kitchen  →  200  {"products": [{"id": 1, "name": "Mug", …}, {"id": 6, "name": "Teapot", …}], "total": 2, "next_page": null}
GET /check/calls                →  200  {"query_calls": 1}
GET /products?name=Sofa         →  200  {"products": [], "total": 0, "next_page": null}
GET /products/3                 →  200  {"id": 3, "name": "Lamp", "price": 29, "category": "home", "stock": 1, "discount": 0}
GET /check/calls                →  200  {"query_calls": 1}
GET /products/99                →  404  {"error": "Product not found"}
GET /check/helper               →  200  {"many": [{"id": 2, "name": "Notebook"}, {"id": 4, "name": "Pen"}, {"id": 7, "name": "Stapler"}], "empty": [], "one": {"name": "Eva Rossi", "city": "Rome"}, "missing": null, "no_params": {"customers": 5}}

Your task: below migrate(), write the helper query. It runs the SQL with its params and answers a list of dictionaries. With one=True, it answers the first dictionary, or None. Then make get_products and get_product get their rows from it, in one line each.

Assignment
Follow the Coding Tutorial to write the query helper and use it in get_products and get_product.

Hint
Always build the full list of dictionaries first. Only then, if one is true, take out the first one. Remember that result[0] on an empty list is an IndexError, so return None in that case.