python - to_sql pyodbc 计数字段不正确或语法错误
问题描述
我正在从 api 网站下载 Json 数据,并使用 sqlalchemy、pyodbc 和 pandas 的 to_sql 函数将该数据插入 MSSQL 服务器。
我最多可以下载 10000 行,但是我必须将块大小限制为 10,否则我会收到以下错误:
DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT 字段不正确或语法错误 (0) (SQLExecDirectW)') [SQL: 'INSERT INTO [TEMP_produce_entity_details]
大约有 5 亿行要下载,它只是以这种速度爬行。关于解决方法的任何建议?
谢谢,
解决方案
在提出这个问题时,pandas 0.23.0 刚刚发布。该版本将默认行为.to_sql()
从调用 DBAPI.executemany()
方法更改为构造表值构造函数 (TVC),通过一次.execute()
调用 INSERT 语句插入多行来提高上传速度。不幸的是,这种方法经常超过 T-SQL 对存储过程的 2100 个参数值的限制,从而导致问题中引用的错误。
此后不久,随后发布的 pandasmethod=
为.to_sql()
. 默认 - method=None
- 恢复了之前的 using 行为.executemany()
,而指定method="multi"
将告诉.to_sql()
使用较新的 TVC 方法。
大约在同一时间,SQLAlchemy 1.3 发布,它添加了一个fast_executemany=True
参数,create_engine()
使用 Microsoft 的 SQL Server ODBC 驱动程序大大提高了上传速度。通过这种增强,method=None
证明至少与method="multi"
避免 2100 参数限制时一样快。
因此,对于当前版本的 pandas、SQLAlchemy 和 pyodbc,使用.to_sql()
Microsoft 的 SQL Server ODBC 驱动程序的最佳方法是使用fast_executemany=True
和默认行为.to_sql()
,即
connection_uri = (
"mssql+pyodbc://scott:tiger^5HHH@192.168.0.199/db_name"
"?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = create_engine(connection_uri, fast_executemany=True)
df.to_sql("table_name", engine, index=False, if_exists="append")
对于在 Windows、macOS 和 Microsoft 支持其 ODBC 驱动程序的 Linux 变体上运行的应用程序,这是推荐的方法。如果您需要使用 FreeTDS ODBC,则.to_sql()
可以使用 和 调用,method="multi"
如下chunksize=
所述。
(原答案)
在 pandas 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, index=False, if_exists='replace',
method='multi', chunksize=tsql_chunksize)
但是,最快的方法仍然可能是:
将 DataFrame 转储到 CSV 文件(或类似文件),然后
让 Python 调用 SQL Server
bcp
实用程序将该文件上传到表中。
推荐阅读
- r - 将 ebay Feed API 与 R 一起使用
- r - 如何仅在 ggplot2 图的一侧添加边框?
- python-3.x - KeyError:设置索引时“列中都没有['minute']”
- java - 当我完成活动以获得结果时调用 OnCreate 和 onActivityResult
- c# - 对象的深拷贝
- python - 如何将不同年份的月度价格回报合并到一个数据框中?
- reactjs - 在 ReactHook 中使用 Math.random
- php - 使用 Varnish 时修改路由和 url
- laravel - 使用行数据并将其插入 Laravel 中的另一个表中
- javascript - 尝试传递 HTML 代码和一些变量时不需要的逗号