11. Capstone: your data in a browser

Everything so far has been Flask on its own. This page connects it to the real project. You will write one route that opens the same music_time_machine.db Chapter 16 fills, runs a single query, and renders the result through a template. No OAuth, no charts, no multi-page dashboard, just proof that Flask and your data meet on a page.

This is the same boundary the chapter opened with: Chapter 16's scripts write the database, and Flask reads it. The capstone deliberately stays small so the whole flow fits on one screen. Chapter 18 takes this exact shape, route plus query plus template, and grows it into the full dashboard.

The route: one query, one template

Create app.py with a single route. It opens the database with a small helper, runs one query for your most recent snapshot, and passes the rows to a template. The query is wrapped in try/except so a missing or empty database shows an empty page instead of crashing, which matters because a fresh clone has no snapshots yet.

This is a fresh capstone version of app.py. It replaces the small practice routes from earlier in the chapter so the final example can focus on one complete route, query, and template.

app.py

from contextlib import closing
import sqlite3

from flask import Flask, render_template

app = Flask(__name__)

# The same SQLite file Chapter 16 writes to.
DATABASE_PATH = "music_time_machine.db"


def get_db_connection():
    """Open the database with name-based row access."""
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    return conn


@app.route("/")
def home():
    """Render the most recent short-term snapshot as a simple list."""
    tracks = []
    try:
        with closing(get_db_connection()) as conn:
            tracks = conn.execute(
                """
                SELECT t.name, t.artist_name, s.rank
                FROM snapshots s
                JOIN tracks t ON t.track_id = s.track_id
                WHERE s.time_range = 'short_term'
                  AND s.snapshot_date = (
                      SELECT MAX(snapshot_date)
                      FROM snapshots
                      WHERE time_range = 'short_term'
                  )
                ORDER BY s.rank
                LIMIT 20
                """
            ).fetchall()
    except sqlite3.OperationalError as exc:
        # No snapshots table yet (a fresh clone has no data): fall back to the
        # empty state rather than raising a 500 on the reader's first run. Print
        # the reason so a genuine schema mismatch (a wrong table or column name)
        # still shows in the terminal instead of hiding behind the empty page.
        print(f"Query skipped: {exc}")
        tracks = []

    return render_template("home.html", tracks=tracks)


if __name__ == "__main__":
    app.run(debug=True)

The template: list or empty state

Create templates/home.html. The {% if tracks %} branch lists the rows; the {% else %} branch is the empty state for a database with no snapshots yet.

templates/home.html

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Music Time Machine</title>
</head>
<body>
  <h1>Your recent top tracks</h1>

  {% if tracks %}
    <ol>
      {% for track in tracks %}
        <li>{{ track.name }} by {{ track.artist_name }}</li>
      {% endfor %}
    </ol>
  {% else %}
    <p>No snapshots yet. Run a Chapter 16 snapshot first, then refresh this page.</p>
  {% endif %}
</body>
</html>

This template is deliberately standalone rather than extending the base.html layout from the layouts and static files page. The capstone is the smallest possible proof that Flask can read your data and render it; Chapter 18 rebuilds this page on the shared layout, with the navigation and styling a real dashboard needs.

Why the empty state matters

The first time you run Flask against this project the database may have no snapshots, or no database file at all if you are starting from a fresh clone. Without the {% else %} branch and the try/except around the query, that first run would greet you with a stack trace instead of a page. The empty state turns a confusing failure into a clear instruction: run a snapshot, then refresh. If you need to populate data, the snapshot scripts are covered in Chapter 16.

Run it

Start the development server from the project folder:

Terminal

python app.py

Visit http://127.0.0.1:5000. If Chapter 16 has written snapshots, you will see your most recent top tracks. If not, you will see the empty-state message, which is itself a small success: Flask ran, the route matched, the template rendered, and the database was read safely.

That is the whole loop, route then query then template, in about thirty lines. Chapter 18 keeps this shape and adds the parts a real dashboard needs: a styled layout, summary cards, a Chart.js visualisation, the browser OAuth flow, and the Analytics, Playlist Manager, and Settings pages.