python oracle数据库脚本导出

zhbvictor 2011-05-08

import os,sys

importcx_Oracle

defsearch(sqlstr,**argv):

cursor.execute(sqlstr,argv)

returncursor.fetchall()

defgetcols(tabname):

cols=search('selectcolumn_namefromuser_tab_columnswheretable_name=:V1orderbycolumn_id',V1=tabname)

s=''

forcolincols:

s=s+','+col[0]

returns[1:]

if__name__=='__main__':

ifsys.argv.__len__()!=3:

print"""PleaseInputConnectArgumentsAndSQLExportDirectory.\nEx:oracle_export.pyaa/bb@testdbe:\sqlfile"""

exit(-1)

desdir=sys.argv[2].

ifnotos.path.isdir(desdir):

print"TargetdirectoryisnotExists."

exit(-3)

try:

conn=cx_Oracle.Connection(sys.argv[1].split('/')[0],sys.argv[1].split('/')[1].split('@')[0],sys.argv[1].split('@')[-1])

exceptcx_Oracle.DatabaseError,strerr:

print"ConnectDataBaseFailure.\n%s"%strerr

exit(-2)

cursor=conn.cursor()

objs=search('selectdistincttype,namefromuser_source')

forobjinobjs:

f=open(os.path.join(desdir,obj[0].replace('','_')+''+obj[1])+'.sql','w')

str=search('selecttextfromuser_sourcewherename=:v1andtype=:v2orderbyline',V1=obj[1],V2=obj[0])

f.write('CREATEORREPLACE\n')

foriinstr:

f.write(i[0])

f.write('/\n')

f.close()

objs=search('selectview_name,textfromuser_views')

forobjinobjs:

f=open(os.path.join(desdir,'VIEW'+obj[0])+'.sql','w')

f.write('CREATEORREPLACE\n')

f.write('VIEW('+getcols(obj[0])+')AS\n')

f.write(obj[1])

f.write('/\n')

f.close()

cursor.close()

conn.close()

相关推荐