使用pandas to_sql向MySQL插入数据时行数远超DataFrame问题排查

问题还原 #

本地通过XAMPP搭建MySQL服务器，数据库名为MySQLDB，尝试将约5万行的pandas DataFrame插入新建表datatable，但插入后表行数超过100万。执行代码如下：

import pandas as pd
from sqlalchemy import create_engine
import mysql.connector

pandas_db = pd.read_csv('filename.csv', index_col = [0])

engine = create_engine('mysql+mysqlconnector://root:@localhost:[port]/MySQLDB', echo=False)

pandas_db.to_sql(name='datatable', con=engine, if_exists = 'replace', chunksize = 100, index=False)

可能原因排查 #

1. CSV读取解析错误
   使用index_col=[0]时，如果CSV第一列存在重复值、格式异常，可能导致pandas解析时重复生成行；也可能源CSV文件本身就包含大量重复行（比如导出时重复写入）。
2. 代码重复执行
   若多次运行插入代码，虽然if_exists='replace'会先删除原表再插入，但如果执行过程中出现中断后重试，可能存在临时数据残留（概率极低）。
3. 数据库端特殊配置
   比如表上存在自动插入数据的触发器、存储过程，或MySQL的复制/同步配置异常，但在本地XAMPP默认配置下几乎不可能出现。

改进方案与验证步骤 #

1. 先验证源数据与DataFrame的正确性 #

在插入前确认DataFrame的行数和重复情况，排除源数据问题：

import pandas as pd

pandas_db = pd.read_csv('filename.csv', index_col=[0])
# 打印DataFrame总行数
print(f"DataFrame总行数: {len(pandas_db)}")
# 统计重复行数量
print(f"DataFrame重复行数: {pandas_db.duplicated().sum()}")
# 可选：检查CSV源文件的行数（命令行执行）
# wc -l filename.csv

如果这里的行数已经超过5万，说明问题出在CSV读取或源文件本身，需清理CSV或调整读取参数（比如去掉index_col=[0]测试）。

2. 优化插入代码，增加日志与验证 #

打开SQLAlchemy的echo=True查看执行日志，同时调整插入参数减少连接开销，插入后验证数据库行数：

from sqlalchemy import create_engine

# 创建引擎时开启echo，查看SQL执行细节
engine = create_engine('mysql+mysqlconnector://root:@localhost:[port]/MySQLDB', echo=True)

# 先手动删除表，确保环境干净
with engine.connect() as conn:
    conn.execute("DROP TABLE IF EXISTS datatable")
    conn.commit()

# 使用method='multi'批量插入，减少连接次数，降低异常概率
pandas_db.to_sql(
    name='datatable',
    con=engine,
    if_exists='replace',
    chunksize=1000,  # 增大chunksize提高效率
    index=False,
    method='multi'
)

# 验证数据库表行数
with engine.connect() as conn:
    count = conn.execute("SELECT COUNT(*) FROM datatable").scalar()
    print(f"数据库表最终行数: {count}")

3. 排查数据库端异常 #

如果上述步骤后行数仍异常，登录XAMPP的phpMyAdmin，查看datatable的结构和数据：

• 检查是否存在触发器：进入表的“触发器”标签页，确认无自动插入数据的触发器。
• 查看数据是否有规律重复：执行SELECT COUNT(*), [主键/唯一列] FROM datatable GROUP BY [主键/唯一列] HAVING COUNT(*) > 1排查重复数据。

总结 #

90%以上的概率是CSV读取解析错误或源文件本身存在重复行，优先验证DataFrame的行数和重复情况；其次检查代码是否被重复执行；最后排查数据库端的特殊配置。

内容的提问来源于stack exchange，提问作者neil