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

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

一个月之前, 我带人完成了我的第一个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=pathRemark: 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")

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。