SQLite数据库：轻量级数据存储解决方案（世界上部署最广泛的数据库）

引言

在当今的数据驱动时代，数据库的选择对于开发者来说至关重要。MySQL作为一款功能强大的开源数据库，广泛应用于各种大型项目中。然而，在某些场景下，我们并不需要如此复杂的数据库系统，这时，轻量级的SQLite便成为了一个理想的选择。

特别是在Python开发中，SQLite以其简单易用、无需配置等特点，使其成为Python应用程序中本地数据存储的理想选择。本文将深入探讨在Python中使用SQLite作为轻量级替代MySQL的数据库解决方案。

一、SQLite简介

SQLite 是一款轻量级、跨平台的嵌入式关系型数据库，无需独立服务器进程，可直接集成到应用程序中，所有操作在应用进程内完成。其设计核心是 “简单、快速、可靠”，尤其适配内存与资源受限的环境，同时支持大部分 SQL 标准，兼具零配置、免安装、体积小巧的优势。​

与传统客户端 - 服务器数据库不同，SQLite 的完整数据库仅存储在单个跨平台磁盘文件中，无需依赖操作系统额外分配访问权限，易用性与可靠性突出，且为开源项目。

SQLite是世界上部署最广泛的数据库，其应用场景包括：

• 嵌入式设备 / 物联网（IoT）：如智能设备本地数据存储、传感器数据缓存​
• 桌面应用：如本地工具（日志分析、数据统计工具）、客户端软件配置存储​
• 移动开发：Android/iOS 本地数据库（如 APP 离线数据、用户偏好设置）​
• 小型 Web 服务：基于 Flask/Django 的轻量接口（并发量 < 1k QPS 场景）​
• 数据分析和数据处理脚本：Python 批量数据清洗、临时数据存储（替代 Excel/CSV 的结构化管理）​
• 应用程序的本地缓存：作为分布式系统本地缓存，降低远程数据库访问压力

二、SQLite的主要特点

• 轻量高效：库体积极小，编译后仅几百KB，资源占用低，读写性能在中小规模数据场景下表现优异；
• 无需配置：无需复杂安装与配置流程，无需初始化数据库服务、管理用户权限或调整网络端口；
• 跨平台支持：数据库文件兼容Windows、Linux、macOS等主流操作系统，迁移时直接拷贝文件即可；
• 标准兼容：支持大多数SQL-92标准语法，包括事务、视图、触发器和子查询等常见关系型数据库功能；
• 接口简单：Python内置sqlite3模块，API设计清晰易用，开发者可快速上手。

三、为什么要用 SQLite？

1. 无服务器架构：SQLite无需独立数据库服务器，数据存取在进程内完成，降低系统复杂度；
2. 零配置部署：无需数据库安装与管理配置，真正实现开箱即用；
3. 单一磁盘文件：整个数据库存储在单个文件中，便于迁移、备份与共享；
4. 极小的资源占用：基础版本小于400KB，精简版本可低于250KB，适合资源敏感型环境；
5. 自包含无依赖：除标准C库外不依赖任何第三方库，环境兼容性强；
6. ACID事务支持：保证数据操作的原子性、一致性、隔离性与持久性，支持多线程安全访问；
7. 丰富的SQL支持兼容SQL92标准多数语法，包括复杂查询、索引、触发器与事务控制；
8. ANSI-C编写，接口规范：底层由C实现并提供简洁API，为多种语言提供API接口（C、Java、PHP、Python等）；
9. 卓越的可移植性：支持从嵌入式设备到移动端（iOS/Android），再到桌面和服务器操作系统；
10. 高性能本地访问：在本地读写场景中，其速度远超许多传统客户端-服务器型数据库。

四、主流数据库对比分析

特性	SQLite	MySQL	PostgreSQL	MongoDB
架构类型	文件型数据库，嵌入式无服务器架构	客户端-服务器（C/S）架构	客户端-服务器（C/S）架构	客户端-服务器（C/S）架构
安装配置	零配置（仅需操作.db文件）	需配置服务、端口、账号权限	需要安装配置	需要安装配置
文件大小	几百KB	几十MB	几十MB	几十MB
数据存储	单文件（跨平台兼容）	多文件（依赖操作系统文件系统）	多文件	多文件
网络访问	不支持	支持	支持	支持
并发连接	单写入器，并发能力有限	多用户并发，支持多连接同时操作	多用户并发，支持大量并发连接	高并发支持
存储上限	可达140TB	可达256TB	无理论存储上限，可支持海量数据存储	无明确上限，支持海量数据
资源占用	极致轻量（运行内存<10MB）	需预留进程内存（默认>50MB）	需预留进程内存，资源占用相对较高	需预留进程内存，资源占用相对较高
数据类型	支持基础数据类型，相对简单	支持常规数据类型，满足多数通用场景需求	支持丰富的数据类型，包括一些特殊、复杂类型	文档类型（类JSON结构）
ACID实现	通过全局锁保障ACID特性，并发写入有一定限制	ACID实现由存储引擎决定，不同存储引擎表现有差异	完全支持ACID特性，在事务一致性等方面表现出色	不支持传统ACID事务，支持多文档事务
复制功能	本身无复制功能	原生支持复制功能，可实现数据的同步复制等	原生支持复制功能，复制机制灵活且强大	支持副本集等复制机制
事务支持	完全ACID兼容	完全ACID兼容（需配置隔离级别）	完全ACID兼容	不支持传统ACID事务，支持多文档事务
SQL标准支持	兼容SQL92核心特性（子查询、视图、触发器）	兼容SQL92及扩展特性	兼容SQL标准且有大量扩展	无SQL标准，使用类JSON查询语法
Python集成	依赖标准库sqlite3（零额外安装）	需安装pymysql/mysql-connector	需安装psycopg2等库	需安装pymongo库
性能（小数据集）	读写速度快（本地文件操作）	需网络/进程通信开销	需网络/进程通信开销，复杂查询性能强	文档存储，读写效率高，适合非结构化数据
适用场景	轻量级应用、嵌入式设备、本地存储	传统Web应用、中小型业务系统	复杂查询、大型系统、地理空间数据	非结构化数据、高吞吐读写、分布式文档存储
学习成本	低	中	中	中

SQLite与MySQL的对比:
尽管SQLite在轻量级应用中表现出色，但与MySQL相比，仍有一些差异：

• 功能丰富性：MySQL提供了更为丰富的功能，如存储过程、触发器、事务管理等。
• 并发处理：MySQL更适合处理高并发场景，而SQLite在并发处理方面相对较弱。
• 数据安全性：MySQL提供了更为完善的数据安全机制，适合对数据安全性要求较高的应用。

五、Python中使用SQLite：从入门到高阶

Python作为一门简洁易读的编程语言，与SQLite的结合可谓是天作之合。Python标准库中自带了sqlite3模块，使得开发者可以轻松地使用SQLite数据库，无需额外安装。

本章节将带你从基础到高级，全面掌握在Python中使用SQLite的技巧。

1.连接到SQLite数据库

使用sqlite3.connect()函数来连接SQLite数据库。这个函数接受一个参数，即数据库文件的路径。如果指定的数据库文件不存在，SQLite会自动创建一个新的数据库文件。

import sqlite3

# 连接到数据库（如果不存在则创建）
conn = sqlite3.connect('example.db')

同时还支持其他参数使用方法

# 使用内存数据库（临时，程序结束即消失）
conn = sqlite3.connect(':memory:')

# 带有额外参数的连接
conn = sqlite3.connect(
    'example.db',
    timeout=5,  # 等待锁释放的超时时间（秒）
    isolation_level=None,  # 自动提交模式
    check_same_thread=True  # 是否检查同一线程
)

参数说明：

• database: 数据库文件路径或":memory:"表示内存数据库
• timeout: 等待数据库锁的超时时间（秒）
• isolation_level: 隔离级别，None表示自动提交，其他值需要显式提交
• check_same_thread: 是否检查连接在同一线程中使用

2.创建游标对象

连接成功后，需要创建一个游标对象。游标用于执行 SQL 语句并处理结果。可以使用连接对象的 cursor() 方法创建游标对象。示例代码如下

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

3. 执行 SQL 语句： Cursor.execute和Cursor.executemany

作用：执行单一或批量 SQL 语句。
方法：

• execute(sql, parameters)：执行单条 SQL，参数化防止 SQL 注入。
• executemany(sql, seq_of_parameters)：批量执行 SQL。

创建表

可以使用 CREATE TABLE 语句在数据库中创建表，以下是一个创建 users 表的示例：

# 执行SQL语句创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE
    )
''')

如果该表已经存在，则不会执行创建操作

插入数据

使用 INSERT INTO 语句向表中插入数据。可以使用占位符（?）来防止 SQL 注入攻击。示例代码如下：

# 插入单条数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
               ('Alice', 30, 'alice@example.com'))

# 插入多条数据
users = [
    ('Bob', 25, 'bob@example.com'),
    ('Charlie', 35, 'charlie@example.com')
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users)

# 获取最后插入的ID
last_id = cursor.lastrowid

查询数据

使用 SELECT 语句从表中查询数据。查询结果可以通过游标对象的 fetchone()、fetchmany() 或 fetchall() 方法获取

# 查询所有数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 查询单条数据
cursor.execute("SELECT * FROM users WHERE id=?", (1,))
row = cursor.fetchone()
print(row)


# 查询多条数据
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
rows = cursor.fetchmany(5)  # 获取最多5条记录
print(rows)

更新数据

使用 UPDATE 语句更新表中的数据。示例代码如下：

# 更新数据
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, 'Alice'))

# 检查受影响的行数
print(f"受影响的行数: {cursor.rowcount}")

删除数据

使用 DELETE 语句从表中删除数据。示例代码如下：

# 删除数据
cursor.execute("DELETE FROM users WHERE id = ?", (3,))

4.事务处理

在SQLite中，事务是一组作为单个逻辑单元执行的SQL语句，是不可分割的数据库操作序列，要么全部执行成功，要么全部失败回滚。使用事务可以确保数据的一致性，**即使在出现错误时也可以回滚到事务开始之前的状态。**在sqlite3中，我们可以使用commit()方法提交事务，使用rollback()方法回滚事务。

可以使用 BEGIN、COMMIT 和 ROLLBACK 语句来管理事务:

BEGIN - 开始事务，显式开始一个新的事务块。从此之后的所有SQL操作都属于这个事务，直到遇到COMMIT或ROLLBACK。特点：

• 在BEGIN之后，数据库进入"事务模式"
• 所有的修改暂时不会永久保存到数据库文件
• 其他连接看不到未提交的修改（隔离性）

COMMIT - 提交事务，将当前事务中的所有操作永久保存到数据库。特点：

• 只有执行COMMIT后，修改才会真正生效
• 提交后事务结束，数据库回到自动提交模式
• 其他连接可以看到提交后的修改

ROLLBACK - 回滚事务,撤销当前事务中的所有操作，恢复到事务开始前的状态。特点：

• 取消所有未提交的修改
• 通常用在错误处理中
• 回滚后事务结束

thon

try:
    # 开始事务
    conn.execute('BEGIN')
    
    print("=== 执行事务操作 ===")
    # 操作1：正常插入（会成功）
    cursor.execute('INSERT INTO users (name, age, email) VALUES (?,?,?)', 
                  ('TestUser1', 30, 'test1@example.com'))
    print("插入TestUser1成功")
    
    # 操作2：故意制造错误 - 插入重复邮箱
    cursor.execute('INSERT INTO users (name, age, email) VALUES (?,?,?)', 
                  ('TestUser2', 35, 'alice@example.com'))  # 重复邮箱！
    print("这行不会执行到")
    
    # 操作3：更新操作（不会执行，因为上一步已经出错）
    cursor.execute('UPDATE users SET age = 99 WHERE name =?', ('Alice',))
    
    # 提交事务
    conn.commit()
    print("事务提交成功")
    
except Exception as e:
    # 回滚事务
    conn.rollback()
    print(f"事务失败，已回滚: {e}")

• 触发了真正的约束错误：尝试插入重复邮箱alice@example.com
• SQLite立即抛出异常：在第二条INSERT语句执行时就检测到错误
• 事务自动回滚：整个事务被撤销，包括成功的第一条INSERT
• 事务原子性：整个事务要么全部成功，要么全部失败
• 数据一致性：数据库恢复到事务开始前的状态

5.关闭数据库连接

在完成数据库操作后，需要关闭游标对象和数据库连接，以释放资源。示例代码如下：

# 关闭游标对象
cursor.close()
# 关闭数据库连接
conn.close()

6.上下文管理器

Connection 和 Cursor 支持 with 语句，自动管理资源。

with sqlite3.connect("example.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())
# 连接自动关闭

另外也可以通过上下文管理器实现事务回滚

# 自动提交/回滚的事务
try:
    with conn:
        print("=== 在事务中执行操作 ===")
        # 操作1：正常插入（会成功）
        cursor.execute('INSERT INTO users (name, age, email) VALUES (?,?,?)', 
                      ('TestUser1', 30, 'test1@example.com'))
        print("插入TestUser1成功")
        
        # 操作2：故意制造错误 - 插入重复邮箱
        cursor.execute('INSERT INTO users (name, age, email) VALUES (?,?,?)', 
                      ('TestUser2', 35, 'alice@example.com'))  # 重复邮箱！
        print("这行不会执行到")
        
        # 操作3：更新操作（不会执行）
        cursor.execute('UPDATE users SET age = 99 WHERE name =?', ('Alice',))
        
except sqlite3.IntegrityError as e:
    print(f"捕获到完整性错误: {e}")
    print("with块自动执行了回滚！")

# with conn: 自动管理事务：开始、提交、回滚都是自动的

# 原子性保证：任何操作失败都会导致整个事务回滚

# TestUser1被回滚了：虽然第一条INSERT显示"成功"，但最终被回滚了

# 异常传播：错误会正常抛出，需要外部try-except处理
    

7.使用Row工厂获取字典式结果

# 将行作为字典返回
conn.row_factory = sqlite3.Row  # 设置行工厂
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE age > ?", (30,))

# 先获取所有数据
rows = cursor.fetchall()

# 然后进行两种处理
print("=== 逐行访问 ===")
for row in rows:
    print(f"ID: {row['id']}, Name: {row['name']}, Age: {row['age']}")

print("\n=== 转换为字典列表 ===")
users = [dict(row) for row in rows]
print(users)

逐行访问输出：

ID: 1, Name: Alice, Age: 31
ID: 5, Name: Frank, Age: 40
ID: 8, Name: Eva, Age: 32
ID: 9, Name: Eva, Age: 32
ID: 10, Name: Eva, Age: 32

字典列表输出：

[
    {'id': 1, 'name': 'Alice', 'age': 31, 'email': 'alice@example.com'},
    {'id': 5, 'name': 'Frank', 'age': 40, 'email': 'frank@example.com'},
    {'id': 8, 'name': 'Eva', 'age': 32, 'email': None},
    {'id': 9, 'name': 'Eva', 'age': 32, 'email': None},
    {'id': 10, 'name': 'Eva', 'age': 32, 'email': None}
]

8.自定义函数

sqlite3 允许注册 Python 函数作为 SQLite 的自定义 SQL 函数。

# 创建自定义聚合函数
class Average:
    def __init__(self):
        self.sum = 0
        self.count = 0
    
    def step(self, value):
        if value is not None:
            self.sum += value
            self.count += 1
    
    def finalize(self):
        return self.sum / self.count if self.count else 0
        
# 注册自定义聚合函数
conn.create_aggregate("avg_custom", 1, Average)

# 使用自定义聚合函数
cursor.execute("SELECT avg_custom(age) FROM users")
result = cursor.fetchone()
print(f"平均年龄: {result[0]}")

9.备份数据库

def backup_db(src_conn, dst_path):
    """备份数据库到文件"""
    with open(dst_path, 'wb') as f:
        for line in src_conn.iterdump():
            f.write(f'{line}\n'.encode('utf-8'))

这段代码的本质是生成数据库的完整 SQL 备份，包含：

• 所有表结构（CREATE TABLE 语句）
• 所有表中的数据（INSERT 语句）

10.数据类型

SQLite支持以下五种基本数据类型，也称为"存储类"：

• NULL：表示空值
• INTEGER：带符号整数，根据值的大小存储在1、2、3、4、6或8字节中
• REAL：浮点数，存储为8字节的IEEE浮点数
• TEXT：文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储
• BLOB：二进制大对象，完全根据输入存储（不做任何转换） SQLite 使用

类型转换规则

SQLite使用一组规则来自动转换数据类型，这可能导致意外行为（隐式转换）：

# 示例1：数字与字符串相加
cursor.execute("SELECT 1.0 + '2'")  # 输出: 3.0（字符串'2'被转换为数字2.0）
result = cursor.fetchone()
print(f"1.0 + '2' = {result[0]}")  # 输出: 1.0 + '2' = 3.0

# 示例2：非数字字符串与数字相加
cursor.execute("SELECT 'abc' + 1")  # 输出: 1（字符串'abc'被转换为0）
result = cursor.fetchone()
print(f"'abc' + 1 = {result[0]}")  # 输出: 'abc' + 1 = 1

# 示例3：布尔值的处理
cursor.execute("SELECT TRUE, FALSE")  # 输出: (1, 0)
result = cursor.fetchone()
print(f"TRUE = {result[0]}, FALSE = {result[1]}")  # 输出: TRUE = 1, FALSE = 0

类型亲和性 (Type Affinity)

类型亲和性指的是 SQLite 表中的列会 “倾向于” 存储特定类型的数据，但不会严格限制只能存储该类型的数据。当插入数据时，SQLite 会根据列的亲和性尝试将数据转换为最合适的类型，以优化存储和查询效率。
在创建表时，可以为列指定类型亲和性，这会影响值如何存储和转换。

import sqlite3


conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# 创建带有不同亲和性列的表
cursor.execute('''
    CREATE TABLE test_affinity (
        col_any,          -- 无亲和性：完全不进行类型转换，数据保持原始存储类型
        col_int INTEGER,  -- INTEGER亲和性：倾向于存储整数类型，会尝试将插入的数据转换为整数
        col_text TEXT,    -- TEXT亲和性 ：倾向于存储文本类型，会尝试将插入的数据转换为字符串
        col_real REAL,    -- REAL亲和性：倾向于存储浮点类型，会尝试将插入的数据转换为浮点数
        col_blob BLOB     -- BLOB亲和性：与无亲和性类似，但更强调存储二进制数据
    )
''')

日期和时间处理

SQLite没有专门的日期/时间类型，但提供了函数来处理日期和时间：

使用文本存储日期和时间

cursor.execute("CREATE TABLE events (id INTEGER, event_date TEXT, event_time TEXT)")

插入日期和时间

cursor.execute("INSERT INTO events VALUES (1, date('now'), time('now'))")
cursor.execute("INSERT INTO events VALUES (2, '2023-12-25', '14:30:00')")

查询和操作日期

cursor.execute("SELECT * FROM events WHERE event_date > date('now', '-7 days')")
recent_events = cursor.fetchall()
print("最近7天的事件:", recent_events)

使用CAST进行显式类型转换

为了避免意外的隐式转换，可以使用CAST函数进行显式类型转换：

cursor.execute("SELECT CAST('123' AS INTEGER)")  # 输出: 123
cursor.execute("SELECT CAST(123.45 AS TEXT)")    # 输出: '123.45'
cursor.execute("SELECT CAST('hello' AS REAL)")   # 输出: 0.0（无法转换）

自定义数据类型和转换器

SQLite 本身支持有限的数据类型，但可以通过自定义数据类型来存储更复杂的数据。在 sqlite3 模块中，可以使用 register_adapter() 和 register_converter() 函数注册自定义的适配器(adapter)和转换器(converter)来实现自定义数据类型的存储和读取。示例代码如下：

• 适配器(Adapter)：适配器函数将Python对象转换为SQLite可以存储的格式（通常是bytes、str、int或float）。
  -转换器(Converter)：转换器函数将SQLite存储的格式转换回Python对象。

基本示例：Point类

import sqlite3

# 自定义数据类型
class Point:
    def __init__(self, x, y):
        self.x = x
        self.y = y
    
    def __repr__(self):
        return f"Point({self.x}, {self.y})"
    
    def __eq__(self, other):
        return isinstance(other, Point) and self.x == other.x and self.y == other.y

# 适配器函数，将Point对象转换为字节类型
def adapt_point(point):
    return f"{point.x},{point.y}".encode('utf-8')

# 转换器函数，将字节类型转换为Point对象
def convert_point(s):
    x, y = map(int, s.decode('utf-8').split(','))
    return Point(x, y)

# 注册适配器和转换器
sqlite3.register_adapter(Point, adapt_point)
sqlite3.register_converter("POINT", convert_point)

# 连接数据库并使用自定义数据类型
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()

# 创建表，使用自定义的POINT类型
cursor.execute('CREATE TABLE points (id INTEGER PRIMARY KEY, p POINT)')

# 插入Point对象
p1 = Point(1, 2)
p2 = Point(3, 4)
cursor.execute('INSERT INTO points (p) VALUES (?)', (p1,))
cursor.execute('INSERT INTO points (p) VALUES (?)', (p2,))
conn.commit()

# 查询并转换回Point对象
cursor.execute('SELECT p FROM points')
results = cursor.fetchall()

for row in results:
    point_obj = row[0]
    print(f"检索到的点: {point_obj}, 类型: {type(point_obj)}")
    print(f"坐标: x={point_obj.x}, y={point_obj.y}")

# 验证数据是否正确存储和检索
cursor.execute('SELECT p FROM points WHERE id = 1')
retrieved_p1 = cursor.fetchone()[0]
print(f"原始对象: {p1}, 检索到的对象: {retrieved_p1}")
print(f"对象是否相等: {p1 == retrieved_p1}")

conn.close()

更复杂的示例：处理JSON数据

SQLite本身没有原生的JSON类型，但我们可以使用适配器和转换器来处理JSON数据：

import sqlite3
import json

# 适配器：将Python对象转换为JSON字符串
def adapt_json(data):
    return json.dumps(data)

# 转换器：将JSON字符串转换回Python对象
def convert_json(s):
    return json.loads(s.decode('utf-8'))

# 注册JSON适配器和转换器
sqlite3.register_adapter(dict, adapt_json)
sqlite3.register_adapter(list, adapt_json)
sqlite3.register_converter("JSON", convert_json)

# 连接数据库
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()

# 创建表
cursor.execute('CREATE TABLE data (id INTEGER PRIMARY KEY, config JSON)')

# 插入JSON数据
config_data = {
    "name": "Example",
    "settings": {
        "width": 800,
        "height": 600,
        "fullscreen": False
    },
    "tags": ["game", "example", "test"]
}

cursor.execute('INSERT INTO data (config) VALUES (?)', (config_data,))
conn.commit()

# 查询数据
cursor.execute('SELECT config FROM data WHERE id = 1')
result = cursor.fetchone()[0]

print("检索到的配置:")
print(f"类型: {type(result)}")
print(f"内容: {result}")
print(f"名称: {result['name']}")
print(f"宽度: {result['settings']['width']}")

conn.close()

处理日期时间对象

SQLite没有原生的日期时间类型，但我们可以创建适配器和转换器来处理Python的datetime对象：

import sqlite3
from datetime import datetime, date

# 适配器：将datetime对象转换为ISO格式字符串
def adapt_datetime(dt):
    return dt.isoformat()

# 转换器：将ISO格式字符串转换回datetime对象
def convert_datetime(s):
    return datetime.fromisoformat(s.decode('utf-8'))

# 适配器：将date对象转换为ISO格式字符串
def adapt_date(d):
    return d.isoformat()

# 转换器：将ISO格式字符串转换回date对象
def convert_date(s):
    return date.fromisoformat(s.decode('utf-8'))

# 注册适配器和转换器
sqlite3.register_adapter(datetime, adapt_datetime)
sqlite3.register_adapter(date, adapt_date)
sqlite3.register_converter("DATETIME", convert_datetime)
sqlite3.register_converter("DATE", convert_date)

# 连接数据库
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()

# 创建表
cursor.execute('''
    CREATE TABLE events (
        id INTEGER PRIMARY KEY,
        name TEXT,
        event_date DATE,
        event_time DATETIME
    )
''')

# 插入数据
now = datetime.now()
today = date.today()

cursor.execute(
    'INSERT INTO events (name, event_date, event_time) VALUES (?, ?, ?)',
    ('测试事件', today, now)
)
conn.commit()

# 查询数据
cursor.execute('SELECT name, event_date, event_time FROM events WHERE id = 1')
name, event_date, event_time = cursor.fetchone()

print(f"事件名称: {name}")
print(f"事件日期: {event_date}, 类型: {type(event_date)}")
print(f"事件时间: {event_time}, 类型: {type(event_time)}")

conn.close()

11.ROWID 的使用：表的 “天然行标识”

ROWID 是 SQLite 中每个表（除 WITHOUT ROWID 表外）默认存在的特殊列，本质是 64 位整数，用于唯一标识表中的每一行数据。即使创建表时未显式定义主键，SQLite 也会自动生成隐藏的 ROWID 列，它相当于表的 “天然行标识”，可用于高效定位、查询和操作数据。

需要注意的是，若表中显式定义了 INTEGER PRIMARY KEY 类型的主键（如 id INTEGER PRIMARY KEY），该主键会 “覆盖” ROWID 的作用 —— 此时主键列的值等同于 ROWID，且不再有单独的隐藏 ROWID 列；

import sqlite3  
  
# 连接到SQLite数据库  
conn = sqlite3.connect('test.db')  
cursor = conn.cursor()  
  
# 插入一条数据并获取ROWID  
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('彭昱畅', 28))  
rowid = cursor.lastrowid  
print(f"Inserted row with ROWID: {rowid}")  
  
# 使用ROWID查询数据  
query = "SELECT * FROM users WHERE rowid = ?"  
cursor.execute(query, (rowid,))  
result = cursor.fetchone()  
print(result)  
  
# 关闭游标和连接  
cursor.close()  
conn.close()

核心使用场景分为两部分：一是插入数据后获取 ROWID，二是通过 ROWID 精准查询数据。插入数据时，执行 INSERT 语句后，通过 cursor.lastrowid 能直接获取当前插入行的 ROWID，这一步可快速记录新行的唯一标识；查询数据时，利用 WHERE rowid = ? 条件，能绕过全表扫描，直接定位到目标行，相比普通字段查询更高效（尤其表中无其他索引时）。

12.使用内存数据库

除了磁盘上的文件数据库外，SQLite还支持内存数据库。内存数据库将数据存储在RAM中，因此读写速度非常快，但数据在关闭连接后会丢失。

import sqlite3  
  
# 连接到内存数据库  
conn = sqlite3.connect(':memory:')  
cursor = conn.cursor()  
  
# 在内存数据库中创建表并插入数据  
cursor.execute('''  
    CREATE TABLE users (  
        id INTEGER PRIMARY KEY AUTOINCREMENT,  
        name TEXT NOT NULL,  
        age INTEGER  
    )  
''')  
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('彭昱畅', 28))  
  
# 查询并打印数据  
cursor.execute("SELECT * FROM users")  
for row in cursor.fetchall():  
    print(row)  
  
# 关闭游标和连接（数据将丢失）  
cursor.close()  
conn.close()

13.使用UUID作为SQLite主键

在 SQLite 中，主键的核心作用是唯一标识表中的每一行数据，传统常用自增整数（如INTEGER PRIMARY KEY AUTOINCREMENT）作为主键，但在分布式系统、多数据源同步等场景下，自增主键易出现重复问题；而UUID（通用唯一识别码） 能生成全局唯一的字符串，可完美解决这类唯一性冲突，成为跨系统、多节点场景下的优质主键选择。

什么是 UUID？

UUID（Universally Unique Identifier，通用唯一标识符）是一种由 128 位二进制数组成的标准化标识符（通常以 36 个字符的字符串形式呈现，如 550e8400-e29b-41d4-a716-446655440000），目的是让分布式系统中，不同设备、不同时间生成的 ID “无需协调即可避免重复”。

目前最常用的是 UUID v4（随机生成型），也就是代码中用 uuid.uuid4() 生成的类型，另外也存在其他版本如 v1 基于时间 + MAC 地址，v3/v5 基于哈希，逻辑类似但生成方式不同）。

UUID 的核心价值在于 “全局唯一性”，适用以下场景：

1. 分布式 / 多节点数据写入：多台设备或服务同时向数据库写入数据时，自增主键可能因节点独立计数导致重复，UUID 可确保每一行的主键在所有节点中唯一；
2. 跨数据库数据同步 / 合并：需将多个 SQLite 数据库的表数据合并（如多终端数据汇总到服务器）时，UUID 能避免不同数据库中 “相同自增 ID 对应不同数据” 的冲突；
3. 隐私保护与安全需求：自增主键（如 1、2、3…）会暴露数据总量和增长规律（如用户 ID=100 可推测已有 100 个用户），UUID 的随机字符串形式可隐藏这类敏感信息；
4. 提前生成主键：业务中需先确定主键再后续操作（如先生成文件 ID 再上传文件）时，UUID 可在本地直接生成，无需依赖数据库自增逻辑。

import sqlite3
import uuid

conn = sqlite3.connect("file_manage.db")
cursor = conn.cursor()

# 2. 创建表：指定id为TEXT类型主键，存储UUID
cursor.execute('''
    CREATE TABLE IF NOT EXISTS files (
        id TEXT PRIMARY KEY,  -- UUID作为主键
        name TEXT NOT NULL,   -- 文件名
        size INTEGER          -- 文件大小（单位：字节）
    )
''')

# 3. 生成UUID并插入数据，uuid.uuid4()生成随机UUID，转换为字符串存入数据库
file_uuid = str(uuid.uuid4())
file_info = (file_uuid, "report_2024.pdf", 204800)  # (UUID, 文件名, 大小)
cursor.execute('INSERT INTO files (id, name, size) VALUES (?, ?, ?)', file_info)
print(f"已插入数据，UUID主键：{file_uuid}")

# 4. 通过UUID查询数据（精准定位，性能高效）
cursor.execute('SELECT * FROM files WHERE id = ?', (file_uuid,))
result = cursor.fetchone()
print(f"\n通过UUID查询结果：")
print(f"主键(UUID)：{result[0]}，文件名：{result[1]}，大小：{result[2]}字节")

# 5. 提交事务并关闭连接
conn.commit()
conn.close()

解析UUID：为何“几乎唯一”而非“绝对唯一”

在分布式系统、数据库主键、API请求标识等场景中，UUID（通用唯一识别码）的“唯一性”是其核心价值。但很多人会疑惑：UUID为何是“几乎唯一”而非“绝对唯一”？这需要从其底层的位空间设计和概率模型两方面拆解。

1. 为什么UUID“几乎唯一”？核心在于128位的“巨大空间”

UUID v4（最常用的随机生成版本）的核心生成逻辑是：从128位的二进制空间中，随机选取一个值（其中有6位固定用于标识“版本”和“变体”，实际随机位为122位）。

这种设计的关键优势，是128位带来的“天文级别的可能组合数”：

• 128位二进制对应的总组合数 = 2^{128} ≈ 3.4×10³⁸（约340万亿亿亿亿）。
• 这个数量级是什么概念？可以通过以下类比理解：
  • 地球的总原子数约为 10^{50}，UUID的组合数虽略少于地球原子数，但已远超“人类可感知的所有场景需求”（如分布式系统标识、文件命名、设备编号等）；
  • 即使全球每秒生成1万亿个UUID v4，要耗尽所有组合，也需要约 10^{21} 年——而宇宙当前年龄仅约138亿年（即 1.38×10^{10}年），两者差距悬殊。

2.为什么不是“绝对唯一”？存在理论上的重复概率
从数学本质来看，只要满足两个条件：“存在多个生成行为”且“每个行为的结果来自有限集合”，就必然存在“结果重复的概率”——UUID也不例外，只是其重复概率极低，低到在工程实践中可忽略不计。

具体概率可通过概率论中的“生日悖论”模型计算（生日悖论核心是：在有限集合中，元素重复的概率增长速度远超直觉）：

• 当生成约 2^{64} 个UUID v4时，重复的概率才会上升到“1/2”（即50%）；
• 而 2^{64}约等于1.8×10¹⁹，这个数量级意味着：即使你每秒生成100万个UUID，也需要约5800万年才能达到“50%重复概率”的阈值。

在现实工程中，没有任何系统会在生命周期内生成如此海量的UUID（例如，一个高并发系统每天生成10亿个UUID，一年也仅生成3.65×10¹¹个，距离1.8×10¹⁹的阈值相差18个数量级），因此“UUID重复”几乎不可能发生。

14.性能优化

a.批量操作：使用 executemany() 进行批量插入。

SQLite 对单次插入操作有一定的 overhead（开销），包括事务处理、索引更新等。如果逐条插入大量数据，会导致频繁的磁盘 I/O 和事务提交，严重影响性能。

executemany() 允许在一次数据库调用中执行多个参数化查询，将多条记录一次性发送到数据库，从而减少通信开销和事务处理次数。配合显式事务（BEGIN…COMMIT）可以进一步提升性能。

import sqlite3
import time

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

cursor.execute('''
    CREATE TABLE IF NOT EXISTS user_actions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        action_type TEXT,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )
''')

# 准备测试数据
test_data = [(i % 1000, f'action_{i % 10}') for i in range(10000)]

# 方法1：逐条插入（慢）
start_time = time.time()
for user_id, action_type in test_data:
    cursor.execute(
        "INSERT INTO user_actions (user_id, action_type) VALUES (?, ?)",
        (user_id, action_type)
    )
conn.commit()
single_time = time.time() - start_time
print(f"逐条插入时间: {single_time:.3f}秒")

# 清空表
cursor.execute("DELETE FROM user_actions")
conn.commit()

# 方法2：批量插入（快）
start_time = time.time()
cursor.executemany(
    "INSERT INTO user_actions (user_id, action_type) VALUES (?, ?)",
    test_data
)
conn.commit()
batch_time = time.time() - start_time
print(f"批量插入时间: {batch_time:.3f}秒")
print(f"性能提升: {single_time/batch_time:.1f}倍")

conn.close()


# 逐条插入时间: 0.022秒
# 批量插入时间: 0.019秒
# 性能提升: 1.2倍

b.合理使用索引：为常用查询字段创建索引。

索引通过创建有序的数据结构（B 树），允许数据库快速定位符合条件的记录，而无需扫描整个表。对于查询频繁、数据量大的表，合适的索引能显著提升查询速度。

但索引并非越多越好：

• 索引会占用额外存储空间
• 插入、更新、删除操作需要维护索引，会增加开销
• 对于数据量小的表或返回大部分数据的查询，索引可能反而降低性能

单列索引（Single-column Index）：

# 1. 单列索引（Single-column Index）
# 定义：在表的单个列上创建的索引
# 作用：加速基于该列的查询、排序和分组操作
# 原理：类似书本目录，为列值建立有序的B树结构，避免全表扫描
# 适用：经常按某列查询的场景，如按姓名查找用户

cursor.execute('CREATE INDEX IF NOT EXISTS idx_users_name ON users(name)')
conn.commit()
# 示例：按姓名查询（使用索引）
cursor.execute('SELECT * FROM users WHERE name = ?', ('Alice',))

复合索引（Composite Index）：

# 2. 复合索引（Composite Index）
# 定义：在表的多个列上同时创建的索引
# 作用：优化多列联合查询的性能
# 原理：按列顺序生成复合键，建立有序结构
# 适用：经常需要多列联合查询的场景

cursor.execute('CREATE INDEX IF NOT EXISTS idx_users_name_age ON users(name, age)')
conn.commit()
# 示例：多列联合查询（使用复合索引）

# 🔑 最左前缀原则（重要！）
# 复合索引 (name, age) 支持以下查询模式：
# ✅ 使用完整索引
cursor.execute('SELECT * FROM users WHERE name = ? AND age = ?', ('Alice', 30))

# ✅ 使用索引的第一部分
cursor.execute('SELECT * FROM users WHERE name = ?', ('Alice',))

# ✅ 使用索引的第一部分和范围查询
cursor.execute('SELECT * FROM users WHERE name = ? AND age > ?', ('Alice', 25))

# ✅ 条件顺序不影响索引使用（优化器会重新排序）
cursor.execute('SELECT * FROM users WHERE age = ? AND name = ?', (30, 'Alice'))

# ❌ 不使用索引（缺少最左列）
cursor.execute('SELECT * FROM users WHERE age = ?', (30,))

# ❌ 不使用索引进行范围查询后的精确匹配
cursor.execute('SELECT * FROM users WHERE name LIKE ? AND age = ?', ('A%', 30))

索引之所以能提升查询性能，核心原理类似于书籍的目录：

1.避免全表扫描

• 无索引时，数据库需要逐行检查每条记录（全表扫描），就像在没有目录的书中逐页查找内容
• 有索引时，数据库通过索引直接定位到符合条件的记录位置，跳过无关数据

2.有序数据结构

• 索引会创建一个有序的数据结构（通常是 B 树或 B + 树），将索引字段的值与记录的物理位置关联起来
• 有序结构使数据库可以使用二分查找等高效算法，快速定位目标数据

3.减少 IO 操作

• 数据库操作中，磁盘 IO 是最耗时的环节之一
• 索引可以大幅减少需要读取的数据量，从而减少 IO 操作次数

4.复合索引的额外优势

• 对于多条件查询，复合索引可以一次性定位满足所有条件的记录
• 避免了使用多个单列索引时可能的索引合并开销

索引的局限性：

• 索引不适用于所有操作符：某些操作符（如LIKE以通配符开头）可能无法有效使用索引
• 小表不需要索引：对于非常小的表，全表扫描可能比使用索引更快
• 索引会增加存储空间：每个索引都需要额外的磁盘空间
• 索引会降低写操作性能：插入、更新和删除操作需要维护索引

c.PRAGMA设置：调整SQLite的PRAGMA参数优化性能。

PRAGMA 是 SQLite 中的特殊命令，用于查询和修改数据库的内部配置。这些命令不是标准 SQL 的一部分，而是 SQLite 特有的功能。PRAGMA 语句可以在运行时更改数据库行为，无需重新编译或重新创建数据库。PRAGMA 参数控制着缓存大小、同步模式、查询优化等关键行为，合理调整可以显著提升性能。

日志模式 (Journal Mode)

# 设置为预写日志模式 (Write-Ahead Logging)
cursor.execute("PRAGMA journal_mode = WAL;")

作用：WAL 模式允许多个读取器和一个写入器同时操作数据库，显著提高并发性能。

可选值：

• DELETE (默认)：事务结束时删除回滚日志
• TRUNCATE：截断回滚日志而不是删除
• PERSIST：保留回滚日志但清空内容
• MEMORY：将回滚日志存储在内存中
• WAL：使用预写日志模式（推荐用于高并发）

建议：对于需要高并发读写的应用程序，强烈推荐使用 WAL 模式。

同步设置 (Synchronous)

# 平衡安全性和性能
cursor.execute("PRAGMA synchronous = NORMAL;")

作用：控制 SQLite 在提交事务时如何同步数据到磁盘。

可选值：

• OFF (0)：不同步，性能最高但风险最大
• NORMAL (1)：在大多数情况下同步（推荐）
• FULL (2)：完全同步，最安全但性能最低

建议：对于大多数应用，NORMAL 提供了安全性和性能的良好平衡。

缓存大小 (Cache Size)

# 设置缓存大小为 2000 页（约 16MB）
cursor.execute("PRAGMA cache_size = 2000;")

作用：指定内存中缓存的数据库页数。

注意：缓存大小以页为单位，默认页大小为 4KB。可以使用负值表示千字节（如 -2000 表示 2000KB）。

建议：根据可用内存调整此值，较大的缓存可以减少磁盘 I/O。

临时存储 (Temp Store)

# 将临时表和索引存储在内存中
cursor.execute("PRAGMA temp_store = MEMORY;")

作用：控制临时表和索引的存储位置。

可选值：

• DEFAULT (0)：使用编译时默认设置
• FILE (1)：使用文件存储
• MEMORY (2)：使用内存存储（推荐用于性能）

建议：如果内存充足，使用 MEMORY 可以显著提高临时操作的性能。

页大小 (Page Size)

# 设置数据库页大小为 8KB（必须在创建数据库前设置）
cursor.execute("PRAGMA page_size = 8192;")

作用：设置数据库页的大小。

注意：页大小只能在数据库创建之前设置，且必须是 2 的幂次方，介于 512 和 65536 之间。

建议：较大的页大小（如 8KB）通常对大型数据库和大量数据操作更有效。

内存映射 (MMap Size)

# 启用 300MB 的内存映射
cursor.execute("PRAGMA mmap_size = 300000000;")

作用：使用内存映射文件 I/O 而不是传统的读/写系统调用。

建议：对于大型数据库，内存映射可以显著提高读取性能。

自动清理 WAL (Auto Checkpoint)

# 设置 WAL 自动检查点
cursor.execute("PRAGMA wal_autocheckpoint = 1000;")

作用：设置 WAL 文件自动执行检查点的页数阈值。

建议：较小的值可以减少 WAL 文件大小，但可能增加写操作的开销。

优化器开关 (Optimizer Hints)

# 启用查询优化器统计信息
cursor.execute("PRAGMA optimize;")

作用：收集数据库统计信息，帮助查询优化器做出更好的决策。

建议：定期运行此命令，特别是在大量数据变更后。

关键参数说明：

• cache_size：设置缓存大小，越大缓存效果越好（但不超过可用内存）
• synchronous：控制磁盘同步方式，OFF 性能最好但安全性最低
• journal_mode：设置日志模式，WAL 模式适合高并发场景
• temp_store：控制临时表存储位置，MEMORY 减少磁盘 I/O

配置示例：

import sqlite3

def optimize_sqlite_performance(conn):
    """配置 SQLite 数据库性能参数"""
    cursor = conn.cursor()
    
    # 启用 WAL 模式（提高并发性能）
    cursor.execute("PRAGMA journal_mode = WAL;")
    
    # 设置同步模式为 NORMAL（平衡安全性和性能）
    cursor.execute("PRAGMA synchronous = NORMAL;")
    
    # 设置缓存大小为 2000 页（约 16MB）
    cursor.execute("PRAGMA cache_size = 2000;")
    
    # 将临时表存储在内存中
    cursor.execute("PRAGMA temp_store = MEMORY;")
    
    # 启用内存映射（提高读取性能）
    cursor.execute("PRAGMA mmap_size = 300000000;")
    
    # 设置 WAL 自动检查点
    cursor.execute("PRAGMA wal_autocheckpoint = 1000;")
    
    # 启用外键约束（建议开启以保证数据完整性）
    cursor.execute("PRAGMA foreign_keys = ON;")
    
    # 收集优化器统计信息
    cursor.execute("PRAGMA optimize;")
    
    conn.commit()

# 使用示例
conn = sqlite3.connect('example.db')
optimize_sqlite_performance(conn)

查看当前 PRAGMA 设置：

def show_pragma_settings(conn):
    """显示当前 PRAGMA 设置"""
    cursor = conn.cursor()
    pragmas = [
        "journal_mode", "synchronous", "cache_size", 
        "temp_store", "page_size", "mmap_size"
    ]
    
    for pragma in pragmas:
        cursor.execute(f"PRAGMA {pragma};")
        result = cursor.fetchone()
        print(f"{pragma}: {result[0]}")

d.定期维护：使用VACUUM命令回收未使用空间。

SQLite 数据库在长期使用过程中，随着数据的增删改操作，会产生碎片和未使用的空间。这些空间虽然不再被使用，但仍然占据着数据库文件的容量。VACUUM 命令是 SQLite 提供的一个重要维护工具，用于优化数据库性能并回收未使用的磁盘空间。它通过重建数据库文件来消除碎片并回收未使用的空间。执行 VACUUM 命令时，SQLite 会创建一个新的数据库文件，然后将所有数据和索引从原数据库复制到新文件中，最后替换原数据库文件。

为什么需要 VACUUM？

1. 空间回收
   当从 SQLite 数据库中删除数据或整个表时，不会立即回收磁盘空间，而是标记为 “空闲”。随着时间推移，数据库文件中可能包含大量未使用的空间，导致文件体积膨胀。
2. 性能优化
   数据库碎片化会导致数据存储在不连续的位置，增加磁盘寻道时间，降低查询性能。VACUUM 命令通过重建整个数据库来回收这些空闲空间，同时优化表和索引的物理存储布局，减少碎片，提升 I/O 效率。对于频繁进行删除和更新操作的数据库，定期执行 VACUUM 非常重要。
3. 减少文件大小
   通过回收未使用的空间，VACUUM 可以显著减小数据库文件的大小，特别是在大量删除操作后。

基本用法示例：

执行 VACUUM 命令

import sqlite3
import time


def vacuum_demo():
    import sqlite3, os

    db_file = 'vacuum_example.db'
    if os.path.exists(db_file):
        os.remove(db_file)
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()

    # 创建测试表并插入大量数据
    cur.execute('CREATE TABLE logs (id INTEGER PRIMARY KEY, msg TEXT)')
    for i in range(1000):
        cur.execute('INSERT INTO logs (msg) VALUES (?)', (f'日志{i}',))
    conn.commit()

    # 删除大部分数据，文件体积未立即变小
    cur.execute('DELETE FROM logs WHERE id <= 900')
    conn.commit()

    # 查看VACUUM前数据库文件大小
    size_before = os.path.getsize(db_file)
    print(f'VACUUM前文件大小: {size_before/1024:.1f} KB')

    # 执行VACUUM命令回收未使用空间
    cur.execute('VACUUM')
    conn.commit()

    # 查看VACUUM后数据库文件大小
    size_after = os.path.getsize(db_file)
    print(f'VACUUM后文件大小: {size_after/1024:.1f} KB')
    print(f'回收空间: {(size_before-size_after)/1024:.1f} KB')

    conn.close()

vacuum_demo()

# VACUUM前文件大小: 28.0 KB
# VACUUM后文件大小: 8.0 KB
# 回收空间: 20.0 KB

带条件的 VACUUM

SQLite 3.35.0 (2021-03-12) 及以上版本支持带表名的 VACUUM 命令，可以只对特定表进行优化：

# 只对特定表执行 VACUUM（需要 SQLite 3.35.0+）
cursor.execute("VACUUM users;")  # 仅优化 users 表

自动执行 VACUUM

你可以设置自动 VACUUM 模式，让 SQLite 在每次事务提交时自动执行部分清理工作：

# 启用自动 VACUUM 模式
cursor.execute("PRAGMA auto_vacuum = INCREMENTAL;")  # 或 FULL

# 对于 INCREMENTAL 模式，还需要定期执行
cursor.execute("PRAGMA incremental_vacuum;")

auto_vacuum 模式选项：

• NONE (默认)：禁用自动 VACUUM
• FULL：在每次事务提交时自动执行 VACUUM，自动整理并回收空间，文件始终紧凑，但写入性能略有影响。
• INCREMENTAL：只在手动执行PRAGMA incremental_vacuum时逐步回收空间，适合大库分阶段整理。

使用注意事项：

1. 资源消耗：VACUUM 操作需要创建整个数据库的副本，因此需要足够的磁盘空间（至少与原数据库相同的大小）和较长的处理时间（对于大型数据库）。
2. 并发访问：在执行 VACUUM 期间，数据库会被锁定，其他进程无法访问。对于需要高可用性的应用程序，应在低峰期执行此操作。
3. 频率
   不需要过于频繁地执行 VACUUM。通常建议在以下情况下执行：

• 数据库文件异常增大后
• 执行了大量删除操作后
• 定期维护时（如每周或每月）

4. 备份
   在执行 VACUUM 前，最好先备份数据库，以防万一操作过程中出现意外。

替代方案：增量 VACUUM

对于大型数据库，完整的 VACUUM 操作可能需要很长时间。SQLite 提供了增量 VACUUM 作为替代方案：

def incremental_vacuum(conn, pages_to_vacuum=100):
    """
    执行增量 VACUUM
    
    参数:
        conn: 数据库连接
        pages_to_vacuum: 要清理的页数
    """
    cursor = conn.cursor()
    
    # 启用增量 VACUUM 模式
    cursor.execute("PRAGMA auto_vacuum = INCREMENTAL;")
    
    # 执行增量 VACUUM
    cursor.execute(f"PRAGMA incremental_vacuum({pages_to_vacuum});")
    
    conn.commit()

最佳实践

• 定期执行：根据数据库的使用频率和修改量，制定合理的 VACUUM 计划。
• 监控空间使用：定期检查数据库文件大小和空闲页数量，确定是否需要执行 VACUUM。
• 选择合适的时间：在数据库负载较低时执行 VACUUM，避免影响正常业务。
• 备份优先：在执行 VACUUM 前始终创建备份。
• 考虑使用自动 VACUUM：对于频繁删除操作的数据库，可以考虑启用自动 VACUUM 模式。

e. WAL模式：对于高并发读场景，使用 PRAGMA journal_mode=WAL 。

WAL（预写日志）模式是SQLite 3.7.0版本引入的一种替代传统回滚日志的机制。在传统模式下，SQLite使用回滚日志实现原子提交和回滚，而WAL模式采用了完全不同的方法，将修改先写入一个单独的WAL文件，然后再定期批量应用到主数据库文件。|

SQLite的WAL（Write-Ahead Logging）模式是一种先进的日志机制，专门设计用于提高数据库的并发性能，特别是在读多写少的场景下。

WAL模式的工作原理

传统回滚模式：

• 写入时直接修改数据库文件
• 使用独占锁保证数据一致性
• 读操作和写操作相互阻塞

WAL模式：

• 写入操作先记录到WAL文件中
• 读操作可以同时访问数据库文件和WAL文件
• 写入操作不会阻塞读取操作

WAL模式的工作流程:

1. 写入阶段：所有修改首先被写入WAL文件
2. 提交阶段：事务提交时，只需在WAL文件中添加提交记录
3. 检查点阶段：定期将WAL文件中的修改批量应用到主数据库文件
4. 读取阶段：读取操作结合主数据库和WAL文件的内容，提供一致的数据视图

注意：
WAL模式会创建两个额外文件：.wal和.shm。需要确保这些文件与主数据库文件一起备份和传输

检查点管理：需要定期执行检查点以防止WAL文件无限增长，可以使用自动检查点或手动管理检查点

基本启用方法

import sqlite3

def enable_wal_mode(db_path):
    """启用数据库的WAL模式"""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 启用WAL模式
    cursor.execute("PRAGMA journal_mode=WAL;")
    result = cursor.fetchone()
    
    if result[0] == "wal":
        print("WAL模式已启用")
    else:
        print(f"启用WAL模式失败，当前模式: {result[0]}")
    
    conn.close()
    return result[0] == "wal"

# 使用示例
enable_wal_mode("example.db")

完整配置示例

import sqlite3

def configure_wal_database(db_path):
    """配置使用WAL模式的数据库"""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 启用WAL模式
    cursor.execute("PRAGMA journal_mode=WAL;")
    
    # 设置同步模式为NORMAL（平衡性能和数据安全）
    cursor.execute("PRAGMA synchronous=NORMAL;")
    
    # 设置WAL自动检查点（每1000页触发一次）
    cursor.execute("PRAGMA wal_autocheckpoint=1000;")
    
    # 设置较大的缓存大小
    cursor.execute("PRAGMA cache_size=2000;")
    
    # 启用内存映射I/O
    cursor.execute("PRAGMA mmap_size=300000000;")
    
    # 提交更改
    conn.commit()
    
    # 验证设置
    cursor.execute("PRAGMA journal_mode;")
    journal_mode = cursor.fetchone()[0]
    
    cursor.execute("PRAGMA wal_autocheckpoint;")
    autocheckpoint = cursor.fetchone()[0]
    
    print(f"日志模式: {journal_mode}")
    print(f"自动检查点: {autocheckpoint}")
    
    conn.close()
    
    return journal_mode == "wal"

# 使用示例
configure_wal_database("example.db")

参考文献

https://blog.youkuaiyun.com/tekin_cn/article/details/145966118
https://www.oryoy.com/news/python-zhong-shi-yong-sqlite-zuo-wei-qing-liang-ji-ti-dai-mysql-de-shu-ju-ku-jie-jue-fang-an.html

https://guangzai.blog.youkuaiyun.com/article/details/138764428?fromshare=blogdetail&sharetype=blogdetail&sharerId=138764428&sharerefer=PC&sharesource=weixin_66397563&sharefrom=from_link
https://blog.youkuaiyun.com/u013172930/article/details/147951183

https://www.cnblogs.com/l199616j/p/10694036.html

https://mp.weixin.qq.com/s/fk76PE_btkK7FAymGJWkEA

https://www.runoob.com/sqlite/sqlite-intro.html