def dealSql(): try: dblist = connectDb.getConnectInfo() dbconn = connectDb.connectDb(dblist) dbconncr = dbconn.cursor() #业务类型为1、2、6、7(告警类) 3、8(采集类) dbconncr.execute("select * from monitor.t_mot_conf a where a.business_type in('1','2','6','7','3','8','13')") #dbconncr.execute("select * from monitor.t_mot_conf a where a.business_type in('2')") result = dbconncr.fetchall() for confArray in result: #要执行的SQL是预警类 if confArray[9] == '1': if confArray[2] in ["SQL00", "SQL01"]: sql0(confArray[1], dbconncr, confArray[4], confArray[7], confArray[0], confArray[2], confArray[3]) elif confArray[2] in ["SQL10", "SQL11"]: startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr) for startTimeIndex in range(len(startTimeInterValList)): sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5], confArray[4], confArray[0], confArray[7], confArray[2], confArray[3], startTimeInterValList[startTimeIndex][1], dbconn) else: with open(r'error.log','a') as error_log: print >> error_log,('%s %s $s' % (time.ctime(), "'M_ID:'" + confArray[0], 'SQL Tpye Error:')) #信息采集 else: if confArray[2] == "SQL1": startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr) for startTimeIndex in range(len(startTimeInterValList)): dealCollectInfo.sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5], confArray[0], startTimeInterValList[startTimeIndex][1], dbconn) connectDb.closeConnect(dbconn) except Exception,e: with open(r'error.log','a') as error_log: print >> error_log,('%s %s %s' % (time.ctime(),'Deal Data Faild:',e))
def file_check(phoneNumList,role_name): try: dblist = connectDb.getConnectInfo() dbconn = connectDb.connectDb(dblist[0]) cursor = dbconn.cursor() cursor.execute(sql) log_tables=cursor.fetchall() log_list = [] for log_table in log_tables: cursor.execute(check_sql % (log_table)) check_result=cursor.fetchall() check_list=[] for x in check_result: y=x[0].split("/") check_list.append(y[2]) check_list=set(check_list) if (len(check_list) <= 1): #print "S" pass else: #生成短信告警信息 log_list.append('check_time:%s,bill.%s be exception: %s, please check!!!!'%(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),log_table,check_list)) cursor.close() connectDb.closeConnect(dbconn) #是否发短信 if (len(log_list) < 1): pass else: #短信发送 message.message_send(log_list,phoneNumList) except Exception,e: with open(r'error.log','a') as error_log: print >> error_log,('%s %s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),' Excute ft_info_data SQL Faild:',e))
def dealSql(): try: dblist = connectDb.getConnectInfo() #VOP库连接 dbconn = connectDb.connectDb(dblist[0]) dbconncr = dbconn.cursor() #业务类型为12(告警类) dbconncr.execute("select * from monitor.t_mot_conf a where a.business_type = '12' and not exists(select 1 from monitor.t_mot_noacq_plan t where t.acq_bg_time < to_char(sysdate,'hh24miss') and t.acq_ed_time > to_char(sysdate,'hh24miss') and a.m_id = t.m_id)") result = dbconncr.fetchall() for confArray in result: #要执行的SQL是预警类 if confArray[9] == '1': if confArray[2] in ["SQL00", "SQL01"]: sql0(confArray[1], dbconncr, confArray[4], confArray[7], confArray[0], confArray[2], confArray[3], confArray[5], dbconn) elif confArray[2] in ["SQL10", "SQL11"]: startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr) for startTimeIndex in range(len(startTimeInterValList)): sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5], confArray[4], confArray[0], confArray[7], confArray[2], confArray[3], startTimeInterValList[startTimeIndex][1], dbconn) else: with open(r'error.log','a') as error_log: print >> error_log,('%s %s $s' % (time.ctime(), "'M_ID:'" + confArray[0], 'SQL Tpye Error:')) #信息采集 else: if confArray[2] == "SQL1": startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr) for startTimeIndex in range(len(startTimeInterValList)): dealCollectInfo.sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5], confArray[0], startTimeInterValList[startTimeIndex][1], dbconn) connectDb.closeConnect(dbconn) except Exception,e: with open(r'error.log','a') as error_log: print >> error_log,('%s %s %s' % (time.ctime(),'Deal Data Faild:',e))
def exeProcedure(sql): #try: filename='./config/dbinfo.ini' dblist=connectDb.getConnectInfo(filename) dbconn=connectDb.connectDb(dblist[0]) cursor=connectDb.getCursor(dbconn) connectDb.executeSQL(cursor,sql) Res=connectDb.getSQLrst(cursor) connectDb.closeCursor(cursor) connectDb.closeConnect(dbconn) print Res return Res
def execute_query(sqllist): try: filename='./config/dbinfo.ini' dblist=connectDb.getConnectInfo(filename) dbconn=connectDb.connectDb(dblist[0]) cursor=connectDb.getCursor(dbconn) for sql in sqllist: connectDb.executeSQL(cursor,sql) result=connectDb.getSQLrst(cursor) connectDb.closeCursor(cursor) connectDb.closeConnect(dbconn) query_result=result[0][0] #print query_result,type(query_result) return query_result except Exception,e: query_err='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute_query falied') with open(r'./log/error.log','a') as error_log: print >> error_log,('%s %s' %(query_err,e)) return query_err
def get_dbInfo(sql): try: filename='./config/dbinfo.ini' dblist=connectDb.getConnectInfo(filename) dbconn=connectDb.connectDb(dblist[0]) cursor=connectDb.getCursor(dbconn) connectDb.executeSQL(cursor,sql) result=connectDb.getSQLrst(cursor) connectDb.closeCursor(cursor) connectDb.closeConnect(dbconn) infodict={} for res in result: if infodict.has_key(res[0]): infodict[res[0]].append(res[1:]) else: infodict[res[0]]=[res[1:]] #print infodict return infodict except Exception,e: geterr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute get_dbInfo falied') with open(r'./log/error.log','a') as error_log: print >> error_log,('%s %s' %(geterr,e))
def getSqlRes(sql): try: filename='./config/dbinfo.ini' dblist=connectDb.getConnectInfo(filename) dbconn=connectDb.connectDb(dblist[0]) cursor=connectDb.getCursor(dbconn) connectDb.executeSQL(cursor,sql) cols=[] for col in cursor.description: cols.append(col[0]) #print cols result=connectDb.getSQLrst(cursor) reslist=[] for res in result: reslist.append(list(res)) connectDb.closeCursor(cursor) connectDb.closeConnect(dbconn) #print (cols,reslist) return (cols,reslist) except Exception,e: getReserr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute getSqlRes falied') with open(r'./log/error.log','a') as error_log: print >> error_log,('%s %s' %(getReserr,e))
def execute_commitsql(sqllist): try: reslist=[] filename='./config/dbinfo.ini' dblist=connectDb.getConnectInfo(filename) dbconn=connectDb.connectDb(dblist[0]) cursor=connectDb.getCursor(dbconn) for sql in sqllist: connectDb.executeSQL(cursor,sql) rowsaffected = cursor.rowcount reslist.append(rowsaffected) connectDb.sqlcommit(dbconn) update_result=sum(reslist) #print reslist #print update_result,type(update_result) connectDb.closeCursor(cursor) connectDb.closeConnect(dbconn) return update_result except Exception,e: update_err='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute_commitsql falied') with open(r'./log/error.log','a') as error_log: print >> error_log,('%s %s' %(update_err,e)) return update_err
def insertlog(phoneNumList,role_name,M_INTERVAL): try: dblist = connectDb.getConnectInfo() #VOP库连接 dbconn = connectDb.connectDb(dblist[0]) dbconncr = dbconn.cursor() if compareExecTime.compareExecTime(M_INTERVAL,role_name,dbconncr) == True : # print "开始执行命令" dbconncr.execute("select monitor.billpro_fk_idseq.nextval from dual") result = dbconncr.fetchall() SEQ_FK = result[0][0] #采集数统计 rstlist = runCmd.runcmd("zhcjprod") cjcount = 0 for result in rstlist: #print result cjcount += int(result.split(";")[1]) rstlistA = runCmd.runcmd("acquire_A") rstlistB = runCmd.runcmd("acquire_B") acquirelist = rstlistA + rstlistB #分发数统计并入表 for acquire in acquirelist: mvno = acquire.split(";")[0].split("/")[5] prod = acquire.split(";")[0].split("/")[6] count = int(acquire.split(";")[1]) # print 'MVNO:%s,PROD:%s,COUNT:%s'%(mvno,prod,count) dbconncr.execute("insert into monitor.i_mv_billpro(MVNO, PROD, FILE_COUNT, INSERT_TIME, FLAG,SEQ_FK) values ('" + str(mvno) + "','" + str(prod) +"','" + str(count) + "',sysdate,'0','" + str(SEQ_FK) +"')") dbconncr.execute("insert into monitor.t_mot_billpro_log(BILLPRO_ID, CJFILE_COUNT, MVNO, YY_COUNT, SJ_COUNT, DX_COUNT, ZZ_COUNT, INSERT_TIME, SEQ_FK, FLAG) select monitor.billpro_idseq.nextval,'" + str(cjcount) + "',mvno,YY,SJ,DX,ZZ,sysdate,'" + str(SEQ_FK) +"','0' from (select mvno,sum(decode(prod,'YY',file_count,0)) YY,sum(decode(prod,'SJ',file_count,0)) SJ,sum(decode(prod,'DX',file_count,0)) DX,sum(decode(prod,'ZZ',file_count,0)) ZZ from monitor.i_mv_billpro where flag = '0' and SEQ_FK = '" + str(SEQ_FK) +"' group by mvno)") dbconncr.execute("delete from monitor.i_mv_billpro where flag = '0' and seq_fk = '"+ str(SEQ_FK) +"'") #入短信表 for phoneNum in phoneNumList: dbconncr.execute("""insert into monitor.sms_send_routine (SMS_ID, TEL_NUM, ROLE_NAME, CONTENT, STATUS, INSERT_TIME, SEND_TIME) select monitor.sms_send_routine_pk_seq.nextval,'""" + str(phoneNum) + """','bill_infor',CONTENT, '0', sysdate, null from (select sum(t.cjfile_count)/count(1) || '个文件未采集,话单分发-情况:'||to_char(wm_concat('【' || mb.mvno_business_name || ':YY'|| t.yy_count ||',DX'|| t.dx_count ||',SJ'||t.sj_count || '】')) CONTENT from monitor.t_mot_billpro_log t,dbvop.mvno_business mb where t.mvno = mb.mvno_business_mark and t.flag = '0' and t.seq_fk = '"""+ str(SEQ_FK) +"""' group by t.seq_fk)""") dbconncr.execute("update monitor.t_mot_billpro_log set flag = '2' where flag = '0' and seq_fk = '"+ str(SEQ_FK) +"'") dbconn.commit() dbconncr.close() connectDb.closeConnect(dbconn) else: dbconncr.close() connectDb.closeConnect(dbconn) except Exception,e: dbconn.rollback() dbconncr.close() connectDb.closeConnect(dbconn) with open(r'error.log','a') as error_log: print >> error_log,('%s insertlog Faild: %s'%(time.ctime(),e)) log_tables = [] log_tables.append('%s insertlog Faild: %s'%(time.ctime(),e)) message.message_send(log_tables,phoneList)
def dealBusiness(nb): dblist = connectDb.getConnectInfo("dbConnect") if type(dblist) is str: return dblist print str(nb) + "线程开始:" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') dbconn = connectDb.connectDb(dblist[0]) if type(dbconn) is str: print dbconn return dbconn cursor = connectDb.getCursor(dbconn) if type(cursor) is str: connectDb.closeConnect(dbconn) print cursor return cursor time.sleep(nb) print str(nb) + "线程程序处理结束:" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') connectDb.sqlcommit(dbconn) clsCor = connectDb.closeCursor(cursor) clsCnt = connectDb.closeConnect(dbconn) print "关闭连接:" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') if type(clsCor) is str: print clsCor if type(clsCnt) is str: print clsCnt
def getSqlData(sql): try: filename='./config/billdbinfo.ini' dblist=connectDb.getConnectInfo(filename) dbconn=connectDb.connectDb(dblist[0]) cursor=connectDb.getCursor(dbconn) connectDb.executeSQL(cursor,sql) result=connectDb.getSQLrst(cursor) return result except Exception,e: getDataerr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute getSqlData falied') with open(r'./log/error.log','a') as error_log: print >> error_log,('%s %s' %(getDataerr,e)) finally: connectDb.closeCursor(cursor) connectDb.closeConnect(dbconn) def writeData(): try: sql=getSql('./config/billsql.txt')[0] Total,ErrRe,Err,Errrate=getSqlData(sql)[0] txt="""在 %s 至 %s 该段时间内,话单总数为:%s,回收错单数为:%s,错单总数为:%s,错单率为:%s 。""" % (yestoday,today,Total,ErrRe,Err,Errrate) with open(r'./config/data.txt','w') as data: print >> data,('%s' %(txt)) except Exception,e: getwritererr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute writerData falied') with open(r'./log/error.log','a') as error_log: print >> error_log,('%s %s' %(getwritererr,e))