Oracle表结构转换SqlSERVER表结构&…

在审计工作中,有时需要将Oracle的表结构修改后再SqlSERVER中创建表结构,然后将数据导入到SqlSERVER中,在修改表结构的过程中方法狠多.手工修改,最蠢的方法,或者用工具UE批量修改,还凑合的方法,最方便的方法就是用脚本唰的一下,全部改好：

vi parameter.ini
#sysType---1:Linux;others:windows
sysType=1
#readFiledir is OldFile
readFiledir=/root/tmpmycnf/dbquery/yoon/oldfile
#writeFiledir is NewFile
writeFiledir=/root/tmpmycnf/dbquery/yoon/newfile



vi sql.py

#! /usr/bin/python
# -*- coding: utf-8 -*-
#Author:Yoon
#Version:1.0
#Date:2014-07-07

import os
import sys

classApplication(object):
def __init__(self):
self._oldFileDir = ''
self._newFileDir = ''
self._inifilename ='./parameter.ini'
self._start = 'false'

defreadInitFile(self,keyName):
data =open(self._inifilename,'r')
for line in data:
if keyName ==line.split('=')[0].strip():
returnline.split('=')[1].strip()
break
data.close()

defgetNewLine(self,oldLine):
if oldLine.strip().startswith('createtable'):
tablename =oldLine.strip().split('.')[1].strip()
newline = 'create table ' + tablename+ oldLine[-1]
else:
ifoldLine.strip().startswith('('):
newline = oldLine
elifoldLine.startswith(')'):
newline = 'null' + oldLine[-1] +oldLine + oldLine[-1]
self._start = 'false'
else:
strList = oldLine.split('')
newline = ''
for string in strList:
if string == '':
newline += ' '
elifstring.strip().upper().startswith('DEFAULT'):
continue
elif (string.upper() == 'NOT') or(string.upper() == 'NULL'):
continue
elifstring.upper().startswith('VARCHAR2'):
newline += 'varchar(' +str(int(string[string.find('(')+1:string.find(')')])*3) + ')'
elif (string.upper() == 'NUMBER') or(string.upper() == 'DATE') or (string.upper() == 'DATETIME') or(string.upper() == 'TIMESTAMP(6)') or (string.upper() =='INTEGER'):
newline += 'varchar(100)'
elif((string.upper().startswith('NUMBER')) or(string.upper().startswith('DATE')) or(string.upper().startswith('DATETIME')) or(string.upper().startswith('TIMESTAMP(6)')) or(string.upper().startswith('INTEGER'))) and (string[-1] =='\n'):
newline += 'varchar(100)'
elif(string.upper().startswith('NUMBER,')) or(string.upper().startswith('DATE,')) or(string.upper().startswith('DATETIME,')) or(string.upper().startswith('TIMESTAMP(6),')) or((string.upper().startswith('NUMBER(')) and (',' in string)) or((string.upper().startswith('NUMBER(')) and (',' in string)) or(string.upper().startswith('INTEGER,')):
newline += 'varchar(100)null'
elifstring.upper().startswith('NUMBER('):
newline +='varchar(100)'
else:
i = 0
while (1 == 1):
if strList[i] == '':
i += 1
else:
filedName=strList[i]
break
if string == filedName:
newline = string +newline
if ',' in string:
newline += 'null'+string[string.rfind(','):]
return newline

defcreateNewFileFromOldFileList(self):
readFileDir =self.readInitFile('readFiledir')
writeFileDir =self.readInitFile('writeFiledir')
sysType =self.readInitFile('sysType')
if sysType == '1':
sepStr = '/'
else:
sepStr = '\\'
listfile=os.listdir(readFileDir)
for file in listfile:
start = 'false'
writeFile =open(writeFileDir+sepStr+file, 'w')
readData =open(readFileDir+sepStr+file,'r')
for line in readData:
if line.lower().startswith('createtable'):
self._start = 'true'
if self._start=='true':
newLine =self.getNewLine(line)
writeFile.write(newLine)
readData.close()
writeFile.close()

app = Application()
app.createNewFileFromOldFileList()

[root@db01 ~]#
[root@yoon ~]# /usr/bin/python sql.py