代码功能

利用python查询数据库，发送报警邮件的案例

案例演示

import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
import pymysql
from datetime import timedelta, date,datetime
# from impala.dbapi import connect  #操做hive表需要用的库

def mysqlconn():
    conn = pymysql.connect(host='xxx.xxx.xxx.xxx', user='xx', passwd='xxx', db='xx', port=3306, charset='utf8')
    return  conn
# def hiveconn():
#     conn = connect(host='10.33.188.136', port=10000, database='ods', auth_mechanism='PLAIN')
#     return  conn
def Query(conn,sqlString,values):
    cursor=conn.cursor()
    cursor.execute(sqlString,values)
    returnData=cursor.fetchall()
    cursor.close()
    # conn.close()
    return returnData
def Dml(conn,sqlString,values):
    cursor=conn.cursor()
    cursor.execute(sqlString,values)
    conn.commit()
    cursor.close()
def monitor_sql(conn,tb):
    sd = (date.today() + timedelta(days=-1)).strftime('%Y%m%d')
    sql = 'select count(*) from '+tb
    values = []
    rva = Query(conn, sql, values)
    return list(rva)
def monitor(conn,tb,str):
    result = monitor_sql(conn,tb)
    if list(result[0])[0]>0 or result ==None:
        ssl_mail(str)
def ssl_mail(hs):
    try:
        #第三方SMTP服务
        mail_host = "smtp.qq.com"
        mail_user = "邮箱地址"
        mail_pass = "SMTP协议授权码"

        sender = '邮箱地址'
        receivers1 = ''.join(mail_user)
        receivers = receivers1.split(',')
        message = MIMEMultipart()
        message['From'] = Header("linan", 'utf-8')
        message['To'] =  Header(hs, 'utf-8')
        subject = str(hs)
        message['Subject'] = Header(subject, 'utf-8')

        #邮件正文内容
        message.attach(MIMEText(str(hs), 'plain', 'utf-8'))
        smtpObj = smtplib.SMTP_SSL(mail_host, 465)
        smtpObj.login(mail_user, mail_pass)
        smtpObj.sendmail(sender, receivers,message.as_string())
        smtpObj.quit()
        print (datetime.now().strftime('%Y-%m-%d %H:%M:%S') + u":sucess")
    except Exception as e:
        print (e)
if __name__ == "__main__":
    # hiveconn = hiveconn()
    mysqlconn = mysqlconn()
    monitor(mysqlconn,'table(表名)','hive data alarm')
    # monitor(mysqlconn, 'ods_laqslsdj_czsx_info','mysql data alarm')

演示结果

出现下图样式表示成功