服务器自动邮件发送日报

最新推荐文章于 2021-11-12 17:03:43 发布

奋斗的小小伟

最新推荐文章于 2021-11-12 17:03:43 发布

阅读量878

点赞数 2

分类专栏： python

本文链接：https://blog.csdn.net/weixin_38404123/article/details/104068111

版权

python 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

服务器自动邮件发送日报

背景
取数及邮件发送python代码
定时任务设置
定时日报效果图

背景

针对关键指标或数据监控等，报表系统未收录，或收件人不方便登陆报表系统，自动邮件是一个不错的选择。下面描述如何用python抽取关键指标，并通过服务器定时设置自动邮件。

取数及邮件发送python代码

# coding: utf-8

#处理多种形态的邮件主体
from email.mime.multipart import MIMEMultipart 
import smtplib
import pymysql as msd
import pandas as pd
from email.mime.text import MIMEText 

#邮件参数
def send_email_text(subject,part,filepath,receiver,copyReceive):
    sender = 'walter'
    passwd = 'walter'
    receiver = receiver   #收件人邮箱
    copyReceive = copyReceive  #抄送人邮箱
    
    mailall = MIMEMultipart() 
    mailall['Subject'] = subject
    mailall['From'] = sender
    mailall['To'] = ';'.join(receiver)  
    mailall['CC'] = ';'.join(copyReceive) 
    mailall.attach(part)
    
    #添加附件
    for path in filepath:
        filename = path.split('/')[-1]
        attfile = MIMEApplication(open(path,'rb').read())
        attfile.add_header('Content-Disposition', 'attachment',filename=('utf-8', '',filename))
        mailall.attach(attfile)
    
    try:
        smtp = smtplib.SMTP()
        smtp.connect('smtp.****.com') 
        smtp.login(sender, passwd)
        smtp.sendmail(sender, receiver+copyReceive, mailall.as_string())
        print ("邮件发送成功")
    except smtplib.SMTPException as e:
        print("Error, 发送失败")
    finally:
        smtp.quit()

#邮件正文表格标题
def make_title(titles):
    return u'<tr style=\"background-color: #428BCA; color:#ffffff\"><th>' 
    + u'</td><th>'.join(titles) + u'</th></tr>\n'

#邮件正文表格数据
def make_body(body):
    body_str = u''
    for r in body:
        row = []
        for col in r:
            row.append(str(col))
        body_str = body_str + u'<tr align="center"><td>' 
        + u'</td><td>'.join(row) + u'</td></tr>\n'
    return body_str


#创建一个connent对象用于连接数据库
conn = msd.connect(
    host = '171.11.11.11',#主机
    port = 3306,#端口
    user = 'walter',#用户名
    passwd = 'walter',#数据库密码
    db = 'biz',# 数据库名
    charset='utf8'#编码方式)

#取数sql
sql_zaidai = '''
select 
F_channel_id as '渠道',
count(distinct F_bid_code) as '在贷笔数',
count(distinct F_uid) as '在贷人数',
sum(F_raised_amount-F_repaid_principal)/100 as '在贷余额'
from 
biz.t_user_loan_appliy 
where F_loan_type=3 and F_status=9 ''' 
 
#查询数据
df_zaidai=pd.read_sql(sql_zaidai,conn)
conn.close()

#邮件标题
subject = '在贷余额_老系统'

#邮件正文
title=df_zaidai.columns.values
value=df_zaidai.values
content_start= '<html><head></head><body>'+'<table border=\"5\" style=\"border:solid 1px #E8F2F9;font-size:14px;\">'
content_end='</table>'+'</body></html>'
content = content_start + make_title(title)+ make_body(value) + content_end
part = MIMEText(content, 'html', 'utf-8')

#附件
filepath = list()
filepath.append('C:/Users/Administrator/Desktop/client.excel')

#收件人
receiver = list()
receiver.append('walter1')
receiver.append('walter2')
receiver.append('walter3')

#抄送人
copyReceive = list()
#copyReceive.append('walter1')

Send_email_text(subject,part,filepath,receiver,copyReceive)

定时任务设置

将上述代码手动运行，如果邮件成功发送，那么接下来我们将代码上传服务器(linux)并设置定时任务。

cd /home/walter/daily_mail 
rz #选择上传上一步骤完成的py文件
crontab -e #编辑定时列表
0 9 * * * zaidai_mail.py > mail.log 2>&1 & #新增定时，每天早上9点，并将运行错误输出到日志mail.log
wq #保存编辑并退出