Practice: kiosk ledger

from pathlib import Path
Path("cities.csv").write_text("id,name\n1,Nairobi\n2,Mombasa\n3,Kisumu\n", encoding="utf-8")
Path("sales.csv").write_text("city_id,units,price\n1,12,10.5\n2,9,22.0\n3,2,31.0\n1,7,10.5\n", encoding="utf-8")
print("seeded")

import csv
import sqlite3
from pathlib import Path

con = sqlite3.connect("kiosk.db")
con.execute("DROP TABLE IF EXISTS sales")
con.execute("DROP TABLE IF EXISTS cities")
con.execute("CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("CREATE TABLE sales (city_id INTEGER, units INTEGER, price REAL)")
with Path("cities.csv").open(encoding="utf-8", newline="") as handle:
    for row in csv.DictReader(handle):
        con.execute("INSERT INTO cities VALUES (?, ?)", (int(row["id"]), row["name"]))
with Path("sales.csv").open(encoding="utf-8", newline="") as handle:
    for row in csv.DictReader(handle):
        con.execute("INSERT INTO sales VALUES (?, ?, ?)", (int(row["city_id"]), int(row["units"]), float(row["price"])))
print(con.execute(
    "SELECT c.name, SUM(s.units), ROUND(SUM(s.units * s.price), 2) "
    "FROM sales s JOIN cities c ON c.id = s.city_id GROUP BY c.name ORDER BY c.name"
).fetchall())
Path("kiosk.sql").write_text("\n".join(con.iterdump()) + "\n", encoding="utf-8")
print("wrote kiosk.sql", Path("kiosk.sql").stat().st_size, "bytes")
con.commit()
con.close()