需求背景:
从一张大表中取记录更新另一张大表的的字段,如采用oracle直接update ,好久都没有更新完成的。
处理:
采用python,主线程游标查询原始表,开启线程从游标中读取记录,批量更新目标大表的字段。
#!/usr/bin/python
#coding: utf-8
import cx_Oracle as dbdrive
import os,Queue
from threading import Lock,Thread
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
from ultity.oraclehelper import DbHelper
#使用时只需要配置这两个sql
selectsql="select res10,serv_id from tmp_serv_gridflag "
updatesql="update tmp_serv_gridflag1 set res10=:1 where serv_id=:2"
con=dbdrive.connect("user","passwd",'IP:1521/orcl')
cur=con.cursor()
cur.execute(selectsql)
lock=Lock()
def update():
o=DbHelper()
while True:
lock.acquire()
rlt=cur.fetchmany(800)
lock.release()
if len(rlt)==0:
break
#sql="insert into tmp_serv_gridflag1 values(:1,:2)"
o.run_batch(updatesql, rlt)
for i in range(10):
t=Thread(target=update)
t.start()