def updateFirDate(self, connList, nowDate): result = '' try: conn = M6.Connection(self.IRIS_IP, self.IRIS_ID, self.IRIS_PW, Database=self.IRIS_DB) cursor = conn.cursor() ipList = '\'%s\'' % '\',\''.join(connList) sql = """ UPDATE TACS_TB_LNKG_UNIT SET CONN_YN = 'Y', FIRST_CONN_DATE = '%s' ,LAST_CONN_DATE = '%s' WHERE EMS_IP IN (%s) """ % (nowDate, nowDate, ipList) result = cursor.Execute2(sql) if cursor: cursor.Close() if conn: conn.close() except: __LOG__.Exception() return result
def updateEmsConnectCheck(self, emsIp, nowDate, firstConnDate, connectCheck): __LOG__.Trace('emsIp : %s , nowDate : %s, firstConnDate : %s' % (emsIp, nowDate, firstConnDate)) result = '' try: conn = M6.Connection(self.IRIS_IP, self.IRIS_ID, self.IRIS_PW, Database=self.IRIS_DB) cursor = conn.cursor() sql = 'UPDATE TACS_TB_LNKG_UNIT ' updateData = list() whereData = list() updateData.append(" SET CONN_YN = '%s'" % connectCheck) whereData.append(" WHERE EMS_IP = '%s'" % str(emsIp)) if (firstConnDate == None or firstConnDate == '') and 'Y' == connectCheck: updateData.append(" FIRST_CONN_DATE = '%s'" % str(nowDate)) if 'Y' == connectCheck: updateData.append(" LAST_CONN_DATE = '%s'" % str(nowDate)) updateQuery = ' '.join( [sql, ','.join(updateData), ','.join(whereData)]) result = cursor.Execute2(updateQuery) cursor.Close() conn.close() except: __LOG__.Exception() return result
def selectCollectCheck( self, workId, lastChgDate ) : conn = None result = '' __LOG__.Trace("WORK_ID : %s , LAST_CHG_DATE : %s" % (workId, lastChgDate)) try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database ='tacs') cursor = conn.cursor() sql = """ SELECT COUNT(*) FROM TACS.TACS_WORK_COLLECT_CHECK WHERE WORK_ID = '%s' AND LAST_CHG_DATE = '%s' """ % (workId, lastChgDate) cursor.Execute2(sql) for oneRaw in cursor : result = oneRaw[0].encode('utf-8') except : __LOG__.Exception() finally : if conn : conn.close() return result
def selectNmEqpData(self, eqpNm): eqpNm = eqpNm.strip() __LOG__.Trace('eqpNm = %s' % eqpNm) conn = None result = '' try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql = """SELECT EQP_EMS_NM FROM TACS.TNG_IM_EQP_BAS WHERE EQP_NM ='%s'""" % str(eqpNm) cursor.Execute2(sql) for oneRaw in cursor: result = oneRaw[0].encode('utf-8') except: __LOG__.Exception() finally: if conn: conn.close() return result
def selectRelationCheck(self) : __LOG__.Trace('!! Relation Check !!') conn = None result = '' try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database = 'tacs' ) cursor = conn.cursor() sql = """ SELECT RELATION_CHECK_YN FROM TACS.TACS_CORE_EMS_RELATION_CHECK LIMIT 1 """ resultMsg = cursor.Execute2(sql) __LOG__.Trace(sql) __LOG__.Trace(resultMsg) if 'OK' in resultMsg : for oneRaw in cursor : result = oneRaw[0].encode('utf-8') else : __LOG__.Trace('Query Fail!! ') except : __LOG__.Exception() finally : if conn : conn.close() return result
def selectNmEmsData (self, emsNm) : emsNm = emsNm.strip() __LOG__.Trace('emsNm = %s' % emsNm) resultDict = {'expEmsNm' : '' ,'expEmsIp' : '', 'vendor' : '', 'eqpTyp' : ''} conn = None try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() #ipList = '\'%s\'' % '\',\''.join(svrIpList) sql = """ SELECT EQP_NM, REP_IP_ADDR, SPLY_BP_ID, EQP_TYP FROM TACS.TNG_IM_EQP_BAS WHERE EQP_EMS_NM = '%s' """ % emsNm resultMsg = cursor.Execute2(sql) for oneRaw in cursor : resultDict['expEmsNm'] = oneRaw[0].encode('utf-8') resultDict['expEmsIp'] = oneRaw[1].encode('utf-8') resultDict['vendor'] = oneRaw[2].encode('utf-8') resultDict['eqpTyp'] = oneRaw[3].encode('utf-8') except : __LOG__.Exception() finally : if conn : conn.close() return resultDict['expEmsNm'], resultDict['expEmsIp'], resultDict['vendor'], resultDict['eqpTyp']
def selectWorkInfoCnt (self, workId) : workId = workId.strip() __LOG__.Trace('workId = %s' % workId ) conn = None result = 0 try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database ='tacs') cursor = conn.cursor() sql = """ SELECT COUNT(*) FROM TACS.TACS_WORK_INFO WHERE WORK_ID = '%s' """ % workId cursor.Execute2(sql) for oneRaw in cursor : result = int(oneRaw.encode('utf-8')) except : __LOG__.Exception() finally : if conn : conn.close() return result
def selectIdOmpData (self, tacsEqpId) : tacsEqpId = tacsEqpId.strip() __LOG__.Trace('emsId = %s' % tacsEqpId) resultList = list() conn = None try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database = 'tacs' ) cursor = conn.cursor() sql = """ SELECT EQP_ID FROM TACS.TNG_IM_EQP_BAS WHERE EMS_EQP_ID = '%s' AND EQP_TYP LIKE '%s' """ % (tacsEqpId, '%EMS') resultMsg = cursor.Execute2(sql) for oneRaw in cursor : resultList.append(oneRaw[0].encode('utf-8')) except : __LOG__.Exception() finally : if conn : conn.close() return resultList
def insertCollectCheck(self, workId, lastChgDate): conn = None __LOG__.Trace("WORK_ID : %s , LAST_CHG_DATE : %s" % (workId, lastChgDate)) try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql = """ INSERT INTO TACS.TACS_WORK_COLLECT_CHECK(WORK_ID, LAST_CHG_DATE) VALUES('%s', '%s') """ % (workId, lastChgDate) result = cursor.Execute2(sql) __LOG__.Trace("INSERT RESULT TACS.TACS_WORK_COLLECT_CHECK : %s" % result) except: __LOG__.Exception() finally: if conn: conn.close()
def selectLkngUnit(self, emsIp): emsIp = emsIp.strip() __LOG__.Trace('emsIp : %s' % emsIp) conn = None resultDict = {'mqNm': '', 'unitDistYn': ''} try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql = "SELECT WORK_MQ_NM, UNIT_DIST_YN FROM TACS.TACS_TB_LNKG_UNIT WHERE EMS_IP = '%s'" % str( emsIp) cursor.Execute2(sql) for oneRaw in cursor: resultDict['mqNm'] = oneRaw[0].encode('utf-8') resultDict['unitDistYn'] = oneRaw[1].encode('utf-8') except: __LOG__.Exception() finally: if conn: conn.close() return resultDict
def deleteCollectCheck(self, workId, lastChgDate): conn = None __LOG__.Trace("WORK_ID : %s , LAST_CHG_DATE : %s" % (workId, lastChgDate)) try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql = """ DELETE FROM TACS.TACS_WORK_COLLECT_CHECK WHERE WORK_ID = '%s' AND LAST_CHG_DATE = '%s' """ % (workId, lastChgDate) result = cursor.Execute2(sql) __LOG__.Trace("DELETE RESULT TACS.TACS_WORK_COLLECT_CHECK : %s" % result) except: __LOG__.Exception() finally: if conn: conn.close()
def selectIDX(self): conn = None result = '' try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql = """ SELECT IDX FROM TACS.TACS_WORK_INFO_IDX """ cursor.Execute2(sql) for oneRaw in cursor: result = oneRaw[0] except: __LOG__.Exception() finally: if conn: conn.close() return result
def deleteWorkEqpInfo(self, workId, idx, key, partition): __LOG__.Trace('key : %s | partition : %s | workId : %s | idx : %s' % (key, partition, workId, idx)) conn = None try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() workSql = """/*+ LOCATION( key = '%s' AND PARTITION = '%s' ) */ DELETE FROM TACS.TACS_WORK_INFO WHERE IDX = '%s' and WORK_ID = '%s' """ % (key, partition, idx, workId) resultWorkMsg = cursor.Execute2(workSql) eqpSql = """/*+ LOCATION( key = '%s' AND PARTITION = '%s' ) */ DELETE FROM TACS.TACS_WORK_INFO WHERE IDX = '%s' and WORK_ID = '%s' """ % (key, partition, idx, workId) resultEqpMsg = cursor.Execute2(eqpSql) except: __LOG__.Exception() finally: if conn: conn.close()
def selectIpOtherEqpData(self, svrIpList, emsEqpId): __LOG__.Trace('svrIp = %s | parentEqpId : %s ' % (svrIpList, emsEqpId)) conn = None result = '' try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() ipList = '\'%s\'' % '\',\''.join(svrIpList) if type(emsEqpId) is list: emsEqpId = '\'%s\'' % '\',\''.join(emsEqpId) elif type(emsEqpId) is str: emsEqpId = '\'%s\'' % emsEqpId else: __LOG__.Trace('TACS EQP ID invalid : %s' % emsEqpId) __LOG__.Trace(ipList) sql = """ SELECT EQP_EMS_NM FROM TACS.TNG_IM_EQP_BAS WHERE ( REP_IP_ADDR IN (%s) OR REP_IP_ADDR_1 IN (%s) OR REP_IP_ADDR_2 IN (%s) OR REP_IP_ADDR_3 IN (%s) OR REP_IP_ADDR_4 IN (%s) OR REP_IP_ADDR_5 IN (%s) ) AND EMS_EQP_ID IN (%s) LIMIT 1 """ % (ipList, ipList, ipList, ipList, ipList, ipList, emsEqpId) resultMsg = cursor.Execute2(sql) __LOG__.Trace(sql) __LOG__.Trace(resultMsg) if 'OK' in resultMsg: for oneRaw in cursor: result = oneRaw[0].encode('utf-8') else: __LOG__.Trace('Query Fail!! ') except: __LOG__.Exception() finally: if conn: conn.close() __LOG__.Trace(result) return result
def execute(self, sql): conn = M6.Connection(self.IRIS_HOST, self.ID, self.PWD) cur = conn.cursor() cur.SetFieldSep(self.COLSEP) cur.SetRecordSep(self.ROWSEP) cur.Execute(sql) return (conn, cur)
def updateDistributeYn(self, hint, workId, emsIp, workStaDate): __LOG__.Trace( 'hint({}), workId({}), emsIp({}), workStaDate({})'.format( hint, workId, emsIp, workStaDate)) conn = None try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() selectSql = ''' {} SELECT MAX(IDX + 0) FROM TACS.TACS_WORK_INFO WHERE WORK_ID = '{}' AND WORK_STA_DATE = '{}' '''.format(hint, workId, workStaDate) __LOG__.Trace('query: {}'.format(selectSql)) cursor.Execute2(selectSql) idx = None for oneRow in cursor: idx = oneRow[0] if not idx: raise Exception('Unavailable IDX({})'.format(idx)) updateSql = ''' {} UPDATE TACS_EQP_INFO SET EXP_YN = 'Y' , EXP_DATE = SYSDATE WHERE IDX = '{}' AND EXP_VALID_YN = 'Y' AND ( EMS_IP = '{}' OR EXP_EMS_IP = '{}' ) '''.format(hint, idx, emsIp, emsIp) __LOG__.Trace('query: {}'.format(selectSql)) cursor.Execute2(updateSql) except Exception as e: __LOG__.Exception() finally: if conn: conn.close()
def connect(self, host, user, pwd, direct, db): LOG.info('%s %s %s %s %s' % (host, user, pwd, str(direct), db)) self.conn = M6.Connection(host, str(user), str(pwd), Direct=direct, Database=str(db)) self.cursor = self.conn.Cursor() self.cursor.SetFieldSep(str(self.field_sep)) self.cursor.SetRecordSep(str(self.record_sep)) self.cursor.SetTimeout(self.timeout)
def initConnect(self): self.conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') __LOG__.Trace('IRIS Connect!') try : self.cursor = self.conn.cursor() self.cursor.SetFieldSep('|^|') self.cursor.SetRecordSep('|^-^|') except : __LOG__.Exception() finally : self.conn.commit()
def m6_execute(query, is_select=False): conn = M6.Connection('127.0.0.1:5050', 'test', 'test') c = conn.Cursor() c.SetFieldSep('|^|') c.SetRecordSep('|^-^|') print print c.Execute2(query) if is_select: for row in c: print row c.Close() conn.close()
def selectDate(self, workId, workStaDate): workIdKey = workId[-1] __LOG__.Trace('workIdKey = %s / workId = %s / workStaDate = %s' % (workIdKey, workId, workStaDate)) result = '' conn = None try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql1 = """/*+ LOCATION( key = '%s' and partition = '%s' ) */ SELECT MAX(IDX+0) FROM TACS.TACS_WORK_INFO WHERE WORK_ID = '%s' """ % (workIdKey, workStaDate, workId) cursor.Execute2(sql1) IDX = '' for oneRaw in cursor: IDX = oneRaw[0] sql2 = """/*+ LOCATION( key = '%s' and partition = '%s' ) */ SELECT LAST_CHG_DATE FROM TACS.TACS_WORK_INFO WHERE IDX = '%s' """ % (workIdKey, workStaDate, IDX) cursor.Execute2(sql2) lastChgDate = '' for oneRaw in cursor: result = oneRaw[0].encode('utf-8') __LOG__.Trace(result) cursor.Close() conn.close() except: __LOG__.Exception() finally: if conn: conn.close() return result
def selectIpCommonEqpDataRelationOff(self, svrIpList): __LOG__.Trace('svrIp = %s | Realtion Off )' % (svrIpList)) conn = None result = '' try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() ipList = '\'%s\'' % '\',\''.join(svrIpList) __LOG__.Trace(ipList) sql = """ SELECT EQP_EMS_NM FROM TACS.TNG_IM_EQP_BAS WHERE ( REP_IP_ADDR IN (%s) OR REP_IP_ADDR_1 IN (%s) OR REP_IP_ADDR_2 IN (%s) OR REP_IP_ADDR_3 IN (%s) OR REP_IP_ADDR_4 IN (%s) OR REP_IP_ADDR_5 IN (%s) ) LIMIT 1 """ % (ipList, ipList, ipList, ipList, ipList, ipList) resultMsg = cursor.Execute2(sql) __LOG__.Trace(sql) __LOG__.Trace(resultMsg) if 'OK' in resultMsg: for oneRaw in cursor: result = oneRaw[0].encode('utf-8') else: __LOG__.Trace('Query Fail!! ') except: __LOG__.Exception() finally: if conn: conn.close() return result
def selectIpEmsData(self, emsIp): emsIp = emsIp.strip() __LOG__.Trace('emsIp = %s' % emsIp) resultDict = { 'tacsEqpId': '', 'expEmsNm': '', 'expEmsIp': '', 'vendor': '', 'eqpTyp': '' } conn = None try: conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database='tacs') cursor = conn.cursor() sql = """ SELECT EQP_NM, REP_IP_ADDR, SPLY_BP_ID, EQP_TYP, EQP_ID FROM TACS.TNG_IM_EQP_BAS WHERE EQP_TYP like '%EMS%' AND (REP_IP_ADDR = '{}' OR REP_IP_ADDR_1 = '{}' OR REP_IP_ADDR_2 = '{}' OR REP_IP_ADDR_3 = '{}' OR REP_IP_ADDR_4 = '{}' OR REP_IP_ADDR_5 = '{}') LIMIT 1 """.format(emsIp, emsIp, emsIp, emsIp, emsIp, emsIp) resultMsg = cursor.Execute2(sql) for oneRaw in cursor: resultDict['expEmsNm'] = oneRaw[0].encode('utf-8') resultDict['expEmsIp'] = oneRaw[1].encode('utf-8') resultDict['vendor'] = oneRaw[2].encode('utf-8') resultDict['eqpTyp'] = oneRaw[3].encode('utf-8') resultDict['tacsEqpId'] = oneRaw[4].encode('utf-8') except: __LOG__.Exception() finally: if conn: conn.close() return resultDict['expEmsNm'], resultDict['expEmsIp'], resultDict[ 'vendor'], resultDict['eqpTyp'], resultDict['tacsEqpId']
def irisInitConnection(self): try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database = self.IRIS_DB) self.conn = conn cursor = conn.cursor() cursor.SetFieldSep('|^|') cursor.SetRecordSep('|^-^|') self.cursor = cursor except : __LOG__.Exception() time.sleep(60) self.irisInitConnection()
def load(dat_path, table_name, ctl, dat): partition_date = dat_path.split("/")[-1].split(".")[0].replace("_", "") + "0000" conn = M6.Connection(info.host, info.user_id, info.user_passwd, Direct=info.direct, Database=info.database) c = conn.Cursor() c.SetFieldSep(info.field_sep) c.SetRecordSep(info.record_sep) start = time.time() print(c.LoadString(table_name, "0", partition_date, ctl, dat)) print(time.time() - start)
def irisInitConnection(self) : conn = M6.Connection (self.IRIS_IP, self.IRIS_ID, self.IRIS_PW, Database = self.IRIS_DB) self.conn = conn try : cursor = conn.cursor() cursor.SetFieldSep('|^|') cursor.SetRecordSep('|^-^|') self.cursor = cursor except : __LOG__.Exception() self.cursor.Close() conn.close()
def __init__(self, ip, port, user, pwd, db, field_sep, record_sep, timeout, remove, error_path, direct=False): self.conn = False self.cursor = False self.host = "%s:%s" % (str(ip), str(port)) self.record_sep = record_sep self.load_status = { 'load_starttime': None, 'load_endtime': None, 'load_runtime': None, 'dat_size': None, 'record_count': None, 'success_count': 0, 'table': None, 'key': None, 'partition': None, 'success_or_fail': 'fail', 'fail_cause': '' } self.remove = False if remove.lower() in ('true', 't'): self.remove = True self.error_path = error_path try: LOG.info('%s %s %s %s %s' % (self.host, user, pwd, str(direct), db)) self.conn = M6.Connection(self.host, str(user), str(pwd), Direct=direct, Database=str(db)) self.cursor = self.conn.Cursor() self.cursor.SetFieldSep(str(field_sep)) self.cursor.SetRecordSep(str(record_sep)) self.cursor.SetTimeout(str(timeout)) updateState(1) except Exception as e: LOG.error(getTracebackStr()) LOG.error(e, exc_info=True)
def updateIdx( self ) : conn = None try : conn = M6.Connection(self.IRIS, self.IRIS_ID, self.IRIS_PASS, Database ='tacs') cursor = conn.cursor() sql = """ UPDATE TACS.TACS_WORK_INFO_IDX SET IDX = IDX + 1 """ cursor.Execute2(sql) except : __LOG__.Exception() finally : if conn : conn.close()
def main(): doc = ET.parse('conf/IS.xml') root = doc.getroot() host = root.find("Summary").find("Sum_info").find( "Connection").attrib['url'] host = host.split('://')[1] user = root.find("Summary").find("Sum_info").find( "Connection").attrib['user'] pwd = root.find("Summary").find("Sum_info").find( "Connection").attrib['pass'] conn = M6.Connection(host, user, pwd) c = conn.Cursor() q = ''' CREATE TABLE MSF_TEST_TABLE_MAIN( k TEXT, p TEXT, INCOME TEXT, EXPENSES TEXT ) datascope LOCAL ramexpire 30 diskexpire 34200 partitionkey k partitiondate p partitionrange 10 ; ''' c.Execute2(q) c.SetFieldSep('|^|') c.SetRecordSep('\n') c.Load('MSF_TEST_TABLE_MAIN', 'k', '20180615000000', 'data/MSF_TEST_TABLE_MAIN.ctl', 'data/MSF_TEST_TABLE_MAIN_0.dat') c.Load('MSF_TEST_TABLE_MAIN', 'k', '20180615001000', 'data/MSF_TEST_TABLE_MAIN.ctl', 'data/MSF_TEST_TABLE_MAIN_1.dat') c.Load('MSF_TEST_TABLE_MAIN', 'k', '20180615002000', 'data/MSF_TEST_TABLE_MAIN.ctl', 'data/MSF_TEST_TABLE_MAIN_2.dat') c.Load('MSF_TEST_TABLE_MAIN', 'k', '20180615003000', 'data/MSF_TEST_TABLE_MAIN.ctl', 'data/MSF_TEST_TABLE_MAIN_3.dat') c.Close() conn.close()
def selectEmsIp(self): try: conn = M6.Connection(self.IRIS_IP, self.IRIS_ID, self.IRIS_PW, Database=self.IRIS_DB) cursor = conn.cursor() sql = """ SELECT WORK_MQ_NM, REAL_MQ_NM, EMS_IP, UNIT_DIST_YN, REG_DATE, REG_USER_ID, FIRST_CONN_DATE, LAST_CONN_DATE, CONN_YN FROM TACS_TB_LNKG_UNIT """ cursor.Execute2(sql) resultEmsDataList = [] for resultData in cursor: resultEmsDataDict = { "workMqNm": "", "realMqNm": "", "emsIp": "", "unitDistYn": "", "regDate": "", "regUserId": "", "firstConnDate": "", "lastConnDate": "", "connYn": "" } resultEmsDataDict["workMqNm"] = resultData[0].encode('utf-8') resultEmsDataDict["realMqNm"] = resultData[1].encode('utf-8') resultEmsDataDict["emsIp"] = resultData[2].encode('utf-8') resultEmsDataDict["unitDistYn"] = resultData[3].encode('utf-8') resultEmsDataDict["regDate"] = resultData[4].encode('utf-8') resultEmsDataDict["regUserId"] = resultData[5].encode('utf-8') resultEmsDataDict["firstConnDate"] = resultData[6].encode( 'utf-8') resultEmsDataDict["lastConnDate"] = resultData[7].encode( 'utf-8') resultEmsDataDict["connYn"] = resultData[8].encode('utf-8') resultEmsDataList.append(resultEmsDataDict) cursor.Close() conn.close() return resultEmsDataList except: __LOG__.Exception()
def insertCheck(self, nowDate): try: conn = M6.Connection(self.IRIS_IP, self.IRIS_ID, self.IRIS_PW, Database=self.IRIS_DB) cursor = conn.cursor() sql = """ INSERT INTO TACS.TACS_EMS_CONNECTION_CHECK(EVNT_DATE) VALUES ('%s') """ % nowDate cursor.Execute2(sql) except: __LOG__.Exception() finally: cursor.Close() conn.close()