PY-L4-41 · Flask + SQLite — Database-Backed Blog

Learning Goals

3 min

Wire a Flask app to a SQLite database via a CRUD module.
Use a per-request connection helper.
Show a list of posts, view single post, create a post.
Apply Post/Redirect/Get + parameterised queries.

Warm-Up · Three Files

5 min

blog/
├─ app.py
├─ blog_db.py
├─ templates/
│   ├─ base.html
│   ├─ index.html
│   ├─ post.html
│   └─ new.html
└─ blog.db   (auto-created)

Today's big idea

Keep the data layer separate. blog_db.py knows SQL. app.py knows Flask. Templates know HTML. Three files, one job each.

Plan · Data Layer + Routes

14 min

blog_db.py

import sqlite3
from pathlib import Path

DB = Path("blog.db")

def get_conn():
    con = sqlite3.connect(DB)
    con.row_factory = sqlite3.Row
    return con

def init():
    with get_conn() as con:
        con.execute("""
            CREATE TABLE IF NOT EXISTS posts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                body  TEXT NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        """)

def list_posts():
    with get_conn() as con:
        return [dict(r) for r in con.execute(
            "SELECT * FROM posts ORDER BY id DESC"
        )]

def get_post(pid):
    with get_conn() as con:
        row = con.execute("SELECT * FROM posts WHERE id = ?", (pid,)).fetchone()
    return dict(row) if row else None

def add_post(title, body):
    with get_conn() as con:
        return con.execute(
            "INSERT INTO posts (title, body) VALUES (?, ?)",
            (title, body),
        ).lastrowid

app.py

from flask import Flask, render_template, request, redirect, url_for, flash, abort
import blog_db as db

app = Flask(__name__)
app.secret_key = "dev-secret"   # rotate before deployment
db.init()

@app.route("/")
def index():
    return render_template("index.html", posts=db.list_posts())

@app.route("/post/<int:pid>")
def show(pid):
    post = db.get_post(pid)
    if not post:
        abort(404)
    return render_template("post.html", post=post)

@app.route("/new", methods=["GET", "POST"])
def new():
    if request.method == "POST":
        title = request.form.get("title", "").strip()
        body  = request.form.get("body", "").strip()
        errs = []
        if len(title) < 3:  errs.append("Title too short.")
        if len(body)  < 10: errs.append("Body too short.")
        if errs:
            return render_template("new.html", errors=errs,
                                   title=title, body=body)
        pid = db.add_post(title, body)
        flash("Post published 🎉")
        return redirect(url_for("show", pid=pid))
    return render_template("new.html")

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

Templates

12 min

<!-- templates/base.html -->
<!doctype html>
<html><head>
  <meta charset="utf-8">
  <title>{% block title %}My blog{% endblock %}</title>
  <style>
    body{font-family:system-ui;max-width:680px;margin:2rem auto;padding:0 1rem}
    nav{margin-bottom:2rem} .flash{background:#e7f7e0;padding:.5rem 1rem;border-radius:6px}
    .err{color:#b00}
  </style>
</head><body>
  <nav>
    <a href="{{ url_for('index') }}">Home</a> ·
    <a href="{{ url_for('new') }}">New post</a>
  </nav>
  {% with msgs = get_flashed_messages() %}
    {% for m in msgs %}<p class="flash">{{ m }}</p>{% endfor %}
  {% endwith %}
  {% block content %}{% endblock %}
</body></html>

<!-- templates/index.html -->
{% extends "base.html" %}
{% block content %}
  <h1>Latest posts</h1>
  {% for p in posts %}
    <article>
      <h2><a href="{{ url_for('show', pid=p.id) }}">{{ p.title }}</a></h2>
      <small>{{ p.created_at }}</small>
    </article>
  {% else %}
    <p>No posts yet — <a href="{{ url_for('new') }}">write the first one</a>.</p>
  {% endfor %}
{% endblock %}

<!-- templates/post.html -->
{% extends "base.html" %}
{% block title %}{{ post.title }}{% endblock %}
{% block content %}
  <h1>{{ post.title }}</h1>
  <small>{{ post.created_at }}</small>
  <p style="white-space:pre-wrap">{{ post.body }}</p>
  <p><a href="{{ url_for('index') }}">← back</a></p>
{% endblock %}

<!-- templates/new.html -->
{% extends "base.html" %}
{% block content %}
  <h1>New post</h1>
  {% for e in errors %}<p class="err">{{ e }}</p>{% endfor %}
  <form method="post">
    <p><input name="title" value="{{ title or '' }}" placeholder="title" required></p>
    <p><textarea name="body" rows="10" placeholder="write…" required>{{ body or '' }}</textarea></p>
    <button>Publish</button>
  </form>
{% endblock %}

Run python app.py. Open http://localhost:5000. Write your first post. Restart the server. The post is still there — SQLite persists.

Try It Yourself

13 min

01 🟢 Edit a post

Add an /edit/<int:pid> route + template. Update the title and body via PRG.

02 🟡 Delete with confirmation

Add a delete button on the post page that POSTs to /delete/<int:pid>. Confirm with the browser's onsubmit="return confirm(...)".

03 🔴 Search

Add a search box on the home page. Filter posts by title LIKE using a parameterised SQL query.

Hint

def search(q):
    with get_conn() as con:
        return [dict(r) for r in con.execute(
            "SELECT * FROM posts WHERE title LIKE ? ORDER BY id DESC",
            (f"%{q}%",))]

Mini-Challenge · Tag System

8 min

Add tags to posts. Schema: a comma-separated tags column, OR a separate post_tags bridge table for proper many-to-many. Show tag chips on each post; clicking a chip filters the home page to posts with that tag.

Recap

3 min

The shape: data layer in one file, Flask in another, Jinja templates third. Every write is parameterised. Every successful POST redirects. SQLite gives you persistence with zero server admin. Tomorrow we add auth — sign-up and login.

Homework

4 min

Add "edit" and "delete" to your blog. Then publish three real posts about something you care about. Push the code to GitHub.

import sqlite3 from pathlib import Path DB = Path("blog.db") def get_conn(): con = sqlite3.connect(DB) con.row_factory = sqlite3.Row return con def init(): with get_conn() as con: con.execute(""" CREATE TABLE IF NOT EXISTS posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, body TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP ) """) def list_posts(): with get_conn() as con: return [dict(r) for r in con.execute( "SELECT * FROM posts ORDER BY id DESC" )] def get_post(pid): with get_conn() as con: row = con.execute("SELECT * FROM posts WHERE id = ?", (pid,)).fetchone() return dict(row) if row else None def add_post(title, body): with get_conn() as con: return con.execute( "INSERT INTO posts (title, body) VALUES (?, ?)", (title, body), ).lastrowid

from flask import Flask, render_template, request, redirect, url_for, flash, abort import blog_db as db app = Flask(__name__) app.secret_key = "dev-secret" # rotate before deployment db.init() @app.route("/") def index(): return render_template("index.html", posts=db.list_posts()) @app.route("/post/<int:pid>") def show(pid): post = db.get_post(pid) if not post: abort(404) return render_template("post.html", post=post) @app.route("/new", methods=["GET", "POST"]) def new(): if request.method == "POST": title = request.form.get("title", "").strip() body = request.form.get("body", "").strip() errs = [] if len(title) < 3: errs.append("Title too short.") if len(body) < 10: errs.append("Body too short.") if errs: return render_template("new.html", errors=errs, title=title, body=body) pid = db.add_post(title, body) flash("Post published 🎉") return redirect(url_for("show", pid=pid)) return render_template("new.html") if __name__ == "__main__": app.run(debug=True)

<!doctype html> <html><head> <meta charset="utf-8"> <title>{% block title %}My blog{% endblock %}</title> <style> body{font-family:system-ui;max-width:680px;margin:2rem auto;padding:0 1rem} nav{margin-bottom:2rem} .flash{background:#e7f7e0;padding:.5rem 1rem;border-radius:6px} .err{color:#b00} </style> </head><body> <nav> <a href="{{ url_for('index') }}">Home</a> · <a href="{{ url_for('new') }}">New post</a> </nav> {% with msgs = get_flashed_messages() %} {% for m in msgs %}<p class="flash">{{ m }}</p>{% endfor %} {% endwith %} {% block content %}{% endblock %} </body></html>  {% extends "base.html" %} {% block content %} <h1>Latest posts</h1> {% for p in posts %} <article> <h2><a href="{{ url_for('show', pid=p.id) }}">{{ p.title }}</a></h2> <small>{{ p.created_at }}</small> </article> {% else %} <p>No posts yet — <a href="{{ url_for('new') }}">write the first one</a>.</p> {% endfor %} {% endblock %}  {% extends "base.html" %} {% block title %}{{ post.title }}{% endblock %} {% block content %} <h1>{{ post.title }}</h1> <small>{{ post.created_at }}</small> <p style="white-space:pre-wrap">{{ post.body }}</p> <p><a href="{{ url_for('index') }}">← back</a></p> {% endblock %}  {% extends "base.html" %} {% block content %} <h1>New post</h1> {% for e in errors %}<p class="err">{{ e }}</p>{% endfor %} <form method="post"> <p><input name="title" value="{{ title or '' }}" placeholder="title" required></p> <p><textarea name="body" rows="10" placeholder="write…" required>{{ body or '' }}</textarea></p> <button>Publish</button> </form> {% endblock %}