SQL, short for Structured Query Language, is a language for working with databases. A database can keep records such as drink orders, then answer useful questions: Which orders used vanilla? Which customers are named Alex? How much peppermint was used this month? These questions are queries. section 12.0 section 12.1
Instead of loading every order from a JSON file into a Python list and saving the whole list again, an application can let a database keep its own records in a file. For a new order, the application connects to the database and saves just that order; earlier orders remain there. section 12.0
A small SQLite workflow uses SQL through Python’s sqlite3 library:
- Connect to a database file, such as orders.db, and create a cursor for sending commands. section 12.0
- Create an orders table if it does not already exist, naming columns for name, drink, flavor, and topping. section 12.0
- Insert one submitted order with placeholders and its matching values, then commit the change. section 12.0
- Select all saved orders, fetch the rows, and pass them to a page that loops through the results. section 12.0
Saving and listing café orders
An order form collects a customer name, drink, flavor, and topping. Saving it sends an INSERT command to the orders table. A separate list page runs SELECT * FROM orders, receives every row, and displays each order in a table. The database handles keeping and retrieving the records, while the pages handle user input and display. section 12.0 section 12.1
. . .
import sqlite3 #1
. . .
con = sqlite3.connect("orders.db") #2
cur = con.cursor()
cur.execute(
"CREATE TABLE IF NOT EXISTS orders(name, drink, flavor, topping);") #3
app = Flask(name)
. . .
Inspect the connection setup and the SQL command that creates the orders table before any orders are saved. section 12.0
