先看看效果图
代码：
1.生成图片
新建generate_picture.py，主要功能是查询数据库，生成图片和附件用于嵌入邮件
我这里的数据库是阿里的Maxcompute数据库，如果是SQL Server或者Mysql可以看我另一个博客
BI自动化之Python自动查询数据库生成Excel并每天定时邮件发送
import matplotlib.pyplot as pltfrom odps import ODPSimport datetimeimport timeimport osimport shutilfrom 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='./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<= and pt>=1215group 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=2026group 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")#生成excelwith 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 timeimport pandas as pdfrom odps import ODPSimport numpy as npimport pymysqlimport smtplibfrom email.mime.application import MIMEApplicationfrom email.mime.image import MIMEImagefrom email.mime.multipart import MIMEMultipartfrom email.mime.text import MIMETextimport datetimeimport generate_picture#生成图片generate_picture.generate_pic()o = ODPS('id', '密码', '数据库',endpoint='./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=2026group 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=2026group 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=2026 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的tabledef 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: Calibrifont-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_htmldef sendmail(content_html):mail_host = "" # 设置服务器mail_user = "xxxxx@" # 用户名mail_pass = "xxxxx" # 口令sender = "xxxx@"to_addr = "8xxx@"receivers = to_addrmsg = 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并每天定时邮件发送
