1 数据库与表 —— 先想清楚「存什么」，再动手建表

是什么？为什么学？

数据库（database）是有组织地存放数据的仓库，表（table）是仓库里的分类货架。文件存数据（CSV/JSON）是「把东西堆在桌上」，数据库是「把东西分门别类放进带标签的格子」。之前存数据用文件，但文件有个问题——数据一多就乱：没法按条件快速查、多人同时写会冲突、数据关系复杂时无从下手。数据库就是专门干这个的：海量、有序、能查、能并发。SQLite 是 Python 自带的轻量数据库，不用装服务器、就是一个文件，最适合入门；学会它，SQL 语法和所有主流数据库（MySQL、PostgreSQL）是通用的。

底层原理

SQLite 把整个数据库做成一个文件（.db），文件内部按「页」（page，通常 4KB）组织，表的数据以 B 树（一种能快速查找的树状结构）存储，主键就是树的「索引键」，所以按主键查数据快到几乎不随数据量增长变慢。SQLite 是嵌入式数据库：没有独立服务器进程，你的程序直接读写这个文件（对比 MySQL 要装后台服务）——这就是它轻量、适合入门的原因。

生活类比

数据库像一个图书馆：图书馆（数据库）里有一排排书架（表），每本书（记录）有固定的摆放位（字段）。找书不需要一本本翻——按编号（主键）直接定位。文件存储则像把书全堆在家地板上：书少还行，书多了找一本要翻半天。

注意事项总结

1 连接内存数据库 + 建表 + 插入：

import sqlite3

# 连接内存数据库（:memory: 表示数据只存在内存中，程序结束自动消失，适合练习）
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# 建表：先定义"这本书有什么字段"，IF NOT EXISTS 表示"已存在就不重复建"
cursor.execute("""
CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,   -- 自增主键，唯一身份证
    title TEXT NOT NULL,                    -- 书名，NOT NULL 不能为空
    author TEXT,                            -- 作者
    price REAL,                             -- 价格（小数）
    year INTEGER                            -- 出版年份
)
""")

# 插入一条记录（? 是占位符，值放在第二个参数里）
cursor.execute(
    "INSERT INTO books (title, author, price, year) VALUES (?, ?, ?, ?)",
    ("三体", "刘慈欣", 68.0, 2008),
)
cursor.execute("SELECT * FROM books")
print(cursor.fetchall())

conn.close()

运行结果：

[(1, '三体', '刘慈欣', 68.0, 2008)]

connect(":memory:") 创建内存库（不落盘，练习零残留）；cursor 是「执行 SQL 的手」；CREATE TABLE 里先想好字段：id 用 AUTOINCREMENT 自动编号，title 标 NOT NULL 保证书名不能为空；插入时 ? 占位、值从第二个参数传入；fetchall() 把结果取成列表，每行是一个元组。建表前先花 1 分钟在纸上写字段清单，比直接敲代码快得多。

易错点

• 错误写法：字段类型乱写（如 price TEXT） → 问题：小数按文本存，排序/计算都错 → 正确写法：金额用 REAL，整数 INTEGER，文本 TEXT。
• 错误写法：忘了主键 → 问题：无法唯一标识一行 → 正确写法：每张表都设主键，一般 id INTEGER PRIMARY KEY AUTOINCREMENT。
• 错误写法：重复运行 CREATE TABLE 报已存在 → 问题：第二次运行报「table already exists」 → 正确写法：加 IF NOT EXISTS。

记忆口诀：库装表、表装行；建表先定字段名，主键唯一不能忘。

2 连接与游标 —— sqlite3 的「三板斧」

是什么？为什么学？

sqlite3 的三个固定动作：连接（connect）→ 游标（cursor）→ 执行（execute）。连接是「通向数据库文件的管道」，游标是「管道里操作数据的手」。几乎所有数据库代码都长一个样，记住三板斧，SQLite 就入门了一半。

底层原理

conn = sqlite3.connect(...) 时，Python 打开数据库文件（或内存库）并持有文件句柄；conn.cursor() 创建游标——SQLite 引擎执行 SQL 后，结果集（result set）就存放在游标里；fetchall() / fetchone() 把结果「读出来」。所以：execute 只负责执行，取结果必须用 fetch 系列。连接默认开启事务管理（见知识点 4），写操作后要 commit() 才真正落盘。

生活类比

连接像自来水管道：把水厂（数据库文件）和家（程序）接通；游标像水龙头：打开水龙头（execute）水才流出来，用杯子接水（fetchall）才能喝到。水管不关（不 close()）会漏水占资源；水龙头不接（不 fetch）水就白白流走了。

注意事项总结

1 连接 + 游标 + 批量插入 + 排序查询：

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE t (name TEXT, score INTEGER)")

rows = [("小明", 90), ("小红", 85), ("小刚", 78)]
cur.executemany("INSERT INTO t VALUES (?, ?)", rows)   # 批量插入，比循环高效

cur.execute("SELECT * FROM t ORDER BY score DESC")    # 按分数从高到低
print(cur.fetchall())
conn.close()

运行结果：

[('小明', 90), ('小红', 85), ('小刚', 78)]

executemany 接收「一条带占位符的 SQL + 一个列表」，SQLite 会批量执行，比 for 循环一条条 execute 快一个数量级；ORDER BY score DESC 在数据库内部排序；fetchall() 一次取回所有行。取数据时注意：fetchall 每调用一次，游标里的结果就少一批，重复调用会返回空列表。

易错点

• 错误写法：用完不 conn.close() → 问题：文件句柄占用，Windows 上可能锁住 db 文件，删不掉 → 正确写法：用完 conn.close()；或用 with sqlite3.connect(...) as conn: 自动关。
• 错误写法：fetchall() 连续调用两次 → 问题：第二次返回 []，结果取过一次就没了 → 正确写法：一次取完存进变量：rows = cur.fetchall()。
• 错误写法：只 execute 不 fetch，又想拿结果 → 问题：结果集在游标里没人读 → 正确写法：查询后必须 fetchall() / fetchone() / fetchmany()。

记忆口诀：连接开管道，游标来操作，execute 执行、fetch 取结果，用完都要关。

3 参数化查询 —— 为什么用 ? 而不是拼接字符串

是什么？为什么学？

参数化查询（parameterized query）：SQL 里用 ? 占位，值通过第二个参数单独传入，而不是把值拼进 SQL 字符串。这是今天最重要的安全知识点——数据库最常见的攻击方式 SQL 注入（SQL injection，攻击者把恶意 SQL 代码伪装成输入数据），就是靠「不拼接」防住的。

底层原理

数据库处理 SQL 分两步：先解析编译（把字符串变成执行计划），再执行。拼接写法让用户输入参与「解析」——输入变成了 SQL 语法的一部分，攻击者就能改写你的 SQL；参数化写法让用户输入只参与「执行」——数据库把 ? 的位置固定为「一个值」，传入的任何内容都被当作数据（哪怕里面写着 DROP TABLE 也只是个普通字符串）。同样是执行，参与哪个阶段，决定了安全还是危险。

生活类比

拼接 SQL 像把收货地址直接印在快递单模板上——用户写「新疆」，快递单就变成「发往新疆」；用户如果写「新疆；顺便把车开走」，这句指令也会被照单执行。参数化查询则像快递单和货物分开：地址栏永远是「一个地址」的位置，填什么都只是地址，永远变不成额外指令。

注意事项总结

1 拼接 vs 参数化的对比实验：

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE users (name TEXT, password TEXT)")
cur.execute("INSERT INTO users VALUES ('admin', 'secret123')")
cur.executemany("INSERT INTO users VALUES (?, ?)", [("alice", "abc"), ("bob", "xyz")])

user_input = "' OR '1'='1"                                  # 模拟恶意输入
sql = f"SELECT * FROM users WHERE name = '{user_input}'"    # 错误做法：拼接
print("拼接版查出", len(cur.execute(sql).fetchall()), "行")

cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))  # 正确做法：参数化
print("参数化版查出", len(cur.fetchall()), "行")
conn.close()

运行结果：

拼接版查出 3 行
参数化版查出 0 行

用户输入 ' OR '1'='1，拼接后 SQL 变成 WHERE name = '' OR '1'='1'——条件永远成立，3 个用户全被查出来（注入成功！）；换成参数化查询后，同样的输入被数据库当成「一个字面值」，查出 0 行（注入失效）。同一份输入，两种写法，天壤之别。

2 模糊搜索的标准写法（LIKE + 参数化）：

keyword = input("输入书名关键词：")
cursor.execute("SELECT title, author FROM books WHERE title LIKE ?", (f"%{keyword}%",))
rows = cursor.fetchall()

LIKE 是模糊匹配，%关键字% 表示「中间包含关键字」。规则：凡是 SQL 里有来自外部的值，一律用 ? 占位传参，绝不拼接字符串。

易错点

• 错误写法：f"... WHERE title = '{title}'" 拼接 → 问题：用户输入变成 SQL 代码，可注入、可删表 → 正确写法：一律 ? 占位 + 元组传参。
• 错误写法：? 个数和参数个数对不上 → 问题：报 Incorrect number of bindings → 正确写法：数清 ?，参数元组元素个数一致。
• 错误写法：单值忘了写逗号：(name) → 问题：(name) 只是括号不是元组 → 正确写法：单值也要写 (name,)（逗号才是元组）。
• 错误写法：? 用在表名/列名（如 FROM ?） → 问题：表名、列名不能参数化 → 正确写法：表名/列名写死在 SQL，只有「值」能参数化。

记忆口诀：SQL 占位用问号，值走参数不拼接；注入最怕这句话，安全底线记心间。

4 事务与 commit —— 为什么改了数据没生效

是什么？为什么学？

事务（transaction）是一组要么全部成功、要么全部不生效的操作。commit() 是「确认定稿」，rollback() 是「全部撤销」。新手最常遇到的怪现象「明明 UPDATE 了，重启程序数据还是老的」——十有八九是忘了 commit()。

底层原理

SQLite 默认在「回滚日志」模式下工作：写操作（INSERT/UPDATE/DELETE）开始前，先把旧数据写进日志文件，修改只存在于内存（对其他连接不可见）；commit() 时才把修改真正写入主数据库文件并清空日志；如果崩溃或 rollback()，就用日志把旧数据还原。所以未 commit 的修改：自己的连接能看到，其他连接看不到，程序退出就丢——这就是「改了没生效」的真相。

生活类比

事务像银行转账：从 A 扣 100、给 B 加 100，两步必须都成功；中间任何一步失败（比如网络断了），银行不能只扣钱不打钱——必须整体撤销。commit 就是柜员的「确认」按钮：不按确认，这笔操作只是「待办草稿」；按了确认，才真正生效。

注意事项总结

1 commit 让修改真正生效（转账事务）：

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE account (name TEXT, money REAL)")
cur.execute("INSERT INTO account VALUES ('甲', 1000), ('乙', 1000)")
cur.execute("UPDATE account SET money = money - 100 WHERE name = '甲'")
cur.execute("UPDATE account SET money = money + 100 WHERE name = '乙'")
conn.commit()
print(cur.execute("SELECT name, money FROM account").fetchall())
conn.close()

运行结果：

[('甲', 900.0), ('乙', 1100.0)]

2 中途出错 → rollback 全部撤销，不留半截数据：

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE account (name TEXT, money REAL)")
cur.execute("INSERT INTO account VALUES ('甲', 1000), ('乙', 1000)")
conn.commit()  # 先提交初始数据
try:
    cur.execute("UPDATE account SET money = money - 100 WHERE name = '甲'")
    cur.execute("UPDATE account SET nope = 1 WHERE name = '乙'")  # 故意写错字段名
    conn.commit()
except Exception as e:
    conn.rollback()  # 撤销本事务的所有修改
    print("出错已回滚：", e)
print(cur.execute("SELECT name, money FROM account").fetchall())
conn.close()

运行结果：

出错已回滚： no such column: nope
[('甲', 1000.0), ('乙', 1000.0)]

第二条 UPDATE 字段名写错抛异常，except 里 rollback() 把第一条也撤销，账户恢复原样（要么一起成功，要么一起不留）。

3 不 commit 的后果——另一个连接什么都看不到：

db_path = os.path.join(tempfile.gettempdir(), "demo_no_commit.db")
conn = sqlite3.connect(db_path)
cur = conn.cursor()
cur.execute("CREATE TABLE account (name TEXT, money REAL)")
cur.execute("INSERT INTO account VALUES ('甲', 1000)")
cur.execute("UPDATE account SET money = 0 WHERE name = '甲'")  # 没 commit！
conn2 = sqlite3.connect(db_path)  # 第二个连接看
print("未 commit，另一个连接看到：", conn2.execute("SELECT name, money FROM account").fetchall())
conn.commit()
print("commit 后，另一个连接看到：", conn2.execute("SELECT name, money FROM account").fetchall())

运行结果：

未 commit，另一个连接看到： []
commit 后，另一个连接看到： [('甲', 0.0)]

记住：增删改之后，commit() 不是可选项，是必选项。

易错点

• 错误写法：增删改后忘了 conn.commit() → 问题：数据「改了没生效」，重开程序就没了 → 正确写法：每次增删改后执行 conn.commit()。
• 错误写法：try/except 里忘了 rollback() → 问题：出错后数据半新半旧，留下脏数据 → 正确写法：except 分支里必须 conn.rollback()。
• 错误写法：每条 execute 都 commit → 问题：性能差（每次写盘），事务的意义也没了 → 正确写法：一组相关操作一个事务，最后 commit 一次。

记忆口诀：增删改后要 commit，出错 rollback 全撤销；一起成功一起撤，事务保险不出错。

5 动手实践：图书管理数据库

做一个完整的「图书管理」程序：添加、显示全部、按价格区间查询、删除，全部走参数化查询 + 事务提交。

# book_manager.py —— 图书管理数据库
import sqlite3

DB_FILE = "books.db"

def get_conn():
    """建表并返回连接（每步独立连接，用完关闭）"""
    conn = sqlite3.connect(DB_FILE)
    conn.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author TEXT,
        price REAL,
        year INTEGER
    )
    """)
    return conn

def add_book(title, author, price, year):
    conn = get_conn()
    conn.execute("INSERT INTO books (title, author, price, year) VALUES (?, ?, ?, ?)",
                 (title, author, price, year))
    conn.commit()
    conn.close()
    print(f"已添加《{title}》")

def show_all():
    conn = get_conn()
    rows = conn.execute("SELECT id, title, author, price, year FROM books").fetchall()
    conn.close()
    if not rows:
        print("书库为空")
        return
    print(f"{'ID':<4}{'书名':<28}{'作者':<20}{'价格':<8}{'年份'}")
    print("-" * 70)
    for id_, title, author, price, year in rows:
        print(f"{id_:<4}{title:<28}{str(author):<20}{price:<8}{year}")

def query_by_price(low, high):
    conn = get_conn()
    rows = conn.execute(
        "SELECT title, price, year FROM books WHERE price BETWEEN ? AND ? ORDER BY price",
        (low, high),
    ).fetchall()
    conn.close()
    if not rows:
        print(f"没有价格在 {low}~{high} 元的书")
        return
    for title, price, year in rows:
        print(f"《{title}》 {price} 元（{year} 年）")

def delete_book(book_id):
    conn = get_conn()
    conn.execute("DELETE FROM books WHERE id = ?", (book_id,))
    conn.commit()
    conn.close()
    print(f"已删除 ID={book_id} 的书")

def main():
    while True:
        print("\n1.添加图书  2.显示全部  3.价格查询  4.删除  5.退出")
        choice = input("请选择：").strip()
        if choice == "1":
            title = input("书名：")
            author = input("作者：")
            price = float(input("价格："))
            year = int(input("出版年份："))
            add_book(title, author, price, year)
        elif choice == "2":
            show_all()
        elif choice == "3":
            low = float(input("最低价格："))
            high = float(input("最高价格："))
            query_by_price(low, high)
        elif choice == "4":
            show_all()
            delete_book(int(input("输入要删除的 ID：")))
        elif choice == "5":
            print("再见")
            break
        else:
            print("无效选择")

if __name__ == "__main__":
    main()

运行效果：

1.添加图书  2.显示全部  3.价格查询  4.删除  5.退出
请选择：1
书名：三体
作者：刘慈欣
价格：68
出版年份：2008
已添加《三体》
请选择：2
ID  书名                          作者                  价格     年份
----------------------------------------------------------------------
3   Python 编程从入门到实践        Eric Matthes          89.0     2020
...
8   三体                          刘慈欣                68.0     2008

退出程序后再启动，数据还在——这就是数据库相比普通文件的价值：持久化 + 结构化 + 可查询。

升级挑战：加一个「按作者搜索」功能（WHERE author = ?），并把搜索关键词用 LIKE 改成模糊匹配。

今日总结

能说清「数据库 → 表 → 字段 → 记录」的层级关系，独立设计一张简单的表（想清楚字段类型：REAL/INTEGER/TEXT） 用标准库 sqlite3：connect → cursor → execute → fetch 三板斧，executemany 批量插入更高效 增删改查 CRUD：INSERT / SELECT / UPDATE / DELETE，WHERE 条件筛选、ORDER BY 排序、COUNT/AVG/SUM 统计 参数化查询：SQL 用 ? 占位、值走参数，绝不拼接——防住 SQL 注入（同一输入拼接版查出 3 行、参数化版查出 0 行） 事务机制：增删改后必须 commit()，出错 rollback() 全部撤销；「要么一起成功、要么一起不留」 图书管理数据库能添加、显示、价格查询、删除，数据重启不丢

报错	原因	修复
sqlite3.OperationalError: no such table: books	表还没创建就去查询	先执行 CREATE TABLE IF NOT EXISTS 再查询；检查数据库文件路径是否一致
sqlite3.OperationalError: near "?"	? 写在了不该出现的位置，或 SQL 语法错误	检查 SQL 语法；确认占位符只出现在 VALUES/WHERE 等值的位置
sqlite3.ProgrammingError: Incorrect number of bindings	占位符数量与传入参数数量不一致	数一数 ? 的个数，和第二个参数的元组元素个数对齐
增删改后数据没变	忘记调用 conn.commit()	每次增删改后执行 conn.commit()
查不到中文数据	写入和查询的编码不一致，或值里有多余空格	统一用 encoding="utf-8"；用 strip() 清理输入；LIKE 模糊匹配代替精确匹配