数据库基本操作

Python 操作 SQLite 数据库：连接池、游标与事务控制全解析

小象实战讲义 · Python数据分析实战

在前一节中，我们学习了 SQL 的核心语法与查询逻辑。在 Python 数据分析实战中，我们通常需要通过程序自动化连接数据库、批量导入数据、动态执行 SQL 查询并将结果转化为 Pandas DataFrame 进行后续的可视化与探索。Python 内置的 sqlite3 标准库提供了一套高度符合 Python DB-API 2.0 规范的轻量级数据库驱动。本节将深入剖析数据库连接对象（Connection）、游标对象（Cursor）、结果抓取（Fetch）以及事务提交（Commit / Rollback）的完整控制链路。

💡 核心导读

• SQLite 驱动架构：理解 Connection（物理连接与事务边界）与 Cursor（工作区与结果集指针）的分工。
• 批量数据入库：掌握 cursor.execute() 与 cursor.executemany() 的性能差异与参数化防注入规范。
• 结果集抓取三剑客：精通 fetchone()、fetchmany(size) 与 fetchall() 的内存与遍历策略。
• 事务与持久化控制：深入理解 conn.commit()、conn.rollback() 与 with conn: 上下文事务管理。
• Pandas 与 SQLite 无缝对接：掌握 pd.read_sql_query() 与 df.to_sql() 的极速数据流转。

1. sqlite3 核心架构：连接与游标

在 Python 中操作 SQLite 数据库，核心依靠两个关键对象协同工作：

┌─────────────────────────────────────────────────────────────┐
│                    Python DB-API 2.0 运行模型               │
├─────────────────────────────────────────────────────────────┤
│ 1. Connection (连接对象) ➔ 代表与数据库文件的物理连接通道      │
│    • conn = sqlite3.connect("database.db")                  │
│    • 负责事务提交 (conn.commit()) 与连接关闭 (conn.close()) │
├─────────────────────────────────────────────────────────────┤
│ 2. Cursor (游标对象) ➔ 私有的 SQL 工作区与结果集指针        │
│    • cursor = conn.cursor()                                 │
│    • 负责发送并执行 SQL 指令 (cursor.execute())             │
│    • 负责按需拉取查询结果 (cursor.fetchall())                │
└─────────────────────────────────────────────────────────────┘

注意参数化查询规范：为了防止 SQL 注入并提高执行效率，向 SQL 语句中动态传入变量时，必须使用占位符 ?（参数化查询），严禁使用 Python 字符串拼接（如 f"SELECT * WHERE id={user_input}"）。

2. 结果集提取：Fetch API 矩阵对比

执行 SELECT 查询后，数据库会将结果集保存在游标的缓冲区中，我们可以通过以下三种方式读取：

3. 事务控制（Transaction Control）与数据持久化

在关系型数据库中，所有的插入、更新与删除操作默认都在一个**事务（Transaction）**中进行：

• conn.commit()：显式提交事务，将内存中受影响的更改真正持久化写入磁盘文件；
• conn.rollback()：回滚事务，如果在批量操作中途发生异常，撤销当前事务内的所有修改，保障数据的一致性；
• 如果忘记调用 conn.commit() 就直接执行了 conn.close()，所有的写入操作都将被系统悄然丢弃！

4. Python 代码实战：从原生游标操作到 Pandas 极速 SQL 对接

下面我们通过一段 Python 脚本，完整演示从创建本地 .db 文件、批量安全插入数据、事务回滚保护，到使用 Pandas pd.read_sql_query() 极速加载数据的全过程。

# 示例 1：使用 sqlite3 进行参数化插入、游标遍历与事务回滚演示

import sqlite3
import os
import pandas as pd

db_filename = "store_database.db"

# 1. 建立数据库连接并获取游标
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()

# 2. 创建商品库存表 (products)
cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    category TEXT,
    price REAL,
    stock INTEGER
);
""")

# 3. 使用 executemany 进行参数化批量插入 (使用 ? 占位符)
initial_products = [
    ("降噪蓝牙耳机", "数码", 399.0, 50),
    ("人体工学校椅", "家居", 680.0, 20),
    ("4K机械键盘", "数码", 299.0, 100),
    ("保温咖啡杯", "百货", 88.0, 200),
    ("智能手环", "数码", 199.0, 80)
]

insert_sql = "INSERT INTO products (title, category, price, stock) VALUES (?, ?, ?, ?);"
cursor.executemany(insert_sql, initial_products)
conn.commit()  # 提交事务
print(f"✅ 成功批量插入 {cursor.rowcount} 条商品记录！")

接下来，我们演示使用游标获取数据，并通过 Pandas 直接从 SQLite 中执行 SQL 统计查询：

# 示例 2：游标 fetch 演示与 Pandas 结合极速分析

# 1. 游标单条与批量读取演示
cursor.execute("SELECT title, price, stock FROM products WHERE category = '数码';")

# 抓取第一条
first_item = cursor.fetchone()
print(f"\n[fetchone 读取] 第一款数码产品: 名称={first_item[0]}, 价格={first_item[1]}元")

# 抓取剩余所有数码产品
remaining_items = cursor.fetchall()
print(f"[fetchall 读取] 剩余 {len(remaining_items)} 款数码产品:")
for title, price, stock in remaining_items:
    print(f"   • {title:<10} | 售价: {price:>6.1f}元 | 库存: {stock}件")

# 2. 进阶：使用 Pandas 的 pd.read_sql_query 一键将 SQL 查询转为 DataFrame
query_sql = """
SELECT category, 
       COUNT(*) AS item_count, 
       AVG(price) AS avg_price, 
       SUM(price * stock) AS total_inventory_value
FROM products
GROUP BY category
ORDER BY total_inventory_value DESC;
"""

# 直接传入 SQL 与连接对象，无需手动游标与类型转换
summary_df = pd.read_sql_query(query_sql, conn)

print("\n=== 使用 Pandas pd.read_sql_query 聚合分析结果 ===")
print(summary_df)

# 关闭连接
conn.close()

# 清理测试数据库文件
if os.path.exists(db_filename):
    os.remove(db_filename)

📝 动手练一练

1. 安全与规范分析题：为什么在执行 SQL 插入时，强烈反对写成 cursor.execute(f"INSERT INTO users VALUES ('{user_name}')") 这种字符串格式化方式？应该采用什么标准写法？

   👉 点击查看参考答案

   参考答案： 使用 Python 字符串格式化拼接 SQL 语句会产生严重的 SQL 注入（SQL Injection） 安全漏洞。当外部输入包含特殊 SQL 字符（如 ' OR 1=1 --）时，会破坏原本的 SQL 逻辑，甚至导致全表数据被恶意篡改或删除。 标准安全写法：必须使用参数化占位符 ? 并以元组传参： cursor.execute("INSERT INTO users VALUES (?)", (user_name,))。

2. 编程练习：请编写一段 Python 脚本，使用 sqlite3.connect(":memory:") 创建一个内存数据库，新建一张 students (id, name, score) 表，插入 3 名学生的成绩（如 张三: 90, 李四: 82, 王五: 95），然后使用 pd.read_sql_query() 查询出成绩大于 85 分的所有学生并打印成 DataFrame。

   👉 点击查看参考答案

   参考答案：

   import sqlite3
   import pandas as pd
   
   conn = sqlite3.connect(":memory:")
   cursor = conn.cursor()
   
   # 建表与插入
   cursor.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);")
   cursor.executemany("INSERT INTO students (name, score) VALUES (?, ?);", [("张三", 90), ("李四", 82), ("王五", 95)])
   conn.commit()
   
   # Pandas 读取
   df_top = pd.read_sql_query("SELECT * FROM students WHERE score > 85;", conn)
   print("=== 成绩大于 85 分的优秀学员 ===")
   print(df_top)
   
   conn.close()

本章小结

在本节中，我们掌握了 Python 驱动数据库的核心技术：

• 深刻理解 Connection（连接与事务）和 Cursor（工作区与指针）的职责划分；
• 熟练运用 fetchone、fetchmany 与 fetchall 进行按需结果拉取；
• 牢记 conn.commit() 事务提交与参数化 ? 占位符安全规范；
• 掌握 pd.read_sql_query() 将 SQL 高度集成为 Pandas 数据流的工业级分析技巧。

📋 行动清单

• 理解为什么说 SQLite 是单机数据分析、小微系统与本地特征存储的最佳拍档。
• 尝试在自己电脑上创建一个 .db 数据库文件，并用 Python 写入几条测试数据。

—— 小象教研组