代码功能
利用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')
演示结果
出现下图样式表示成功