Python 流式导入大 CSV 到 SQLite:executemany 分批事务实践
【摘要】 针对大 CSV 流式导入 SQLite 的内存与事务开销问题,本文用 csv.reader 逐行读取、executemany 每 5000 行提交,并设置 WAL 与 busy_timeout,给出最小脚本和本机耗时/内存对比方法。
问题场景
把几十万行 CSV 导入 SQLite 时,常见写法是 readlines() 后一次性 executemany,或逐行 execute 并提交。前者会把文件内容整体放进内存;后者在默认提交策略下会产生大量事务开销,导入很慢。SQLite 本身适合嵌入式场景,但需要控制事务边界和写入方式。
本文的最小实践是:csv.reader 流式读取;每 5000 行调用一次 executemany;用 with conn 明确提交;连接初始化时开启 WAL 与 busy_timeout。示例使用占位数据和演示路径,运行结果以本机为准。
关键点
- 流式读取:
csv.reader每次只取一行,不把整个 CSV 读进列表。 - 分批写:
executemany一次绑定多行,减少 Python 与 SQLite 之间的调用次数。 - 显式事务:
with conn:在批次结束时提交,异常时回滚当前批次。executemany本身不等于自动分批提交。 - WAL:
PRAGMA journal_mode=WAL让读写冲突更少,适合单写者加并发读的场景。 busy_timeout:遇到锁时等待指定毫秒再报错,不能替代合理的事务粒度。synchronous=NORMAL:在 WAL 下减少同步开销,但崩溃时可能丢失最近事务,导入场景需自行权衡。
最小导入脚本
保存为 import_csv.py。示例假设 CSV 有表头,列顺序为 id,user_id,amount,created_at,且 id 唯一。
import csv
import sqlite3
import sys
BATCH_SIZE = 5000
SCHEMA = '''
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY,
user_id TEXT NOT NULL,
amount REAL NOT NULL,
created_at TEXT NOT NULL
);
'''
INSERT_SQL = '''
INSERT OR REPLACE INTO events (id, user_id, amount, created_at)
VALUES (?, ?, ?, ?)
'''
def connect_db(db_path):
conn = sqlite3.connect(db_path)
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA synchronous=NORMAL')
conn.execute('PRAGMA busy_timeout=5000')
conn.execute(SCHEMA)
return conn
def parse_row(row):
if len(row) != 4:
raise ValueError(f'列数应为4,实际为{len(row)}: {row!r}')
return (int(row[0]), row[1], float(row[2]), row[3])
def import_csv(csv_path, db_path):
conn = connect_db(db_path)
total = 0
batch = []
try:
with open(csv_path, 'r', encoding='utf-8-sig', newline='') as f:
reader = csv.reader(f)
next(reader, None)
for line_no, row in enumerate(reader, start=2):
try:
batch.append(parse_row(row))
except ValueError as exc:
raise ValueError(f'{csv_path}:{line_no}: {exc}') from exc
if len(batch) >= BATCH_SIZE:
with conn:
conn.executemany(INSERT_SQL, batch)
total += len(batch)
batch.clear()
if batch:
with conn:
conn.executemany(INSERT_SQL, batch)
total += len(batch)
finally:
conn.close()
return total
def main():
if len(sys.argv) != 3:
print('用法: python import_csv.py data.csv demo.db', file=sys.stderr)
raise SystemExit(2)
count = import_csv(sys.argv[1], sys.argv[2])
print(f'imported={count}')
if __name__ == '__main__':
main()
运行方法:
python import_csv.py data.csv demo.db
可选检查:
python - <<'PY'
import sqlite3
conn = sqlite3.connect('demo.db')
print(conn.execute('select count(*) from events').fetchone()[0])
conn.close()
PY
如果 CSV 没有表头,把 next(reader, None) 去掉,并让数据第一行也参与解析。
耗时与内存对比
下面的脚本生成临时 CSV,然后分别跑分批 executemany 和逐行提交,打印耗时与 Python 侧峰值内存。它只用于观察差异,不提供固定性能数字;请以本机输出为准,并多跑几次看波动。
保存为 bench.py:
import csv
import os
import sqlite3
import tempfile
import time
import tracemalloc
N = 50000
BATCH_SIZE = 5000
SCHEMA = '''
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY,
user_id TEXT NOT NULL,
amount REAL NOT NULL,
created_at TEXT NOT NULL
);
'''
INSERT_SQL = '''
INSERT OR REPLACE INTO events (id, user_id, amount, created_at)
VALUES (?, ?, ?, ?)
'''
def make_csv(path, n):
with open(path, 'w', encoding='utf-8', newline='') as f:
writer = csv.writer(f)
writer.writerow(['id', 'user_id', 'amount', 'created_at'])
for i in range(n):
writer.writerow([i, f'u{i % 1000}', f'{i % 1000}.25', '2024-01-01T00:00:00Z'])
def open_db(path):
for suffix in ('', '-wal', '-shm'):
try:
os.remove(path + suffix)
except FileNotFoundError:
pass
conn = sqlite3.connect(path)
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA synchronous=NORMAL')
conn.execute('PRAGMA busy_timeout=5000')
conn.execute(SCHEMA)
return conn
def load_batch(csv_path, db_path):
conn = open_db(db_path)
total = 0
batch = []
try:
with open(csv_path, 'r', encoding='utf-8-sig', newline='') as f:
reader = csv.reader(f)
next(reader, None)
for row in reader:
batch.append((int(row[0]), row[1], float(row[2]), row[3]))
if len(batch) >= BATCH_SIZE:
with conn:
conn.executemany(INSERT_SQL, batch)
total += len(batch)
batch.clear()
if batch:
with conn:
conn.executemany(INSERT_SQL, batch)
total += len(batch)
finally:
conn.close()
return total
def load_per_row_commit(csv_path, db_path):
conn = open_db(db_path)
total = 0
try:
with open(csv_path, 'r', encoding='utf-8-sig', newline='') as f:
reader = csv.reader(f)
next(reader, None)
for row in reader:
params = (int(row[0]), row[1], float(row[2]), row[3])
with conn:
conn.execute(INSERT_SQL, params)
total += 1
finally:
conn.close()
return total
def measure(name, func, csv_path, db_path):
tracemalloc.start()
started = time.perf_counter()
rows = func(csv_path, db_path)
elapsed = time.perf_counter() - started
_, peak = tracemalloc.get_traced_memory()
tracemalloc.stop()
peak_mib = peak / 1024 / 1024
print(f'{name}: rows={rows}, seconds={elapsed:.3f}, python_peak_mib={peak_mib:.2f}')
def main():
with tempfile.TemporaryDirectory() as tmpdir:
csv_path = os.path.join(tmpdir, 'events.csv')
make_csv(csv_path, N)
measure('batch_5000', load_batch, csv_path, os.path.join(tmpdir, 'batch.db'))
measure('per_row_commit', load_per_row_commit, csv_path, os.path.join(tmpdir, 'row.db'))
if __name__ == '__main__':
main()
运行:
python bench.py
tracemalloc 统计的是 Python 分配峰值,不包含 SQLite 页缓存和操作系统文件缓存;所以它适合验证没有把整个 CSV 加载进内存,不能等同于进程总内存。耗时也会受磁盘、文件系统、是否首次运行、WAL 检查点等影响。
边界与限制
- 分批提交不是全有或全无。中途失败时,前面批次已经落库;需要幂等键、唯一约束,或先写临时表再合并。
BATCH_SIZE=5000只是示例。行宽、索引数量、磁盘和并发都会影响合适值,应在目标环境压测后调整。INSERT OR REPLACE在冲突时可能表现为删除再插入,触发器和外键行为要确认;需要保留原行时改用 upsert。- WAL 会生成
-wal和-shm文件;网络文件系统、只读介质或特殊部署环境可能不支持。导入结束后可按需执行PRAGMA wal_checkpoint(TRUNCATE),但并发读时可能等待。 busy_timeout只处理锁等待,不解决长事务。多个写者仍应串行化,避免同时大批量写同一库。- 示例未处理 CSV 引号、换行、编码和坏行。生产环境要校验列数、类型和空值,并记录行号。
- 导入前建太多二级索引会拖慢写入。可在数据导入后建索引,但主键和必要唯一约束仍需保留。
【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)