代码收藏家 Python 魔法学院 – 第29篇：Python 数据库操作-SQLite ⭐⭐⭐
目录
引言
想象一下，你是一名魔法学院的图书管理员，负责管理成千上万的魔法书。每本书都包含了珍贵的魔法知识，而你的任务就是高效地存储、查找和更新这些知识。在编程的世界里，数据库就是你的魔法书架，而 SQLite 则是一个轻便、灵活的书架，适合存放小型但重要的数据。
今天，我们将一起学习如何使用 Python 操作 SQLite 数据库，掌握这门“魔法”，让你在数据的世界里游刃有余。
1. SQLite 简介
1.1 什么是 SQLite？
SQLite 是一个嵌入式数据库引擎，它不需要单独的服务器进程，数据库就是一个文件。你可以把它想象成一个魔法书架，书架本身就是一个文件，里面存放着各种魔法书（表和数据）。
1.2 SQLite 的优点
| 优点 | 说明 |
|---|---|
| 轻量级 | SQLite 非常小巧，适合嵌入到应用程序中，就像一个小型魔法书架。 |
| 零配置 | 无需安装或配置，直接使用，就像打开一本书一样简单。 |
| 跨平台 | 支持 Windows、Linux、macOS 等多种操作系统，适合任何魔法学院。 |
| 开源 | SQLite 是开源的，可以自由使用和修改，就像一本开放的魔法书。 |
2. Python 中的 SQLite 操作
2.1 安装 SQLite
Python 自带了 sqlite3 模块，因此你无需额外安装任何东西。只需在代码中导入 sqlite3 模块即可开始使用。
import sqlite3
2.2 连接数据库
在 SQLite 中，数据库是一个文件。我们可以使用 sqlite3.connect() 方法来连接数据库。如果数据库文件不存在，SQLite 会自动创建一个新的数据库文件。
# 连接到一个名为 example.db 的数据库文件
conn = sqlite3.connect('example.db')
小提示：conn 是数据库连接对象，就像你打开了一个魔法书架的门。
2.3 创建表
在数据库中，数据存储在表中。我们可以使用 CREATE TABLE 语句来创建表。
# 创建一个名为 users 的表
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.commit()
代码解释：
cursor：游标对象，用于执行 SQL 语句。CREATE TABLE：创建表的 SQL 语句。IF NOT EXISTS：如果表不存在则创建，避免重复创建。conn.commit()：提交事务，保存更改。表结构：
| 字段名 | 类型 | 说明 |
|---|---|---|
id |
INTEGER |
主键，唯一标识每条记录 |
name |
TEXT |
用户名 |
age |
INTEGER |
用户年龄 |
2.4 插入数据
插入数据使用 INSERT INTO 语句。
# 插入两条用户数据
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 30)")
conn.commit()
代码解释：
INSERT INTO：插入数据的 SQL 语句。VALUES：指定要插入的值。插入后的数据：
| id | name | age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | 30 |
2.5 查询数据
查询数据使用 SELECT 语句。
# 查询所有用户数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
结果为：
(1, 'Alice', 25)
(2, 'Bob', 30)
代码解释：
SELECT * FROM users：查询 users 表中的所有数据。fetchall()：获取所有查询结果。2.6 更新数据
更新数据使用 UPDATE 语句。
# 将 Alice 的年龄更新为 26
cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
conn.commit()
更新后的数据：
| id | name | age |
|---|---|---|
| 1 | Alice | 26 |
| 2 | Bob | 30 |
2.7 删除数据
删除数据使用 DELETE 语句。
# 删除名为 Bob 的用户
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
conn.commit()
删除后的数据：
| id | name | age |
|---|---|---|
| 1 | Alice | 26 |
2.8 关闭连接
操作完成后，记得关闭数据库连接。
conn.close()
小提示：关闭连接就像关上魔法书架的门，确保数据安全。
3. SQLite 操作的高级技巧
3.1 使用上下文管理器
Python 的 with 语句可以帮助我们自动管理资源的释放，避免忘记关闭连接。
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
代码解释：
with 语句会自动关闭连接，无需手动调用 conn.close()。3.2 使用参数化查询
为了防止 SQL 注入，建议使用参数化查询。
name = 'Alice'
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))
rows = cursor.fetchall()
for row in rows:
print(row)
代码解释：
? 是占位符，(name,) 是参数值。3.3 事务处理
SQLite 支持事务处理，可以确保数据的一致性。
try:
cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 28)")
cursor.execute("INSERT INTO users (name, age) VALUES ('David', 35)")
conn.commit()
except sqlite3.Error as e:
print(f"An error occurred: {e}")
conn.rollback()
代码解释：
conn.commit()：提交事务。conn.rollback()：回滚事务，撤销未提交的更改。4. 实战案例：学生成绩管理系统
4.1 需求分析
我们需要创建一个学生成绩管理系统，能够实现以下功能：
4.2 数据库设计
我们设计一个 students 表，包含以下字段：
id: 学生ID，主键，自增name: 学生姓名age: 学生年龄grade: 学生年级4.3 代码实现
import sqlite3
def create_table():
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS students
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL,
grade TEXT NOT NULL)''')
conn.commit()
def add_student(name, age, grade):
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
cursor.execute("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)", (name, age, grade))
conn.commit()
def get_students():
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
return cursor.fetchall()
def update_student(student_id, name, age, grade):
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
cursor.execute("UPDATE students SET name = ?, age = ?, grade = ? WHERE id = ?", (name, age, grade, student_id))
conn.commit()
def delete_student(student_id):
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM students WHERE id = ?", (student_id,))
conn.commit()
# 示例操作
create_table()
add_student('Alice', 20, 'A')
add_student('Bob', 22, 'B')
print(get_students())
update_student(1, 'Alice Smith', 21, 'A+')
print(get_students())
delete_student(2)
print(get_students())
结果为：
[(1, 'Alice', 20, 'A'), (2, 'Bob', 22, 'B')]
[(1, 'Alice Smith', 21, 'A+'), (2, 'Bob', 22, 'B')]
[(1, 'Alice Smith', 21, 'A+')]
5. 常见问题解答（FAQ）
Q1：SQLite 数据库文件损坏了怎么办？
SQLite 数据库文件可能会因为意外断电、程序崩溃或文件系统错误而损坏。以下是解决方法：
- 使用 SQLite 命令行工具修复：
- 打开命令行工具，运行以下命令：
sqlite3 damaged.db ".recover" | sqlite3 recovered.db - 这将尝试修复损坏的数据库文件并生成一个新的文件
recovered.db。 - 备份数据库：
- 定期备份数据库文件，避免数据丢失。
- 使用 WAL 模式：
- 启用 SQLite 的 WAL（Write-Ahead Logging）模式，可以提高数据库的稳定性和性能：
PRAGMA journal_mode=WAL;
Q2：如何优化 SQLite 查询性能？
- 使用索引：
- 为经常查询的字段创建索引，例如：
CREATE INDEX idx_name ON users (name); - 索引可以显著加快查询速度，但会增加插入和更新操作的开销。
- 避免在循环中执行 SQL 语句：
- 尽量将多次操作合并为一次批量操作，例如使用
executemany()：data = [('Alice', 25), ('Bob', 30)] cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data) - 使用事务：
- 将多个操作放在一个事务中，减少磁盘 I/O 操作：
with conn: cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)") cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 30)")
Q3：SQLite 支持多线程吗？
SQLite 支持多线程，但需要注意以下几点：
- 线程安全模式：
- 确保 SQLite 编译时启用了线程安全模式（默认情况下是启用的）。
- 连接对象：
- 每个线程应该使用独立的数据库连接对象，避免多个线程共享同一个连接。
- WAL 模式：
- 启用 WAL 模式可以提高多线程并发性能：
PRAGMA journal_mode=WAL;
Q4：SQLite 数据库文件可以加密吗？
SQLite 本身不支持加密，但可以通过以下方式实现：
- 使用第三方库：
- 例如
sqlcipher，它是一个支持加密的 SQLite 分支。 - 文件系统加密：
- 将数据库文件放在加密的文件系统或磁盘分区中。
6. 总结
通过本文的学习，你已经掌握了如何使用 Python 操作 SQLite 数据库。SQLite 虽然轻量，但功能强大，非常适合小型应用和原型开发。希望你能通过本文的学习，进一步提升自己的 Python 开发技能，成为一名真正的“魔法师”。
恭喜你完成了本次学习任务！ 你已经掌握了 Python 操作 SQLite 数据库的基本技能，接下来可以尝试在实际项目中应用这些知识，进一步提升自己的开发能力。
作者：码力全開
