在审计工作中,有时需要将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 New File
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
class Application(object):
def __init__(self):
self._oldFileDir = ''
self._newFileDir = ''
self._inifilename = './parameter.ini'
self._start = 'false'
def readInitFile(self,keyName):
data = open(self._inifilename,'r')
for line in data:
if keyName == line.split('=')[0].strip():
return line.split('=')[1].strip()
break
data.close()
def getNewLine(self,oldLine):
if oldLine.strip().startswith('create table'):
tablename = oldLine.strip().split('.')[1].strip()
newline = 'create table ' + tablename + oldLine[-1]
else:
if oldLine.strip().startswith('('):
newline = oldLine
elif oldLine.startswith(')'):
newline = 'null' + oldLine[-1] + oldLine + oldLine[-1]
self._start = 'false'
else:
strList = oldLine.split(' ')
newline = ''
for string in strList:
if string == '':
newline += ' '
elif string.strip().upper().startswith('DEFAULT'):
continue
elif (string.upper() == 'NOT') or (string.upper() == 'NULL'):
continue
elif string.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'
elif string.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
def createNewFileFromOldFileList(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('create table'):
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