Dynamic Websites, APIs, and Databases

A complete dynamic web server

from flask import Flask
from datetime import datetime
app = Flask(__name__)

@app.route("/hours")
def hours():
    if 7 <= datetime.now().hour < 18:
        return "<p>We are <b>open</b> right now!</p>"
    return "<p>We are closed. Back at 7 a.m.</p>"

app.run(port=5000)

• Flask is a framework: it handles the routine parts of being a server
• Each route connects a path to a function; the function's return value is the response

Localhost, ports, and templates

• localhost means "this computer": browser and server on the same laptop
• A port is a numbered door: 443 for HTTPS, 5000 or 3000 for testing
• Two visitors a minute apart at 5:59 and 6:00 see different pages: that is "dynamic"
• Real back ends fill in templates (HTML with blanks) rather than gluing strings

The menu is the API

• An API is the defined set of requests a program accepts and the answers it gives
• Callers are often programs, not people: page JavaScript, a mobile app, a partner, an AI
• Each address in an API is an endpoint; answers come back as data, not designed pages

JSON: the common tongue of the web

{
  "products": [
    {"id": 1, "name": "Sourdough loaf", "price": 7.5, "in_stock": true},
    {"id": 2, "name": "Rye bread", "price": 6.0, "in_stock": false}
  ]
}

• Curly braces: named values. Square brackets: lists. Almost exactly a Python dictionary and list

Verbs: nouns in the URL, verbs in the method

Why not just save to a file?

• Many writers at once: two customers buying the last challah must not both succeed
• Speed at scale: an index finds one order in ten million in milliseconds
• Reliability: a transaction is all-or-nothing, even if the power fails halfway
• Questions: "who ordered rye three times last quarter?" is one line
• The database is a separate program (PostgreSQL, MySQL, SQLite) your back end talks to

Reading: SELECT

SELECT name, email FROM customers WHERE city = 'Boston';

SELECT status, COUNT(*) AS how_many
FROM orders
GROUP BY status;

• SELECT picks columns, FROM picks the table, WHERE filters rows
• COUNT, SUM, AVG, MIN, MAX are aggregate functions: every dashboard is a stack of these

Combining tables: JOIN

SELECT customers.name, products.name, orders.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products  ON orders.product_id  = products.product_id
WHERE orders.status = 'placed';

• A JOIN stitches rows together using the keys, so "Dana Levi, Rye bread, 1" comes out readable

Writing: INSERT, UPDATE, DELETE

INSERT INTO orders (customer_id, product_id, quantity, status)
VALUES (3, 1, 2, 'placed');

UPDATE orders SET status = 'ready' WHERE order_id = 88;

DELETE FROM orders WHERE order_id = 89;

• DELETE FROM orders; with no WHERE deletes every order in the company
• CREATE TABLE defines columns, data types, keys, and constraints the database enforces

Which database?

NoSQL and ORMs

• NoSQL relaxes the rules: a document database (MongoDB) stores JSON-like records with any fields
• Price of flexibility: fewer guarantees, clumsier joins. Rule of thumb: start relational
• An ORM lets Python treat rows as objects and writes the SQL behind the scenes
• Claude Code will almost certainly use an ORM; the concepts underneath are unchanged

Presentation, application, data

• Presentation tier: the front end in the browser or app
• Application tier: the back-end program; where business logic lives (prices, permissions, workflows)
• Data tier: the database

Three consequences

• The browser never touches the database directly; the back end checks identity and applies rules
• Each tier can be changed independently: redesign the front end without touching the data
• Each tier can live on different hardware: today one laptop, tomorrow ten servers (Session 4)