python定時郵件發送
2020-08-12 20:43:56
實現功能：
- 從數據庫中獲取前一天訂單數據，分割區域生成彙總檔案（.csv）和明細檔案（.csv）儲存，將檔案儲存到指定目錄下
- 生成彙總檔案內容截圖，繪製產品分佈餅圖截圖，儲存兩個截圖，將檔案儲存到指定目錄下
- 通過郵件將每日各區域所需的兩個檔案及兩個截圖在每日早上8點準時發送及抄送各區域指定人員
匯入數據所需的包~
#郵件發送模組-----------
import smtplib # 載入smtplib模組
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
#數據連線清洗模組
import psycopg2
import pandas as pd
import datetime
import os
import matplotlib.pyplot as plt
#表格內容優化
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, colors, Alignment,Border,Side,PatternFill
#截圖
import pythoncom
from PIL import ImageGrab, Image
from win32com.client import Dispatch, DispatchEx
import uuid
執行指令碼
#數據庫連線及數據清洗--------------------------------------------------------------------------
def get_data(database_config,sql,k):
conn = psycopg2.connect(database=database_config["database"],
user=database_config["user"],
password=database_config["password"],
host=database_config["host"],
port=database_config["port"])
cursor = conn.cursor()
cursor.execute(sql)
df = pd.DataFrame(cursor.fetchall()) #獲取訂單明細數據
lst = list()
for a in cursor.description:
lst.append(a[0])
df.columns = lst
df = df[df['region']==k]
cursor.close()
#數據清洗
df_sum = df.groupby('package_name').agg({'uid':'nunique','id':'count'}) #獲取訂單彙總數據
df_sum['percent'] = df_sum['uid']/df_sum['uid'].sum() #計算沒類商品訂單購買使用者數佔比
df_sum['new_package'] = list(df_sum.index)
df_sum.loc[df_sum['percent']<0.02,'new_package'] = '其他' #將佔比小於2%的型別歸類爲其他
return df,df_sum
#檔案內容儲存----------------------------------------------------------------------------------
def get_document_file(file_path,df,df_sum,k):
new_dir = file_path+'\\'+str(datetime.date.today())+'\\'+ k
#建立當日檔案保留目錄
if not os.path.isdir(file_path+'\\'+str(datetime.date.today())):
os.mkdir(os.path.join(file_path,str(datetime.date.today())))
if not os.path.isdir(new_dir):
os.mkdir(os.path.join(file_path,str(datetime.date.today()),k))
#明細數據保留csv檔案-------------------------------------------
df.to_csv(new_dir +'\\'+str(datetime.date.today())+'_'+ k +'_訂單明細檔案.csv',encoding='utf_8_sig')
print(new_dir+'\\'+str(datetime.date.today())+ k +'訂單明細檔案儲存成功')
#彙總數據保留csv檔案--------------------------------------------
#df_sum.to_csv(new_dir+'\\'+str(datetime.date.today())+'_訂單彙總檔案.csv',encoding='utf_8_sig')
font_title = Font(size=12, bold=True, name='微軟雅黑', color="000000")
fill = PatternFill(patternType="solid", start_color="5B9BD5")#純色填充
border = Border(left=Side(border_style='thin',color='000000'),
right=Side(border_style='thin',color='000000'),
top=Side(border_style='thin',color='000000'),
bottom=Side(border_style='thin',color='000000'))
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df_sum.iloc[:,:2], index=True, header=True): #數據寫入ws
ws.append(r)
for row in ws: #標題行格式設定
for cell in row:
cell.border = border
if cell.row == 1:
cell.font = font_title #如果是第一行的，設定大字型
cell.fill = fill #第一行設定背景填充顏色
ws.column_dimensions['A'].width = max(map(lambda x:len(x),list(df_sum.index)))*1.7+1
wb.save(new_dir+'\\'+str(datetime.date.today())+'_'+ k +"_訂單彙總檔案.xlsx")
print(new_dir+'\\'+str(datetime.date.today())+ k +'訂單彙總檔案儲存成功')
return new_dir
#對彙總檔案內容進行截圖-----------------------------------------
def get_document_img(new_dir,df_sum,k):
screen_area = 'A1:C'+str(df_sum.shape[0]+2)
excel = DispatchEx("Excel.Application") # 啓動excel
wb = excel.Workbooks.Open(new_dir+'\\'+str(datetime.date.today())+'_'+ k +"_訂單彙總檔案.xlsx") # 開啓excel
ws = wb.Sheets('sheet') # 選擇sheet
ws.Range(screen_area).CopyPicture() # 複製圖片區域
ws.Paste()
name = str(uuid.uuid4()) # 重新命名唯一值
new_shape_name = name[:6]
excel.Selection.ShapeRange.Name = new_shape_name # 將剛剛選擇的Shape重新命名，避免與已有圖片混淆
ws.Shapes(new_shape_name).Copy() # 選擇圖片
img = ImageGrab.grabclipboard() # 獲取剪貼簿的圖片數據
img.save(new_dir+'\\'+str(datetime.date.today())+'_'+ k+"_彙總數據截圖.png") # 儲存圖片
img_path_doc = new_dir+'\\'+str(datetime.date.today())+'_'+ k+"_彙總數據截圖.png"
wb.Close(SaveChanges=0) # 關閉工作薄，不儲存
excel.Quit() # 退出excel
return img_path_doc
print('彙總數據截圖完成！')
#彙總圖表保留圖片檔案-------------------------------------------
def get_data_img(new_dir,df_sum,k):
df_pie = df_sum.groupby('new_package')['uid'].sum().sort_values(ascending = False)
plt.figure(figsize=(8,8))
plt.pie(x=df_pie, #繪製數據
labels=df_pie.index,#新增程式語言標籤
autopct='%.1f%%',#設定百分比的格式,保留3位小數
pctdistance=0.8, #設定百分比標籤和圓心的距離
labeldistance=1.0,#設定標籤和圓心的距離
startangle=180,#設定餅圖的初始角度
counterclock= False,#是否爲逆時針方向,False表示順時針方向
wedgeprops= {'linewidth':1,'edgecolor':'white'},#設定餅圖內外邊界的屬性值
textprops= {'fontsize':12,'color':'black'},#設定文字標籤的屬性值
)
plt.title(k+'昨日訂單使用者數分佈',fontsize=17)
plt.savefig(new_dir+'\\'+str(datetime.date.today())+'_'+ k+"_訂單使用者分佈圖.png",
dpi=200,
bbox_inches='tight')
img_path_df = new_dir+'\\'+str(datetime.date.today())+'_'+ k+"_訂單使用者分佈圖.png"
return img_path_df
def send_email(new_dir,email_config,img_path,k,v):
try:
msg = MIMEMultipart('related')
msg['From'] = formataddr(["數據分析",email_config['sender']]) # 發件人郵箱暱稱、發件人郵箱賬號
msg['To'] = formataddr(["receiver",v['receive']]) # 收件人郵箱暱稱、收件人郵箱賬號
msg['Cc'] = ','.join(v['cc_mail'])
msg['Subject'] = k + email_config['sub']
print('讀取賬號資訊完成')
#文字資訊
#txt = MIMEText('this is a test mail', 'plain', 'utf-8')
#msg.attach(txt)
#正文內容
body = """
<b>昨日訂單概括:</b><br></br>
<!doctype html> <html><head> <meta charset="utf-8"></head>
<body><div>
<p>各位運營同事大家好，以下是昨日訂單數據情況，包括訂單彙總檔案，訂單明細檔案。套餐型別分佈餅圖，彙總檔案截圖。:</P>
</div> <style type="text/css">p{text-indent: 2em; /*首行字元縮排設定*/}</style>
<p><b>訂單分佈餅圖：</b></p>
<p><img src="cid:image1"></p>
<p><b>彙總檔案內容截圖：</b></p>
<p><img src="cid:image2"></p>
<span style="float:right;">總冠軍：Jordan</span>
</body>
</html>
"""
text = MIMEText(body, 'html') #, 'utf-8'
msg.attach(text)
msgImage = MIMEImage(open(img_path[0], 'rb').read())
# 定義圖片 ID，在 HTML 文字中參照
msgImage.add_header('Content-ID', '<image1>')
msg.attach(msgImage)
msgImage = MIMEImage(open(img_path[1], 'rb').read())
# 定義圖片 ID，在 HTML 文字中參照
msgImage.add_header('Content-ID', '<image2>')
msg.attach(msgImage)
print('正文資訊構建完成')
#附件資訊新增------------------------------------------------------------------------
#彙總附件資訊 ①
path = new_dir+'\\'+str(datetime.date.today())+'_'+ k +"_訂單彙總檔案.xlsx"
file_name = str(datetime.date.today())+'_'+ k+"_訂單彙總檔案.xlsx"
attach = MIMEApplication(open(path,'rb').read(),encoding="utf8")
attach.add_header('Content-Disposition', 'attachment', filename=('gbk', '', file_name))
msg.attach(attach)
print('訂單彙總檔案附件新增成功')
#明細附件資訊 ②
path = new_dir+'\\'+str(datetime.date.today())+'_'+ k +"_訂單明細檔案.csv"
file_name = str(datetime.date.today())+'_'+ k+"_訂單明細檔案.csv"
attach = MIMEApplication(open(path,'rb').read(),encoding="utf8")
attach.add_header('Content-Disposition', 'attachment', filename=('gbk', '', file_name))
msg.attach(attach)
print('訂單明細檔案附件新增成功')
#發送郵件
server = smtplib.SMTP(email_config['mailserver'], email_config['port']) # 發件人郵箱中的SMTP伺服器，埠是25
server.login(email_config['sender'], email_config['passwd']) # 發件人郵箱賬號、郵箱密碼
server.sendmail(email_config['sender'],
[v['receive']]+v['cc_mail'],
msg.as_string()) # 發件人郵箱賬號、收件人郵箱賬號、發送郵件
server.quit()
print('郵件發送成功\n')
except Exception as e:
print(e)
if __name__ == '__main__':
#設定基本檔案參數-----------------------------------------------
file_path = r'C:\Users\Jordan\Desktop\郵件_每日外發3' #設定根目錄
database_config = {
"database":"database",
"user":"admin",
"password":"admin",
"host":"localhost",
"port":"443"}
sql = 'select * from t_order where create_time = current_date-1'
email_config = {
'sender' : '[email protected]', # 發件人郵箱賬號
'passwd' : 'Jordan123',
'mailserver' : 'smtp.baidu.com',
'port': '25',
'sub' : '昨日訂單概括數據'}
receiver_list = {
'華南':{'receive':'[email protected]',
'cc_mail':['[email protected]','[email protected]']},
'華北':{'receive':'[email protected]',
'cc_mail':['[email protected]','[email protected]']},
'西北':{'receive':'[email protected]',
'cc_mail':['[email protected]','[email protected]']},
'西南':{'receive':'[email protected]',
'cc_mail':['[email protected]','[email protected]']},
'華東':{'receive':'[email protected]',
'cc_mail':['[email protected]','[email protected]']},
'華中':{'receive':'[email protected]',
'cc_mail':['[email protected]','[email protected]']}
}
#執行主體內容-------------------------------------------------
for k,v in receiver_list.items():
print('傳入首個參數：',k)
df,df_sum = get_data(database_config,sql,k) #獲取明細數據，彙總數據
new_dir = get_document_file(file_path,df,df_sum,k) #儲存明細數據，彙總數據爲excel檔案
img_path_doc = get_document_img(new_dir,df_sum,k) #儲存彙總檔案截圖數據
img_path_df = get_data_img(new_dir,df_sum,k) #儲存彙總數據餅圖
img_path = [img_path_doc,img_path_df] #截圖檔案路徑
send_email(new_dir,email_config,img_path,k,v)
執行結果如下：
指定目錄下每天生成6個資料夾，每個資料夾內包含對應區域4個檔案。
自動發送6份郵件，每個郵件發送對應的人，郵件正文包含截圖，檔案附於附件中。
