如何用Python将23列20000行DataFrame写入MSSQL并清空原有数据

嗨，看来你已经搞定了单条数据的插入，接下来要处理2万行DataFrame的批量写入+清空表的需求，这里有两种实用的方案，你可以根据自己的技术栈偏好来选：

方案一：用Pandas + SQLAlchemy（最简洁高效） #

这个方法利用Pandas内置的to_sql方法，配合SQLAlchemy的引擎来处理数据库连接，代码量少且性能不错，适合大多数场景。

步骤分解： #

1. 安装依赖（如果还没装的话）：

   pip install sqlalchemy pandas pypyodbc
   

2. 实现代码：

   import pandas as pd
   from sqlalchemy import create_engine
   
   # 1. 构建数据库连接引擎
   # 替换成你的MSSQL凭据
   server = 'XXX'
   database = 'XXX'
   uid = 'XXX'
   pwd = 'XXX'
   conn_str = f'mssql+pypyodbc://{uid}:{pwd}@{server}/{database}?driver=SQL+Server'
   engine = create_engine(conn_str)
   
   # 2. 清空目标表（用TRUNCATE比DELETE快，适合大表）
   with engine.connect() as conn:
       conn.execute('TRUNCATE TABLE MODREPORT')
       conn.commit()
   
   # 3. 将DataFrame写入MSSQL
   # chunksize：分块写入，避免一次性加载大量数据到内存
   # index=False：不把DataFrame的索引作为列写入
   df.to_sql(
       name='MODREPORT',
       con=engine,
       if_exists='append',  # 因为已经清空表，用append即可
       index=False,
       chunksize=1000  # 根据你的内存情况调整，比如1000-5000都可以
   )
   

方案二：用你已有的pypyodbc批量插入（无需额外依赖） #

如果你不想引入SQLAlchemy，也可以基于你现有的pypyodbc连接，用executemany来批量插入数据，效率比循环单条插入高很多。

实现代码： #

import pandas as pd
import pypyodbc

# 1. 建立数据库连接
connection = pypyodbc.connect(
    'Driver={SQL Server};'
    'Server=XXX;'
    'Database=XXX;'
    'uid=XXX;'
    'pwd=XXX'
)
cursor = connection.cursor()

# 2. 清空目标表
cursor.execute('TRUNCATE TABLE MODREPORT')
connection.commit()

# 3. 准备批量插入的数据：将DataFrame转成元组列表
# 获取所有列名，用来构建INSERT语句的字段部分
columns = ', '.join(df.columns)
# 生成占位符：每个字段对应一个?
placeholders = ', '.join(['?'] * len(df.columns))
# 把DataFrame的每一行转成元组
data_tuples = [tuple(row) for row in df.to_numpy()]

# 4. 批量执行插入
insert_query = f"INSERT INTO MODREPORT ({columns}) VALUES ({placeholders})"
cursor.executemany(insert_query, data_tuples)
connection.commit()

# 5. 关闭连接
cursor.close()
connection.close()

关键注意点： #

• TRUNCATE vs DELETE：如果只是清空表数据，TRUNCATE TABLE比DELETE FROM效率高得多，因为它不会记录每一行的删除操作，适合处理大表；但如果需要触发表上的删除触发器，就用DELETE FROM MODREPORT。
• 批量插入的chunksize：如果你的DataFrame特别大（比如超过10万行），可以把data_tuples分成多个小批次，循环调用executemany，避免占用过多内存。
• 字段匹配：确保DataFrame的列名和MSSQL表的列名完全一致，顺序也要对应，否则会出现插入错误。

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