分析方法1

import os
import sys
import datetime
from DBClient import DBClient
from HandleFileClass import HandleFileClass
from importlib import import_module
from sqlalchemy.engine import row
import re
from glob2 import glob
class ConfigFileAnalysis(object):
func_name=None
CONNECT_STRING='oracle+cx_oracle://db_name:db_passwd@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=db_ip)(PORT=db_port))(CONNECT_DATA=(SID=db_sid)))'

SOURCE_FOLDER = '.\Data\Source\ConfigFiles'
ARCHIVE_FOLDER = '.\Data\Archive\ConfigFiles'
ERROR_FOLDER = '.\Data\Error\ConfigFiles'

FROM_PATH='D:\software\eclipse\eclipse-workspace\EDAETL'
FIND_FILENAME='config.py'
LST_FOCUSDIR=['DFS_SUMLoader','DfsDispatch','DFSLoaders','EdcDbLoader','M2MESLoaders','MESLoaders','OtherLoaders','SPCLoaders']

SQL_SEARCH = '''
select * from DFS_LOADER_CONFIG
where 1=1
and loader_group=:loader_group
and loader_name=:loader_name
and sql_name=:sql_name
--and row_id=:row_id
and table_name=:table_name
'''
SQL_DELETE='''
delete from DFS_LOADER_CONFIG
where 1=1
and loader_group=:loader_group
and loader_name=:loader_name
and sql_name=:sql_name
and row_id=:row_id
and table_name=:table_name
'''
SQL_INSERT = '''
insert into DFS_LOADER_CONFIG (
LOADER_GROUP,LOADER_NAME,SQL_NAME,ROW_ID,TABLE_NAME,COL_NAME_LIST,UPDATETIME
)values(
:loader_group,:loader_name,:sql_name,:row_id,:table_name,:col_name_list,SYSDATE
)'''



def DataConvert(self, dictdata, t, key, nullable=True, default=None, strfmt=None):
if not dictdata or type(dictdata) is not dict:
raise Exception("Input data must be dict raw_type.", type(dictdata))
elif not nullable and not default and not dictdata[key]:
raise Exception("Not nullable can't set default value is null.")
elif t is int:
# print(dictdata[key])
dictdata[key] = int(dictdata[key]) if dictdata[key] else default
elif t is float:
# A0.03*
if dictdata[key] and dictdata[key][-1:] == '%':
import decimal
dictdata[key] = decimal.Decimal(dictdata[key].rstrip('%')) / 100 if dictdata[key] else default
else: # A0.03&
dictdata[key] = dictdata[key].replace(' ', '')
float(dictdata[key]) if dictdata[key] else default # C0.01 just try convert
dictdata[key] = dictdata[key] if dictdata[key] else default # C0.01
# C0.01 dictdata[key] = float(dictdata[key]) if dictdata[key] else default
elif t is datetime:
dictdata[key] = datetime.datetime.strptime(dictdata[key], strfmt) if dictdata[key] else default
elif t is str:
dictdata[key] = dictdata[key] if dictdata[key] else default
else:
raise Exception("The data raw_type({0}) is not supported".format(t))
def ChangeToOracleType(self,d_dict,type_list,key_list,nullable_list):
length=len(d_dict)
if(length!=len(type_list)):
print('type lost.')
return
elif(length!=len(key_list)):
print('key lost.')
return
elif(length!=len(nullable_list)):
print('nullable lost.')
return
x=0
while(x<length):
if(type_list[x]==datetime):
self.DataConvert(d_dict, type_list[x], key_list[x], nullable=nullable_list[x],strfmt='%Y%m%d%H%M%S')
# print('%s,%s'%(x,d_dict[key_list[x]]))
x+=1
else:
self.DataConvert(d_dict, type_list[x], key_list[x], nullable=nullable_list[x])
# print('%s,%s'%(x,d_dict[key_list[x]]))
x+=1
# print('%s,%s'%(d_dict['FACTORYNAME'],d_dict['NO_ID']))
return True
def Start_Id(self,rows_list):
start_i=0
i=1
for row in rows_list:
row=row.strip()
if row=='# SQL Template':
start_i=i+2
i+=1
return start_i
def PrintRow(self,rows_list,start_i):
i=1
for row in rows_list:
row=row.strip()
if i<start_i:
i+=1
continue
else:
if row[0:1]=="#":
i+=1
continue
else:
print('row %s:%s'%(i,row))
i+=1
def Find_AddSqlId(self,rows_list,start_i):
addsql_id_list=[]
addsql_name_list=[]
i=1
sql_add=0
sql_name=None
for row in rows_list:
row=row.strip()
if i<start_i:
i+=1
continue
else:
if row[0:1]=="#":
i+=1
continue
else:
if row[0:8]=="SQL_ADD_":
sql_add+=1
sql_name=row.split("=")[0].replace(" ","")
# sql_name='%s(%s)'%(sql_name,sql_add)
addsql_id_list.append(i)
addsql_name_list.append(sql_name)
# print('row:%s,SQL_ADD no:%s,name:%s '%(i,sql_add,sql_name))
i+=1
else:
i+=1
# print(row[0:8])
continue
addsql_dict=dict(zip(addsql_id_list,addsql_name_list))
return addsql_dict
def PrintDict(self,dict_name):
self.func_name='PrintDict'
if dict_name:
for k,v in dict_name.items():
print('key :%s ,value :%s'%(k,v))
else:
print('function :%s lost parameter:%s'%(self.func_name,'dict_name'))
def Find_AddSql(self,rows_list,start_i,dict_name):
self.func_name='Find_AddSql'
section_val=[] # record dict_name's val
section_key=[] # record dict_name's key
section_annotate=[] # /* */
addsql_sql_list=[]
addsql_name_list=[]
key_list=[]
if dict_name:
item_cnt=0
for k,v in dict_name.items():
addsql_name_list.append(v)
addsql_sql_list.append('')
key_list.append(k)
item_cnt+=1
sql_dict=dict(zip(addsql_name_list,addsql_sql_list))
# print(sql_dict)
try:
i=1 #row count
for row in rows_list:
row=row.strip()
if i<start_i:
# print('start_i is:%s,no start:%s'%(start_i,i))
i+=1
continue
else:
if row[0:1]=="#":
# print('#:%s,%s'%(i,row))
i+=1
continue
elif row[0:2]=="--":
i+=1
continue
elif row.strip()[0:2]=='/*':
section_annotate.append('/*')
i+=1
continue
elif row.strip()[-2:]=='*/':
if section_annotate:
# print('pop:%s'%row)
section_annotate.pop()
i+=1
continue
else:
i+=1
# print(row)
# break
else:
if len(section_annotate)==0:
if row.replace(" ","")=="'''":
if len(section_key)==0:
# print('pass...,section is null:%s,%s'%(i,row))
i+=1
continue
else:
# print('...:%s,%s'%(i,row))
section_val.pop()
section_key.pop()
i+=1
continue
elif row.replace(" ","")=="":
# print(' :%s,%s'%(i,row))
i+=1
continue
elif dict_name.__contains__(i) :
# print('sql name%s,%s'%(i,row))
section_val.append(dict_name[i])
section_key.append(i)
i+=1
elif len(section_key)==0:
# print('pass:%s,%s'%(i,row))
i+=1
continue
elif i>section_key[-1]:
# print('sql :%s,%s'%(i,row))
sql_dict[section_val[-1]]='%s %s'%(sql_dict[section_val[-1]],row)
i+=1
else:
i+=1
continue
# print(sql_dict)
except Exception as ex:
print(ex)
else:
print('function %s lost parameter %s'%(self.func_name,dict_name))
return sql_dict

if __name__=='__main__':

res_func=None

loader_group=''
loader_name=''
db=None

dbase=import_module("cx_Oracle")
obj=ConfigFileAnalysis()
# obj2=DBClient()
obj3=HandleFileClass()
sys.path.append('..')


# step1:Copy file
obj3.CopyFile(obj.FROM_PATH,obj.SOURCE_FOLDER,obj.FIND_FILENAME,obj.LST_FOCUSDIR)

try:
# step2:Open directory and get file
for config_file in glob(os.path.join(obj.SOURCE_FOLDER,'*'),recursive=True):
print('[INFO]:Load file:%s'%config_file)
if os.path.isfile(config_file):
loader_group=os.path.basename(config_file).split('@')[0]
loader_name=os.path.basename(config_file).split('@')[1]
config_obj=None
# step3:Open file and execute*
try:
config_obj=open(config_file,'r',encoding='utf-8')
#with open(config_file,encoding='utf-8') as config_obj:
file_name=config_obj.name
context=config_obj.readlines()
start_i=obj.Start_Id(context);#obj.PrintRow(context, start_i)#SkipStartId print row
addsql_id_dict=obj.Find_AddSqlId(context,start_i);
#obj.PrintDict(addsql_id_dict)#print dict
if not addsql_id_dict:
print('PASS: "SQL_ADD" not exist in {0}'.format(os.path.basename(file_name)))
obj3.MoveFile(file_name, obj.ERROR_FOLDER)
continue

addsql_dict=obj.Find_AddSql(context,start_i,addsql_id_dict)#obj.PrintDict(addsql_dict)

pattern0=re.compile(r'INSERT INTO([^/]+)/([^/]+)/([^/]+)VALUES([^/]+)/([^/]+)/')

row_id=[]
add_sql_name=[]
table_name=[]
col_name=[]
col2_name=[]
table_dict={}
for k,v in addsql_dict.items():
add_sql_name.append(k)
string0=v.lstrip().rstrip()# remove left and right space
string00=string0.replace("(","/").replace(")","/")# replace '(' and ')' to '/'
result=pattern0.findall(string00)
if result:
result0=result[0]
table_name.append(result0[0].strip().upper())
col_name.append(result0[1].strip().upper())
col2_name.append(result0[4].strip().upper())
else:
print('[ERROR]:there is no matched result! ,string:%s'%string00)
for k,v in addsql_id_dict.items():
row_id.append(k)
table_dict=dict(zip(add_sql_name,table_name));#obj.PrintDict(table_dict)
col_dict=dict(zip(add_sql_name,col_name));#obj.PrintDict(col_dict)
row_dict=dict(zip(add_sql_name,row_id));#obj.PrintDict(row_dict)

# Step4:execute into DB
header=['LOADER_GROUP','LOADER_NAME','SQL_NAME','ROW_ID','TABLE_NAME','COL_NAME_LIST']
my_type=[str,str,str,int,str,str]
nullable=[False,False,False,False,False,True]
data=[]
for i in add_sql_name:
# print('loader_group:%s\n,loader_name:%s\n,sql_name:%s\n,row_id:%s\n,table_name:%s\n,col_name_list:%s\n'%(loader_group,loader_name,i,row_dict[i],table_dict[i],col_dict[i]))
data=[loader_group,loader_name,i,row_dict[i],table_dict[i],col_dict[i]]
record=dict(zip(header,data))
ChangeType_Res=obj.ChangeToOracleType(record,my_type,header,nullable)

if ChangeType_Res:
if not db:
db = DBClient(obj.CONNECT_STRING, encoding="utf-8")
db.begin()
# search
result = db.execute(obj.SQL_SEARCH
,loader_group=record['LOADER_GROUP']
,loader_name=record['LOADER_NAME']
,sql_name=record['SQL_NAME']
#,row_id=record['ROW_ID']
,table_name=record['TABLE_NAME']
)
tab_count=db.searchData(result)#searchData :tab_count,_table_data
print('[DB INFO]:search table DFS_LOADER_CONFIG:%s rows'%tab_count['count_raw'])
# Delete
if tab_count['count_raw']!=0:
print('[DB INFO]:delete data from table which as the following .....')
row_no=1
for j in db._table_data:
print('\t%s row,%s col:%s'%(row_no,tab_count['count_col'],j))
row_no+=1
result_del = db.execute(obj.SQL_DELETE
,loader_group=record['LOADER_GROUP']
,loader_name=record['LOADER_NAME']
,sql_name=record['SQL_NAME']
,row_id=record['ROW_ID']
,table_name=record['TABLE_NAME']
)
# print('[DB INFO]:delete finished!')
# Insert
print('[DB INFO]:insert data:({0},{1},{2},{3},{4})'.format(record['LOADER_GROUP'],record['LOADER_NAME'],record['SQL_NAME'],record['ROW_ID'],record['TABLE_NAME']))
db.execute(obj.SQL_INSERT,
loader_group=record['LOADER_GROUP'],
loader_name=record['LOADER_NAME'],
sql_name=record['SQL_NAME'],
row_id=record['ROW_ID'],
table_name=record['TABLE_NAME'],
col_name_list=record['COL_NAME_LIST']
)
if db:
db.commit()
# print('DB INFO:insert finised!')
else:
print('TYPE ERROR:change data type shut down,there is an error!')

print('[DB INFO]:SUCCESS:%s insert into database!'%(os.path.basename(config_file)))

# step5:MoveFile
if config_obj:
config_obj.close()
obj3.MoveFile(file_name, obj.ARCHIVE_FOLDER)
except dbase.DatabaseError as ex:
print('[DB ERROR]:ex1..................:%s'%ex)
if db:db.rollback()
#MoveFile
if config_obj:
config_obj.close()
obj3.MoveFile(file_name, obj.ERROR_FOLDER)
except Exception as ex:
print('[ERROR]:ex2................:%s'%ex)
if db: db.rollback()
if config_obj:
config_obj.close()
obj3.MoveFile(file_name, obj.ERROR_FOLDER)
else:
print('[ERROR]:There is no config file!')
# step2:Open directory and get file&
print('[INFO]:all file are be analyzed!----------------------------')
except Exception as ex:
print(ex)
finally:
if db:
db.disconnect()
db = None
del db
# Task&