Python ：关系型数据库用 SQLite 妥妥足够了一. 前言最近在使用 Python 实现桌面端应用，其中涉及到

一. 前言

最近在使用 Python 实现桌面端应用，其中涉及到数据库，看到 Python 集成了 SQLite ，那么对于一个基础的桌面端本地应用，它是否可以满足我们的需求呢？

二. SQLite 概览

2.1 支持的能力

支持 SQL 标准，也就是 SELECT / INSERT 等常见的 SQL 标准语法都支持，包括 GroupBy / OrderBy 等
支持视图（Views）/ 触发器 （Triggers） / 存储过程
支持显示的事务控制 ，支持 ACID 能力
支持索引，包括 普通索引、唯一索引、部分索引以及复合索引
支持 JSON ，也就是 JSON 文本的解析、查询和更新
支持多读单写，也就是并发的能力

从这些支持的能力上面大概能看出来，日常使用的关系型数据库能力它都是有的

-- 创建数据表
CREATE TABLE peoples (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
		code INTEGER,
    name TEXT NOT NULL,
    age INTEGER,
    grade TEXT,
    create_date DATE
);

-- 添加索引 ： 这里尝试创建了3种数据类型
CREATE INDEX idx_people_name ON peoples (name);
CREATE UNIQUE INDEX idx_people_code ON peoples (code);
CREATE INDEX idx_people_grade_age ON peoples (grade, age);


-- 插入数据 ： 
INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811001,'Test001', 20, 'A', '2024-09-01');

INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811002,'Test002', 22, 'B', '2024-09-07');

INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811003,'Test003', 20, 'C', '2024-09-05');

INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811004,'Test004', 21, 'C', '2024-09-09');

-- 条件查询
SELECT * FROM peoples WHERE age > 21;
SELECT * FROM peoples ORDER BY create_date;
SELECT age,count(*) FROM peoples group by age;


-- 更新数据
UPDATE peoples SET grade = 'D' WHERE name = 'Test004';


-- 支持 Explan 查询
EXPLAIN SELECT * FROM peoples WHERE name = 'Test002';
EXPLAIN QUERY PLAN SELECT * FROM peoples WHERE name = 'Test002';

2.2 简单看看 SQLite 的 Explain

下面专门来看一下执行计划，了解这个有助于我们后续看 SQLite 的性能：

-- explain 有两种执行方式 ： 


-- 方式一 ：查询执行明细 （也就是上图看到的）
EXPLAIN SELECT * FROM peoples WHERE name = 'Test002';

-- 方式二 ： 通过 EXPLAIN QUERY 查询摘要信息
EXPLAIN QUERY PLAN SELECT * FROM peoples WHERE name = 'Test002';


-- 打印结果 ： 可以明确看到使用的是什么类型的索引
id	parent	notused	   detail
3	        0	0	   SEARCH TABLE peoples USING INDEX idx_people_name (name=?)

三. 数据库性能

前置代码： Python 版 SQLite 使用

import sqlite3
import random
from datetime import datetime

# 1. 连接到数据库（如果数据库文件不存在，会自动创建一个）
conn = sqlite3.connect('example.db')

# 2. 创建一个游标对象，用于执行 SQL 语句
cursor = conn.cursor()

# 4. 使用 for 循环逐条插入 100,000 条数据
current_timestamp_start = datetime.now()
print("开始 Timestamp:", current_timestamp_start)
for i in range(1, 1000000):
    # 生成用户名，例如：User_1, User_2, ..., User_100000
    name = f'User_{i}_{random.randint(1, 100)}'
    age = i % 100 + 1   # 生成年龄，1 到 100 之间循环
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))

    # 每插入 1000 条记录后提交一次，避免过多数据累积在内存中
    if i % 1000 == 0:
        conn.commit()

current_timestamp_end = datetime.now()
print("结束 Timestamp:", current_timestamp_end)
print("耗时 Timestamp:", (current_timestamp_end-current_timestamp_start))


# 8. 关闭游标和连接
cursor.close()
conn.close()

3.1 如果有100万数据，会花多久？

这里插入100万数据，看看要多久：

// 执行结果 （100万数据差不多执行了 8 秒）
开始 Timestamp: 2024-09-19 22:34:49.516289
结束 Timestamp: 2024-09-19 22:34:57.204632

插入2000万数据：

// 执行结果 ：2000万数据大概 3 分钟 ，但是大小达到了 2G 左右
开始 Timestamp: 2024-09-22 13:40:22.255227
结束 Timestamp: 2024-09-22 13:43:40.879537

阶段总结：

如果只是为了做一个桌面端的应用或者简单的应用，肯定是用不到这么大的数据量的 。

单以几万的数据量来说，SQLite 抗下完全没有问题。哪怕全表百万数据量，不论是占用大小还是效率，也都能满足一个桌面端应用的使用。

3.2 不同数据量的搜索性能

百万级数据：毫秒级

千万级数据： ( 使用索引和不使用索引的差距)

100万的数据量下查询其实不消耗多少资源，这里直接在大数据量（2000万）下才考虑使用索引
查询效率不在一个量级，有了索引之后查询效率要快的多
相对的和大多数数据库一样，加入索引后DB文件大了不少 （2000万数据2G ，加入索引后大概在2.5G）

3.3 超大宽表的性能

import sqlite3
import random
from datetime import datetime

# 连接 SQLite 数据库
conn = sqlite3.connect('large_wide_table.db')
cursor = conn.cursor()

# 创建一个包含 50 列的宽表
columns = [f"col_{i} INTEGER" for i in range(50)]
create_table_sql = f"CREATE TABLE IF NOT EXISTS wide_table ({', '.join(columns)});"
cursor.execute(create_table_sql)

# 插入大量数据
def random_data():
    return [random.randint(1, 100) for _ in range(50)]

insert_sql = f"INSERT INTO wide_table VALUES ({', '.join(['?' for _ in range(50)])});"

# 模拟插入 100万 行数据
print("开始 Timestamp:", datetime.now())
for _ in range(1000000):
    cursor.execute(insert_sql, random_data())
print("结束 Timestamp:",  datetime.now())


# 提交事务
conn.commit()


-- 执行结果
开始 Timestamp: 2024-09-22 14:11:21.831756
结束 Timestamp: 2024-09-22 14:11:35.137666


-- 文件大小 ：103M

一共大概50列的 100万数据的结构表，总的占用大小在 100M左右
100万数据的插入时间 10秒出头
10059 条数据无索引查询的效率大概在 0.174s

总结

单纯从性能上说，完全够用了。我预计的数据总量可能就 2-3万，完全可以实现我的桌面端应用的需求。

注意：此处没有考虑网络损耗，也没有考虑应用占用的本地资源损耗，这个在下一篇来了解。

最后的最后 ❤️❤️❤️👇👇👇

👈 欢迎关注 ，超200篇优质文章，未来持续高质量输出 🎉🎉
🔥🔥🔥 系列文章集合，高并发，源码应有尽有 👍👍
走过路过不要错过，知识无价还不收钱 ❗❗

Python ：关系型数据库用 SQLite 妥妥足够了