使用Pandas df.to_sql()分块写入大型SQLite数据库的报错问题

刚搞定过差不多规模的SQLite表处理，给你一套亲测有效的方案，完美适配你4400万行、12GB数据库的场景——核心就是用Pandas分块读写，既不炸内存，又能高效完成列的添加/修改。

核心逻辑 #

直接读取全表肯定会把内存撑爆，所以我们用chunksize把大表拆成小批次DataFrame：

1. 先检查目标新表是否存在，存在则直接删除
2. 分块读取原表，对每个批次做列的添加/修改
3. 第一批次负责创建新表，后续批次直接追加数据
4. 每处理完一批就提交一次事务，避免崩溃丢失进度

完整代码实现 #

import pandas as pd
import sqlite3

# 连接SQLite数据库，这里可以加优化参数提升速度
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()

# 开启SQLite写入优化（可选但强烈推荐）
cursor.execute("PRAGMA journal_mode = WAL")  # 写前日志，提升写入并发
cursor.execute("PRAGMA synchronous = NORMAL")  # 降低同步级别，加快写入速度
cursor.execute("PRAGMA cache_size = -2000000")  # 设置2GB缓存（负数单位是KB）

# 分块读取大表，chunksize根据你的内存调整，比如10万行/块
chunk_iter = pd.read_sql_query("SELECT * FROM your_large_table", conn, chunksize=100000)

first_chunk = True  # 标记是否是第一块，用于创建新表

for chunk in chunk_iter:
    # --------------------------
    # 这里写你的数据处理逻辑
    # 示例：添加新列、修改现有列
    chunk['calculated_col'] = chunk['original_col'] * 1.5  # 新增计算列
    chunk['text_col'] = chunk['text_col'].str.strip()  # 清理文本列
    # --------------------------

    # 写入新表
    if first_chunk:
        # 第一块：删除旧表（如果存在），然后创建新表
        cursor.execute("DROP TABLE IF EXISTS new_processed_table")
        chunk.to_sql('new_processed_table', conn, index=False, if_exists='replace')
        first_chunk = False
    else:
        # 后续块：直接追加到新表
        chunk.to_sql('new_processed_table', conn, index=False, if_exists='append')
    
    # 每批提交一次事务，避免数据丢失
    conn.commit()
    print(f"已处理并写入 {len(chunk)} 行数据")

# 关闭连接
cursor.close()
conn.close()

必看优化要点 #

• chunksize的合理调整：根据你的可用内存来，内存大可以调到20万行/块，内存紧张就设5万，避免内存溢出
• 只读取需要的列：如果不需要原表所有列，直接在SQL查询里指定列名，比如SELECT col1, col2 FROM your_large_table，能大幅降低内存占用
• 矢量化操作优先：处理列的时候尽量用Pandas的矢量化方法（比如str.strip()、*运算），别用apply()循环，4400万行的话循环会慢到离谱
• 索引后加：如果新表需要建索引，等所有数据写入完成后再创建，不然每批追加都更新索引会导致速度骤降
• 事务提交：一定要每批提交一次，不然SQLite的事务日志会越来越大，甚至拖垮整个进程

额外技巧 #

如果需要指定新表的列类型（避免Pandas自动推断出错），可以在第一块写入时用dtype参数：

chunk.to_sql('new_processed_table', conn, index=False, if_exists='replace',
             dtype={'calculated_col': 'FLOAT', 'text_col': 'TEXT'})

内容的提问来源于stack exchange，提问作者data-cruncher524