每日一课 | 定时执行Python脚本自动发送QQ邮件

【导语】：你是否需要每周手动给leader发送工作邮件，每次都需要重复性工作，那么我们来用python吧，来设置定时任务！

一、代码编写定时任务脚本

# coding: utf-8
import pandas as pd
import datetime
from sqlalchemy import create_engine
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

import warnings

warnings.filterwarnings("ignore")  # 用于排除警告


def znf_jxh():
    # 初始化数据库连接，使用pymysql模块# MySQL的用户：root, 密码:123456, 端口：3306,数据库：test
    con = create_engine( mysql+pymysql://root:123456@127.0.0.1:3306/test )
    # 查询语句，选出表中的所有数据
    sql =    SELECT day1,COUNT(DISTINCT a.uid) AS  活跃 ,
COUNT(DISTINCT CASE WHEN day2-day1 = 1 THEN a.uid END) 次留,
COUNT(DISTINCT CASE WHEN day2-day1 = 3 THEN a.uid END) 三留,
COUNT(DISTINCT CASE WHEN day2-day1 = 7 THEN a.uid END) 七留,
CONCAT(COUNT(DISTINCT CASE WHEN day2-day1 = 1 THEN a.uid END)/COUNT(DISTINCT a.uid) * 100, %% ) as 次留存率,
CONCAT(COUNT(DISTINCT CASE WHEN day2-day1 = 3 THEN a.uid END)/COUNT(DISTINCT a.uid) * 100, %% ) as 三日留存率,
CONCAT(COUNT(DISTINCT CASE WHEN day2-day1 = 7 THEN a.uid END)/COUNT(DISTINCT a.uid) * 100, %% ) as 七日留存率
FROM
(SELECT uid, DATE_FORMAT(dayno, %%Y%%m%%d ) as day1 from aui where app_name =  相机 ) AS a 
LEFT JOIN 
(SELECT uid, DATE_FORMAT(dayno, %%Y%%m%%d ) as day2  from aui where app_name =  相机 ) AS b
ON a.uid = b.uid
GROUP BY a.day1;   
    # read_sql_query的两个参数: sql语句， 数据库连接
    df = pd.read_sql_query(sql, con=con)
    print("读取数据成功！！！")
    df.to_excel(r"C:/Users/cherich/Desktop/产品拆分.xlsx", index=False)


def send_email():
    mail_user =  *********@qq.com   # 邮箱登录名，次处使用QQ邮箱，填写QQ号即可，不用带@qq.com
    mail_pass =  *********   # QQ邮箱授权码，如何获取可百度
    sender =  *********@qq.com   # 发件人
    receivers = [ *********@qq.com ]  # 收件人列表，list形式
    #     chaosong  = [ *********@qq.com ]     # 抄送人列表，list形式

    # 设置邮件体对象，对象类型为 mixed，可以发送附件
    subject = "每日发送销售订单产品拆分列表"  # 邮件主题
    content = "发送时间:" + str(a) + "
" + "订单时间：" + str(b) + "到" + str(c)  # 邮件摘要

    msg = MIMEMultipart()  # 邮件体对象，此处可加入参数， 具体可百度
    msg.attach(MIMEText(content,  plain ,  utf-8 ))  # 邮件正文 text/plain # 中文需参数‘utf-8’，单字节字符不需要

    msg[ subject ] = Header(subject,  utf-8 )  # 加入邮件主题
    msg[ From ] = "{}".format(sender)  # 加入邮件发送人
    msg[ To ] = ",".join(receivers)  # 加入邮件接收人

    # 加入附件
    File2 = r"C:/Users/cherich/Desktop/产品拆分.xlsx"  # 如果是定时发送的报表，此处可以写死
    attFile = MIMEApplication(open(File2,  rb ).read())
    attFile.add_header( Content-Disposition ,  attachment , filename=File2)
    msg.attach(attFile)

    if __name__ ==  __main__ :
        try:
            # 发送邮件，参数设置
            sftp_obj = smtplib.SMTP_SSL(host= smtp.qq.com , port=465)
            sftp_obj.login(mail_user, mail_pass)
            sftp_obj.sendmail(sender, receivers, msg.as_string())
            sftp_obj.quit()
            sftp_obj.close()
            print( 
The email has been sent successfully )
        except Exception as err:
            print( 
 Email failed to be sent out. Please check ! )
            print(err)


if __name__ ==  __main__ :
    a = datetime.date.today()  # 用于写入邮件正文内容中
    b = datetime.date.today() - datetime.timedelta(days=6)  # 用于写入邮件正文内容中
    c = datetime.date.today() - datetime.timedelta(days=4)  # 用于写入邮件正文内容中
    znf_jxh()
    send_email()