Python 流式导入大 CSV 到 SQLite:executemany 分批事务实践

举报
yd_229231784 发表于 2026/11/16 08:58:58 2026/11/16
【摘要】 针对大 CSV 流式导入 SQLite 的内存与事务开销问题,本文用 csv.reader 逐行读取、executemany 每 5000 行提交,并设置 WAL 与 busy_timeout,给出最小脚本和本机耗时/内存对比方法。

问题场景

把几十万行 CSV 导入 SQLite 时,常见写法是 readlines() 后一次性 executemany,或逐行 execute 并提交。前者会把文件内容整体放进内存;后者在默认提交策略下会产生大量事务开销,导入很慢。SQLite 本身适合嵌入式场景,但需要控制事务边界和写入方式。

本文的最小实践是:csv.reader 流式读取;每 5000 行调用一次 executemany;用 with conn 明确提交;连接初始化时开启 WAL 与 busy_timeout。示例使用占位数据和演示路径,运行结果以本机为准。

关键点

  1. 流式读取:csv.reader 每次只取一行,不把整个 CSV 读进列表。
  2. 分批写:executemany 一次绑定多行,减少 Python 与 SQLite 之间的调用次数。
  3. 显式事务:with conn: 在批次结束时提交,异常时回滚当前批次。executemany 本身不等于自动分批提交。
  4. WAL:PRAGMA journal_mode=WAL 让读写冲突更少,适合单写者加并发读的场景。
  5. busy_timeout:遇到锁时等待指定毫秒再报错,不能替代合理的事务粒度。
  6. 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)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。