python - 使用 fast_executemany Python pyodbc 加速 pandas 数据帧的插入

Question

我正在尝试将 .csv 文件中包含的数据从我的电脑插入到远程服务器。这些值被插入到包含 3 列的表中，即 Timestamp、Value 和 TimeseriesID。我必须一次插入大约 3000 行，因此我目前正在使用 pyodbc 和 executemany。

到目前为止，我的代码如下所示:

with contextlib.closing(pyodbc.connect(connection_string, autocommit=True)) as conn:
    with contextlib.closing(conn.cursor()) as cursor:
        cursor.fast_executemany = True  # new in pyodbc 4.0.19

            # Innsert values in the DataTable table
            insert_df = df[["Time (UTC)", column]]
            insert_df["id"] = timeseriesID
            insert_df = insert_df[["id", "Time (UTC)", column]]
            sql = "INSERT INTO %s (%s, %s, %s)  VALUES (?, ?, ?)" % (
                sqltbl_datatable, 'TimeseriesId', 'DateTime', 'Value')

            params = [i.tolist() for i in insert_df.values]
            cursor.executemany(sql, params)

当我使用 pyodbc 4.0.19 时，我将选项 fast_executemany 设置为 True，这应该可以加快速度。但是，出于某种原因，当我启用 fast_executemany 选项时，我没有看到任何大的改进。我可以使用任何替代方法来加快文件的插入速度吗？

此外，关于上面显示的代码的性能，我注意到当禁用 autocommit=True 选项时，我包含了 cursor.commit() 命令最后，我的数据导入速度明显加快。有没有我不知道的具体原因？

任何帮助将不胜感激:)

Answer 1

关于您注意到的 cursor.commit() 加速:当您使用 autocommit=True 时，您请求代码为每个事务执行一个数据库事务的插入。这意味着只有在数据库确认数据存储在磁盘上后，代码才会恢复。当您在无数次 INSERT 之后使用 cursor.commit() 时，您实际上是在执行一个数据库事务，并且数据在此期间存储在 RAM 中(它可能被写入磁盘，但不是在您指示数据库完成事务时的所有时间)。

完成事务的过程通常需要更新磁盘上的表、更新索引、刷新日志、同步副本等，这是代价高昂的。这就是为什么您在描述的两种情况之间观察到这种加速的原因。当采用更快的方式时，请注意，在执行 cursor.commit() 之前，您不能 100% 确定数据在数据库中，因此可能需要重新发出查询以防万一错误(任何部分事务都将被回滚)。