使用pyodbc+SQL Server的大容量插入速度慢，无/Nan+解决方案

Question

问题是，试图将数据上传到SQL Server，速度达到每秒122行（17列）。我决定把这个问题和解决方法一起发布在这里，希望有人知道最终的答案

我发现最相关的线索是，但问题明显不同，仍然没有答案： pyodbc - very slow bulk insert speed

这是一个简单的场景，我尝试使用Python将350K行的CSV上传到一个空白的SQL Server表中。在尝试了一种最流行的方法（即将其作为数据帧读取）之后，创建一个fast_executemany=True的sql_alchemy引擎，并使用to_sql（）方法将其存储到数据库中。我得到了122行/秒，这是不可接受的

正如在其他线程中提到的，这种情况在PostgreSQL或Oracle中不会发生，我可以补充说，在MariaDB中也不会发生。因此，我尝试了另一种方法，使用pyodbc cursor.executemany（）查看pandas或sql_alchemy中是否存在错误。同样的速度

下一步是生成合成数据以复制问题并提交错误。。。令我惊讶的是，生成的数据约为每秒8000条记录。世界跆拳道联盟？数据使用的数据类型（显然）与CSV中的数据类型相同

经过数周的尝试，我决定研究一下pydobc本身。在pyodbc-github-dev站点中，我在https://github.com/mkleehammer/pyodbc/wiki/Binding-Parameters找到了一条有趣的信息，特别是在Writing NULL和解决方案和解决方案部分

事实上，CSV第一行的17个字段中有3个被我手动转换为熊猫中的“Nan”或“None”。令我惊讶的是，仅将第一行的有效值替换为None/Nan/NULL，将速度提高到7-8000条记录/秒。请注意，我没有在后续行中更改任何None/Nan，只在第一行中更改

有人知道为什么会这样吗？是否有比将None/Nan替换为有效值更优雅的解决方案

更新：Github页面上似乎有几个相关问题，都指向同一个问题。供参考：https://github.com/mkleehammer/pyodbc/issues/213。从2017年开始，该线程相对较旧，但如何处理None/Nan的问题似乎仍然存在

Answer 1

在与Microsoft SQL Server交谈时，pyodbc中存在一个错误，至少在版本4.0.30之前。总之，SQL Server对不同的字段类型使用不同类型的NULL，pyodbc无法仅从“无”推断出要使用哪个NULL。为了克服这一限制，pyodbc实现了两种方法：

• 允许使用.setinputsizes（）方法将类型和大小传递给游标，或
• 绑定基于找到的第一个非None值的类型

默认情况下，当在第一行中找到None时，参数将绑定到BINARY。每次为同一字段找到不同的类型时，它都会重新检测并尝试重新绑定，但在第一次绑定后的每一行都会这样做，从而导致性能下降

使用.setinputsizes（）方法将字段类型传递给pyodbc.cursor应该完全可以避免此问题，但现在当.setinputsizes（）在第一行中找到“无”时，它将被忽略

pyodbc团队已经意识到这个问题，并将在未来的版本中进行修复。有关此错误的详细信息，请访问https://github.com/mkleehammer/pyodbc/issues/741

目前，唯一有效的解决方法是创建一个虚拟记录作为第一行（插入完成后删除），其中包含该类型的代表性值，以便pyodbc可以正确绑定正确的类型

这个问题会影响所有使用pyodbc的包，包括sqlalchemy，并间接影响pandas