目录/第 66 章 · sqlite3 与 SQL
第 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) 并打印执行成功

我的代码草稿(可复制到本地 .py 文件运行)
参考答案(先自己写,再看)
参考答案一
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 全部并打印

我的代码草稿(可复制到本地 .py 文件运行)
参考答案(先自己写,再看)
参考答案一
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 的结果

我的代码草稿(可复制到本地 .py 文件运行)
参考答案(先自己写,再看)
参考答案一
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 并打印结果

我的代码草稿(可复制到本地 .py 文件运行)
参考答案(先自己写,再看)
参考答案一
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

我的代码草稿(可复制到本地 .py 文件运行)
参考答案(先自己写,再看)
参考答案一
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,打印表行数

我的代码草稿(可复制到本地 .py 文件运行)
参考答案(先自己写,再看)
参考答案一
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