Queries from code

# Never:
conn.execute(f"SELECT * FROM customers WHERE business_name = '{name}'")
# Always:
conn.execute("SELECT * FROM customers WHERE business_name = ?", (name,))

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.executescript("""
CREATE TABLE customers (customer_id TEXT PRIMARY KEY, business_name TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO customers VALUES ('C0001', 'Ada Stores', 'Lagos'), ('C0002', 'Chi''s Bakery', 'Enugu');
""")

name = "Chi's Bakery"
try:
    conn.execute(f"SELECT * FROM customers WHERE business_name = '{name}'").fetchall()
except sqlite3.OperationalError as error:
    print("f-string query failed:", error)

row = conn.execute("SELECT * FROM customers WHERE business_name = ?", (name,)).fetchone()
print("Parameter query found:", row["customer_id"], row["city"])

f-string query failed: near "s": syntax error
Parameter query found: C0002 Enugu

from decimal import Decimal, ROUND_HALF_UP

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/invoicing/"
customers = pd.read_csv(base + "customers.csv")
raw = pd.read_csv(base + "invoices_raw.csv", dtype=str, keep_default_na=False).drop_duplicates()
lines = pd.read_csv(base + "invoice_lines.csv", dtype={"unit_price": str})

def to_kobo(naira):
    return int((Decimal(naira.replace("₦", "").replace(",", "")) * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP))

known = set(customers["customer_id"])
bad = set(lines.loc[lines["quantity"] <= 0, "invoice_id"])
clean = raw[raw["customer_id"].isin(known) & raw["issue_date"].str.match(r"^\d{4}-\d{2}-\d{2}
quot;)
            & (raw["discount_pct"].astype(int) <= 20) & (raw["due_date"] >= raw["issue_date"]) & ~raw["invoice_id"].isin(bad)]

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (customer_id TEXT PRIMARY KEY, business_name TEXT, city TEXT, vat_exempt INTEGER, payment_terms_days INTEGER);
CREATE TABLE invoices (invoice_id TEXT PRIMARY KEY, customer_id TEXT, issue_date TEXT, due_date TEXT, discount_pct INTEGER, paid_kobo INTEGER);
CREATE TABLE invoice_lines (invoice_id TEXT, line_no INTEGER, quantity INTEGER, unit_price_kobo INTEGER);
""")
db.executemany("INSERT INTO customers VALUES (?, ?, ?, ?, ?)", customers.itertuples(index=False))
db.executemany("INSERT INTO invoices VALUES (?, ?, ?, ?, ?, ?)",
               [(r.invoice_id, r.customer_id, r.issue_date, r.due_date, int(r.discount_pct), to_kobo(r.amount_paid)) for r in clean.itertuples()])
db.executemany("INSERT INTO invoice_lines VALUES (?, ?, ?, ?)",
               [(r.invoice_id, r.line_no, r.quantity, to_kobo(r.unit_price)) for r in lines[lines["invoice_id"].isin(clean["invoice_id"])].itertuples()])

top = db.execute("""
    SELECT c.business_name,
           COUNT(DISTINCT i.invoice_id)            AS invoices,
           SUM(l.quantity * l.unit_price_kobo)     AS subtotal_kobo
    FROM customers c
    JOIN invoices i      ON i.customer_id = c.customer_id
    JOIN invoice_lines l ON l.invoice_id = i.invoice_id
    WHERE c.city = ?
    GROUP BY c.customer_id
    ORDER BY subtotal_kobo DESC
    LIMIT 3
""", ("Lagos",)).fetchall()
for business_name, invoices, subtotal in top:
    print(f"{business_name:22} {invoices:3} invoices  ₦{subtotal / 100:,.2f} before discount and VAT")

Chinedu Stores           6 invoices  ₦1,614,343.00 before discount and VAT
Babatunde Foods          5 invoices  ₦1,531,167.80 before discount and VAT
Sade Electronics         6 invoices  ₦1,527,568.10 before discount and VAT