使用 SQLite 处理大量小数据库时，需要考虑数据库文件的数量、管理方式、性能优化等因素。SQLite 是轻量级的数据库，适合嵌入式系统和小型项目，但在处理大量数据库文件时，仍需要仔细设计和管理。
一、问题背景
近期一个项目中，需要处理大量（少于 100 个）结构相同的 SQLite 数据库。这些数据库各自都不大，但需要对它们进行查询并合并结果。这导致了以下几个问题：
- 数据库数量不确定： 数据库数量是动态的，可能随时增加或减少，最大数量不超过 100 个。
- 查询效率： 需要对这些数据库进行频繁的查询，查询需要高效且快速。
- 数据更新频繁： 这些数据库中的数据经常更新，因此需要一种方法能够快速同步更新后的数据。
- 合并结果： 需要将这些数据库中的数据合并成一个结果集，以便进行统一的分析和处理。
二、解决方案
为了解决上述问题，可以采用以下解决方案：
1、使用 ATTACH 命令：
使用 ATTACH 命令可以将多个数据库附加到一个主数据库中，从而实现对多个数据库的统一查询和管理。
如下代码示例：
import sqlite3 as lite# 连接主数据库
con = lite.connect('main.db')# 附加多个数据库到主数据库
for i in range(1, 100):con.execute(f"ATTACH DATABASE 'db{i}.db' As 'db{i}'")# 查询附加的数据库
cur = con.cursor()
cur.execute("""SELECT 'db1',* FROM db1.tableUNION SELECT 'db2',* FROM db2.table""")# 打印查询结果
for row in cur.fetchall():print(row)
2、使用 CREATE TEMP TABLE：
使用 CREATE TEMP TABLE 会在内存中创建一个临时表，以将查询结果存储到临时表中。这可以避免频繁地附加和分离数据库，从而提高查询效率。
如下代码示例：
import sqlite3 as lite# 连接主数据库
con = lite.connect('main.db')# 查询附加的数据库并存储到临时表
cur = con.cursor()
cur.execute("""CREATE TEMP TABLE tmp_table ASSELECT 'db1',* FROM db1.tableUNION SELECT 'db2',* FROM db2.table""")# 查询临时表
cur.execute("SELECT * FROM tmp_table")# 打印查询结果
for row in cur.fetchall():print(row)
3、使用 UNION 或 UNION ALL：
使用 UNION 或 UNION ALL 可以将多个查询结果合并成一个结果集。这可以避免使用 ATTACH 命令或 CREATE TEMP TABLE，从而简化查询操作。
如下代码示例：
import sqlite3 as lite# 连接主数据库
con = lite.connect('main.db')# 查询附加的数据库
cur = con.cursor()
cur.execute("""SELECT 'db1',* FROM db1.tableUNION SELECT 'db2',* FROM db2.table""")# 打印查询结果
for row in cur.fetchall():print(row)
4、使用 Python 的第三方库：
可以使用 Python 的第三方库来简化对多个数据库的操作。这些库可以提供更高级别的 API，方便对多个数据库进行查询和管理。
例如，可以使用 sqlalchemy 库来连接和查询多个数据库。
如下代码示例：
from sqlalchemy import create_engine# 创建引擎列表，用于连接多个数据库
engines = []
for i in range(1, 100):engines.append(create_engine(f'sqlite:///db{i}.db'))# 创建主引擎，用于查询多个数据库
main_engine = create_engine('sqlite:///main.db')# 创建一个 Session 对象，用于执行查询
session = main_engine.sessionmaker()()# 查询多个数据库并合并结果
results = session.execute("""SELECT 'db1',* FROM db1.tableUNION SELECT 'db2',* FROM db2.table""")# 打印查询结果
for row in results:print(row)
处理大量小数据库需要在设计和性能上做出权衡。通过适当的目录结构、批量处理、数据库合并、并行处理以及优化 SQLite 配置，可以显著提高管理效率和性能。同时，如果数据规模继续扩大，迁移到更适合的数据库系统可能是必要的
