批量生成Oracle DDL文件程序(Jython+zxJDBC版)
之前写了一个批量生成Oracle DDL文件的Python程序(链接 http://www.cnblogs.com/harrychinese/archive/2011/10/12/My_First_Python_App_To_Generate_Oracle_DDL.html), 是CPython+cx_Oracle, 我的Windows一直没有装好cx_Oracle, 每次使用这个程序, 都需要登录到Linux上, 很不方便.
今天用Jython2.5.2+zxJDBC重新实现了, zxJDBC是Jython标准库, 装好Jython就有了. 之前那个版本是基于Python2.7, 因为Jython是2.5.2, 需要简单修改点语法, 另外zxJDBC不支持user/pwd@tns的连接写法, 所以对connection这块也要修改一下.
代码:
#JyOraDdlGenerator.py
'''
Created on 2011-11-10
@author: Harry
OraDdlGenerator Jython edition, using zxJDBC
'''
from __future__ import with_statement
from com.ziclix.python.sql import zxJDBC
import sys
class JyOraDdlGenerator:
def __init__(self):
self.argConnect=""
self.argOwner=""
self.argObjectInFile=""
self.argOutputPath=""
self.objectList=[]
self.connectusername=""
self.connectpassword=""
self.connectserverip=""
self.connectserverport=""
self.connectservicename=""
def getObjectList(self):
objectList=[]
try:
with open(self.argObjectInFile, "r") as text_file:
for row in text_file:
objectList.append(row.strip().upper())
except Exception , ex:
print(ex)
return objectList
def composeSQL(self):
sql="""SELECT DBMS_METADATA.GET_DDL(AO.OBJECT_TYPE , AO.OBJECT_NAME, AO.OWNER) DDL_DEF, AO.OWNER||'.'||AO.OBJECT_NAME FULL_OBJECT_NAME FROM ALL_OBJECTS AO
WHERE 1=1
AND AO.STATUS='VALID'
--AND AO.OWNER like :Owner
AND AO.OWNER like '%s'
--AND AO.OBJECT_NAME LIKE :ObjectName
AND AO.OBJECT_NAME LIKE '%s'
AND AO.OWNER NOT IN --to filter out the system schema
(
'PUBLIC'
,'SYSTEM'
,'SYS'
,'EXFSYS'
,'WMSYS'
,'DBSNMP'
,'OUTLN'
,'ORACLE_OCM'
,'ORAAUD'
)
AND AO.OBJECT_TYPE IN --DBMS_METADATA.GET_DDL() does not support PACKAGE BODY
(
''
,'PROCEDURE'
,'TABLE'
,'INDEX'
,'TYPE'
,'VIEW'
,'FUNCTION'
--,'PACKAGE BODY'
,'PACKAGE'
,'SEQUENCE'
)
"""
return sql
def saveDdlToFile(self, fullObjectName, DdlStatement):
#fullFileName="{path}//{fullObjectName}.sql".format(path=self.argOutputPath,fullObjectName=fullObjectName)
fullFileName="%s//%s.sql"%(self.argOutputPath,fullObjectName)
with open(fullFileName, "w") as text_file:
text_file.write(DdlStatement)
def saveRowset(self, cursor):
rows=cursor.fetchall()
for DDL_DEF, FULL_OBJECT_NAME in rows:
#print(DDL_DEF)
self.saveDdlToFile(FULL_OBJECT_NAME, DDL_DEF)
def saveDDL(self):
try:
driver = "oracle.jdbc.driver.OracleDriver"
jdbc_url= "jdbc:oracle:thin:@"+self.connectserverip+":"+self.connectserverport+":"+self.connectservicename
connection = zxJDBC.connect(jdbc_url, self.connectusername, self.connectpassword, driver)
generator.normalizeArguments(connection)
cursor = connection.cursor()
sql=self.composeSQL()
if (self.argObjectInFile==""):
sql2=sql%(self.argOwner,"%")
cursor.execute(sql2)
#cursor.execute(sql, {'Owner':self.argOwner, 'ObjectName':"%"})
self.saveRowset(cursor)
else:
for object1 in self.objectList:
sql2=sql%(self.argOwner,object1)
cursor.execute(sql2)
#cursor.execute(sql, {'Owner':self.argOwner, 'ObjectName':object1})
self.saveRowset(cursor)
except Exception , ex:
print ex
finally:
if (cursor!=None):
cursor.close()
if (connection != None):
connection.close()
def normalizeArguments(self, connection):
if (self.argOwner=="*"):
self.argOwner="%"
elif (self.argOwner=="") and (connection!=None):
self.argOwner=connection.username
if (self.argObjectInFile!=""):
self.objectList= self.getObjectList()
self.argOwner=self.argOwner.upper()
def printUsage(self):
usage="""JyOraDdlGenerator is to generate DDL script file for Oracle Object.
Usage:
JyOraDdlGenerator connection=user/pwd@server_ip:server_port/service_name owner=ownerName objects_in_file=in_file output_path=path
Remark:
1. If owner=*, it means it will this utility will export objects under all users schema
2. If owner option omitted, it means owner=connection.user
3. If objects_in_file option omitted, it means this utility will export all objects under the owner schema"""
print(usage)
def parseArguments(self):
#sys.argv = ["myscript.py", "connection=user1/pwd1@10.10.141.12:1521/orcl", "owner=s", "objects_in_file=/home/user1/1.txt", "output_path=/home/user1/output"]
cmdln_args=sys.argv[1:]
#print(cmdln_args)
argKeyValues=dict([arg.split("=") for arg in cmdln_args])
"""
for arg in argKeyValues.iteritems():
print(arg)
"""
self.argConnect=argKeyValues["connection"]
self.connectusername=self.argConnect[:self.argConnect.index("/")]
self.connectpassword= self.argConnect[self.argConnect.index("/")+1:self.argConnect.index("@")]
self.connectserverip=self.argConnect[self.argConnect.index("@")+1:self.argConnect.index(":")]
self.connectserverport=self.argConnect[self.argConnect.index(":")+1:self.argConnect.rindex("/")]
self.connectservicename=self.argConnect[self.argConnect.rindex("/")+1:]
self.argOutputPath=argKeyValues["output_path"]
if (argKeyValues.has_key("owner")):
self.argOwner=argKeyValues["owner"]
if (argKeyValues.has_key("objects_in_file")):
self.argObjectInFile=argKeyValues["objects_in_file"]
if __name__=="__main__":
generator=JyOraDdlGenerator()
parsed=False
try:
generator.parseArguments()
parsed=True
except Exception , ex:
print("Argument parse failed.")
generator.printUsage()
if(parsed):
generator.saveDDL()
print("............done")