Connect the Shop to a Database in Python

A list inside your code forgets everything when the app stops. A database keeps data in a file instead. From now on the shop's products live in the file shop.db, in the products table, and Python's built-in sqlite3 module lets your app talk to it.

You open a connection to the file once, when the app starts. Then db.execute(...) runs a SQL query, and .fetchall() gives every row it found, as a list. Each row is a tuple: just the values, in the order of the columns you asked for. For example, with a table books that has a title and a year:

rows = db.execute("SELECT title, year FROM books").fetchall()
# rows could be [("Dune", 1965), ("Emma", 1815)]
first = rows[0]
print(first[0], first[1])   # Dune 1965

An API answers with dictionaries, not tuples, so each row becomes a dictionary whose keys say what each value is. A cell that is empty in the database (NULL) arrives in Python as None, and JSON writes it as null.

GET /           →  200  Welcome to the shop!
GET /products   →  200  [{"id": 1, "name": "Mug", "price": 12, "category": "kitchen"},
                         {"id": 2, "name": "Notebook", "price": 7, "category": "office"},
                         … 8 products in all …
                         {"id": 8, "name": "Gift card", "price": 50, "category": null}]

Your task: connect the app to shop.db and make GET /products read the products from the products table, turning each row into a dictionary with the keys id, name, price and category.

Assignment
Follow the Coding Tutorial to open the connection, run the query and turn its rows into the list of products.

Hint
The values of a row come in the same order as the columns of your SELECT, and indexes start at 0: row[0] is the first column you named.