python如何复制oracle数据_ORACLE+PYTHON实战：复制A表数据到B表

最近在学习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 os

import 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 为生成好的SQL

def getInsertSql( fromcur ,totable ):

#习惯这样做 :) 先生成一个字符串模板 再替换

returnstr = 'insert into '+totable+' (SELECTSTR) values(INSERTSTR)'

# 得到游标的描述 cx_Oracle游标描述 本质为一个元组(见下) 第一列为字段名

#[('PROD_INST_ID', , 17, None, 16, 0, 0), ('ACC_NUM', , 32, 96, None, None, 0), ('USER_NAME', , 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这种相当于手动管理的方式，消耗的资源会更多。

期待大神能够解惑。