如何用Python高效导入超大规模CSV至SQL Server？

你当前的Python代码逐行插入500万条数据效率极低，且一次性加载全量数据会占用大量内存，以下是几种可行的优化方案：

一、优化Python批量插入代码 #

替换逐行cursor.execute为批量executemany，同时分块读取CSV避免内存溢出：

import pyodbc
import pandas as pd

# 建立数据库连接
conn_str = (
    r'DRIVER={SQL Server};'
    r'SERVER=-hostname-\SQLEXPRESS;'
    r'DATABASE=myDbs;'
    r'Trusted_Connection=yes;'
)
cnxx = pyodbc.connect(conn_str)
cursor = cnxx.cursor()

# 分块读取CSV，每块10000条（可根据内存调整）
chunk_size = 10000
for chunk in pd.read_csv(r"C:\Users\***\***\file.csv", index_col=0, chunksize=chunk_size):
    # 将数据转换为元组列表，适配executemany
    data = [tuple(row) for row in chunk.itertuples(index=False)]
    # 批量插入
    cursor.executemany('''
        INSERT INTO Divys (ride_id, rideable_type, 
        start_time, end_time, start_station_name, 
        start_station_id, end_station_name, end_station_id, 
        start_lat, start_lng, end_lat, end_lng, 
        member_casual, date_of_the_ride, year_of_the_ride, 
        quarter_of_the_ride, month_of_the_ride, day_of_week, 
        d1, d2, trip_duration_m)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ''', data)
    # 每块提交一次事务
    cnxx.commit()

cnxx.close()

二、使用pandas的to_sql简化批量导入 #

借助SQLAlchemy引擎，to_sql的method='multi'参数会生成批量插入语句，代码更简洁：

import pandas as pd
from sqlalchemy import create_engine

# 创建SQLAlchemy连接引擎
engine = create_engine('mssql+pyodbc://-hostname-\SQLEXPRESS/myDbs?driver=SQL+Server&trusted_connection=yes')

# 分块导入数据
chunk_size = 10000
for chunk in pd.read_csv(r"C:\Users\***\***\file.csv", index_col=0, chunksize=chunk_size):
    chunk.to_sql(
        name='Divys',
        con=engine,
        if_exists='append',
        index=False,
        method='multi'  # 启用批量插入
    )

三、拆分CSV文件（可选） #

如果内存压力仍然较大，可以先将大CSV拆分为多个小文件，再分别导入：

import pandas as pd

# 每个拆分文件100万条记录
chunk_size = 1000000
for idx, chunk in enumerate(pd.read_csv(r"C:\Users\***\***\file.csv", index_col=0, chunksize=chunk_size)):
    chunk.to_csv(f"C:\\Users\\***\\***\\file_part_{idx+1}.csv", index=False)

四、SQL Server原生工具（最快方案） #

对于大规模数据，原生工具的导入效率远高于Python：

1. BULK INSERT（SSMS执行SQL） #

直接在SSMS中执行批量插入语句，前提是SQL Server能访问到CSV文件（本地文件需确保权限）：

BULK INSERT Divys
FROM 'C:\Users\***\***\file.csv'
WITH (
    FIRSTROW = 2,          -- 跳过表头行
    FIELDTERMINATOR = ',', -- CSV字段分隔符
    ROWTERMINATOR = '\n',  -- 行分隔符
    TABLOCK,               -- 锁定表提升导入速度
    CODEPAGE = '65001'     -- 若含中文需指定UTF-8编码
);

2. bcp命令行工具 #

使用微软官方的命令行工具，速度极快，适合批量操作：
打开命令提示符执行：

bcp myDbs.dbo.Divys in "C:\Users\***\***\file.csv" -S -hostname-\SQLEXPRESS -T -c -t, -r\n -F2

参数说明：

• -S：指定SQL Server实例名
• -T：使用Windows身份验证
• -c：以字符格式导入
• -t,：指定字段分隔符为逗号
• -r\n：指定行分隔符
• -F2：跳过第一行表头

额外内存优化建议 #

• 读取CSV时用usecols指定仅需导入的列，减少内存占用
• 用parse_dates参数提前解析日期列，避免后续类型转换
• 对重复率高的字符串列指定dtype='category'，降低内存消耗

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