8. 综合练习：用 Python 操作 SQLite

这是什么：综合练习 = 用 Python 标准库 sqlite3 把前面学的建表、增删改查、JOIN 统计串成一个约 40 行的博客数据层（blog.py），程序里所有存取数据的事都交给它。

为什么用：MySQL 教程最后我们设计了博客表结构，但那要连服务、配密码；SQLite 加 Python 让你一个文件跑通全流程——写完直接就能在 Flask 教程里复用。

如何用：从建 blog.py 开始，建表、插数据、查最新文章、统计阅读量，最后用参数化查询堵住 SQL 注入。

8.1 目标

一个最小可用的数据层 blog.py，提供四个函数：

1. init()——建表（存在就跳过）
2. add_user() / add_article()——写入数据
3. latest_articles()——查最新文章（带作者名）
4. stats()——按作者统计阅读量

8.2 完整代码

import sqlite3

DB = "learn.db"

def get_conn():
    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA foreign_keys = ON")   # SQLite 外键默认关闭
    return conn

def init():
    with get_conn() as conn:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS user (
            id       INTEGER PRIMARY KEY,
            username TEXT NOT NULL UNIQUE,
            email    TEXT
        );
        CREATE TABLE IF NOT EXISTS article (
            id      INTEGER PRIMARY KEY,
            title   TEXT NOT NULL,
            views   INTEGER DEFAULT 0,
            user_id INTEGER NOT NULL REFERENCES user(id)
        );
        """)

def add_user(username, email):
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO user (username, email) VALUES (?, ?)", (username, email))
        return cur.lastrowid                 # 新用户的 id

def add_article(user_id, title, views=0):
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO article (title, views, user_id) VALUES (?, ?, ?)",
            (title, views, user_id))
        return cur.lastrowid

def latest_articles(limit=3):
    with get_conn() as conn:
        return conn.execute("""
        SELECT u.username, a.title, a.views
        FROM article a JOIN user u ON a.user_id = u.id
        ORDER BY a.id DESC LIMIT ?
        """, (limit,)).fetchall()

def stats():
    with get_conn() as conn:
        return conn.execute("""
        SELECT u.username, COUNT(a.id), IFNULL(SUM(a.views), 0)
        FROM user u LEFT JOIN article a ON a.user_id = u.id
        GROUP BY u.id ORDER BY 3 DESC
        """).fetchall()

if __name__ == "__main__":
    init()
    frog = add_user("frog", "frog@example.com")
    panda = add_user("panda", "panda@example.com")
    add_article(frog, "从零学前端：HTML 入门", 120)
    add_article(frog, "CSS 才是网页的灵魂", 85)
    add_article(panda, "MySQL 练手记", 40)
    print(latest_articles(2))
    print(stats())

删掉旧的 learn.db（前几章练过的数据会和 UNIQUE 约束冲突），运行 python blog.py，预期输出：

[('panda', 'MySQL 练手记', 40), ('frog', 'CSS 才是网页的灵魂', 85)]
[('frog', 2, 205), ('panda', 1, 40)]

8.3 代码里的四个要点

特别注意参数化：永远不要用 f-string 拼 SQL。f"... WHERE id = {user_id}" 是注入漏洞的经典入口，? 占位符才是正路。

8.4 动手改

1. 给 blog.py 加一个 top_articles(n)：按阅读量取前 n 篇
2. 给 add_article 加文章不存在作者时的报错提示（提示：外键开着，捕获 sqlite3.IntegrityError）
3. 把 stats() 的 ORDER BY 改成按文章数排序

8.5 小结

• sqlite3 全流程：connect → 开外键 → 建表 → 增删改查 → with 自动提交
• 参数化查询 ? 是防 SQL 注入的唯一正路
• 这套数据层换个连接串就能对接 Flask 教程的数据库一章

下一站：数据库板块总览 🐸