Python操作MySQL执行TRUNCATE表后逐行插入仅留最后一条记录，如何正确实现清表全量插入？

原代码的核心逻辑错误是把仅需执行一次的操作（TRUNCATE清表、事务提交、连接关闭）错误放进了逐行遍历数据的循环内部，导致每处理一行数据就触发一次清表、单条插入、提交的流程，最终表内只会留存最后一次循环写入的单条记录。

原错误结构示意：

conn = pymysql.connect(...)
for row in socrata_response:
    cursor.execute("TRUNCATE TABLE 目标表") # 每遍历一行就清一次存量数据
    cursor.execute("INSERT INTO 目标表(字段) VALUES(%s)", (row['字段值'])) # 逐行单插
    conn.commit() # 每插一行就提交
conn.close()

按照“先取数、再清表、再批量插入、最后统一提交”的顺序重构逻辑，完全避免循环内重复执行清表操作：

1. 先从Socrata接口拉取全量CDC疫情数据，整理成结构化的待插入参数列表
2. 建立MySQL连接，仅执行1次TRUNCATE语句清空目标表存量，不放在循环内
3. 用pymysql内置的批量插入能力，一次性把整理好的全量数据写入表中
4. 所有写入完成后统一提交事务，释放数据库连接

import pymysql
from sodapy import Socrata

# --------------------------
# 步骤1：拉取Socrata全量数据，先取数再准备插入，不要边清表边取数
# --------------------------
socrata_client = Socrata("data.cdc.gov", None)
# 按需调整数据集ID、筛选条件、拉取上限
socrata_data = socrata_client.get("CDC疫情数据集ID", limit=200000)

# 整理成和SQL占位符一一对应的参数元组列表
batch_params = []
for row in socrata_data:
    # 按实际表结构的字段顺序取值，和下方INSERT的字段顺序完全匹配
    batch_params.append((
        row.get("observation_date"),
        row.get("state"),
        row.get("positive_cases"),
        row.get("total_deaths")
    ))

# --------------------------
# 步骤2：建立数据库连接，执行一次清表+批量插入
# --------------------------
conn = pymysql.connect(
    host="数据库地址",
    port=3306,
    user="数据库账号",
    password="数据库密码",
    db="库名",
    charset="utf8mb4"
)
cursor = conn.cursor()

try:
    # 全流程仅执行1次TRUNCATE，放在所有插入操作之前
    cursor.execute("TRUNCATE TABLE cdc_covid_metrics")

    # 批量插入SQL，占位符顺序和batch_params内的元组顺序匹配
    insert_sql = """
        INSERT INTO cdc_covid_metrics 
        (observation_date, state, positive_cases, total_deaths)
        VALUES (%s, %s, %s, %s)
    """
    # 调用executemany一次性传入所有待插入参数，pymysql内部会自动优化批量写入效率
    cursor.executemany(insert_sql, batch_params)

    # 所有插入完成后统一提交
    conn.commit()
    print(f"同步完成，共写入{cursor.rowcount}条数据")

except Exception as e:
    # 捕获异常时回滚，避免数据不一致
    conn.rollback()
    print(f"同步失败，已回滚，错误信息：{str(e)}")

finally:
    # 无论成功失败，都强制释放连接资源
    cursor.close()
    conn.close()

• TRUNCATE的执行边界：必须放在批量插入操作的最前面，整个脚本运行周期内仅执行1次，绝不能放入逐行遍历接口返回数据的循环内部
• 批量插入不要手动拼接多行VALUES的SQL，pymysql的executemany已经做了语法优化、SQL注入防护，比手动拼SQL的安全性和执行效率更高
• 一定要加异常回滚逻辑：MySQL中TRUNCATE是隐式自动提交的DDL操作，如果清表后插入过程中断报错，会出现原数据被清空、新数据未写入的空表问题，必须通过异常捕获做流程兜底
• 数据量超过10万行时，可以把batch_params拆成每1000-5000条一个批次，分多次调用executemany，避免单次请求的数据包大小超过数据库max_allowed_packet配置限制报错
• 事务提交时机要统一：等所有批次的插入都完成后再执行一次conn.commit()，不要每插一批就提交一次，减少事务开销

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