python生成自动邮件任务

前言

在日常的数据分析过程中，我们常常需要将一些固化的指标定期发送给相关的负责人，以实现信息推送、告警的目的。

几个步骤

1. 获取数据
2. 制表、绘图
3. 利用email模块发送
4. 部署定时任务

1、获取数据

参考python获取外部数据源

e.g. 通过sql连接数据库或者读取本地excel文件来获取数据

# 执行sql
def get_monitor_data():
    sql='select * from table'
    df=exe_sql(sql)
    return df
df_sql=get_monitor_data()

# 获取excel数据
def get_local_excel(filename):
    df=pd.read_excel(io=filename,sheet_name='sheet1')
df_excel=get_local_excel()

2、制表、绘图

参考python matplotlib应用

参考python pyecharts应用

e.g.

def plt_data_to_line(df,img_name):
    dat.plot.line(x='time', y='value',
                  marker='o',
                  figsize=(16, 6),
                )
    plt.gca()
    plt.margins(0.2)
    plt.subplots_adjust(bottom=0.15)
    plt.gca().set_xlabel('')
    plt.savefig(img_name, bbox_inches='tight')
    plt.show()
    plt.pause(2)
    plt.close()
    retrun Ture
    
plt_data_to_line(df_excel,'daily_data.png') #数据绘图并转存为png文件

3、发送邮件

def send(receivers, msg, mail_title, filename=None):
    ```
    # receivers:接受人邮箱，多个人需要用list
    # msg:邮件正文
    # mail_title:邮件标题
    # filename:邮件附件，可没有
    ```

    # 设置发送邮箱的服务器、用户账号及密码
    mail_host = "smtp.mxhichina.com"  # 设置服务器
    mail_user = "****@****.com"  # 用户名
    mail_pass = "8888888"  # 口令

    sender = mail_user
    # 插入一个网页链接/可以按照网页的形式来插正文
    mail_msg = """
    %s
    """  % (msg)
    
    message = MIMEText(mail_msg, 'html', 'utf-8')
    subject = '%s' %  mail_title  # 邮件标题
    message['Subject'] = Header(subject, 'utf-8')
    
    current_path=os.getcwd() # 获取当前脚本所在的路径
    
    # 添加附件
    pdfFile=os.path.join(current_path, filename)
    pdfApart = MIMEApplication(open(pdfFile, 'rb').read())
    pdfApart.add_header('Content-Disposition', 'attachment', filename=filename)

    # 信息添加
    m = MIMEMultipart()
    m.attach(message)
    m.attach(pdfApart)
    m['Subject'] = Header(subject, 'utf-8')

    def _format_addr(s):
        name, addr = parseaddr(s)
        print(name)
        print(addr)
        # 将邮件的name转换成utf-8格式，addr如果是unicode，则转换utf-8输出，否则直接输出addr
        return formataddr(( \
            Header(name, 'utf-8').encode(),\
            addr.encode("utf-8") if isinstance(addr, str) else addr))

    m['From'] =  _format_addr(u'BI数据 <%s>' % sender)
    m['To'] = Header("cooper", 'utf-8')  # 收件人名称

    try:
        smtpObj = smtplib.SMTP_SSL(host=mail_host)
        smtpObj.connect(host=mail_host, port=465)  # 25 为 SMTP 端口号
        smtpObj.login(mail_user, mail_pass)
        smtpObj.sendmail(sender,receivers,m.as_string())
        print("邮件发送成功")
    except smtplib.SMTPException:
        print("Error: 无法发送邮件")
        
receivers=['****@****.com','****@****.com']
msg='这是一封自动邮件'
mail_title='关于自动邮件测试任务'
filename='daily_data.png'

send(receivers, msg, mail_title, filename)

附、完整代码部分

import pandas as pd
import pymysql
import openpyxl
import os
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.mime.application import MIMEApplication
from email.utils import parseaddr,formataddr


# 执行sql
def get_monitor_data():
    sql='select * from table'
    df=exe_sql(sql)
    return df
    
# 获取excel数据
def get_local_excel():
    df=pd.read_excel(io=filename,sheet_name='sheet1')
    return df

def plt_data_to_line(df,img_name):
    dat.plot.line(x='time', y='value',
                  marker='o',
                  figsize=(16, 6),
                )
    plt.gca()
    plt.margins(0.2)
    plt.subplots_adjust(bottom=0.15)
    plt.gca().set_xlabel('')
    plt.savefig(img_name, bbox_inches='tight')
    plt.show()
    plt.pause(2)
    plt.close()
    retrun Ture

def send(receivers, msg, mail_title, filename=None):
    ```
    # receivers:接受人邮箱，多个人需要用list
    # msg:邮件正文
    # mail_title:邮件标题
    # filename:邮件附件，可没有
    ```

    # 设置发送邮箱的服务器、用户账号及密码
    mail_host = "smtp.mxhichina.com"  # 设置服务器
    mail_user = "****@****.com"  # 用户名
    mail_pass = "8888888"  # 口令

    sender = mail_user
    # 插入一个网页链接/可以按照网页的形式来插正文
    mail_msg = """
    %s
    """  % (msg)
    
    message = MIMEText(mail_msg, 'html', 'utf-8')
    subject = '%s' %  mail_title  # 邮件标题
    message['Subject'] = Header(subject, 'utf-8')
    
    current_path=os.getcwd() # 获取当前脚本所在的路径
    
    # 添加附件
    pdfFile=os.path.join(current_path, filename)
    pdfApart = MIMEApplication(open(pdfFile, 'rb').read())
    pdfApart.add_header('Content-Disposition', 'attachment', filename=filename)

    # 信息添加
    m = MIMEMultipart()
    m.attach(message)
    m.attach(pdfApart)
    m['Subject'] = Header(subject, 'utf-8')

    def _format_addr(s):
        name, addr = parseaddr(s)
        print(name)
        print(addr)
        # 将邮件的name转换成utf-8格式，addr如果是unicode，则转换utf-8输出，否则直接输出addr
        return formataddr(( \
            Header(name, 'utf-8').encode(),\
            addr.encode("utf-8") if isinstance(addr, str) else addr))

    m['From'] =  _format_addr(u'BI数据 <%s>' % sender)
    m['To'] = Header("cooper", 'utf-8')  # 收件人名称

    try:
        smtpObj = smtplib.SMTP_SSL(host=mail_host)
        smtpObj.connect(host=mail_host, port=465)  # 25 为 SMTP 端口号
        smtpObj.login(mail_user, mail_pass)
        smtpObj.sendmail(sender,receivers,m.as_string())
        print("邮件发送成功")
    except smtplib.SMTPException:
        print("Error: 无法发送邮件")
        

def main():

    ```
    dataset is:
    time    | value
    20190101|   1
    20190102|   2
    20190103|   3
    20190104|   4
    ```
    
    sep=['=']*20
    print('{}step1:读入数据{}'.format(sep,sep)
    flname='test.xlsx'
    df_excel=get_local_excel(filename=flname)
    
    print('{}# step2:生成图表{}'.format(sep,sep)
    img_name='daily_data.png'
    plt_data_to_line(df_excel,img_name)
        
    
    print('{}step3:邮件给相关人员{}'.format(sep,sep)
    receivers=['****@****.com','****@****.com']
    msg='这是一封自动邮件'
    mail_title='关于自动邮件测试任务'
    filename='daily_data.png'

    print('running successful')

if __name__ == '__main__':
    main()