第 66 章 / 70 · 工程 · 测试与第三方
sqlite3 与 SQL
连接 / 建表 / 增删改查 / 参数化 / 事务 / 内存数据库
先介绍1 个 demo6 个任务答案可展开
一、本章介绍
sqlite3 是 Python 内置的关系型数据库,不需要安装服务器,一个文件就是一个数据库(用 :memory: 则是纯内存库)。它是课堂实战「本地数据库」章的核心工具。
这一章用内存数据库演示完整流程:连接、建表、插入、查询、条件过滤、更新、删除、聚合统计、参数化查询(防 SQL 注入)和事务提交/回滚。
本章要点
- connect(':memory:') 内存库
- CREATE TABLE 建表
- INSERT / SELECT / UPDATE / DELETE
- 参数化 ? 占位符防注入
- fetchone / fetchall 取结果
- commit 与 rollback 事务
二、先看 demo
下面这个例子把本章涉及的用法尽量全面地串了一遍,代码带逐行注释,可以直接复制到本地 .py 文件运行。
案例
先看一个完整例子
介绍 · 逐行注释import sqlite3
# 1) 连接内存数据库(文件库把 ':memory:' 换成 '.db' 路径)
con = sqlite3.connect(":memory:")
cur = con.cursor()
# 2) 建表
cur.execute("CREATE TABLE students (name TEXT, score INTEGER)")
# 3) 插入:用 ? 参数化,禁止字符串拼接
cur.executemany("INSERT INTO students VALUES (?, ?)",
[("小明", 90), ("小红", 85), ("小刚", 70)])
con.commit()
# 4) 查询全部
cur.execute("SELECT * FROM students")
print("全部", cur.fetchall())
# 5) 条件查询(仍用参数化)
cur.execute("SELECT name, score FROM students WHERE score >= ?", (85,))
print("高分", cur.fetchall())
# 6) 取一条
cur.execute("SELECT * FROM students WHERE name=?", ("小红",))
print("一条", cur.fetchone())
# 7) 更新与删除
cur.execute("UPDATE students SET score=? WHERE name=?", (95, "小刚"))
cur.execute("DELETE FROM students WHERE name=?", ("小红",))
con.commit()
cur.execute("SELECT * FROM students ORDER BY score DESC")
print("改后", cur.fetchall())
# 8) 聚合统计
cur.execute("SELECT COUNT(*), AVG(score) FROM students")
print("统计", cur.fetchone())
# 9) 事务回滚
cur.execute("INSERT INTO students VALUES (?, ?)", ("临时", 0))
con.rollback()
cur.execute("SELECT COUNT(*) FROM students")
print("回滚后数量", cur.fetchone()[0])
con.close()运行结果
全部 [('小明', 90), ('小红', 85), ('小刚', 70)]
高分 [('小明', 90), ('小红', 85)]
一条 ('小红', 85)
改后 [('小刚', 95), ('小明', 90)]
统计 (2, 92.5)
回滚后数量 2要点:永远用 ? 占位符传参数,不要用 f-string 拼接 SQL(会被注入)。executemany 批量插入;fetchone 取一行、fetchall 取全部。写操作后要 con.commit() 才落盘,con.rollback() 撤销未提交的改动。:memory: 让数据库只存在于内存,适合练习。
三、6 个任务(自己先写,再看答案)
难度从入门到挑战逐步增加。每个任务的答案默认折叠,点开可看参考代码、运行结果;有第二种解法的也一并给出。代码都可以直接运行。
任务 1
连接与建表
自己写 · 附答案入门 ★☆☆解题提示:本题主要用到 connect(':memory:') 内存库、CREATE TABLE 建表、INSERT / SELECT / UPDATE / DELETE。先对照要求自己写一遍,卡住了再展开答案。
用内存库建表 t(a TEXT) 并打印执行成功
参考答案(先自己写,再看)
参考答案一
import sqlite3
con=sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a TEXT)")
print("ok")运行结果
ok任务 2
插入与查询
自己写 · 附答案入门 ★☆☆解题提示:本题主要用到 connect(':memory:') 内存库、CREATE TABLE 建表、INSERT / SELECT / UPDATE / DELETE。先对照要求自己写一遍,卡住了再展开答案。
插入 ('x',) 后 SELECT 全部并打印
参考答案(先自己写,再看)
参考答案一
import sqlite3
con=sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a TEXT)")
con.execute("INSERT INTO t VALUES(?)",("x",))
print(con.execute("SELECT * FROM t").fetchall())运行结果
[('x',)]任务 3
条件查询
自己写 · 附答案进阶 ★★☆解题提示:本题主要用到 connect(':memory:') 内存库、CREATE TABLE 建表、INSERT / SELECT / UPDATE / DELETE。先对照要求自己写一遍,卡住了再展开答案。
插入 1,2,3 后查 a>1 的结果
参考答案(先自己写,再看)
参考答案一
import sqlite3
con=sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a INT)")
con.executemany("INSERT INTO t VALUES(?)",[(1,),(2,),(3,)])
print(con.execute("SELECT a FROM t WHERE a>?",(1,)).fetchall())运行结果
[(2,), (3,)]任务 4
更新数据
自己写 · 附答案进阶 ★★☆解题提示:本题主要用到 connect(':memory:') 内存库、CREATE TABLE 建表、INSERT / SELECT / UPDATE / DELETE。先对照要求自己写一遍,卡住了再展开答案。
插入后 UPDATE 并打印结果
参考答案(先自己写,再看)
参考答案一
import sqlite3
con=sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a INT)")
con.execute("INSERT INTO t VALUES(?)",(1,))
con.execute("UPDATE t SET a=?",(9,))
print(con.execute("SELECT * FROM t").fetchall())运行结果
[(9,)]任务 5
聚合统计
自己写 · 附答案挑战 ★★★解题提示:本题主要用到 connect(':memory:') 内存库、CREATE TABLE 建表、INSERT / SELECT / UPDATE / DELETE。先对照要求自己写一遍,卡住了再展开答案。
插入 3 行后统计 COUNT 与 MAX
参考答案(先自己写,再看)
参考答案一
import sqlite3
con=sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a INT)")
con.executemany("INSERT INTO t VALUES(?)",[(1,),(5,),(3,)])
print(con.execute("SELECT COUNT(*),MAX(a) FROM t").fetchone())运行结果
(3, 5)任务 6
事务回滚
自己写 · 附答案挑战 ★★★解题提示:本题主要用到 connect(':memory:') 内存库、CREATE TABLE 建表、INSERT / SELECT / UPDATE / DELETE。先对照要求自己写一遍,卡住了再展开答案。
插入后 rollback,打印表行数
参考答案(先自己写,再看)
参考答案一
import sqlite3
con=sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a INT)")
con.execute("INSERT INTO t VALUES(?)",(1,))
con.rollback()
print(con.execute("SELECT COUNT(*) FROM t").fetchone()[0])运行结果
0