实现功能：
- 从数据库中获取前一天订单数据，分区域生成汇总文件（.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' : 'Jordan.Li@baidu.com', # 发件人邮箱账号
'passwd' : 'Jordan123',
'mailserver' : 'smtp.baidu.com',
'port': '25',
'sub' : '昨日订单概括数据'}
receiver_list = {
'华南':{'receive':'James.king@baidu.com',
'cc_mail':['kobe.liu@baidu.com','harden.lin@baidu.com']},
'华北':{'receive':'James.king@baidu.com',
'cc_mail':['kobe.liu@baidu.com','harden.lin@baidu.com']},
'西北':{'receive':'James.king@baidu.com',
'cc_mail':['kobe.liu@baidu.com','harden.lin@baidu.com']},
'西南':{'receive':'James.king@baidu.com',
'cc_mail':['kobe.liu@baidu.com','harden.lin@baidu.com']},
'华东':{'receive':'James.king@baidu.com',
'cc_mail':['kobe.liu@baidu.com','harden.lin@baidu.com']},
'华中':{'receive':'James.king@baidu.com',
'cc_mail':['kobe.liu@baidu.com','harden.lin@baidu.com']}
}
#执行主体内容-------------------------------------------------
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份邮件，每个邮件发送对应的人，邮件正文包含截图，文件附于附件中。
定时任务发送
在 计算器右击 --> 选择管理 -->任务计划程序：
前面一步步按向导走就行，除了设置时间，就是启动程序这里有需要注意的地方。设置好执行脚本和程序，就能每天通过window自动调用这个脚本了。
扫一扫
9917
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
