// python · lab

Server-side rendering in pure Python, part 2: rendering from PostgreSQL

2026-08-16 · 4 min read

Part 1 rendered a list frozen into the source code. Now the same ~50-line server renders live rows from PostgreSQL: create the table, query it per request with parameterized SQL, and watch an UPDATE in pgAdmin appear in the browser without restarting anything. One new dependency, two injection lessons.

mindmap — quick refresh SSR part 2 — render from PostgreSQL what changes from part 1 STUDENTS list → students TABLE render() now queries per request everything else identical the request's journey HTTP GET → handler SQL over the wire (:5432) engine: parse → plan → execute rows → template merge → HTML the two injections SQL: use %s parameters, never f-strings HTML: escape() every value same law: data must never become code the payoff UPDATE in pgAdmin → refresh → new page no restart, no code edit this is how every real site works

Prerequisites: part 1 (this builds directly on its code) and PostgreSQL 101 (databases, tables, constraints, pgAdmin). Time: ~45 minutes.

Part 1 had one honest weakness: STUDENTS was a Python list frozen into the program. Add a student? Edit the source, restart the server. Real applications don't work that way — the data lives in a database, and the server renders whatever is there right now. Today the fixed list dies. The plan: same server, same template, but render() gets its rows from PostgreSQL, per request.

Set up the database

Using psql or pgAdmin's Query Tool (this is the containers hierarchy in practice — a university database, default public schema, one table):

CREATE DATABASE university;
-- connect to it: \c university   (or open a pgAdmin Query Tool on it)

CREATE TABLE students (
    id     integer      PRIMARY KEY,
    name   varchar(100) NOT NULL,
    course varchar(50)  NOT NULL
);

INSERT INTO students VALUES
    (1, 'Asha',  'AI'),
    (2, 'Ravi',  'AI'),
    (3, 'Meena', 'Data Science');

The same PRIMARY KEY and NOT NULL constraints from PostgreSQL 101 now guard your lab data — try inserting a duplicate id and watch them work.

One new dependency

Python's standard library speaks HTTP but not PostgreSQL's wire protocol — remember from PostgreSQL 101 that every client talks to the engine over port 5432 in that protocol. The translator is a driver:

pip install "psycopg[binary]"

psycopg is exactly the "your app / ORM" box from the engine simulator: it opens the connection, sends your SQL down the wire, and hands rows back as Python tuples. That's the whole magic.

The server, now database-backed

Save as server.py — the diff from part 1 is small enough to read in one breath: STUDENTS is gone, fetch_students() is new, and render() now escapes its values (the lesson part 1 warned about):

# server.py — part 2: server-side rendering from PostgreSQL
from html import escape
from http.server import HTTPServer, BaseHTTPRequestHandler
from urllib.parse import urlparse, parse_qs

import psycopg  # pip install "psycopg[binary]"

DSN = "dbname=university user=postgres host=127.0.0.1"  # adjust user/password

PAGE = """<!doctype html>
<html>
<head><title>Students</title></head>
<body>
  <h1>Students — {course}</h1>
  <ul>
{rows}
  </ul>
</body>
</html>"""

def fetch_students(course):
    query = "SELECT id, name, course FROM students"
    params = []
    if course != "all":
        query += " WHERE course = %s"     # %s = placeholder, NOT string formatting
        params.append(course)
    query += " ORDER BY id"
    with psycopg.connect(DSN) as conn:
        with conn.cursor() as cur:
            cur.execute(query, params)    # the driver sends value separately
            return cur.fetchall()         # → [(1, 'Asha', 'AI'), ...]

def render(course):
    rows = "\n".join(
        f"    <li>{escape(str(sid))} — {escape(name)} ({escape(c)})</li>"
        for sid, name, c in fetch_students(course)
    )
    return PAGE.format(course=escape(course), rows=rows)

class Handler(BaseHTTPRequestHandler):
    def do_GET(self):
        url = urlparse(self.path)
        if url.path != "/students":
            self.send_error(404, "try /students")
            return
        course = parse_qs(url.query).get("course", ["all"])[0]
        body = render(course).encode("utf-8")
        self.send_response(200)
        self.send_header("Content-Type", "text/html; charset=utf-8")
        self.send_header("Content-Length", str(len(body)))
        self.end_headers()
        self.wfile.write(body)

if __name__ == "__main__":
    print("serving on http://127.0.0.1:8000/students")
    HTTPServer(("127.0.0.1", 8000), Handler).serve_forever()

Run python3 server.py and open http://127.0.0.1:8000/students — same page as part 1, except the rows just crossed a network protocol to get to you.

The journey of one request

You've now met every piece of this pipeline in a previous guide. Watch them work as one machine:

Count the layers a single refresh touches: HTTP parsing (the servers guide), a SQL query over the wire, the engine's parser → planner → executor (PostgreSQL 101), rows back, the template merge (part 1), and an HTTP response. Two protocols, one page. Every Django, Rails, and Spring request you'll ever debug is this exact journey wearing a bigger coat.

Two injections, one law

Look closely at the two deliberate safety choices in the code:

  • SQL: the query uses %s and passes course as a parameter. The driver ships the value separately from the SQL text, so a visitor requesting ?course=AI'; DROP TABLE students;-- sends a harmless string, not a command. Never build SQL with f-strings.
  • HTML: every value goes through escape() before entering the page, so a student named <script>…</script> renders as text, not code — part 1's warning, now actually implemented.

Different layer, same law: data must never be allowed to become code. SQL injection and XSS are both what happens when that law is broken. (OWASP's cheat sheets below are the professional references.)

Prove it's live

The demo that separates part 2 from part 1 — while the server is running, in pgAdmin or psql:

INSERT INTO students VALUES (4, 'Tashi', 'AI');
UPDATE students SET course = 'Data Science' WHERE name = 'Ravi';

Now just refresh the browser. New student, moved student — no code edit, no restart. In part 1 that was impossible. The server doesn't contain the page; it manufactures it from current data, per request. That single sentence is what "database-backed server-side rendering" means.

Exercises

  1. Constraint in the wild: INSERT a student with a duplicate id in pgAdmin, and confirm the error message matches the constraints simulator in PostgreSQL 101.
  2. Sort and count: change the query to ORDER BY name, then add a line under the <ul> showing len(...) students rendered.
  3. A second route: add /courses that renders SELECT DISTINCT course FROM students as a list of links to /students?course=....
  4. Attack yourself, then fix it: make a copy where fetch_students builds the query with an f-string instead of %s, request ?course=AI' OR '1'='1, and watch the filter break (every student leaks). Restore the parameterized version and confirm the same URL is now harmless data.
  5. Feel the connection cost: our code connects to PostgreSQL on every request. Move psycopg.connect to module level and reload rapidly — snappier? That's why real servers use connection pools (and what breaks with threads — recall exercise 4 of part 1).

References

  • OWASP Foundation. (n.d.). Cross site scripting prevention cheat sheet. Retrieved August 16, 2026, from https://cheatsheetseries.owasp.org/cheatsheets/Cross_Site_Scripting_Prevention_Cheat_Sheet.html
  • OWASP Foundation. (n.d.). SQL injection prevention cheat sheet. Retrieved August 16, 2026, from https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
  • The PostgreSQL Global Development Group. (n.d.). Queries (PostgreSQL documentation, Chapter 7). Retrieved August 16, 2026, from https://www.postgresql.org/docs/current/queries.html
  • The Psycopg Team. (n.d.). Psycopg 3 documentation. Retrieved August 16, 2026, from https://www.psycopg.org/psycopg3/docs/
  • Python Software Foundation. (n.d.). http.server — HTTP servers (Python documentation). Retrieved August 16, 2026, from https://docs.python.org/3/library/http.server.html