使用 to_sql 将 Pandas 数据帧中的数据批量插入 Sybase 数据库表失败 [英] Failing bulk insert data from Pandas dataframe into Sybase database table using to_sql
问题描述
我下面的代码的目的是从一个安静的服务中获取数据,对其进行规范化,将其存储在具有必要列的数据帧中,然后最后使用 Pandas 的 to_sql
将其加载到 Sybase 表中.>
错误:
<块引用>文件C:\Program Files\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py",第 467 行,在 do_executemany 中cursor.executemany(语句,参数)sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]','附近的语法不正确.\n (102) (SQLExecDirectW)") [SQL: 'INSERT INTO dbo.contract_test ("CONTRACT_ID", "EXCHANGE_ID", "CURRENCY", "TRADING_CODE") VALUES (?, ?, ?, ?)'] [参数5: (('000/TAIEX', 'TAIEX', 'TWD', 0), ('035420/KORE', 'KORE', 'KRW', 0), ('0TL/LIF', 'LIF', 'NOK', 1), ('100FTSE/LIF', 'LIF', 'GBP', 0), ('101FTSE/LIF', 'LIF', 'GBP', 0), ('10STAT/OM', 'OM', '瑞典克朗', 0), ('10TB/KFX', 'KFX', 'KRW', 0), ('10TBA/KFX', 'KFX', 'KRW', 0) ... 显示 4525 个总绑定参数中的 10 个集 ... ('ZURF/DTB', 'DTB', 'CHF', 0), ('ZX/NYCE', 'NYCE', 'USD', 0))]
<块引用>
进程以退出代码 1 结束
代码:
from sqlalchemy.engine.url import *从 sqlalchemy.connectors.pyodbc 导入 *从 sqlalchemy 导入 create_engine导入 urllib.request 作为请求导入json将熊猫导入为 pd从 pandas.io.json 导入 json_normalize, DataFrameresponse = request.urlopen('http://tfsdscsw5XX/mdsclass/CONTFUTURES--O.json')输出 = response.read()数据=json.loads(输出)df=json_normalize(数据)df1=(df[['CONTRACT_ID','EXCHANGE_ID','CURRENCY','TRADING_CODE']])df2=pd.DataFrame(df1)打印(df2)打印(df2.CONTRACT_ID)连接器 = PyODBCConnector()url = make_url(sybase+pyodbc://myhost/mydatabase?driver=Adaptive Server Enterprise&port=2306")打印(connector.create_connect_args(url))引擎=创建引擎(网址)#这里失败了**df2.to_sql(contract_test",engine,index=False,if_exists=append",schema=dbo")response.close()
数据帧 df2 中的数据样本:
CONTRACT_ID EXCHANGE_ID CURRENCY TRADING_CODE0 0050/TAIEX TAIEX TWD 01 035420/韩国韩国 KRW 02 0TL/LIF LIF NOK 13 100FTSE/LIF LIF 英镑 04 101FTSE/LIF LIF 英镑 0
表 contract_test 定义:
CREATE TABLE contract_test (CONTRACT_ID char(12) 非空,EXCHANGE_ID 字符(12),货币字符(4) 非空,TRADING_CODE smallint)走
请帮忙看看如何解决?我被困在这里了.
外部 SAP ASE (Sybase)dialect 现在是 Sybase 推荐的 SQLAlchemy 方言,如果您使用 SAP ASE ODBC 驱动程序,它确实支持 fast_executemany
.
The purpose of my below code is to get data from a restful service, normalize it, store it in dataframe with necessary columns and then finally load it in Sybase table using Pandas' to_sql
.
Error :
File "C:\Program Files\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 467, in do_executemany cursor.executemany(statement, parameters) sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near ','.\n (102) (SQLExecDirectW)") [SQL: 'INSERT INTO dbo.contract_test ("CONTRACT_ID", "EXCHANGE_ID", "CURRENCY", "TRADING_CODE") VALUES (?, ?, ?, ?)'] [parameters: (('0050/TAIEX', 'TAIEX', 'TWD', 0), ('035420/KORE', 'KORE', 'KRW', 0), ('0TL/LIF', 'LIF', 'NOK', 1), ('100FTSE/LIF', 'LIF', 'GBP', 0), ('101FTSE/LIF', 'LIF', 'GBP', 0), ('10STAT/OM', 'OM', 'SEK', 0), ('10TB/KFX', 'KFX', 'KRW', 0), ('10TBA/KFX', 'KFX', 'KRW', 0) ... displaying 10 of 4525 total bound parameter sets ... ('ZURF/DTB', 'DTB', 'CHF', 0), ('ZX/NYCE', 'NYCE', 'USD', 0))]
Process finished with exit code 1
Code :
from sqlalchemy.engine.url import *
from sqlalchemy.connectors.pyodbc import *
from sqlalchemy import create_engine
import urllib.request as request
import json
import pandas as pd
from pandas.io.json import json_normalize, DataFrame
response = request.urlopen('http://tfsdscsw5XX/mdsclass/CONTFUTURES--O.json')
output=response.read()
data=json.loads(output)
df=json_normalize(data)
df1=(df[['CONTRACT_ID','EXCHANGE_ID','CURRENCY','TRADING_CODE']])
df2=pd.DataFrame(df1)
print(df2)
print(df2.CONTRACT_ID)
connector = PyODBCConnector()
url = make_url("sybase+pyodbc://myhost/mydatabase?driver=Adaptive Server Enterprise&port=2306")
print(connector.create_connect_args(url))
engine=create_engine(url)
#it is failing here**
df2.to_sql("contract_test",engine,index=False,if_exists="append",schema="dbo")
response.close()
Sample of data in dataframe df2 :
CONTRACT_ID EXCHANGE_ID CURRENCY TRADING_CODE
0 0050/TAIEX TAIEX TWD 0
1 035420/KORE KORE KRW 0
2 0TL/LIF LIF NOK 1
3 100FTSE/LIF LIF GBP 0
4 101FTSE/LIF LIF GBP 0
Table contract_test definition :
CREATE TABLE contract_test (
CONTRACT_ID char(12) NOT NULL,
EXCHANGE_ID char(12),
CURRENCY char(4) NOT NULL,
TRADING_CODE smallint
)
GO
Please help as to how can this be resolved ? I am stuck here.
The external SAP ASE (Sybase) dialect is now the recommended SQLAlchemy dialect for Sybase, and it does support fast_executemany
if you use the SAP ASE ODBC driver.
这篇关于使用 to_sql 将 Pandas 数据帧中的数据批量插入 Sybase 数据库表失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!