使用Pandas导入2万+行Excel至Redshift过慢，求优化方案（无S3权限）

原代码用iterrows()逐行插入，每一行都发起一次数据库请求，2万行就产生2万次网络交互，这是耗时超7小时的核心原因。以下是几种高效优化方式：

1. 使用executemany批量插入 #

把DataFrame数据转换成元组列表，通过executemany一次性批量执行插入，大幅减少网络请求次数：

import psycopg2
import pandas as pd

# 建立Redshift连接
conn = psycopg2.connect(
    host='your-redshift-host',
    port='your-redshift-port',
    user='your-username',
    password='your-password',
    dbname='your-database'
)

# 读取Excel到DataFrame
df = pd.read_excel(excel_file)

# 将目标列转换为元组列表
data_tuples = list(df[['dfcolumn1', 'dfcolumn2', 'dfcolumn3']].itertuples(index=False, name=None))

cur = conn.cursor()
cur.execute('TRUNCATE TABLE table_name')
insert_query = "INSERT INTO table_name (tablecolumn1, tablecolumn2, tablecolumn3) VALUES (%s, %s, %s)"

# 批量执行插入
cur.executemany(insert_query, data_tuples)

conn.commit()
cur.close()
conn.close()

2. 分批次提交（适配超大数据量） #

如果担心一次性插入内存或数据库压力过大，可以分批次提交，比如每5000行提交一次：

import psycopg2
import pandas as pd

conn = psycopg2.connect(...)
df = pd.read_excel(excel_file)
data_tuples = list(df[['dfcolumn1', 'dfcolumn2', 'dfcolumn3']].itertuples(index=False, name=None))

cur = conn.cursor()
cur.execute('TRUNCATE TABLE table_name')
insert_query = "INSERT INTO table_name (tablecolumn1, tablecolumn2, tablecolumn3) VALUES (%s, %s, %s)"

# 定义批次大小
batch_size = 5000
for i in range(0, len(data_tuples), batch_size):
    batch = data_tuples[i:i+batch_size]
    cur.executemany(insert_query, batch)
    conn.commit()

cur.close()
conn.close()

3. 使用copy_from（性能最优） #

Redshift的COPY命令是官方优化的批量加载方式，即使没有S3权限，也可以通过本地字符串IO对象模拟数据流，用psycopg2的copy_from实现：

import psycopg2
import pandas as pd
from io import StringIO

conn = psycopg2.connect(...)
df = pd.read_excel(excel_file)

# 处理空值：Redshift默认用'\N'表示NULL，需将DataFrame的NaN转换为该标记
df = df.fillna('\\N')

# 将目标列转换为制表符分隔的字符串IO（避免数据冲突可换其他分隔符，如|）
output = StringIO()
df[['dfcolumn1', 'dfcolumn2', 'dfcolumn3']].to_csv(output, sep='\t', index=False, header=False)
output.seek(0)  # 重置IO指针到开头

cur = conn.cursor()
cur.execute('TRUNCATE TABLE table_name')

# 用copy_from批量加载数据
cur.copy_from(
    output,
    'table_name',
    columns=('tablecolumn1', 'tablecolumn2', 'tablecolumn3'),
    sep='\t',
    null='\\N'
)

conn.commit()
cur.close()
conn.close()

关键注意事项 #

• 确保DataFrame目标列的顺序、数据类型和Redshift表定义完全匹配，避免加载报错。
• 若数据中包含分隔符（如制表符），需更换为数据中不存在的字符作为分隔符。

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