【Python从入门到进阶】使用Python轻松操作SQLite数据库

作者： 分类：所有,python 字数：8942

'# 【Python从入门到进阶】使用Python轻松操作SQLite数据库

一、背景与问题

SQLite 是一个轻量级的嵌入式数据库系统，其核心特点在于无需独立服务器进程即可直接通过 C 语言接口操作数据库。对于 Python 开发者而言，sqlite3 模块提供了对 SQLite 的完整封装，使得数据库操作变得异常简单。

在实际开发中，SQLite 适合用于以下场景：

  • 单机应用的数据持久化（如配置文件、日志记录）
  • 测试环境的临时数据库
  • 小型项目的核心数据存储
  • 本地缓存的持久化存储

但需要注意其局限性：

  • 不适合高并发写入场景（默认并发写入限制为1）
  • 不支持分布式部署
  • 需要手动管理事务和锁机制
  • 数据库文件大小受文件系统限制（通常不超过140MB）

二、基本原理

SQLite 采用文件存储模式，所有数据存储在单个 .sqlite 文件中。其核心存储结构包括：

  1. B-tree 索引结构（用于快速查找）
  2. 页缓存机制（提高读写效率）
  3. 自动增长的文件空间管理
  4. 事务日志机制（保证数据一致性）

Python 的 sqlite3 模块通过以下机制与 SQLite 交互：

  • 使用 connect() 建立数据库连接
  • 通过 cursor() 获取操作句柄
  • 使用 SQL 语句执行增删改查操作
  • 通过 commit() 提交事务
  • 使用 execute()/executemany() 执行 SQL

三、环境准备

确保 Python 环境已安装 sqlite3 模块（Python 3.3+ 自带）：

python3 -m pip install sqlite3

创建测试数据库文件：

import sqlite3

# 创建数据库文件
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
conn.commit()
conn.close()

四、核心实现

1. 基础连接与操作

import sqlite3

# 基础连接
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 创建表（仅当不存在时）
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
""")

# 插入数据
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

conn.close()

关键代码解释：

  • ? 占位符用于防止 SQL 注入
  • AUTOINCREMENT 保证主键自增
  • commit() 必须显式提交事务
  • 查询结果通过 fetchall() 获取

2. 事务处理

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

try:
    # 开始事务
    cursor.execute("BEGIN")
    
    # 批量插入
    cursor.executemany(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        [("Bob", 25), ("Charlie", 35)]
    )
    
    # 原子性操作
    cursor.execute("UPDATE users SET age = age + 1 WHERE age < 30")
    
    # 提交事务
    conn.commit()
except Exception as e:
    # 回滚事务
    conn.rollback()
    print(f"Transaction failed: {e}")
finally:
    conn.close()

关键点：

  • 使用 BEGIN/COMMIT/ROLLBACK 显式控制事务
  • executemany() 优化批量操作
  • 异常处理确保数据一致性

3. 索引优化

# 创建索引
cursor.execute("CREATE INDEX IF NOT EXISTS idx_name ON users (name)")

# 查询优化
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
print(cursor.fetchone())

索引原理：

  • B-tree 索引支持快速查找
  • 聚簇索引（CLUSTERED）提升查询效率
  • 避免全表扫描（SELECT * FROM...）

五、完整案例：学生信息管理系统

项目结构

student_system/
├── main.py
├── database.py
├── gui.py
└── utils.py

数据库操作模块 (database.py)

import sqlite3

def init_db():
    conn = sqlite3.connect('student.db')
    cursor = conn.cursor()
    
    # 创建学生表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            grade INTEGER,
            score REAL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # 创建索引
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_grade ON students (grade)")
    conn.commit()
    conn.close()

图形界面 (gui.py)

import tkinter as tk
from database import init_db

class StudentApp:
    def __init__(self, root):
        self.root = root
        self.root.title("学生信息管理系统")
        self.create_widgets()
        
    def create_widgets(self):
        self.name_entry = tk.Entry(self.root)
        self.name_entry.pack()
        
        self.grade_entry = tk.Entry(self.root)
        self.grade_entry.pack()
        
        self.score_entry = tk.Entry(self.root)
        self.score_entry.pack()
        
        self.add_button = tk.Button(self.root, text="添加学生", command=self.add_student)
        self.add_button.pack()
        
        self.list_button = tk.Button(self.root, text="查看学生", command=self.list_students)
        self.list_button.pack()
        
    def add_student(self):
        name = self.name_entry.get()
        grade = self.grade_entry.get()
        score = self.score_entry.get()
        
        conn = sqlite3.connect('student.db')
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO students (name, grade, score) VALUES (?, ?, ?)",
            (name, grade, score)
        )
        conn.commit()
        conn.close()
        
        self.name_entry.delete(0, tk.END)
        self.grade_entry.delete(0, tk.END)
        self.score_entry.delete(0, tk.END)
        
    def list_students(self):
        conn = sqlite3.connect('student.db')
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM students")
        for row in cursor.fetchall():
            print(row)
        conn.close()

主程序 (main.py)

if __name__ == "__main__":
    init_db()
    root = tk.Tk()
    app = StudentApp(root)
    root.mainloop()

功能说明：

  • 支持添加学生信息（姓名、年级、分数）
  • 支持查看所有学生记录
  • 自动创建数据库和索引
  • 使用 Tkinter 实现图形界面

六、源码解析

1. 数据库连接机制

conn = sqlite3.connect('student.db')
  • 如果文件不存在会自动创建
  • 如果文件存在则直接连接
  • 支持文件路径的相对/绝对路径

2. 事务处理机制

cursor.execute("BEGIN")
# ... 多条SQL语句
conn.commit()
  • BEGIN 会启动一个事务
  • COMMIT 会提交所有更改
  • ROLLBACK 会撤销所有更改
  • 事务处理确保数据一致性

3. 索引优化原理

CREATE INDEX idx_grade ON students (grade)
  • 索引会创建一个辅助数据结构
  • 查询时会优先使用索引
  • 适合频繁查询的字段（如 grade）
  • 会占用额外存储空间

七、进阶使用

1. 使用 SQLite 的扩展功能

# JSON 支持
cursor.execute("SELECT json_object('name' value name) FROM students")

2. 多线程访问

import threading

def worker():
    conn = sqlite3.connect('student.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM students")
    print(cursor.fetchall())
    conn.close()

# 线程安全使用
threads = [threading.Thread(target=worker) for _ in range(10)]
for t in threads:
    t.start()

3. 与 MySQL 的对比

特性SQLiteMySQL
并发写入1 个写者支持多写者
分布式支持不支持支持
事务机制支持 ACID支持 ACID
性能较低较高
学习曲线极低中等

八、性能与工程实践

1. 性能优化方案

问题解决方案优化效果
频繁写入使用事务批量处理提升 10-100 倍
索引失效为查询字段添加索引提升 5-20 倍
大表查询使用分页查询（LIMIT/OFFSET）提升 5 倍
内存占用启用 check_same_thread=False降低内存占用

2. 安全风险分析

SQL 注入示例：

# 错误写法（不安全）
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

安全写法（推荐）：

# 使用参数化查询
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

防范措施：

  • 始终使用参数化查询
  • 对用户输入进行校验
  • 使用 ORM 框架（如 SQLAlchemy）

3. 线程安全注意事项

# 不安全的多线程使用
def unsafe_worker():
    conn = sqlite3.connect('student.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM students")
    print(cursor.fetchall())
    conn.close()

# 安全的多线程使用
def safe_worker():
    conn = sqlite3.connect('student.db', check_same_thread=False)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM students")
    print(cursor.fetchall())
    conn.close()

九、常见问题与踩坑

1. 常见错误分析

错误示例：

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

问题：

  • 忘记关闭连接
  • 未处理游标对象

解决方案：

with sqlite3.connect('test.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())

2. 并发写入冲突

错误示例：

conn = sqlite3.connect('student.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO students...")  # 多个线程同时执行
conn.commit()

解决方案：

def safe_insert(name, grade):
    with sqlite3.connect('student.db', check_same_thread=False) as conn:
        cursor = conn.cursor()
        cursor.execute("INSERT INTO students...")  # 使用上下文管理器

3. 索引失效问题

错误示例：

cursor.execute("SELECT * FROM students WHERE grade > 100")  # 未使用索引

解决方案：

cursor.execute("SELECT * FROM students WHERE grade > 100")  # 自动使用索引

十、最佳实践

1. 推荐方案

  1. 使用上下文管理器：确保资源正确释放
  2. 参数化查询：防止 SQL 注入
  3. 事务处理：保证数据一致性
  4. 索引策略：为频繁查询字段添加索引
  5. 分页查询：避免一次性获取大量数据
  6. 连接池：在高并发场景中使用

2. 推荐配置

# 推荐的连接参数
conn = sqlite3.connect(
    'student.db',
    check_same_thread=False,
    timeout=30,  # 设置超时时间
    isolation_level=None  # 默认事务隔离级别
)

十一、总结

SQLite 作为轻量级数据库，在 Python 开发中具有独特优势。通过 sqlite3 模块，开发者可以快速实现数据持久化功能。本文深入解析了 SQLite 的工作原理，展示了从基础操作到进阶应用的完整实践路径。

在实际开发中，建议：

  • 对于小型项目优先使用 SQLite
  • 对于高并发场景考虑 MySQL/PostgreSQL
  • 在需要分布式部署时考虑 Redis 或 MongoDB
  • 始终遵循参数化查询和事务处理原则
  • 合理使用索引提升查询性能

通过本文的实践案例，读者可以掌握如何在 Python 中高效使用 SQLite 数据库，为开发小型应用和测试环境提供可靠的数据存储方案。

sql , python
最后修改于：2026年09月22日 19:20

评论已关闭

推荐阅读

AIGC实战——Transformer模型
2024年12月01日
python , AI , 机器学习
Socket TCP 和 UDP 编程基础（Python）
2024年11月30日
python , tcp , udp
如何使用 ChatGPT 进行学术润色？你需要这些指令
2024年12月01日
AI
最新 Python 调用 OpenAi 详细教程实现问答、图像合成、图像理解、语音合成、语音识别（详细教程）
2024年11月24日
python , AI
ChatGPT 和 DALL·E 2 配合生成故事绘本
2024年12月01日
python , AI
omegaconf，一个超强的 Python 库！
2024年11月24日
python
【视觉AIGC识别】误差特征、人脸伪造检测、其他类型假图检测
2024年12月01日
python , AI
［超级详细］如何在深度学习训练模型过程中使用 GPU 加速
2024年11月29日
python , AI , 机器学习
Python 物理引擎pymunk最完整教程
2024年11月27日
python
MediaPipe 人体姿态与手指关键点检测教程
2024年11月27日
python
深入了解 Taipy：Python 打造 Web 应用的全面教程
2024年11月26日
python
基于Transformer的时间序列预测模型
2024年11月25日
python , Matplotlib , 机器学习
Python在金融大数据分析中的AI应用（股价分析、量化交易）实战
2024年11月25日
python , Matplotlib , 机器学习
AIGC Gradio系列学习教程之Components
2024年12月01日
python , AI
Python3 `asyncio` — 异步 I/O，事件循环和并发工具
2024年11月30日
python
llama-factory SFT系列教程：大模型在自定义数据集 LoRA 训练与部署
2024年12月01日
python , AI , 机器学习
Python 多线程和多进程用法
2024年11月24日
python
Python socket详解，全网最全教程
2024年11月27日
python , http , websocket
python之plot()和subplot()画图
2024年11月26日
python , Matplotlib
理解 DALL·E 2、Stable Diffusion 和 Midjourney 工作原理
2024年12月01日
python , AI , 机器学习
Copyright © 2026 Tech Blog
All Right Reserved | 京ICP备19053165号