先看看效果图
代码:
1.生成图片
新建generate_picture.py,主要功能是查询数据库,生成图片和附件用于嵌入邮件
我这里的数据库是阿里的Maxcompute数据库,如果是SQL Server或者Mysql可以看我另一个博客
BI自动化之Python自动查询数据库生成Excel并每天定时邮件发送
import matplotlib.pyplot as plt
from odps import ODPS
import datetime
import time
import os
import shutil
from matplotlib import style
# print(plt.style.available)
#先清除文件夹底下所有文件
picpath=r'D:\Python\sendmail\pic'
if not os.listdir(picpath):
shutil.rmtree(picpath)
os.mkdir(picpath)
o = ODPS('id', '密码', '数据库',endpoint='http://service.cn.maxcompute.aliyun.com/api')
curdate = time.strftime('%Y%m%d',time.localtime(time.time()))
yesterday=(datetime.date.today()-datetime.timedelta(days=1)).strftime('%Y%m%d')
sql_trend='''
select pt,sum(actual_amount) 销售,sum(ticket_num) 订单数,round(sum(actual_amount)/sum(ticket_num),2) 客单价,
round(sum(actual_amount)/count(distinct userid)/count(distinct pt),2) 店均销售,round(sum(ticket_num)/count(distinct userid)/count(distinct pt),2) 店均订单数
from
门店销售表
where pt<=20220101 and pt>=20211215
group by pt order by pt
'''
sql_store='''
SELECT regional_company,company,orders
,timeperiod 日期
,sum(actual_amount_cy) 销售
,sum(actual_amount_cy)/count(DISTINCT userid) 单店销售
,sum(comp_day_actual_amount) 同比店销售
,sum(comp_day_actual_amount-actual_amount_lyday)/sum(actual_amount_lyday) 同比店销售增长
,sum(TC) 订单数
,sum(TC)/count(DISTINCT userid) 单店订单数
,sum(comp_day_tc) 同比店订单数
,sum(comp_day_tc-tc_lyday)/sum(tc_lyday) 同比店订单增长
,sum(actual_amount_cy)/sum(tc) 客单价
,count(DISTINCT userid) 营业门店数
,sum(comp_storecount) 同比店营业门店数
FROM 门店日报表 where pt=20220126
group by regional_company,company,orders
,timeperiod
'''
#生成图片
with o.execute_sql(sql_trend).open_reader(tunnel=True) as reader:
df=reader.to_pandas()
def generate_pic():
plt.style.use('Solarize_Light2')
f,axes=plt.subplots(nrows=1,ncols=2,figsize=(16,8))
plt.subplot(121)
plt.bar(df.pt, df.销售, color="orange", linestyle="-", linewidth=5, alpha=0.4)
for a, b in zip(df.pt, df.销售):
plt.text(a, b, b, ha='center', va='bottom', fontdict={'size': 10, 'color': 'blue'})
plt.xlabel='日期'
plt.title='销售趋势'
plt.subplot(1,2,2)
plt.plot(df.pt, df.订单数, color="blue", linestyle="-", linewidth=5, alpha=0.4,marker='o')
for a, b in zip(df.pt, df.订单数):
plt.text(a, b, b, ha='center', va='bottom', fontsize=10)
plt.xlabel='日期'
plt.title='订单数趋势'
plt.savefig(picpath+"\Sales.jpg")
f,axes=plt.subplots(nrows=1,ncols=2,figsize=(16,8))
plt.subplot(121,facecolor='#FFDAB9')
plt.bar(df.pt, df.店均销售, color="orange", linestyle="-", linewidth=5, alpha=0.4)
for a, b in zip(df.pt, df.店均销售):
plt.text(a, b, b, ha='center', va='bottom', fontdict={'size': 10, 'color': 'blue'})
plt.xlabel='日期'
plt.title='店均销售趋势'
plt.subplot(122,facecolor='#FFDAB9')
plt.plot(df.pt, df.店均订单数, color="blue", linestyle="-", linewidth=5, alpha=0.4,marker='o')
for a, b in zip(df.pt, df.店均订单数):
plt.text(a, b, b, ha='center', va='bottom', fontsize=10)
plt.xlabel='日期'
plt.title='店均订单数趋势'
plt.savefig(picpath+"\ADS.jpg")
#生成excel
with o.execute_sql(sql_store).open_reader(tunnel=True) as reader:
df_store=reader.to_pandas()
df_store.to_excel(picpath+"\Storesales.xlsx")
图片大概是这样的:
2.编写主程序
主要功能:
1.查询数据库生成表格插入邮件中
2.发送给对应的联系人
import time
import pandas as pd
from odps import ODPS
import numpy as np
import pymysql
import smtplib
from email.mime.application import MIMEApplication
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import datetime
import generate_picture
#生成图片
generate_picture.generate_pic()
o = ODPS('id', '密码', '数据库',endpoint='http://service.cn.maxcompute.aliyun.com/api')
curdate = time.strftime('%Y%m%d',time.localtime(time.time()))
yesterday=(datetime.date.today()-datetime.timedelta(days=1)).strftime('%Y%m%d')
file_path=r'D:\Python\sendmail\pic'
sql_all='''
SELECT orders
,timeperiod 日期
,sum(actual_amount_cy) 销售
,sum(actual_amount_cy)/count(DISTINCT userid) 单店销售
,sum(comp_day_actual_amount) 同比店销售
,sum(comp_day_actual_amount-actual_amount_lyday)/sum(actual_amount_lyday) 同比店销售增长
,sum(TC) 订单数
,sum(TC)/count(DISTINCT userid) 单店订单数
,sum(comp_day_tc) 同比店订单数
,sum(comp_day_tc-tc_lyday)/sum(tc_lyday) 同比店订单增长
,sum(actual_amount_cy)/sum(tc) 客单价
,count(DISTINCT userid) 营业门店数
,sum(comp_storecount) 同比店营业门店数
FROM 销售报表 where pt=20220126
group by orders
,timeperiod
;
'''
sql_region='''
SELECT region,orders
,timeperiod 日期
,sum(actual_amount_cy) 销售
,sum(actual_amount_cy)/count(DISTINCT userid) 单店销售
,sum(comp_day_actual_amount) 同比店销售
,sum(comp_day_actual_amount-actual_amount_lyday)/sum(actual_amount_lyday) 同比店销售增长
,sum(TC) 订单数
,sum(TC)/count(DISTINCT userid) 单店订单数
,sum(comp_day_tc) 同比店订单数
,sum(comp_day_tc-tc_lyday)/sum(tc_lyday) 同比店订单增长
,sum(actual_amount_cy)/sum(tc) 客单价
,count(DISTINCT userid) 营业门店数
,sum(comp_storecount) 同比店营业门店数
FROM 日报表 where pt=20220126
group by region,orders
,timeperiod
'''
sql_city='''
SELECT city,orders
,timeperiod 日期
,sum(actual_amount_cy) 销售
,sum(actual_amount_cy)/count(DISTINCT userid) 单店销售
,sum(comp_day_actual_amount) 同比店销售
,sum(comp_day_actual_amount-actual_amount_lyday)/sum(actual_amount_lyday) 同比店销售增长
,sum(TC) 订单数
,sum(TC)/count(DISTINCT userid) 单店订单数
,sum(comp_day_tc) 同比店订单数
,sum(comp_day_tc-tc_lyday)/sum(tc_lyday) 同比店订单增长
,sum(actual_amount_cy)/sum(tc) 客单价
,count(DISTINCT userid) 营业门店数
,sum(comp_storecount) 同比店营业门店数
FROM 日报表 where pt=20220126 and city in ('广州市','苏州市','厦门市')
group by city,orders
,timeperiod
'''
#连接数据库
def get_data(sql):
with o.execute_sql(sql).open_reader(tunnel=True) as reader:
df=reader.to_pandas()
return df
#数据格式化一下
def deal_df_all(df_all):
df=df_all.drop('orders',1)
df[['销售','单店销售','单店订单数']]=df[['销售','单店销售','单店订单数']].applymap(lambda x:"%.0f"%x) #去掉小数点
df[['客单价']]=df[['客单价']].applymap(lambda x:"%.2f"%x) #取2位小数
df[['同比店销售','同比店订单数']]=df[['同比店销售','同比店订单数']].fillna(0.0).applymap(lambda x:"%.0f"%x) #去掉小数点
df[['同比店销售增长','同比店订单增长']]=df[['同比店销售增长','同比店订单增长']].fillna(0.0).applymap(lambda x:format(x,'.2%')) #设置百分号
return df
# 传入得到的html的table
def get_html_msg(df_all_html,df_region_html,df_city_html, txt1,end):
# 表格格式
head = \
"""
<head>
<meta charset="utf-8">
<STYLE TYPE="text/css" MEDIA=screen>
table.dataframe {
border-collapse:collapse;
border: 2px solid #a19da2;
/*默认居中auto显示整个表格*/
margin: left
}
table.dataframe thead {
border: 2px solid #91c6e1;
background: #FFFFFF;
padding: 10px 10px 10px 10px;
color: #333333;
}
table.dataframe tbody {
border: 2px solid #91c6e1;
padding: 10px 10px 10px 10px;
}
table.dataframe tr {
}
table.dataframe th {
vertical-align: top;
font-size: 14px;
padding: 10px 10px 10px 10px;
color: #4169E1;
font-family: Calibri;
text-align: center;
}
table.dataframe td{
text-align: center;
padding: 10px 10px 10px 10px;
}
body {
font-family: Calibri;
}
h1 {
color: #5db446
}
div.header h2 {
color: #4169E1;
font-family: 黑体;
}
div.content h2 {
text-align: center;
font-size: 28px;
text-shadow: 2px 2px 1px #de4040;
color: #fff;
font-weight: bold;
background-color: #008eb7;
line-height: 1.5;
margin: 20px 0;
box-shadow: 10px 10px 5pxx #888888;
border-radius: 5px;
}
h3 {
font-size: 22px;
background-color: rgba(0,2,227,0.71);
text-shadow: 2px 2px 1px #de4040;
color: rgba(239,241,234,0.99);
line-height; 1.5;
}
h4 {
color: #e10092;
font-family: Calibri
font-size: 20px;
text-align: center;
}
td img {
/*width: 60px;*/
max-width: 300px;
max-height: 300px;
}
</style>
</head>
"""
body = \
"""
<body>
<div align="left" class="header">
<!--标题-->
<p align="left">{txt1}</p>
</div>
<div class="content">
{df_all_html}
<p align="left"><b>区域表现</b></p>
{df_region_html}
<p align="left"><b>重点关注城市表现</b></p>
{df_city_html}
<p align="left"><p style="font-size:3px;color:orange">同比店定义:开业满一年且去年同一天有营业</p></p>
</div>
</br>
<div id="dateTime"></div>营业趋势追踪:
<table border="2">
<tr>
<th>营业趋势图</th>
<th coslanb="5">
<img src="cid:image1" alt="image1" width="1000" height="400" />
</th>
</tr>
<tr>
<th>店均销售趋势图</th>
<th coslanb="5">
<img src="cid:image2" alt="image2" width="1000" height="400" />
</th>
</tr>
</table>
<p align="left">{end}</p>
</div>
</body>
<br /><br />
""".format( txt1=txt1,df_all_html=df_all_html,df_region_html=df_region_html,df_city_html=df_city_html, end=end)
html_msg = "<html>" + head + body + "</html>"
content_html = MIMEText(html_msg, "html", "utf-8")
return content_html
def sendmail(content_html):
mail_host = "smtp.163.com" # 设置服务器
mail_user = "xxxxx@163.com" # 用户名
mail_pass = "xxxxx" # 口令
sender = "xxxx@163.com"
to_addr = "8xxx@qq.com"
receivers = to_addr
msg = MIMEMultipart('alternative')
msg['subject'] = (curdate)+'营业日报'
with open(file_path+'\Storesales.xlsx', 'rb') as f: #添加附件
attachfile = MIMEApplication(f.read())
attachfile.add_header('Content-Disposition', 'attachment', filename=r'门店销售明细.xlsx')
with open(file_path+'\Sales.jpg','rb') as m1: #添加图片
img1=MIMEImage(m1.read())
img1.add_header('Content-ID','<image1>')
with open(file_path+'\ADS.jpg','rb') as m2: #添加图片
img2=MIMEImage(m2.read())
img2.add_header('Content-ID','<image2>')
msg.attach(attachfile)
msg.attach(img1)
msg.attach(img2)
msg.attach(content_html)
try:
smtpObj = smtplib.SMTP()
smtpObj.connect(mail_host, 25) # 25 为 SMTP 端口号
smtpObj.login(mail_user, mail_pass)
smtpObj.sendmail(sender, receivers, msg.as_string())
smtpObj.quit()
print("邮件发送成功")
except smtplib.SMTPException as e:
print(e)
if __name__ == '__main__':
# 1.读取数据
df_all = deal_df_all(get_data(sql_all))
df_region=deal_df_all(get_data(sql_region))
df_city=deal_df_all(get_data(sql_city))
df_all_html = df_all.to_html(escape=False)
df_region_html = df_region.to_html(escape=False)
df_city_html = df_city.to_html(escape=False)
#邮件正文
strBody1 = """
<tr>Dear all,
</br>
<b>以下为昨日营业情况</b>
</tr>
"""
#邮件结束
strEnding = '''
<p style=""font-family:Calibri;""><br>Best Regards,<br>查看更多数据请登录https://sxxxxxxxxx</p>
'''
content_html=get_html_msg(df_all_html,df_region_html,df_city_html,strBody1,strEnding)
sendmail(content_html)
接下来就是每天如何到点自动发送邮件的问题了,如果是linux就简单了,直接用crontab就好了,windows可以查看我另一个博客BI自动化之Python自动查询数据库生成Excel并每天定时邮件发送