【导语】：你是否需要每周手动给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) + "\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()
二、设置windows定时任务
1.点击开始--点击windows管理工具--点击任务计划程序；
image.png
image.png
image.png
2、点击创建任务--常规--输入任务名称--输入任务描述
image.png
image.png
3、点击触发器--新建--设置任务时间--点击确认
image.png
image.png
4、点击操作--新建--浏览--输入程序或脚本路径（你的python.exe所在路径）--点击确定
image.png
image.png
完成！
希望本文的内容对大家的学习或者工作能带来一定的帮助，每天进步一点点，加油。
网友评论