pandas dataframe与数据库表列不匹配时的高效插入映射方法

最优方案：使用pandas原生to_sql批量插入（无需手动遍历） #

pandas内置的to_sql方法原生支持字段映射，底层走批量插入逻辑，性能是逐行插入的几十到上百倍，操作步骤如下：

1. 若DataFrame列名和数据库表要插入的字段名完全匹配，直接指定columns参数筛选要插入的列即可，数据库表中多余的字段会自动用表设置的默认值（比如示例里的created_at如果设置了默认当前时间，不需要额外处理）：

import pandas as pd
from sqlalchemy import create_engine

# 初始化数据库连接引擎，不同数据库的连接串格式可自行调整
engine = create_engine('mysql+pymysql://用户名:密码@主机地址/数据库名')

# 执行插入
data_frame.to_sql(
    name="db_table",  # 数据库表名
    con=engine,
    if_exists="append",  # 表示追加数据，可选值还有replace(覆盖表)/fail(表存在就报错)
    index=False,  # 不插入DataFrame自带的索引列
    columns=["column_1", "column_2"]  # 显式指定要插入的字段，和DataFrame列名一一对应
)

2. 若DataFrame列名和数据库表字段名不匹配，先重命名DataFrame的列再插入即可：

# 示例：把df的col1、col2映射到表的column_1、column_2
df_mapped = data_frame.rename(columns={
    "col1": "column_1",
    "col2": "column_2"
})
# 再执行上面的to_sql逻辑即可

3. 若需要手动给表中多余字段赋值，直接在DataFrame中新增对应列即可：

# 给column_3和created_at赋值
data_frame["column_3"] = "自定义默认值"
data_frame["created_at"] = pd.Timestamp.now()
# 全列插入
data_frame.to_sql(name="db_table", con=engine, if_exists="append", index=False)

备选方案：用数据库驱动的executemany批量插入 #

如果你不想引入SQLAlchemy依赖，可以直接用对应数据库的Python驱动的批量插入方法，性能同样远高于逐行插入，以MySQL的pymysql为例：

import pymysql

# 提取要插入的数据
insert_values = data_frame[["column_1", "column_2"]].values.tolist()
# 建立数据库连接
conn = pymysql.connect(host="你的主机地址", user="用户名", password="密码", database="数据库名")
cursor = conn.cursor()

# 执行批量插入
sql = "INSERT INTO db_table (column_1, column_2) VALUES (%s, %s)"
cursor.executemany(sql, insert_values)
conn.commit()

# 关闭资源
cursor.close()
conn.close()

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