如何通过pyodbc将pandas DataFrame追加写入SQL Server表

原有代码报错原因 #

pandas自带的to_sql方法仅支持接收SQLAlchemy连接对象作为入参，直接传入pyodbc原生连接必然触发运行错误，完全可以不依赖SQLAlchemy，仅通过pyodbc实现增量追加写入，且不会覆盖表内原有历史数据。

实现逻辑 #

• 构造和目标表字段完全匹配的参数化INSERT语句，使用pyodbc支持的?作为参数占位符
• 将DataFrame转换为pyodbc可识别的行元组列表，提前处理空值适配数据库类型
• 开启pyodbc批量写入优化，一次性提交所有新增数据，最后通过事务提交持久化写入结果

可直接复用的代码 #

import pyodbc
import pandas as pd

# 建立数仓连接
conn = pyodbc.connect(
    'dsn=azure_warehouse_dev;'
    'Trusted_Connection=yes;'
)
cursor = conn.cursor()

# 开启快速批量写入，大幅提升大批次数据写入性能
cursor.fast_executemany = True

try:
    # 提前处理DataFrame空值，将pandas的NaN转换为pyodbc可识别的None
    df_processed = dfmodwh.where(pd.notnull(dfmodwh), None)
    # 严格按照目标表列顺序提取数据，转换为列表格式
    data_rows = df_processed[['date', 'subkey', 'amount', 'age']].to_records(index=False).tolist()
    
    # 构造参数化插入SQL，表名、字段名和目标表dim.h2oresults完全对齐
    insert_sql = """
        INSERT INTO dim.h2oresults (date, subkey, amount, age)
        VALUES (?, ?, ?, ?)
    """
    
    # 批量执行插入
    cursor.executemany(insert_sql, data_rows)
    # 提交事务，数据持久化到数仓
    conn.commit()
    print(f"成功追加写入{len(data_rows)}条数据")

except Exception as e:
    # 写入异常时回滚事务，避免脏数据
    conn.rollback()
    print(f"写入失败，已回滚：{str(e)}")
finally:
    # 关闭连接释放资源
    cursor.close()
    conn.close()

注意事项 #

• 字段顺序必须严格对齐：INSERT语句中书写的字段顺序，必须和从DataFrame提取字段的顺序完全一致，否则会出现数据错位、类型不匹配报错
• 严禁通过字符串拼接的方式构造插入语句，必须使用?作为参数占位符，既可以避免特殊字符（如单引号、换行符）导致的SQL语法错误，也能防范SQL注入风险
• 必须显式调用conn.commit()，否则连接关闭后所有插入操作会自动回滚，数据不会真正写入数仓
• 该逻辑为纯追加模式，不会修改、删除dim.h2oresults表中已有的历史数据，完全满足每日新增数据持续写入的需求
• 如果后续DataFrame新增字段，需要先在数仓目标表中添加对应列，再同步修改INSERT语句中的字段列表和占位符数量即可

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