ORACLE+PYTHON實戰：複製A表資料到B表

標籤：utf8   提升   cut   run   chinese   pre   string   lin   意義   

      最近在學習python ，看到了pythod的oracle，不僅可以一次fetch多條，也可以一次insert多條，想寫一個複製A表資料到B表的程式來看看實際效率能不能提高。寫完發現，非常驚豔！效率提升了近一倍！ 當然可能會認為這個沒有實際意義，其實不然。

     從A表複製資料到B表有很多中方法，一般直接insert即可:

　　insert into tableA select * from tableB ;

   但是當資料量非常大時，到達上億水準的時候，這樣做就很鬱悶了，因為本身會跑很慢，又看不到進度，偶爾還會被資料庫因為復原段不夠而悲劇。

    所以，這種時候，我一般是用遊標來做的：

declare  v_num number ; begin  v_num:=0 ;  for v_cur in (select t.prod_inst_id , t.acc_num , t.user_name from cust30.prod_inst t where rownum <50000 ) loop   insert into test_prod_inst values (v_cur.prod_inst_id , v_cur.acc_num , v_cur.user_name) ;  v_num:=v_num+1 ;   if mod(v_num,50000) = 0 then     commit ;   end if ;   end loop ;end ;

　　

　(也可以用fetch一次多條的方式：bulk  但是實際測試實際快不了多少)。現在的想法就是拿python替代這個，實際代碼如下：

     

#!/home/orashell/python27/bin/python# -*- coding: utf-8 -*-import osimport cx_Oracle#需要設定這個不然插入中文會亂碼os.environ[‘NLS_LANG‘] = ‘SIMPLIFIED CHINESE_CHINA.UTF8‘#目的資料庫trans_to_db = cx_Oracle.connect(‘user/pass#@servicename‘)#來來源資料庫trans_from_db = cx_Oracle.connect(‘user/pass#@servicename‘)#開啟查詢遊標curselect = trans_from_db.cursor()#開啟插入遊標curinsert = trans_to_db.cursor()#根據遊標產生插入的語句 需要根據已經開啟的遊標、目的表名 輸出這樣的#insert into test_prod_inst (PROD_INST_ID,ACC_NUM,USER_NAME) values(:1,:2,:3)#輸入 fromcur 為一個已經開啟的遊標對象#輸入 totable 為目的表名#輸出 returnstr 為產生好的SQLdef getInsertSql( fromcur ,totable ):    #習慣這樣做 :) 先產生一個字串模板 再替換    returnstr = ‘insert into ‘+totable+‘ (SELECTSTR) values(INSERTSTR)‘    # 得到遊標的描述 cx_Oracle遊標描述 本質為一個元組（見下） 第一列為欄位名    #[(‘PROD_INST_ID‘, <type ‘cx_Oracle.NUMBER‘>, 17, None, 16, 0, 0), (‘ACC_NUM‘, <type ‘cx_Oracle.STRING‘>, 32, 96, None, None, 0), (‘USER_NAME‘, <type ‘cx_Oracle.STRING‘>, 250, 750, None, None, 1)]    curdesc = fromcur.description    selectstr = ‘‘    insertstr = ‘‘    num=0    #拼好字串模板的 SELECTSTR 以及 INSERTSTR 部分    for i in curdesc:        num=num+1        selectstr=selectstr+i[0]+‘,‘        insertstr=insertstr+‘:‘+str(num)+‘,‘    #去掉最後一個‘,‘    selectstr=selectstr[0:len(selectstr) - 1]    insertstr=insertstr[0:len(insertstr) - 1]    #替換    returnstr=returnstr.replace(‘SELECTSTR‘,selectstr );    returnstr=returnstr.replace(‘INSERTSTR‘,insertstr );    return  returnstr#實際執行的函數def runmain():    #用一個SQL產生遊標    curselect.execute(‘select  t.prod_inst_id , t.acc_num , t.user_name  from cust30.prod_inst t where rownum<10000 ‘)    #得到插入遊標的    manyinserstr=getInsertSql(curselect, ‘test_prod_inst‘)    #插入遊標 prepare    curinsert.prepare(manyinserstr)    while True:        #fetch cx_Oracle fetch 當fetch 一條的時候 得到的是一行資料的元組 但是如果是多行 得到的是一個list        #所以 fetchone的結果不轉換 不能使用executemany        x=curselect.fetchmany(5000)        #插入        curinsert.executemany(None, x)        #提交        trans_to_db.commit()        #判斷退出        if len(x)==0:            break#執行if __name__ == ‘__main__‘:    runmain()    trans_from_db.close    trans_to_db.close

　　

 

       本以為用這個會慢一些，因為實際上，這批資料庫是過了網路的（資料-本機-資料庫），而使用PLSQL是沒有使用網路。但是用這個插了5000萬資料，結果卻不是這樣，用了64秒，而用前文的第一種方式用了113秒，差不多是一倍的效率，這還是一個資料庫兩個表的複製，如果是兩個資料庫，跨dblink會更加明顯。

原因我猜測是這麼兩個：

       A：在實際insert的時候，cx_Oralce拆成了多個線程去處理。如果考慮實際在特別大資料量的時候，plsql這邊也可以分為多個模處理，效率最終可能會卡在IO上。

       B：oracle的記憶體管理更加複雜，會比python這種相當於手動管理的方式，消耗的資源會更多。

       期待大神能夠解惑。

 

    

 

ORACLE+PYTHON實戰：複製A表資料到B表