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

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

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

# coding: utf-8import pandas as pdimport datetimefrom sqlalchemy import create_engineimport smtplibfrom email.header import Headerfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.mime.application import MIMEApplicationimport warningswarnings.filterwarnings("ignore")# 用于排除警告def znf_jxh():# 初始化数据库连接，使用pymysql模块# MySQL的用户：root, 密码:123456, 端口：3306,数据库：testcon = 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 day2from aui where app_name = '相机') AS bON a.uid = b.uidGROUP 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.commail_pass = '*********'# QQ邮箱授权码 ， 如何获取可百度sender = '*********@qq.com'# 发件人receivers = ['*********@qq.com']# 收件人列表，list形式#chaosong= ['*********@qq.com']# 抄送人列表，list形式# 设置邮件体对象 ， 对象类型为 mixed，可以发送附件subject = "每日发送销售订单产品拆分列表"# 邮件主题content = "发送时间:"str(a)"n""订单时间："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('nThe email has been sent successfully')except Exception as err:print('n 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()