将大型 CSV 文件高效导入 SQLite 数据库的完整优化指南

轻萱小哥_7473

轻萱小哥_7473

2026-11-22

444人浏览

原创

将大型 CSV 文件高效导入 SQLite 数据库的完整优化指南

本文详解如何将数百万行 CSV 数据在几分钟内(而非 72 小时)批量导入 SQLite,核心包括禁用自动提交、使用 pandas.to_sql 分块写入、预处理日期与布尔字段,并避免逐行执行 SQL。

本文详解如何将数百万行 csv 数据在几分钟内(而非 72 小时)批量导入 sqlite,核心包括禁用自动提交、使用 `pandas.to_sql` 分块写入、预处理日期与布尔字段,并避免逐行执行 sql。

将 CSV 转为 SQLite 数据库本应显著提升后续查询效率,但若实现方式不当(如原始代码中每行 INSERT + commit()),性能会断崖式下降——726 万行耗时 72 小时,本质是 I/O 和事务开销被放大了数百万倍。根本优化思路是:减少磁盘写入次数、批量操作、利用 Pandas 内置优化机制。

✅ 关键优化策略

  1. 禁用自动提交,手动单次提交
    SQLite 默认每次 execute() 后隐式提交事务,产生海量磁盘 I/O。改为显式控制事务边界,仅在全部数据插入完成后调用 con.commit()。

  2. 使用 pandas.to_sql(..., chunksize=N) 批量插入
    chunksize 参数让 Pandas 自动将 DataFrame 拆分为批次(如 1000 行/批),每批生成一条 INSERT INTO ... VALUES (...), (...), ... 多值语句,比逐行插入快 10–100 倍。实测 chunksize=1000 在多数场景下平衡内存与速度。

  3. 预处理字段类型,避免运行时转换

    Drizzle ORM TypeScript SQL 数据库工具包
    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 可自动映射为 SQLite NULL,无需手动 if word_count else 0。
  4. 修正 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 倍——从此告别“两分钟等加载,七十二小时等导入”的困境。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

数据库 csv

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
python打包成可执行文件
python打包成可执行文件

本专题为大家带来python打包成可执行文件相关的文章,大家可以免费的下载体验。

2023.07.20

1991

4

python能做什么
python能做什么

python能做的有:可用于开发基于控制台的应用程序、多媒体部分开发、用于开发基于Web的应用程序、使用python处理数据、系统编程等等。本专题为大家提供python相关的各种文章、以及下载和课程。

2023.07.25

5684

7

format在python中的用法
format在python中的用法

Python中的format是一种字符串格式化方法,用于将变量或值插入到字符串中的占位符位置。通过format方法,我们可以动态地构建字符串,使其包含不同值。php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

2023.07.31

1929

3

python教程
python教程

Python已成为一门网红语言,即使是在非编程开发者当中,也掀起了一股学习的热潮。本专题为大家带来python教程的相关文章,大家可以免费体验学习。

2023.08.03

34897

23

python环境变量的配置
python环境变量的配置

Python是一种流行的编程语言,被广泛用于软件开发、数据分析和科学计算等领域。在安装Python之后,我们需要配置环境变量,以便在任何位置都能够访问Python的可执行文件。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

2023.08.04

4067

5

python eval
python eval

eval函数是Python中一个非常强大的函数,它可以将字符串作为Python代码进行执行,实现动态编程的效果。然而,由于其潜在的安全风险和性能问题,需要谨慎使用。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

2023.08.04

4067

5

scratch和python区别
scratch和python区别

scratch和python的区别:1、scratch是一种专为初学者设计的图形化编程语言,python是一种文本编程语言;2、scratch使用的是基于积木的编程语法,python采用更加传统的文本编程语法等等。本专题为大家提供scratch和python相关的文章、下载、课程内容,供大家免费下载体验。

2023.08.11

1303

5

python合并两个列表
python合并两个列表

Python是一种强大的编程语言,具有许多方便的功能和工具。在Python中,有多种方法可以合并两个列表。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

2023.08.10

636

4

python是前端还是后端
python是前端还是后端

Python属于前端也属于后端,其灵活性和丰富的生态系统使得开发人员能够在不同的领域中灵活运用。本专题为大家提供python相关的文章、下载、课程内容,供大家免费下载体验。

2023.08.11

3163

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程