to_sql pyodbc计数字段不正确或语法错误

Question

我正在从api网站下载Json数据，并使用sqlalchemy，pyodbc和pandas的to_sql函数将该数据插入到MSSQL服务器中。

我最多可以下载10000行，但是必须将块大小限制为10，否则会出现以下错误：

DBAPIError：（pyodbc.Error）（“ 07002”，“ [07002] [Microsoft] [SQL
Server本机客户端11.0]
COUNT字段不正确或语法错误（0）（SQLExecDirectW）”）[SQL：’插入到[TEMP_production_entity_details]

大约有5亿行可供下载，它正以这种速度爬行。有任何解决方法的建议吗？

谢谢，

Answer 1

更新：

熊猫0.23.1已恢复0.23.0中引入的问题更改。但是，原始性能的最佳解决方案仍然是CSV->bcp方法，如下所述。

（原始答案）

在熊猫版本0.23.0之前，to_sql将为DataTable中的每一行生成一个单独的INSERT：

exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2)',
    0,N'row000'
exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2)',
    1,N'row001'
exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2)',
    2,N'row002'

大概是为了提高性能，pandas 0.23.0现在会生成一个表值构造函数，以便在每次调用时插入多行

exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6),@P3 int,@P4 nvarchar(6),@P5 int,@P6 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2), (@P3, @P4), (@P5, @P6)',
    0,N'row000',1,N'row001',2,N'row002'

问题在于，SQL
Server存储过程（包括类似的系统存储过程sp_prepexec）仅限于2100个参数，因此，如果DataFrame具有100列，则to_sql一次只能插入约20行。

我们可以chunksize使用

# df is an existing DataFrame
#
# limit based on sp_prepexec parameter count
tsql_chunksize = 2097 // len(df.columns)
# cap at 1000 (limit for number of rows inserted by table-value constructor)
tsql_chunksize = 1000 if tsql_chunksize > 1000 else tsql_chunksize
#
df.to_sql('tablename', engine, if_exists='replace', index=False, chunksize=tsql_chunksize)

但是，最快的方法仍然可能是：

• 将DataFrame转储到CSV文件（或类似文件），然后

• 让Python调用SQL Serverbcp实用程序将文件上传到表中。