分析方法2
import datetime
class DBClient(object):
'''
classdocs
'''
imp_module = "cx_Oracle"
connection = None
cursor = None
conn_str = ''
exec_params = {}
enable_debug = False
_statement = ''
_parameters = {}
_keywordParameters = None
_columns = []
_isdisconnect = True
_log = None
_dbEngine = None
_connObj = None
_row_data=[]
_table_data=[]
def __init__(self, conn_str, autocommit=False, logging_obj=None, enable_debug=False, **params):
'''
Constructor
'''
self.enable_debug = enable_debug
import logging
if logging_obj and type(logging_obj) is type(logging):
self._log = logging_obj
else:
logging.basicConfig(level=logging.DEBUG)
self._log = logging
self._log.info("Non set logging! Using default logging.")
try:
# A0.03*
if conn_str.startswith('oracle+cx_oracle://'):
self.imp_module = "cx_Oracle"
self._log.debug("Import module: sqlalchemy")
try:
import sqlalchemy
except ImportError as e:
raise e
self.conn_str = conn_str
self._log.debug("Connecting to DB...(%s)", self.conn_str)
self._dbEngine = sqlalchemy.create_engine(self.conn_str, connect_args=params)
self._dbEngine.connect()
self._connObj = self._dbEngine.connect()
self.connection = self._connObj.connection
#print('self.connection:%s'%self.connection)
self._isdisconnect = False
if autocommit: self.connection.autocommit = True
#print('autocommit:%s'%autocommit)
self.cursor = self.connection.cursor()
#print('self.cursor:%s'%self.cursor)
else:
self._log.debug("Import module: %s", self.imp_module)
try:
import importlib
Database = importlib.import_module(self.imp_module)
except ImportError as e:
raise e
# if db_type != conn_str:
# conn_str = conn_str.lstrip(db_type + "://")
# import re
# conn_str = re.sub(r"(?P<id>[^:]+):(?P<pw>[^@]+)@", r"\g<id>/\g<pw>@", conn_str)
self.conn_str = conn_str
self._log.debug("Connecting to DB...(%s)", self.conn_str)
self.connection = Database.connect(self.conn_str, **params)
self._isdisconnect = False
self.cursor = self.connection.cursor()
except Exception as e:
self._log.warn(e)
raise e
def begin(self):
if self.imp_module == 'cx_Oracle' and self.connection:
if self.connection.autocommit:
self.connection.autocommit = False
self.cursor.close()
self.cursor = self.connection.cursor()
print('db.is already open! now i close it and try it again!')
print("db.begin!")
return self.connection.begin()
else:
return None
def execute(self, statement, *parameters, **keywordParameters):
# print('statement:%s'%statement)
# print('keywordParameters:%s'%keywordParameters)
self._parameters = parameters
self._keywordParameters = keywordParameters
if self._statement != statement:
self._statement = statement
self._columns.clear()
if parameters:
print(parameters)
print(parameters[0])
print(type(parameters[0]))
if parameters[0] and type(parameters[0]) is dict:
import re
# Dynamic parameters naming: https://docs.oracle.com/cd/E11882_01/timesten.112/e21642/names.htm#TTSQL191
pattern = re.compile(r':([\w|_|#|$|@]+)[\s|,]')
self._columns = pattern.findall(statement)
# self._columns = sorted(pattern.findall(statement), key=len, reverse=True)
if self.enable_debug: self._log.debug("SQL include columns: %s", self._columns)
if self._columns:
self.exec_params.clear()
if parameters[0] and type(parameters[0]) is dict:
for paramter in self._columns:
try:
self.exec_params[paramter] = parameters[0][paramter]
except KeyError:
self._log.debug("The 'parameters' lose key: %s.", paramter)
return self.cursor.execute(statement, self.exec_params, **keywordParameters)
return self.cursor.execute(statement, *parameters, **keywordParameters)
def commit(self):
if self.connection:
return self.connection.commit()
else:
return None
def disconnect(self):
self._statement = None
del self._statement
if self._keywordParameters:
self._keywordParameters.clear()
self._keywordParameters = None
del self._keywordParameters
self._parameters = None
del self._parameters
self.conn_str = None
del self.conn_str
self.db_type = None
del self.db_type
self.close()
try:
self.connection.close()
except:
pass
finally:
self.connection = None
del self.connection
self._isdisconnect = True
self._dbEngine = None
del self._dbEngine
self._connObj = None
del self._connObj
def close(self):
try:
self.cursor.close()
except:
pass
finally:
self.cursor = None
del self.cursor
def rollback(self):
if self.connection:
return self.connection.rollback()
else:
return None
def searchData(self,result):
'''printing table row data and return count of table'''
flag=1
count_col=0
t_head=['count_raw','count_col']
t_count=[]
if self._table_data:
self._table_data.clear()
i=0
while(flag==1):
row=result.fetchone()
if row !=None:
# print(row)
self._table_data.append(row)
j=0
while(j<len(row)):
j=j+1
count_col=j
i=i+1
else:
flag=0
count_raw=i
t_count.append(count_raw)
t_count.append(count_col)
table_count=dict(zip(t_head,t_count))
# print('count:%s'%table_count)
# ir=1
# for irow in self._table_data:
# print('%s row:%s'%(ir,irow))
# ir+=1
return table_count
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):
print(d_dict)
print(type_list)
print(key_list)
print(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
if __name__ == "__main__":
#CONNECT_STRING = ''
db = DBClient(CONNECT_STRING,encoding="utf-8")
db.begin()
db.connection.autocommit=True
db.begin()
tab_count={}
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=['DFSLoaders','CoolingStageLoader','SQL_ADD_GLASS_INFO',62,'OLED_CS_GLASS_INFO','GLASS_KEY, GLASS_ID, STEP_ID, PRODUCT_ID, EQUIPMENT_ID, UNIT_ID, RECIPE_ID, GLASS_JUDGE, LOT_ID, CASSETTE_ID, GLASS_SIZE_X, GLASS_SIZE_Y, GLASS_START_TIME, GLASS_END_TIME, TOTAL_DEFECT']
record=dict(zip(header,data))
if db.ChangeToOracleType(record, my_type, header, nullable):
print('True')
# sql='''select a.*,count(*)
# from(
# select col1,col2,col3
# from
# (select 'mytext'col1,'mytxt2'col2,'1' col3 from dual union all
# select 'myval1' col1,'myval2'col2,'2' col3 from dual union all
# select 'myval1' col1,'myval2'col2,'3' col3 from dual union all
# select 'myval1' col1,'my2'col2,'4' col3 from dual)
# where 1=1
# and col1=:col1
# and col2=:col2 )a
# group by a.col1,a.col2,a.col3
# '''
# result = db.execute(sql,
# col1='myval1',
# col2='myval2'
# )
# db.searchData(result)
# sql2='''select * from parts_invt_sn_cn_ppm
# where 1=1
# and sdwt_rawid=:sdwt_rawid
# and parts_no=:parts_no
# '''
# result2 = db.execute(sql2,sdwt_rawid='139',parts_no='142511128'
# # result2 = db.execute(sql2,sdwt_rawid='11111',parts_no='142511128'
# )
# tab_count=db.searchData(result2)
# row_no=1
# if tab_count['count_raw']==0:
# print('there is no data, insert into table.....')
# sql_insert='''
# insert into parts_invt_sn_cn_ppm (
#
# )
# values
# '''
# else:
# print('delete data from table which as the following .....')
# for i in db._table_data:
# print('%s row:%s'%(row_no,i))
# row_no+=1
# print(tab_count)
# print('delete finished, insert into table.....')
# sql3='''select * from DFS_MACHINE_CONFIG
# where 1=1
# --and sdwt_rawid=:sdwt_rawid
# --and parts_no=:parts_no
# '''
# result3 = db.execute(sql3#,sdwt_rawid='139',parts_no='142511128'
# )
# t=db.searchData(result3)
# print(t)
