Pythonを使用してsqlファイルをDBに書き込む
13489 ワード
Python学習第二弾
#coding = UTF-8
import os, sys, time, shutil
class NdbFlush:
def __init__(self):
self._ROOT_PATH_ = None
self._TNS_LIST_ = {}
self._FILE_LIST_ = {} # {SCHEMA:{'TAB':[], 'SEQ':[], 'PKGH':[]}, SCHEMA:{...}, ...}
self._ORA_CFG_FILE_ = None
def _UnInit_(self):
self._ROOT_PATH_ = None
self._TNS_LIST_ = None
self._FILE_LIST_ = None
self._ORA_CFG_FILE_ = None
#######################################
#
#######################################
def GetFilePath(self):
expath = os.getcwd()
if os.path.isdir(expath):
return expath
elif os.path.isfile(expath):
return os.path.dirname(expath)
#######################################
#
#######################################
def GetOraInfo(self):
ora_file = self._ROOT_PATH_ + '/' + self._ORA_CFG_FILE_
tns_list = {}
fh = open(ora_file, 'r')
for ora in fh.readlines():
ora = ora.replace('
', '')
if ora and len(ora) > 5:
ora_list = []
schema = ora.split('/')[0].upper()
ora_list = tns_list.get(schema)
if (ora_list and len(ora_list) > 0):
ora_list.append(ora)
else:
ora_list = [ora]
tns_list[schema] = ora_list
return tns_list
#######################################
# SQL
#######################################
def LoadNdbList(self, _list_):
#print('[LoadNdbList] _list_ =', _list_, '\tlen(_list_)', len(_list_))
lst = {}
try:
for itm in _list_:
#print('\tITM =', itm)
file_split = itm.split('_')
#print('\tfile_split[0] =', file_split[0], '\tfile_split[1] =', file_split[1])
#schema = {}
files = []
schema = lst.get(file_split[1].upper())
#print('\tschema =', schema)
if schema and len(schema) > 0:
files = schema.get(file_split[0])
#print('\t\tfiles =', files)
if files and len(files) > 0:
files.append(itm)
schema[file_split[0]] = files
#print('\t\t\tfiles 1 =', files)
#print('\t\t\tschema =', schema)
else:
files = [itm]
schema[file_split[0]] = files
#print('\t\t\tfiles 2 =', files)
else:
#print('\t', schema, file_split[0], itm)
schema = {}
files =[itm]
schema[file_split[0]] = files
#print('\tschema =', schema)
lst[file_split[1].upper()] = schema
#print('\tLST =', lst)
#print('-' * 80)
except Exception as e:
#print(' ( : [])
')
print(e)
lst = {}
#print(lst)
return lst
#######################################
# SQL
#######################################
def GetSqlFileList(self):
filelist = []
sqlPath = self._ROOT_PATH_ + '/files/'
for file in os.listdir(sqlPath):
if file[-4:].upper() == '.SQL':
if filelist:
filelist.append(file)
else:
filelist = [file]
#print('
', filelist, '
')
print(' [' + sqlPath + '] [%d] SQL ' % len(filelist))
return filelist
#######################################
# SQL DB
#######################################
def SqlFlushDB(self, _runMode_ = 'M'):
# schema loop
file_list = self._FILE_LIST_
ora_list = self._TNS_LIST_
filePath = self._ROOT_PATH_ + '/files/'
for im in file_list:
#print('schema =', im)#, lst.get(im))
# file type loop begin
# SEQ
for xm in file_list.get(im):
#print('\ttype =', xm, '
\tlist =', file_list.get(im).get(xm))
if 'SEQ' == xm.upper():
self.InitBat(im, file_list, ora_list, filePath, xm, _runMode_)
# TAB
for xm in file_list.get(im):
#print('\ttype =', xm, '
\tlist =', file_list.get(im).get(xm))
if 'TAB' == xm.upper():
self.InitBat(im, file_list, ora_list, filePath, xm, _runMode_)
# TAB
for xm in file_list.get(im):
#print('\ttype =', xm, '
\tlist =', file_list.get(im).get(xm))
if 'PKGH' == xm.upper():
self.InitBat(im, file_list, ora_list, filePath, xm, _runMode_)
# not in (TAB, SEQ)
for xm in file_list.get(im):
#print('\ttype =', xm, '
\tlist =', file_list.get(im).get(xm))
if 'TAB' != xm.upper() and 'SEQ' != xm.upper() and 'PKGH' != xm.upper():
self.InitBat(im, file_list, ora_list, filePath, xm, _runMode_)
# file type loop end
def InitBat(self, _schema_, _fileList_, _oraList_, _filePath_, _fileType_, _runMode_ = 'M'):
# file name loop
for file in _fileList_.get(_schema_).get(_fileType_):
#print('\t\t', file)
filePath = _filePath_
sqlpath = filePath + file
fh = open(sqlpath, 'a+')
fh.write('
exit')
fh.close()
tnslst = ''
# ora conf loop
fht = open(sqlpath + '.bat', 'a+')
fht.write('title [' + file + ']
echo off
')
fht.write('cd ' + filePath + '
')
fht.write('cls
')
# tns loop
for tns in _oraList_.get(_schema_):
#print('\t\t\t', tns)
tnslst += tns + ', '
fht.write(('@echo "[ %s ]' %file) + (' -> [ %s]"' %tns) + '
')
fht.write('@echo ...
')
fht.write('sqlplus ' + tns + ' @' + file + ' >> ' + file + '.log
')
fht.flush()
#fht.write('@pause
')
fht.write('@echo FINISH>' + file + '.ok')
fht.write('
exit')
fht.close()
print(('[ %s ]' %file) + (' -> [ %s]' %tnslst))
if _runMode_ == 'M':
self.RunBat(sqlpath, _runMode_)
else:
os.system(r'' + sqlpath + '.bat')
#time.sleep(1)
try:
fhl = open(r'' + sqlpath + '.log', 'r')
lines = fhl.readlines()
lineidx = 0
errFlag = False
fhl.close()
for line in lines:
lineU = line.upper()
if lineU.find('ERROR') >= 0:
errFlag = True
break
lineidx += 1
if errFlag:
print('\t>>[Status] Failed..')
print('\t [ ' + lines[lineidx].replace('
', '') + ' ]')
print('\t [ ' + lines[lineidx + 1].replace('
', '') + ' ]')
else:
print('\t>>[Status] Success..')
if os.path.isfile(r'' + sqlpath + '.log'):
os.remove(r'' + sqlpath + '.log')
shutil.move(sqlpath, sqlpath.replace('/files/', '/finish/'))
except Exception as e:
print('\t :', e)
print('-' * 70)
def RunBat(self, _fileName_, _runMode_):
state = 'START'
while True:
#print(runnext)
if state == 'START':
os.system('start ' + r'' + _fileName_ + '.bat')
state = 'RUNNING'
#print(1)
elif state == 'FINISH':
#print(9)
break
elif state == 'RUNNING':
time.sleep(1)
#print(2)
try:
fh = open(r'' + _fileName_ + '.ok', 'r')
state = fh.read().replace('
', '')
except:
state = 'RUNNING'
else:
break
def CleanFile(self, _mode_ = 'Finish'):
tmpPath = self._ROOT_PATH_ + '/files/'
for file in os.listdir(tmpPath):
ffff = file.upper()
delFlag = False
if _mode_ == 'Finish':
if ffff[-4:] == '.BAT' or ffff[-7:] == '.SQL.OK':
delFlag = True
else:
if ffff[-4:] == '.LOG' or ffff[-4:] == '.BAT' or ffff[-7:] == '.SQL.OK':
delFlag = True
if delFlag:
tmpFile = os.path.join(tmpPath, file)
if os.path.isfile(tmpFile):
os.remove(tmpFile)
def Launcher(self):
#l = ['tab_lpms_xxx.sql', 'tab_lpms_xx1x.sql', 'tab_lpms_xxxxx.sql', 'tab_wlt_xxx.sql', 'seq_lpms_xxx.sql', 'pkgh_jone_xxx.sql', 'pkgb_jone_xxx.sql', 'pubk_jone_xxx.sql']
#self._FILE_LIST_ = self.LoadNdbList(l)
# bat
self._ROOT_PATH_ = self.GetFilePath().replace('\\', '/')
print(' :', self._ROOT_PATH_)
_clean_ = True
_show_list_ = False
_dosMode_ = 'M'
#workp = 'D:/NdbFlush'
ipt = input('>>')
ipt = ipt.upper()
if ipt == 'V':
_show_list_ = True
elif ipt == 'D1':
_dosMode_ = 'S'
elif ipt == 'D2':
_dosMode_ = 'M'
elif ipt == 'Q':
exit()
elif ipt == 'C':
_clean_ = True
else:
print(' ...')
print('=' * 70)
print('
')
self.CleanFile('Begin')
self._FILE_LIST_ = self.LoadNdbList(self.GetSqlFileList())
# BEGIN
if _show_list_:
lst = self._FILE_LIST_
for im in lst:
# schema
print('schema =', im)#, lst.get(im))
for xm in lst.get(im):
# file type
print('\ttype =', xm, '
\tlist =', lst.get(im).get(xm))
# END
self._ORA_CFG_FILE_ = 'ora_tns_info.conf'
self._TNS_LIST_ = self.GetOraInfo()
self.SqlFlushDB(_dosMode_)
if _clean_:
self.CleanFile()
self._UnInit_()
return self._FILE_LIST_, self._TNS_LIST_
def usage():
print('=' * 70)
print('=\t[ NDB ] v1.0')
print('=\t2015-07-05 by L')
print('-' * 60)
print('=\t[ Q: ; ]')
print('=\t[ V: SQL ; ]')
print('=\t[ D1: ; D2: [ ]; ]')
print('=\t[ \t : ')
print('=\t \t\t1)SQL:[$WorkPath$/files/]')
print('=\t \t\t2)TNS:[$WorkPath$/ora_tns_info.conf]')
print('=\t[ ; ]')
print('=' * 70)
if __name__ == '__main__':
usage()
ndb = NdbFlush()
lst, ora = ndb.Launcher()
print('
')
print('=' * 70)
print('
')
print(' , , log $WorkPath$/file/*.log...
[Enter]')
input('')
'''
print('[MAIN] lst =', lst)
print('[MAIN] ora =', ora)
print('
')
print('
')
print('-' * 100)
print('
')
for im in lst:
# schema
print('schema =', im)#, lst.get(im))
for xm in lst.get(im):
# file type
print('\ttype =', xm, '
\tlist =', lst.get(im).get(xm))
'''