python：df.to_sql和fast_executemany = true仍然不够快，对于通过VPN连接的SQL Server中的850k记录

Question

我正在尝试使用Python编写一个ETL过程，以将数据从本地计算机更新为SQL Server。我正在处理的文件有46列和约850K记录。我在没有fast_executemany = true的情况下尝试了，但这花了近3个小时。我在线阅读并添加了fast_executemany = true，并用两者的10k记录进行了测试。 fast_executemany = true仅在10k记录中仅提高10-15秒。我在网上读到人们已经使用了这一点，只需要5分钟才能插入近一百万行。

以下是我的python脚本。

print('Time Starts: ', datetime.now().strftime("%m/%d/%Y %H:%M:%S"))



# Variable names
Server='server'
Database='db'
Driver='SQL Server Native Client 11.0'
Database_Con = 'mssql+pyodbc://@' + Server + '/' + Database + '?trusted_connection=yes&driver=' + Driver


# Creating an engine connection 
engine=create_engine(Database_Con, fast_executemany=True).connect()


test.to_sql('TEST123',
                  engine,
                  schema='dbo',
                  chunksize=math.floor(2000/len(test.columns)), 
                  #chunksize=1000,
                  method='multi',
                  if_exists='append',
                  index=False
                  )

print('Time Ends: ', datetime.now().strftime("%m/%d/%Y %H:%M:%S"))

我在这里缺少任何东西，还是有任何更快的方法可以做到这一点。由于访问问题，我无法使用散装插入物。

编辑

我看到了文章在说fast_executemany默认情况下，fast_executemany使用多插入作为一种方法，在上面的一节中，我还提到了块大小和方法='multi'，这就是为什么它无法正常工作的原因。我删除了块和方法后，插入850K记录的时间从3小时下降到25分钟左右。

以下是我的代码。

print('Time Starts: ', datetime.now().strftime("%m/%d/%Y %H:%M:%S"))



# Variable names
Server='server'
Database='db'
Driver='SQL Server Native Client 11.0'
Database_Con = 'mssql+pyodbc://@' + Server + '/' + Database + '?trusted_connection=yes&driver=' + Driver


# Creating an engine connection 
engine=create_engine(Database_Con, fast_executemany=True).connect()


test.to_sql('TEST123',
                  engine,
                  schema='dbo',
                  if_exists='append',
                  index=False
                  )

print('Time Ends: ', datetime.now().strftime("%m/%d/%Y %H:%M:%S"))

尽管这比以前好得多，但我觉得必须更快一些。我认为这不应该花费超过5分钟。

Answer 1

我在这里发布了我的发现作为答案。我必须从test.to_sql中删除块和方法，因为fast_executemany根据我阅读的文章将方法设置为默认情况下的多插入方法。它的时间从3小时降低到或多或少。

对于戈德·汤普森（Gord Thompson）的观点来说，因为我正在使用VPN连接连接到公司的网络，这就是为什么它相对较慢，但20分钟仍然不错。

print('Time Starts: ', datetime.now().strftime("%m/%d/%Y %H:%M:%S"))

# Variable names
Server='server'
Database='db'
Driver='SQL Server Native Client 11.0'
Database_Con = 'mssql+pyodbc://@' + Server + '/' + Database + '?trusted_connection=yes&driver=' + Driver


# Creating an engine connection 
engine=create_engine(Database_Con, fast_executemany=True).connect()


test.to_sql('TEST123',
                  engine,
                  schema='dbo',
                  if_exists='append',
                  index=False
                  )

print('Time Ends: ', datetime.now().strftime("%m/%d/%Y %H:%M:%S"))