MERGE INTO ADMIN.JHJ_SBJLXX t1
USING (select count(*) c from JHJ_SBJLXX t2 where t2.pjh='9B35882F2F' ) x ON (x.c > 0)
when matched then
update set t1.ckh='4013501' , t1.jhsj=to_date('2019-05-14 16:41:37', 'yyyy-mm-dd hh24:mi:ss') , t1.YWBJSJ='' , t1.SFJH='1' where t1.pjh='9B35882F2F'
when not matched then
insert (t1.sbmc, t1.sbbh, t1.xzqh, t1.xzqhmc, t1.sxbm, t1.sxmc, t1.pjh, t1.jhrxm, t1.jhrzjhm, t1.jhrlxdh, t1.qhsj, t1.sfjh, t1.jhsj, t1.ckh, t1.bmmc, t1.ywlx, t1.ywbjsj) VALUES ('2楼排号机', '2L01', '420105007000', '湖北省武汉市汉阳区五里墩街办事处', '99', '不动产解押', '9B35882F2F', '李航', '412823199212103659', '',to_date( '2019-05-14 16:41:00', 'yyyy-mm-dd hh24:mi:ss') , '1', to_date('2019-05-14 16:41:37', 'yyyy-mm-dd hh24:mi:ss'), '4013501', '公安局', '0','')下面是我的代码,用excutemany进行批量插入,总共1000条数据,有时候用了10分钟,有时30分钟都完成不了,这个是为什么啊。 jh_insert_sql="""MERGE INTO ADMIN.JHJ_SBJLXX t1
USING (select count(*) c from ADMIN.JHJ_SBJLXX t2 where t2.pjh=:pjh1 and t2.xzqh=:xzqh1) x ON (x.c > 0)
when matched then
update set t1.ckh=:ckh1 , t1.jhsj=to_date(:jhsj1, 'yyyy-mm-dd hh24:mi:ss') , t1.YWBJSJ=:ywbjsj1 , t1.SFJH=:sfjh1 where t1.pjh=:pjh
when not matched then
insert (t1.sbmc, t1.sbbh, t1.xzqh, t1.xzqhmc, t1.sxbm, t1.sxmc, t1.pjh, t1.jhrxm, t1.jhrzjhm, t1.jhrlxdh, t1.qhsj, t1.sfjh, t1.jhsj, t1.ckh, t1.bmmc, t1.ywlx, t1.ywbjsj) VALUES (:sbmc, :sbbh, :xzqh, :xzqhmc, :sxbm, :sxmc, :pjh, :jhrxm, :jhrzjhm, :jhrlxdh,to_date(:qhsj, 'yyyy-mm-dd hh24:mi:ss') , :sfjh, to_date(:jhsj, 'yyyy-mm-dd hh24:mi:ss'), :ckh, :bmmc, :ywlx,:ywbjsj)"""
count += 1
# 每次插入300条数据
if count >= 30:
try:
hyzw_mysql_cursor.executemany(jh_insert_sql, insert_jh_list)
hyzw_mysql_conn.commit()
print('执行'+str(3*num)+'条')
except Exception as e:
print(e)
print(insert_jh_list)
insert_jh_list = []
count = 0
num+=1
if count > 0:
hyzw_mysql_cursor.executemany(jh_insert_sql, insert_jh_list)
hyzw_mysql_conn.commit()
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货