Today is Friday and we discuss careers in computing: https://www.youtube.com/watch?v=nUDcV136z1w

At the start of every class, please:

Make sure you are working from your programming folder
Make sure your python virtual environment is activated
Make sure you can access our test template at 127.0.0.1:5000

Part A

Please install extension SQLite Viewer by Florian Klampfer

Please copy and paste the latest app.py into Visual Studio Code. The latest file can be found here: https://github.com/bmackenty/25-26-9th-grade/blob/main/04-school-store/app.py

What is a database here?

We use SQLite. Everything is stored in one file: school_store.db.

Data is organized in tables (like spreadsheets).

• users (who can log in)

• categories (product groups)

• products (what we sell)

• sales (each checkout)

• sale_items (line items inside a sale)

Primary keys and foreign keys

• A primary key is a row’s unique ID (e.g., products.id).

• A foreign key is a pointer to a row in another table (e.g., sale_items.sale_id points to sales.id).

• Foreign keys are safety rails so the database doesn’t allow “nonsense links.”

CRUD

• Create (INSERT) — add a product or a sale

• Read (SELECT) — show inventory or stats

• Update (UPDATE) — edit a product or reduce stock after a sale

• Delete (DELETE) — rarely used here

JOIN (idea only)

• JOIN lets us read from two tables at once (e.g., product + category name).

Part B — Quick checks (2 minutes)

• Which table stores one row per checkout?

• Which table stores items inside a sale?

• What do foreign keys do?

Part C — Four tiny, DB-focused additions

Pre-flight (start of class)

• Work from your programming folder.

• Activate your virtual environment.

• cd 25-26-9th-Grade/04-school-store

• Run python app.py

• Visit http://127.0.0.1:5000 and log in (admin/admin123 by default).

Task 1) Enforce — Turn on foreign key safety rails

Where: In get_db_connection() (add one line).
Edit:

def get_db_connection():
    """Create a database connection."""
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    # Enforce foreign key rules on every connection
    conn.execute("PRAGMA foreign_keys = ON;")
    return conn

Add a tiny check route (anywhere above if name == 'main':):

@app.route('/db/foreign_keys')
def db_foreign_keys():
    conn = get_db_connection()
    row = conn.execute("PRAGMA foreign_keys;").fetchone()
    conn.close()
    return jsonify({"foreign_keys_enforced": bool(row[0])})

Done when: Visit /db/foreign_keys → {"foreign_keys_enforced": true}.

Task 2) Read — Simple database counts (how many rows?)

Where: Add this route above if name == 'main':.
Edit:

@app.route('/db/stats')
def db_stats():
    if not session.get('user_id'):
        return jsonify({"error": "login required"}), 401
    conn = get_db_connection()
    stats = {
        "users": conn.execute("SELECT COUNT(*) FROM users").fetchone()[0],
        "categories": conn.execute("SELECT COUNT(*) FROM categories").fetchone()[0],
        "products": conn.execute("SELECT COUNT(*) FROM products").fetchone()[0],
        "sales": conn.execute("SELECT COUNT(*) FROM sales").fetchone()[0],
        "sale_items": conn.execute("SELECT COUNT(*) FROM sale_items").fetchone()[0],
    }
    conn.close()
    return jsonify(stats)

Done when: Logged in, visit /db/stats and see counts for 5 tables.

Task 3) Create (safely) — Seed three demo products only if empty

Why: Students need data to “see” DB results.
Where: Add this route above if name == 'main':.
Edit:

@app.route('/db/seed-if-empty')
def db_seed_if_empty():
    if not session.get('user_id'):
        return jsonify({"error": "login required"}), 401
    conn = get_db_connection()
    try:
        count = conn.execute("SELECT COUNT(*) FROM products").fetchone()[0]
        if count == 0:
            # Use existing categories; default data already inserted in init_database()
            conn.executemany("""
                INSERT INTO products (name, description, price, cost, stock_quantity, min_stock_level, category_id, sku)
                VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM categories WHERE name = ? LIMIT 1), ?)
            """, [
                ('Granola Bar', 'Oats and honey', 1.00, 0.35, 4, 5, 'Food & Drinks', 'SKU-GRN-001'),
                ('Notebook A5', 'Ruled, 80 pages', 3.50, 1.20, 40, 10, 'School Supplies', 'SKU-NTB-101'),
                ('Water Bottle', '750 ml', 1.50, 0.60, 2, 5, 'Food & Drinks', 'SKU-WTR-750'),
            ])
            conn.commit()
            msg = "Seeded 3 demo products."
        else:
            msg = "Products table already has data. No changes made."
        return jsonify({"success": True, "message": msg})
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "error": str(e)})
    finally:
        conn.close()

Done when: Logged in, visit /db/seed-if-empty → it reports that it seeded (first time) or says there was already data.

Task 4) Read (filtered) — Very simple “low stock” JSON

Why: Shows a SELECT with a condition.
Where: Add this route above if name == 'main':.
Edit:

@app.route('/api/low-stock')
def api_low_stock():
    if not session.get('user_id'):
        return jsonify({"error": "login required"}), 401
    conn = get_db_connection()
    rows = conn.execute("""
        SELECT p.id, p.name, p.sku, p.stock_quantity, p.min_stock_level,
               c.name AS category_name
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.id
        WHERE p.stock_quantity <= p.min_stock_level
        ORDER BY p.stock_quantity ASC, p.name ASC
        LIMIT 25
    """).fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])

Done when: Logged in, visit /api/low-stock and see JSON (if empty, lower a product’s stock_quantity to meet/beat min_stock_level, then refresh).

Task 5) Update — Restock a product (increase stock in the DB)

Why: Practice an UPDATE that changes one column in one row.
Where: Add this route above if name == 'main':.
Edit:

@app.route('/inventory/restock/', methods=['POST'])
def restock_product(product_id):
    if not session.get('user_id'):
        return redirect(url_for('login'))
    try:
        amount = int(request.form.get('amount', '0'))
    except ValueError:
        amount = 0

    if amount <= 0:
        flash('Restock amount must be a positive number.', 'error')
        return redirect(url_for('inventory'))

    conn = get_db_connection()
    conn.execute("""
        UPDATE products
        SET stock_quantity = stock_quantity + ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    """, (amount, product_id))
    conn.commit()
    conn.close()

    flash(f'Restocked product #{product_id} by +{amount}.', 'success')
    return redirect(url_for('inventory'))

Template hint (very small form next to each product in inventory.html):

<form action="{{ url_for('restock_product', product_id=p['id']) }}" method="post" style="display:inline;">
  <input type="number" name="amount" min="1" value="5" style="width:4rem;">
  <button class="btn btn-sm btn-secondary" type="submit">Restock</button>
</form>

Done when: In /inventory, submit “Restock” for a product with amount “5”; after refresh, the product’s stock_quantity increases by 5 and a green success flash appears.

Task 6) Read — Export all products to CSV (download from the DB)

Why: Practice a SELECT and return data as a file.
Where: Add this route above if name == 'main':.
Edit:

import csv
from io import StringIO
from flask import Response

@app.route('/export/products.csv')
def export_products_csv():
    if not session.get('user_id'):
        return redirect(url_for('login'))

    conn = get_db_connection()
    rows = conn.execute("""
        SELECT p.id, p.name, p.sku, p.price, p.cost, p.stock_quantity, p.min_stock_level,
               c.name AS category_name, p.created_at, p.updated_at
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.id
        ORDER BY p.name
    """).fetchall()
    conn.close()

    output = StringIO()
    writer = csv.writer(output)
    writer.writerow(['id','name','sku','price','cost','stock_quantity','min_stock_level','category_name','created_at','updated_at'])
    for r in rows:
        writer.writerow([
            r['id'], r['name'], r['sku'], r['price'], r['cost'],
            r['stock_quantity'], r['min_stock_level'], r['category_name'],
            r['created_at'], r['updated_at']
        ])

    csv_data = output.getvalue()
    output.close()

    return Response(
        csv_data,
        mimetype='text/csv',
        headers={'Content-Disposition': 'attachment; filename=products.csv'}
    )

Done when: Logged in, visit /export/products.csv and your browser downloads products.csv containing all products from the database.

Part D — Student checklist (5 minutes)

• Restart the server (Ctrl+C, then python app.py).

• Log in at http://127.0.0.1:5000.

• Visit in order:

  • /db/foreign_keys → must say true.

  • /db/seed-if-empty → seed on first run.

  • /db/stats → counts show up.

  • /api/low-stock → shows items at/below min stock (adjust a product if needed).

  • In /inventory, use the Restock form → stock increases.

  • /export/products.csv → CSV downloads.

Exit Ticket (1 minute)

• What do foreign keys do for us?

• What SQL did we use to count rows in a table?

• How did we filter for low-stock items?

• Which SQL verb changes a value in a row?