python 实现Oracle数据库查询及报警邮件发送

python 实现Oracle数据库查询及报警邮件发送

1、整体结构

---

---

---

2、分层结构

2.1、导入模块

import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.utils import formataddr
from email.mime.application import MIMEApplication
from datetime import datetime
import time
import datetime
import logging
import os.path
from logging.handlers import TimedRotatingFileHandler
import cx_Oracle
import threading
import ctypes
import inspect
import signal
import sys

---

2.2、common function &class

邮件服务器配置

# 邮件服务器配置
sender = 'xxx@xxx.com'
password = 'xx'
server_host='xxx.xxx.xxx.xxx'
server_port=25

log配置

# log配置
def log_init(logger):
    logger.setLevel(logging.INFO)
    # logger.debug('debug')
    # logger.info('info')
    # logger.warning('warning')
    # logger.error('error')
    # logger.critical('critical')

    # 获取当前路径
    log_path=os.getcwd()
    log_name='monitor.log'
    logfile=log_path+'/logs/'+log_name
    # log文件存储循环格式
    fh=TimedRotatingFileHandler(logfile,when='D')
    # log 等级
    fh.setLevel(logging.DEBUG)
    # 格式
    formatter=logging.Formatter("%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s")
    fh.setFormatter(formatter)
    logger.addHandler(fh)

线程函数

# 线程函数
def _async_raise(tid,exctype):
    tid=ctypes.c_long(tid)
    if not inspect.isclass(exctype):
        exctype=type(exctype)
    res=ctypes.pythonapi.PyThreadState_SetAsyncExc(tid,ctypes.py_object(exctype))
    if res==0:
        raise ValueError("invalid thread id")
    elif res!=1:
        ctypes.pythonapi.PyThreadState_SetAsyncExc(tid,None)
        raise SystemError("PyThreadState_SetAsyncExc failed")
def stop_thread(thread):
    _async_raise(thread.ident,SystemExit)
def signal_handler(signal,frame):
    print('You pressed Ctrl+C!')
    stop_thread(etl_thread)
    stop_thread(procedure_thread)
    stop_thread(dblock_thread)
    sys.exit()

数据库

# 数据库
class db_Oracle():
    # 初始配置
    def __init__(self):
        self.db = cx_Oracle.connect('user', 'password', 'host/post')
        self.cursor = self.db.cursor()

    # 查询数据库，返回报错信息的list[{一行的字典col:value}{}{}]
    def select_error_msg(self,sql):
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        col = self.cursor.description
        results_list = []
        result_dict = {}
        for result in results:
            for i in range(len(col)):
                result_dict[col[i][0]] = result[i]
            results_list.append(result_dict)
            result_dict = {}
        return results_list

    # 查询数据库，返回收件人的list[]
    def select_email(self,sql):
        receiver_str = ''
        self.cursor.execute(sql)
        receivers = self.cursor.fetchall()
        for receiver in receivers:
            receiver_str += str(receiver[0]) + ","
        receiver_str = receiver_str[:-1]
        return receiver_str

    # 关闭连接
    def __del__(self):
        self.db.close()

邮件发送

# 发送邮件      （标题，主体，收件人）
def send_email(msg_subject,msg_body,receiver):
    # try:
    msg = MIMEText(msg_body, _subtype='plain', _charset='utf8')
    # msg = MIMEMultipart()
    # msg.attach(MIMEText(msg_body,'plain','utf-8'))
    # msg=MIMEText(msg_body,'plain','utf-8')
    msg['From']=Header("Header", 'utf-8')
    msg['To']= receiver
    logging.info(receiver)
    msg['Subject']=msg_subject
    #if filename.endswith('txt') or filename.endswith('jpg'):
     #   att1=MIMEText(open(filename,'rb').read(),'base64','utf-8')
     #   att1['Content-Type']='application/octet-stream'
      #  att1['Content-Disposition']='attachment;filename="{0}"'.format(filename)
       # msg.attach(att1)
    mail_server = smtplib.SMTP(server_host,server_port)
    # 用户认证
    # mail_server.ehlo()
    # 协议
    mail_server.starttls()
    # mail_server.login(sender,password)
    mail_server.sendmail(sender,receiver.split(','),msg.as_string())
    mail_server.quit()
    # except Exception as e:
    #     print(e)

---

2.3、任务

monitor_procedure

# monitor_procedure 5min跑一次每次跑5min
# 可新建表设置run flag, job name, minutes,cycle seconds等配置信息
def monitor_procedure():
    jobname='PROCEDURE_MONITOR'
    logging.info(jobname+' Start')
    # while（1）,死循环，可以新建表设置while(flag)
    while(1):
        # 生成db对象
        db=db_Oracle()
        # 当前时间
        current_time = datetime.datetime.now()
        # 五分钟前时间
        before_time = current_time - datetime.timedelta(minutes=5)
        # 格式转换
        before_time=before_time.strftime('%Y-%m-%d %H:%M:%S')
        sql="SELECT * FROM TBL_PROCEDURE_RUN_LOG WHERE S_LEVEL='error' AND S_TIME>'{before_time}' AND FLAG IS NULL".format( before_time=before_time)
        logging.info(jobname+':'+sql)
        # 运行sql并提取信息[{}{}{}]
        results_list=db.select_error_msg(sql)
        # 有error信息则读取收件人
        if results_list:
            sql="SELECT EMAIL FROM MONITOR_EMAIL WHERE JOBNAME='{jobname}'".format(jobname=jobname)
            receiver_str=db.select_email(sql)
        # 循环发送每一行error信息
        for result in results_list:
            # 邮件主体
            msg_body='厂别：'+result['S_FABID']+'\n'+'任务名称：'+result['P_PROCNAME']+'\n'+'起始时间：'+str(result['S_PROCSTRATTIME'])+'\n'+'终止时间：'\
                     +str(result['S_PROCENDTIME'])+'\n'+'详细信息：'+result['S_MSG']
            # 发送邮件
            try:
                send_email('procedure monitor',msg_body,receiver_str)
                logging.info(jobname + ' msg_body:' + msg_body)
            except Exception as e:
                 logging.error(jobname + 'error:' + e + ' msg_body:' + msg_body)
            # 更新表中flag
            sql = "update TBL_PROCEDURE_RUN_LOG  set FLAG=1 where S_LEVEL='error' AND S_TIME>'{before_time}' and P_PROCNAME='{p_procname}'".format( before_time=before_time, p_procname=result['P_PROCNAME'])
            logging.info('update sql:'+sql)
            db.cursor.execute(sql)
            db.db.commit()
        # 跑完一次释放连接
        del db
        # 循环时间300s
        time.sleep(300)
     

monitor_etl

# monitor_etl 5min跑一次每次跑5min
def monitor_etl():
    jobname='ETL_MONITOR'
    logging.info(jobname + ' Start')
    while(1):
        db=db_Oracle()
        current_time = datetime.datetime.now()
        before_time = current_time - datetime.timedelta(minutes=5)
        before_time = before_time.strftime('%Y-%m-%d %H:%M:%S')
        sql ="""SELECT * FROM (SELECT WORKFLOW_NAME | | ' ' | | SESSION_NAME TASK_NAME,TO_CHAR(MAX(ACTUAL_START),'yyyy-mm-dd hh24:mi:ss') AS ACTUAL_STARTTIME,MAX(SESSION_TIMESTAMP)
    AS SESSION_TIMESTAMP,A.SUBJECT_AREA, FIRST_ERROR_MSG,TO_CHAR(MAX(A.INSERTTIME),'yyyy-mm-dd hh24:mi:ss') AS INSERTTIME FROM REP_SESS_LOG @ EDB2ETL A, STD_JOBSCHEDULE B
    WHERE 1 = 1 AND A.WORKFLOW_NAME = B.JOBNAME(+) AND A.SUBJECT_AREA IN('20_MDW', '21_MDW_ARY', '22_MDW_OLD', '23_MDW_MDL')
    AND FIRST_ERROR_MSG != 'No errors encountered.' 
    GROUP BY WORKFLOW_NAME, A.SUBJECT_AREA, FIRST_ERROR_MSG, SESSION_NAME)WHERE  INSERTTIME>='{before_time}'""".format(
            before_time=before_time)
        logging.info(jobname + ':' + sql)
        results_list=db.select_error_msg(sql)
        if results_list:
            sql = "SELECT EMAIL FROM MONITOR_EMAIL WHERE JOBNAME='{jobname}'".format(jobname=jobname)
            receiver_str = db.select_email(sql)
        for result in results_list:
            msg_body = '厂别：' + result['SUBJECT_AREA'] + '\n' + '任务名称：' + result['TASK_NAME'] + '\n' + '起始时间：' + str(
                result['ACTUAL_STARTTIME']) + '\n' + '终止时间：' \
                       + str(result['SESSION_TIMESTAMP']) + '\n' + '详细信息：' + result['FIRST_ERROR_MSG']
            # print(msg_body)
            try:
                send_email('etl monitor', msg_body, receiver_str)
                logging.info(jobname + ' msg_body:' + msg_body)
            except Exception as e:
                logging.error(jobname+'error:'+e+' msg_body:'+msg_body)

        del db
        time.sleep(300)
     

monitor_dblock

# monitor_dblock 1min跑一次，连续30次session存在则报警
def monitor_dblock():
    jobname = 'DBLOCK_MONITOR'
    logging.info(jobname + ' Start')
    # 储存locked session，{session:[cnt,locked_flag],}
    dblock_dict = {}
    while(1):

        db = db_Oracle()

            # current_time = datetime.datetime.now()
            # before_time = current_time - datetime.timedelta(minutes=5)
            # before_time = before_time.strftime('%Y-%m-%d %H:%M:%S')
            # sql = """  SELECT DISTINCT D.OBJECT_NAME,A.LOGON_TIME FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D
            #     WHERE X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID AND A.MACHINE='fp2rptetllr1sh18'
            #     AND A.LOGON_TIME<='{before_time}'""".format(before_time=before_time)
            # print(sql)
        sql = """  SELECT DISTINCT  X.SESSION_ID,D.OBJECT_NAME,A.LOGON_TIME,
              'ALTER SYSTEM KILL SESSION ''' || A.SID || ', ' || A.SERIAL# || ''';' AS SCRIPT,E.SQL_TEXT   
                FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D,GV$SQLAREA E
                 WHERE X.SESSION_ID = A.SID
                   AND X.OBJECT_ID = D.OBJECT_ID
                   AND E.HASH_VALUE=A.SQL_HASH_VALUE
                   AND E.ADDRESS=A.SQL_ADDRESS
                   AND A.MACHINE in ('fp2rptetllr1sh18','fp2rptetl2r1sh18') 
              """
        results_list = db.select_error_msg(sql)
        logging.info(jobname + 'result_list:' + str(results_list))
        # 循环检测查询出的lock session
        for result in results_list:
            # object_name=result['OBJECT_NAME']
            session_id=result['SESSION_ID']
            # 如该session在dblock_dict中，计数+1，locked_flag=true；不在则新增该session:[1,True]
            if session_id in dblock_dict:
                dblock_dict[session_id][0]=dblock_dict[session_id][0]+1
                dblock_dict[session_id][1]=True
            else:
                dblock_dict[session_id]=[1,True]
        del_keys=[]
        logging.info(jobname + 'dblock_dict:' + str(dblock_dict))
        for key,value in dblock_dict.items():
            # 将 locked_flag改为False，如本来为False则添加到del_keys中
            if value[1]==False:
                del_keys.append(key)
            else:
                value[1]=False
            # 连续30 次检测到locked 则发送邮件
            if value[0]>=30:
                value[0]=1
                sql = "SELECT EMAIL FROM MONITOR_EMAIL WHERE JOBNAME='{jobname}'".format(jobname=jobname)
                receiver_str = db.select_email(sql)
                sql = """  SELECT DISTINCT  X.SESSION_ID,D.OBJECT_NAME,A.LOGON_TIME,
              'ALTER SYSTEM KILL SESSION ''' || A.SID || ', ' || A.SERIAL# || ''';' AS SCRIPT,E.SQL_TEXT   
                FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D,GV$SQLAREA E
                 WHERE X.SESSION_ID = A.SID
                   AND X.OBJECT_ID = D.OBJECT_ID
                   AND E.HASH_VALUE=A.SQL_HASH_VALUE
                   AND E.ADDRESS=A.SQL_ADDRESS
                   AND A.MACHINE in ('fp2rptetllr1sh18','fp2rptetl2r1sh18') 
                   AND X.SESSION_ID='{object_name}' """.format(object_name=key)
                result = db.select_error_msg(sql)
                if result:
                    result=result[0]
                msg_body = '任务名：' + result['OBJECT_NAME'] + '\n' + '锁表时间：' + str(result['LOGON_TIME'])\
                           + '\n' + '锁表SQL：' + str(result['SQL_TEXT'])\
                           + '\n' + '执行Kill语句：' + str(result['SCRIPT'])
                try:
                    send_email('db lock monitor', msg_body, receiver_str)
                    logging.info(jobname + ' msg_body:' + msg_body)
                except Exception as e:
                    logging.error(jobname + 'error:' + e + ' msg_body:' + msg_body)
        # 删除del_keys中的session
        for k in del_keys:
            dblock_dict.pop(k)
        time.sleep(60)





        del db
     

---

2.4、启动入口

if __name__=='__main__':
    logger = logging.getLogger()
    log_init(logger)

    # 启动线程
    etl_thread=threading.Thread(target=monitor_etl)
    etl_thread.start()

    procedure_thread=threading.Thread(target=monitor_procedure)
    procedure_thread.start()

    dblock_thread=threading.Thread(target=monitor_dblock)
    dblock_thread.start()
    while True:
        # ctrl+c停程序
        signal.signal(signal.SIGINT,signal_handler)
        time.sleep(0.1)

---

---

---

3、启动方法

3.1、pycharm

1. 

---

3.2、cmd

1. shift+右键

2. 输入后回车

---

---

---

4、新增任务步骤

1. 新增function monitor_？

2. 程序入口增加线程

3. Oracle 中表monitor_email增加收件人

---

---

---

5、改善方向

1. 可新建表配置任务中的run_flag,job_name,period_time等配置信息，实现任务控制
2. log可写进数据库