A database is a program that stores data and performs operations on it. Instead of treating each coffee-shop order as a loose piece of information, the application keeps orders together in a place built for storing, finding, and summarizing records. section 12.1
For a growing coffee shop, menu choices can still come from text files, but submitted orders need more dependable storage. A database lets the backend save an order, retrieve stored orders later, search using criteria, or summarize information such as the number of vanilla orders in a month. section 12.0 section 12.1
A small SQLite-backed order database works in a few clear steps:
- Connect to an orders.db database with sqlite3, then create an orders table with name, drink, flavor, and topping columns if it does not already exist. section 12.0
- Turn submitted form values into an order dictionary and insert those values into the orders table with an SQL INSERT command. section 12.0
- Commit the insertion so the new order is saved. section 12.0
- Run SELECT * FROM orders to fetch the stored rows when the application needs an order list. section 12.0
- Pass the retrieved rows to an HTML template and loop through them to display one table row per order. section 12.0
From an order form to an order list
A customer chooses a drink, flavor, and topping in a web form. The application collects those values, saves them as one row in orders, and shows the customer a confirmation page. Later, the /list route fetches every row and the template displays columns for name, drink, flavor, and topping. The frontend collects and displays information; the backend stores and retrieves it. section 12.0 section 12.1
SQL, or Structured Query Language, is the language used to work with databases. Here, SQL creates a table, adds rows with INSERT, and reads rows with SELECT. Keeping these data operations in database functions such as save_order() and get_orders() makes the web routes easier to follow. section 12.0 section 12.1
Listing 12. 3 app.py: Add orders to the table
. . .
from flask import Flask, render_template, request
def save_order(order):
con = sqlite3.connect("orders.db")
cur = con.cursor()
cur.execute(
"INSERT INTO orders(name,drink,flavor,topping) VALUES(?,?,?,?);",
(order["name"], order["drink"], order["flavor"], order["topping"]),
)
con.commit()
return
. . .
Inspect the save_order() function to see a form-derived order inserted into SQLite and committed. section 12.0
Try it yourself: add a new order through the form, open the order list, and check that the same name and choices appear in the table. Then make another order and notice that the list can show both saved records. section 12.0
