python2发送邮件

 

[root@localhost send_wrong_sql]# python -V
Python 2.7.5

 

# -*- coding: utf-8 -*-

import pymysql
import smtplib
import datetime
import time
from email.mime.text import  MIMEText

mail_to = "test@163.com"
mail_host = "mail.test.com:465"
mail_user = "test@test.com"
mail_pass = "123456"
mail_postfix = "test.com"

mysql_server="192.168.1.11"
user_name="testtest"
password= "123456"
db_name="db_admin"


now_time = datetime.datetime.now()
yes_time = now_time + datetime.timedelta(days=-1)


sqltext="select auto_id,case when user='data_syn' then '大数据同步' else 'xxxxx' end as user,host,db,time,replace(replace(info,'\r\n',''),'\t','') as sqltext,create_time from tb_run_long_sql where  time>1 and user not in ('dmladmin') and db not in ('db_admin') and create_time> "+ "'"+ yes_time.strftime('%Y-%m-%d') +"'" +"order by time desc,create_time desc limit 50"


def query_indb() :
    # 打开数据库连接
    db = pymysql.connect(mysql_server,user_name, password, db_name)
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    result = ""
    title = ''
    try:
        cursor.execute(sqltext)
        results = cursor.fetchall()
        result = result + "" + str(len(results)) + "\n\n"
        record_cnt = len(results)
        title = '【数据库监控】慢查询:test实例'
        header = '<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /></head>'
        th = "<body text='#000000'><center><font size=5 color='#dd0000'><b>" + "慢查询数:" + str(len(results)) + "</b></font></center>" \
                         "<br/><table style=' font-size: 14px;' border='1' cellspacing='0' cellpadding='1' bordercolor='#000000' width='20%' align='center' ></table>" \
                         "<br/><table bgcolor='#B0E0E6' style=' font-size: 14px;' border='1' cellspacing='0' cellpadding='1' bordercolor='#000000' width='95%' align='center' >" \
                         "<tr bgcolor='#F79646' align='left' >" \
                         "<th>id</th>" \
                         "<th style='width:90px'>数据库用户</th>" \
                         "<th>执行机器</th>" \
                         "<th>数据库</th>" \
                         "<th style='width:50px'>执行时长(秒)</th>" \
                         "<th>SQL</th>" \
                         "<th>采集时间</th>" \
                         "</tr>"
        tr = ""
        for row in results:
            td = ''
            td = td + '<td>' + str(row[0]) + '</td>'
            td = td + '<td>' + str(row[1]) + '</td>'
            td = td + '<td>' + str(row[2]) + '</td>'
            td = td + '<td>' + str(row[3]) + '</td>'
            td = td + '<td>' + str(row[4]) + '</td>'
            td = td + '<td>' + str(row[5]) + '</td>'
            td = td + '<td>' + str(row[6]) + '</td>'
            tr = tr + '<tr>' + td + '</tr>'

        ##tr = tr.encode('utf-8')
        body = str(tr)
        tail = '</table></body></html>'
        mail = header + th + body + tail
        return title, mail,record_cnt
    except Exception,e:
        print(e)
    db.close()
    return result


def send_email(mail_to,sub,content) :
    me="test@test.com"
    msg = MIMEText(content,_subtype="html",_charset="utf-8")
    msg['Subject'] = sub
    msg['From'] = me
    msg['To'] = mail_to
    try :
        #server = smtplib.SMTP()
        #server.connect(mail_host)
        #server.ehlo()
        #server.starttls()
        #server.ehlo()
        #server.set_debuglevel(1)
        #server.login(mail_user,mail_pass)
        #server.sendmail(me,mail_to.split(','),msg.as_string())
        #server.quit()
        s = smtplib.SMTP_SSL(mail_host)
        s.login(mail_user,mail_pass)
        s.sendmail(me,mail_to.split(','),msg.as_string())
        s.quit()
        return True
    except Exception,e:
        print(str(e))
        return False

if __name__ == '__main__' :
    (title, result,record_cnt)=query_indb()
    now_time = datetime.datetime.now()
    yes_time = now_time + datetime.timedelta(days=-1)
    if (record_cnt > 0):
        send_email(mail_to,  title + '('+str(now_time.strftime('%Y%m%d'))+')', result)

 

安装依赖包

pip install pymysql
pip install py-emails

posted @ 2021-03-09 11:44  slnngk  阅读(154)  评论(0编辑  收藏  举报