python - cx-oracle-executemany()-batch-insert-error-expecting-number
问题描述
我有一个清单
绑定插入:
[(3, 18, '01-10-2021', 'M51078', 'AABCM5192K', '01280', 'AABCM5192K', None, None, 'X95091', 'AZEPK0300N',
'C', 'CO', 'N01477', 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 'N',
'N', 'Y', '05-10-2021', 'SYSTEM', None, None), (4, 18, '01-10-2021', 'M51078', 'AABCM5192K', '01280', 'AABCM5192K', None, None, 'LP1059', 'ASCPC7149Q', 'C', 'CO', 'TEST2', 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0,
123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 123000000.0, 'N', 'N', 'Y', '05-10-2021', 'SYSTEM', None, None), (5, 18, '01-10-2021', None, 'AABCM5192K', None, None, 'ILFSLOU01', 'AAACL7361E', None, 'AAACG7034K', 'C', 'CO', 'N01582', 0.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0,
1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 'N', 'N', 'Y', '05-10-2021', 'SYSTEM', None, None), (6, 18, '01-10-2021', None, 'AABCM5192K', None, None, 'ILFSSHA01', 'AABCS3644N', None, 'ADPPA5335E', 'C', 'CO', 'N01490', 0.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0,
1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 'N', 'N', 'Y', '05-10-2021', 'SYSTEM', None, None)]
当列表仅包含一个元组时,它成功插入数据库我尝试使用 bind_insert 中的所有单个元组,但是当所有 3 个元组都存在时,它在 executemany() 中给出 Expecting number 错误
我的代码:
for b in bind_insert :
for index, value in enumerate(b):
if isinstance(value, float) and math.isnan(value):
b[index] = None
elif isinstance(value, type(pd.NaT)):
b[index] = None
print("before")
start_time = time.time()
cursor.setinputsizes(None, 25)
cursor.executemany("insert into "+config.NcsOwner+".COLL_CLIENT_SUBMISSION_DTLS values (:1,:2,to_date(:3,'DD-MM-YYYY'),:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,to_date(:60,'DD-MM-YYYY'),:61,to_date(:62,'DD-MM-YYYY'),:63)", bind_insert)
connection.commit()
错误:
Exception has occurred: TypeError
expecting number
File "D:\Alok\CR06645_PythonCode\insertdf.py", line 42, in InsertIntoDB
cursor.executemany("insert into "+config.NcsOwner+".COLL_CLIENT_SUBMISSION_DTLS values (:1,:2,to_date(:3,'DD-MM-YYYY'),:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,to_date(:60,'DD-MM-YYYY'),:61,to_date(:62,'DD-MM-YYYY'),:63)", bind_insert)
File "D:\Alok\CR06645_PythonCode\CR6645script.py", line 1073, in CollateralValidation
bVal=insertdf.InsertIntoDB(FinalDataframe,fileCSVName)
File "D:\Alok\CR06645_PythonCode\CR6645script.py", line 34, in readCSV
abc=CollateralValidation(dfFileNames)
解决方案
您可以更喜欢使用
date
datetime
模块类型而不是TO_DATE
DB层的转换- 参数化查询字符串而不是连接一个(主要是更安全)
- 一个循环,以便动态获取
bind_insert
list(:1, :2, :3, ... :63
),而不是通过使用 tuple(bind_insert[0]
)的第一个元素来显式编写它们
如
bind_insert=[
(3,18,'TEST1',datetime.date(2021,10,1),'M51078'...),
(4,18,'TEST2',datetime.date(2021,10,1),'M51078'...),
.....
]
val=""
j=0
for i in bind_insert[0]:
j+=1
val+=':'.join(' '+str(j)) + ','
values=val.rstrip(",")
table = config.NcsOwner+".coll_client_submission_dtls"
my_query = f"INSERT INTO {table} VALUES({values})"
cursor.executemany(my_query,bind_insert)
推荐阅读
- sql - sql server 2016 比 2008 慢
- c# - 变量用动态值初始化一次
- amazon-web-services - AWS 无法连接到 Java springboot API 端点
- sql-server - Hibernate HQL 查询错误:将 varchar 数据类型转换为 smalldatetime
- java - 通过 Netbeans 强制提交
- ajax - 十月 CMS - API 生成器 - 如何在数据库中创建和更新数据
- java - java.io.NotSerializableException:-> 没有为类 java.sql.Timestamp 找到反序列化的构造函数
- python - 试图将摩尔斯电码翻译成英文
- javascript - 仅显示一定数量的 wordpress 评论
- matlab - 如何在matlab中截断字符串