定時執行Python腳本自動發送QQ郵件頭條網

2020-12-14 22:09:50 佚名

【導語】：你是否需要每週手動給leader發送工作郵件，每次都需要重複性工作，那麼我們來用python吧，來設置定時任務！

一、代碼編寫定時任務腳本

<code># 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:[email protected]: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()
/<code>