使用Pandas to_sql向Google Cloud SQL插入数据速度极慢求助

以下是针对该问题的具体解决思路：

• 开启批量插入模式
  pandas的to_sql默认会逐行执行INSERT语句，这会产生大量网络往返请求，在Cloud SQL这类远程数据库场景下延迟极高。只需添加method='multi'参数，让pandas把多行数据拼成一个批量INSERT语句：

  df.to_sql('consumption', e, if_exists='append', index=False, method='multi')
  

  也可以配合chunksize参数控制每次批量插入的行数，比如chunksize=100，进一步优化性能。

• 优化SQLAlchemy连接池配置
  当前引擎创建逻辑中，每次获取连接都通过Connector新建连接，频繁的连接创建/销毁会带来额外开销。给create_engine添加连接池参数，复用已有连接：

  return sqlalchemy.create_engine(
      "postgresql+pg8000://",
      creator=getconn,
      pool_size=5,  # 保持的空闲连接数
      max_overflow=10,  # 允许临时扩容的连接数
      pool_recycle=3600  # 定期回收连接避免失效
  )
  

• 替换为性能更好的数据库驱动
  pg8000是纯Python实现的Postgres驱动，性能远不如基于C扩展的psycopg2。可以切换到psycopg2：

  1. 安装依赖：pip install psycopg2-binary
  2. 修改引擎URL和连接逻辑：

     def getconn() -> psycopg2.extensions.connection:
         conn = connector.connect(
             instance_connection_name,
             "psycopg2",
             user=db_user,
             password=db_pass,
             db=db_name,
             ip_type=ip_type,
         )
         return conn
     
     return sqlalchemy.create_engine(
         "postgresql+psycopg2://",
         creator=getconn,
         # 加上连接池配置
     )
     

• 切换到私有网络连接
  如果你的应用部署在GCP同VPC内，切换到PRIVATE IP连接可以避免公网延迟，进一步提升数据传输速度。只需确保环境变量PRIVATE_IP设为True即可。

• 手动实现批量插入（极端情况）
  如果上述方法仍不生效，可以手动构造批量INSERT语句，直接通过SQLAlchemy执行：

  from sqlalchemy import text
  
  # 生成字段列表（替换为你的实际列名）
  columns = ", ".join(df.columns)
  # 生成占位符
  placeholders = ", ".join([f":{col}" for col in df.columns])
  insert_stmt = text(f"INSERT INTO consumption ({columns}) VALUES ({placeholders})")
  
  with e.begin() as conn:
      # 将DataFrame转为字典列表，适配参数绑定
      data = df.to_dict("records")
      conn.execute(insert_stmt, data)
  

补充说明：你用psql通过CSV插入速度快，是因为psql使用了Postgres的COPY命令，这是专门为批量数据导入设计的高效机制，而to_sql默认的逐行INSERT无法与之相比。通过上述批量插入优化，可以大幅缩小两者的性能差距。

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