Python DataFrame写入MySQL：重复项更新而非新增的高效方案咨询

你的原有思路问题 #

• 遍历DataFrame每行删除再追加：可行但效率极低。每一行都要单独发送DELETE请求，来回数据库的通信开销大，数据量稍大就会变慢，还可能出现临时数据不一致的问题（比如删了行还没插入新数据时，其他查询会看不到这条记录）。
• 你看到的INSERT ... NOT IN语句：只能实现新增不重复行，完全满足不了你“重复时更新Start Date”的需求——它会直接跳过重复的行，不会对已有数据做任何更新操作。

更优方案（推荐） #

前提：设置联合唯一索引 #

首先得给projects表的Project和Company列加联合唯一索引，这样MySQL才能识别“重复行”：

ALTER TABLE projects ADD UNIQUE INDEX idx_project_company (Project, Company);

方案1：批量INSERT + ON DUPLICATE KEY UPDATE #

利用MySQL的INSERT ... ON DUPLICATE KEY UPDATE语法，一次性处理新增和更新逻辑：

1. 把DataFrame数据转换成批量插入的SQL格式，再加上更新规则：

INSERT INTO projects (Project, Company, `Start Date`, Industry)
VALUES 
('项目A', '公司X', '2024-01-01', '科技'),
('项目B', '公司Y', '2024-02-01', '制造'),
('项目A', '公司X', '2024-03-01', '互联网') -- 这行会触发更新
ON DUPLICATE KEY UPDATE 
`Start Date` = VALUES(`Start Date`), -- 用新值更新Start Date
Industry = VALUES(Industry); -- 同步更新Industry（按需调整）

2. 在Python中结合sqlalchemy执行该SQL（推荐用参数化查询避免特殊字符问题）：

from sqlalchemy import create_engine, text
import pandas as pd

engine = create_engine('mysql+pymysql://user:password@host/dbname')

# 构造参数化的批量插入语句
params = [tuple(row) for _, row in df[['Project', 'Company', 'Start Date', 'Industry']].iterrows()]
sql = text("""
INSERT INTO projects (Project, Company, `Start Date`, Industry)
VALUES (:p, :c, :sd, :i)
ON DUPLICATE KEY UPDATE 
`Start Date` = VALUES(`Start Date`),
Industry = VALUES(Industry)
""")

# 批量执行
with engine.connect() as conn:
    conn.execute(sql, [{"p": p, "c": c, "sd": sd, "i": i} for p, c, sd, i in params])
    conn.commit()

方案2：临时表批量同步（适合大数据量） #

如果你的DataFrame数据量很大，用临时表的方式效率更高：

1. 创建和正式表结构一致的临时表：

CREATE TEMPORARY TABLE temp_projects LIKE projects;

2. 把DataFrame批量写入临时表：

df.to_sql('temp_projects', engine, if_exists='replace', index=False)

3. 从临时表同步数据到正式表，自动处理新增和更新：

INSERT INTO projects (Project, Company, `Start Date`, Industry)
SELECT Project, Company, `Start Date`, Industry FROM temp_projects
ON DUPLICATE KEY UPDATE 
`Start Date` = temp_projects.`Start Date`,
Industry = temp_projects.Industry;

这种方式先批量写入临时表，再用一次SQL完成同步，避免了逐行操作的开销，还能保证数据一致性。

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