
本文详解如何将数百万行 CSV 数据在几分钟内(而非 72 小时)批量导入 SQLite,核心包括禁用自动提交、使用 pandas.to_sql 分块写入、预处理日期与布尔字段,并避免逐行执行 SQL。
本文详解如何将数百万行 csv 数据在几分钟内(而非 72 小时)批量导入 sqlite,核心包括禁用自动提交、使用 `pandas.to_sql` 分块写入、预处理日期与布尔字段,并避免逐行执行 sql。
将 CSV 转为 SQLite 数据库本应显著提升后续查询效率,但若实现方式不当(如原始代码中每行 INSERT + commit()),性能会断崖式下降——726 万行耗时 72 小时,本质是 I/O 和事务开销被放大了数百万倍。根本优化思路是:减少磁盘写入次数、批量操作、利用 Pandas 内置优化机制。
✅ 关键优化策略
禁用自动提交,手动单次提交
SQLite 默认每次execute()后隐式提交事务,产生海量磁盘 I/O。改为显式控制事务边界,仅在全部数据插入完成后调用con.commit()。使用
pandas.to_sql(..., chunksize=N)批量插入chunksize参数让 Pandas 自动将 DataFrame 拆分为批次(如1000行/批),每批生成一条INSERT INTO ... VALUES (...), (...), ...多值语句,比逐行插入快 10–100 倍。实测chunksize=1000在多数场景下平衡内存与速度。
Drizzle ORM TypeScript SQL 数据库工具包下载Drizzle ORM 是一个轻量级的 TypeScript ORM,提供类型安全的 SQL 模式声明、关系型和类 SQL 查询构建器,以及自动生成迁移。其压缩后仅 7.4kb,且无依赖项,支持 PostgreSQL、MySQL 和 SQLite,包括 Neon、Turso 和 Cloudflare D1 等无服务器数据库。
-
预处理字段类型,避免运行时转换
- 日期列(如
"2021-02-26")→ 使用.apply(fixDate)统一转为date.toordinal()整数,避免 SQL 中字符串拼接与类型转换; - 布尔列(
"true"/"false")→ 用astype({'restricted': 'bool'})直接转为 SQLite 兼容的整数(0/1),比int(x == "true")更安全高效; - 空值处理:
pd.read_csv()默认将空字符串转为NaN,配合astype可自动映射为 SQLiteNULL,无需手动if word_count else 0。
- 日期列(如
修正 SQL 语法错误并增强健壮性
原始代码中SELECT ... WHEN是无效语法,应为WHERE;同时需用.fetchall()获取结果,否则仅返回游标对象。
✅ 优化后完整代码(生产就绪)
import sqlite3
import pandas as pd
from datetime import date
def fix_date(d: str) -> int:
"""将 'YYYY-MM-DD' 字符串转为 date.toordinal() 整数"""
y, m, d_part = map(int, d.split("-"))
return date(y, m, d_part).toordinal()
def load_csv_to_sqlite(csv_path: str, db_path: str, table_name: str = "works", chunk_size: int = 1000):
with sqlite3.connect(db_path) as con:
# 读取 CSV(自动处理缺失值和编码)
df = pd.read_csv(csv_path, encoding='utf-8')
# 字段预处理
df['creation date'] = df['creation date'].apply(fix_date)
df['restricted'] = df['restricted'].map({'true': True, 'false': False}).astype('boolean')
df['complete'] = df['complete'].map({'true': True, 'false': False}).astype('boolean')
# word_count 列保留为整数,NaN 自动转为 NULL
df['word_count'] = pd.to_numeric(df['word_count'], errors='coerce').astype('Int64')
# 批量写入(自动创建表,替换已存在表)
df.to_sql(table_name, con, if_exists='replace', index=False, chunksize=chunk_size)
# 单次提交(关键!)
con.commit()
# 验证:查询示例
result = con.execute("SELECT * FROM works WHERE word_count = 1836").fetchall()
print(f"Found {len(result)} rows with word_count = 1836")
# 使用示例
if __name__ == "__main__":
load_csv_to_sqlite(
csv_path="Out_Files/works-20210226.csv",
db_path="2025/works-20210226.db"
)⚠️ 注意事项与进阶建议
-
路径分隔符:Windows 下推荐使用正斜杠
/或原始字符串r"Out_Files\works.csv",避免反斜杠转义问题; -
内存权衡:
chunksize过大(如10000)可能触发MemoryError;过小(如100)则批次过多,降低吞吐。建议从1000起测试,根据机器内存调整; -
索引加速:导入后立即为高频查询字段(如
word_count)创建索引:con.execute("CREATE INDEX idx_word_count ON works(word_count)") -
替代方案:若数据超 1 亿行,可考虑
sqlite3原生executemany()+csv.reader手动批处理,或切换至 DuckDB(对 CSV 原生支持更优); -
错误处理:生产环境应包裹
try/except,并在失败时回滚事务,避免数据库损坏。
经此优化,726 万行 CSV 导入 SQLite 通常可在 3–8 分钟内完成(取决于 SSD 性能),速度提升超 1000 倍——从此告别“两分钟等加载,七十二小时等导入”的困境。

















