我的第一个Python程序--批量生成Oracle DDL文件

一个月之前, 我带人完成了我的第一个Java项目, 部署在Linux平台上. 二十天前, 我写了几个bash shell脚本(start from scratch).  昨天, 我写好了我的第一个python程序. 有点老树开花的感觉嘛!

稍微评论一下这几个语言:

Java语言有点落后, 语法拖沓. 但社区真强大, 有很多优秀的类库包, 有很好的论坛, 博客文章.

Python语言我喜欢, 写法灵活, 网上讲python上手快, 我倒不这么认为, 我觉得需要有很好的感觉才能领悟和驾驭,  因此注定只能是一种小众语言. 

这个程序是生成Oracle数据库对象的DDL脚本. Python版本为2.7, 使用了cx_Oracle包来访问数据库, 版本为cx_Oracle-5.1,  生成DDL部分是使用了Oracle的存储过程DBMS_METADATA.GET_DDL(). (注: cx_Oracle真不好装, 我在Linux上用源码装成功了, 但在Windows下装好后, 却怎么也用不起来. )

该程序支持命令行参数的形式,  共4个参数:

connection参数, 指定连接字符串, 形式为user/pwd@tns

owner参数[可缺省], 指定数据库对象的Owner, 如缺省, 则输出的Owner为connection中的的User.

argObjectInFile参数[可缺省], 指定数据库对象的名, 每行一个对象名, 如缺省, 则输出指定Owner下的所有的对象

argOutputPath参数, 指定生成DDL文件的存放路径. 

代码:

#/usr/bin/env python

'''
Created on 2011-9-23

@author: Harry Chinese
'''

import cx_Oracle
import sys


class OraDdlGenerator:

def __init__(self):
self.argConnect=""
self.argOwner=""
self.argObjectInFile=""
self.argOutputPath=""
self.objectList=[]


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 as 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.OBJECT_NAME LIKE :ObjectName
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)
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:
connection = cx_Oracle.Connection(self.argConnect)
generator.normalizeArguments(connection)
connection.outputtypehandler = self.OutputTypeHandler
cursor = connection.cursor()
sql=self.composeSQL()

if (self.argObjectInFile==""):
cursor.execute(sql, {'Owner':self.argOwner, 'ObjectName':"%"})
self.saveRowset(cursor)
else:
for object in self.objectList:
cursor.execute(sql, {'Owner':self.argOwner, 'ObjectName':object})
self.saveRowset(cursor)
except Exception as ex:
print ex
finally:
if (cursor!=None):
cursor.close()
if (connection != None):
connection.close()




def OutputTypeHandler(self, cursor, name, defaultType, size, precision, scale):
if defaultType == cx_Oracle.CLOB:
return cursor.var(cx_Oracle.LONG_STRING, 900000, cursor.arraysize)
if defaultType == cx_Oracle.BLOB:
return cursor.var(cx_Oracle.LONG_BINARY, 900000, cursor.arraysize)



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="""OraDdlGenerator is to generate DDL script file for Oracle Object.
Usage:
OraDdlGenerator connection=user/pwd@tns 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=edwuser/edwuser@sdbtest", "owner=s", "objects_in_file=/home/edwuser/1.txt", "output_path=/home/edwuser/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.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=OraDdlGenerator()
parsed=False
try:
generator.parseArguments()
parsed=True
except Exception as ex:
print("Argument parse failed.")
generator.printUsage()
if(parsed):
generator.saveDDL()
print("............done")