SQLite下pandas to_sql提速时fast_executemany参数报错的适配方案问询

报错根源 #

fast_executemany是pyodbc驱动专属的连接配置项，仅适配SQL Server等基于pyodbc的远端数据库场景。SQLAlchemy连接SQLite时默认使用pysqlite方言，该方言的组件完全不识别这个参数，传入create_engine后就会触发参数校验失败的TypeError。
即便用事件钩子手动给cursor设置cursor.fast_executemany = True的属性也无法生效：pysqlite驱动的cursor对象本身不存在这个属性，设置后要么报AttributeError，要么不会触发任何提速效果。之前参考的pyodbc优化方案和SQLite的底层驱动逻辑完全不兼容，不能直接跨场景套用。

SQLite场景下的to_sql可落地提速方案 #

• 关闭隐式自动事务，减少磁盘刷盘开销
  SQLite默认对每条DML操作都触发一次磁盘fsync落盘，这是写入慢的核心原因。在创建引擎时传入isolation_level=None配置，关闭默认的逐语句自动提交逻辑，让批量插入的操作在一个事务内统一落盘，可大幅降低IO开销。
  对应配置代码：

  disk_engine = create_engine(
      'sqlite:///2021_database.db',
      isolation_level=None
  )
  

• 调大to_sql的单块写入行数
  pandas的to_sql默认单次仅提交10行数据写入，会产生大量重复的SQL解析、IO调用。根据DataFrame的实际大小，把chunksize参数调整到1000~10000的区间，可显著减少重复交互次数。对常规规模的业务数据，设置chunksize=5000基本能把百万行级数据的写入耗时从分钟级压到秒级。
  示例调用：

  data.to_sql(
      'data',
      disk_engine,
      if_exists='replace',
      chunksize=5000
  )
  

• 通过连接事件临时调整SQLite的IO策略
  对本地测试、非核心生产库的场景，可以在连接建立的钩子中配置SQLite的PRAGMA参数，进一步降低写入时的落盘开销。执行PRAGMA synchronous = OFF可以关闭写入时的强制同步刷盘，执行PRAGMA journal_mode = MEMORY可以把事务日志从磁盘挪到内存，两项配置叠加后提速效果十分明显。
  示例钩子代码：

  from sqlalchemy import event
  
  @event.listens_for(disk_engine, "connect")
  def tune_sqlite_io(dbapi_conn, conn_record):
      cur = dbapi_conn.cursor()
      cur.execute("PRAGMA synchronous = OFF")
      cur.execute("PRAGMA journal_mode = MEMORY")
      cur.close()
  

注意：如果是承载核心业务数据的生产库，不建议开启journal_mode = MEMORY的配置，遇到异常断电时会有数据丢失风险。

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