第72课 - 数据库基础：SQLite 与 Python

学习目标

完成本课后，你将能够：

理解 SQLite：掌握 SQLite 的特点、适用场景及其在 Python 中的集成方式。
连接与操作数据库：使用 Python 标准库 sqlite3 模块连接、创建和管理 SQLite 数据库。
执行基本 CRUD 操作：通过 Python 代码执行数据库表的创建（Create）、数据插入（Insert）、查询（Read）、更新（Update）和删除（Delete）。
管理事务与连接：理解数据库事务的概念，并安全地关闭数据库连接。

核心概念

1. 什么是 SQLite？

SQLite 是一个轻量级的、嵌入式的关系型数据库。它的"轻量级"体现在整个数据库（定义、表、索引和数据）都存储在单个文件中（如 .db 或 .sqlite 文件）。你不需要安装和配置一个独立的数据库服务器，非常适合用于本地应用、移动App、小型项目或原型开发。

2. Python 的 `sqlite3` 模块

Python 的标准库自带了 sqlite3 模块，它提供了基于 DB-API 2.0 规范的数据库接口。这意味着你无需安装任何第三方库（如 SQLAlchemy），即可直接操作 SQLite 数据库。

核心工作流：

建立连接：通过 sqlite3.connect('数据库文件名') 创建一个连接对象。
创建游标：通过连接对象的 cursor() 方法创建一个游标对象。游标用于执行 SQL 语句和获取查询结果。
执行 SQL：使用游标的 execute() 方法执行各种 SQL 命令。
提交与回滚：对于数据变更操作（INSERT, UPDATE, DELETE），需要调用连接对象的 commit() 方法来提交事务，使更改永久生效。如果出错，可调用 rollback() 回滚。
关闭连接：操作完成后，关闭游标和连接。

代码示例

以下是一个完整的示例，涵盖了创建数据库、创建表、插入数据、查询数据、更新数据和删除数据的全过程。

import sqlite3

def main():
    # 1. 连接到数据库（如果文件不存在，会自动创建）
    # 使用 'with' 语句可以确保连接在代码块结束后自动关闭
    with sqlite3.connect('students.db') as conn:
        print("成功连接到数据库 students.db")

        # 2. 创建一个游标对象
        cursor = conn.cursor()

        # 3. 创建一个名为 'students' 的表（如果尚不存在）
        # 使用 """ 多行字符串编写 SQL 语句更清晰
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 自增主键
            name TEXT NOT NULL,                    -- 学生姓名，不能为空
            age INTEGER,                           -- 年龄
            grade TEXT                             -- 年级
        );
        """
        cursor.execute(create_table_sql)
        print("表 'students' 已准备就绪。")

        # 4. 插入一些示例数据
        # 使用占位符 ? 来安全地插入参数，避免 SQL 注入
        students_data = [
            ('张三', 18, '高一'),
            ('李四', 19, '高二'),
            ('王五', 17, '高一')
        ]
        insert_sql = "INSERT INTO students (name, age, grade) VALUES (?, ?, ?);"
        cursor.executemany(insert_sql, students_data)  # executemany 可插入多行
        print(f"成功插入 {len(students_data)} 条学生记录。")

        # 重要：提交事务，使插入操作生效
        conn.commit()

        # 5. 查询所有学生数据
        select_sql = "SELECT id, name, age, grade FROM students;"
        cursor.execute(select_sql)
        # cursor.fetchall() 返回一个包含所有结果行的列表
        # 每一行是一个元组 (tuple)
        all_students = cursor.fetchall()
        print("\n所有学生信息：")
        for student in all_students:
            print(f"ID: {student[0]}, 姓名: {student[1]}, 年龄: {student[2]}, 年级: {student[3]}")

        # 6. 条件查询：查询高一的学生
        select_by_grade_sql = "SELECT name, age FROM students WHERE grade = ?;"
        cursor.execute(select_by_grade_sql, ('高一',))  # 注意参数是元组 ('高一',)
        grade1_students = cursor.fetchall()
        print(f"\n高一的学生 ({len(grade1_students)}人):")
        for student in grade1_students:
            print(f"  {student[0]}, {student[1]}岁")

        # 7. 更新数据：将张三的年龄更新为19岁
        update_sql = "UPDATE students SET age = ? WHERE name = ?;"
        cursor.execute(update_sql, (19, '张三'))
        conn.commit()
        print("\n已将张三的年龄更新为19岁。")

        # 8. 删除数据：删除名为王五的学生
        delete_sql = "DELETE FROM students WHERE name = ?;"
        cursor.execute(delete_sql, ('王五',))
        conn.commit()
        print("已删除学生王五。")

        # 9. 验证更新和删除的结果
        cursor.execute(select_sql)
        final_students = cursor.fetchall()
        print("\n最终学生列表：")
        for student in final_students:
            print(f"ID: {student[0]}, 姓名: {student[1]}, 年龄: {student[2]}, 年级: {student[3]}")

    # 'with' 语句块结束，连接会自动关闭
    print("\n数据库连接已关闭。")

if __name__ == "__main__":
    main()

运行输出（首次运行时）：

成功连接到数据库 students.db
表 'students' 已准备就绪。
成功插入 3 条学生记录。

所有学生信息：
ID: 1, 姓名: 张三, 年龄: 18, 年级: 高一
ID: 2, 姓名: 李四, 年龄: 19, 年级: 高二
ID: 3, 姓名: 王五, 年龄: 17, 年级: 高一

高一的学生 (2人):
  张三, 18岁
  王五, 17岁

已将张三的年龄更新为19岁。
已删除学生王五。

最终学生列表：
ID: 1, 姓名: 张三, 年龄: 19, 年级: 高一
ID: 2, 姓名: 李四, 年龄: 19, 年级: 高二

数据库连接已关闭。

实践练习

练习 1：创建书籍数据库（基础）

创建一个名为 books.db 的 SQLite 数据库，并在其中创建一个 books 表，包含以下字段：

id (整数，主键，自增)
title (文本，非空)
author (文本)
publication_year (整数) 然后，插入两本你喜欢的书籍的信息，并查询并打印所有书籍。

预期输出示例：

数据库连接成功。
表 'books' 创建完成。
插入数据成功。
书籍列表：
1. 《三体》 - 刘慈欣 (2008)
2. 《百年孤独》 - 加西亚·马尔克斯 (1967)

练习 2：多条件查询（进阶）

在上面的 books.db 基础上：

再插入一本2020年出版的书籍。
编写查询，找出所有 publication_year 大于 2000 年的书籍，并打印它们的标题和作者。

预期输出示例：

插入新书成功。
2000年以后的书籍：
《三体》 - 刘慈欣
《代码整洁之道》 - 罗伯特·C·马丁

练习 3：数据更新与删除（综合）

在 books.db 中：

将作者为"刘慈欣"的书籍的出版年份更新为 2006。
删除 publication_year 小于 2000 的书籍。
查询并打印最终剩余的所有书籍。

常见错误

忘记 commit()：执行了 INSERT、UPDATE 或 DELETE 语句后，如果没有调用 conn.commit()，数据不会被保存到数据库文件中。这是初学者最常见的错误。
SQL 注入风险：绝对不要使用 Python 的字符串格式化（如 f-string 或 %）来拼接 SQL 语句的参数。
- 错误：cursor.execute(f"SELECT * FROM students WHERE name = '{name}'")
- 正确：cursor.execute("SELECT * FROM students WHERE name = ?", (name,)) 使用参数化查询（占位符 ?）是防止 SQL 注入攻击的唯一安全方法。
连接未关闭：虽然使用 with 语句可以自动关闭连接，但如果手动管理连接，请确保在 finally 块中关闭它，否则可能导致文件锁或资源泄漏。
误解 fetchall()：对于大型结果集，fetchall() 会将所有数据一次性加载到内存。对于海量数据，应使用 fetchone() 或 fetchmany() 进行迭代处理。
表或字段名拼写错误：SQL 语句中的表名、字段名必须与创建时完全一致（通常不区分大小写，但建议保持统一）。

小结

SQLite 是 Python 中进行轻量级数据持久化存储的理想选择，它无需服务器，以文件形式存在。
使用 Python 的 sqlite3 模块，核心流程是：连接 -> 创建游标 -> 执行 SQL -> 提交事务 -> 关闭连接。
CRUD 是数据库操作的四大基石：CREATE TABLE (建表)、INSERT INTO (插入)、SELECT (查询)、UPDATE (更新)、DELETE (删除)。
安全与健壮性至关重要：务必使用参数化查询防止 SQL 注入，并及时 commit() 数据变更。
with sqlite3.connect(...) 语法是管理数据库连接的推荐方式，能确保资源被正确释放。

你现在已经掌握了使用 Python 操作数据库的入门钥匙。下一步，我们将深入学习更复杂的 SQL 查询语句。

练习编辑器

python