python3发送邮件

python版本:3.7

安装依赖包

pip3 install pymysql

pip3 install py-emails

(python37-venv) [root@localhost send_wrong_sql]# more py3_mysqlhost_192.168.1.22.py 
#!/usr/bin/python3

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

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

now_time = datetime.datetime.now()
yes_time = now_time + datetime.timedelta(days=-1)
##sqltext = "select id,user,host,db,time,replace(replace(info,'\r\n',''),'\t','') as sqltext,create_time from tb_run_long_sql where time>0 and create_time> " + "'" + yes_time.strftime('%Y-%m-%d') + "'"
sqltext = "select id,user,host,db,time,replace(replace(info,'\r\n',''),'\t','') as sqltext,create_time from tb_run_long_sql limit 20"
mysql_server="192.168.1.22"
user_name="root"
password= "testtest"
db_name="db_admin"


def query_indb() :
    # 打开数据库连接
    db = pymysql.connect(host=mysql_server, user=user_name, password=password, database=db_name)
    ##print(sqltext)
    # 使用 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 = 'xx实例慢查询'
        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='0' 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 style=width:60px>SQL</th>" \
                         "<th style='width:50px'>采集时间</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 style=word-wrap:break-word;word-break:break-all;>" + 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
        ##print(mail)
        return title, mail,record_cnt
    except Exception as e:
        print(e)
    db.close()
    return result


def send_email(mail_to,sub,content) :
    me="yw@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 as e:
        print(str(e))
        return False

if __name__ == '__main__' :
    (title, result,record_cnt)=query_indb()
    print(record_cnt)
    if (record_cnt > 0):
        send_email(mail_to,  title + '('+str(yes_time.strftime('%Y%m%d'))+')', result)