Win10定时执行Python脚本发送QQ邮件
程序员文章站
2024-03-18 14:02:10
...
一、python代码
# coding: utf-8
import pandas as pd
import xlrd
import re
import datetime
from mlxtend.frequent_patterns import apriori
from mlxtend.frequent_patterns import association_rules
# pip install pymysql
# pip install sqlalchemy
import numpy as np
import pymysql
from sqlalchemy import create_engine
import smtplib
from email.message import EmailMessage
from email.header import Header
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
import warnings
warnings.filterwarnings("ignore") #用于排除警告
def znf_jxh():
# 初始化数据库连接,使用pymysql模块# MySQL的用户:root, 密码:root, 端口:3306,数据库:lean2
con = create_engine('mysql+pymysql://root:aaa@qq.com:3306/tableau')
# 查询语句,选出表中的所有数据
sql = '''
SELECT
date(a.下单时间) as 下单时间,
a.部门,
a.`所属部门`,
a.`所属团队`,
a.订单类型,
a.客户编号,
a.客户类型,
a.订单号,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.订单产品, ';', b.help_topic_id + 1 ), ';',- 1 ) AS 产品编号_num,
SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( a.订单产品, ';', b.help_topic_id + 1 ), ';',- 1 ), "(", 1 ) AS 产品编号,
replace(substr(SUBSTRING_INDEX( SUBSTRING_INDEX( a.订单产品, ';', b.help_topic_id + 1 ), ';',- 1 ),locate("(",SUBSTRING_INDEX( SUBSTRING_INDEX( a.订单产品, ';', b.help_topic_id + 1 ), ';',- 1 ))+1),")","") AS 数量
FROM
`订单总表` AS a
JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( a.订单产品 ) - length( REPLACE ( a.订单产品, ';', '' ) ) + 1 )
WHERE
SUBSTRING_INDEX( SUBSTRING_INDEX( a.订单产品, ';', b.help_topic_id + 1 ), ';',- 1 ) <> ""
AND
(date(a.下单时间) BETWEEN date(date_sub(now(),interval 6 day)) AND date(date_sub(now(),interval 4 day)) )
;
'''
# read_sql_query的两个参数: sql语句, 数据库连接
df = pd.read_sql_query(sql, con = con)
print("读取数据成功!!!")
df.to_excel(r"D:\Python定时任务脚本/产品拆分.xlsx",index = False)
def send_email():
mail_user = '2270159000' # 邮箱登录名,次处使用QQ邮箱,填写QQ号即可,不用带@qq.com
mail_pass = 'ljhjjvnaryljdigb' # QQ邮箱授权码,如何获取可百度
sender = 'aaa@qq.com' # 发件人
receivers = ['aaa@qq.com','aaa@qq.com'] # 收件人列表,list形式
# chaosong = ['aaa@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"D:\Python定时任务脚本/产品拆分.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()
二、python定时任务
1.点击开始--点击windows管理工具--点击任务计划程序;
2、点击创建任务--常规--输入任务名称--输入任务描述--点击触发器--新建--设置任务时间--点击确认--点击操作--新建--输入程序或脚本路径(你安装python/anaconda的python.exe所在路基):C:\ProgramData\Anaconda3\python.exe -- 添加参数(你需要执行脚本的全路径):D:\Python定时任务脚本\znfprclist.py -- 起始于(脚本所放的路径,最后的斜杠不要):D:\Python定时任务脚本 -- 点击确定--;
3、脚本代码路径:D:\Python定时任务脚本\znfprclist.py
上一篇: python五子棋之人人对战