导入包
import smtplib
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
# 构建邮件头信息，包括发件人，接收人，标题等
import datetime
from pymysql import *
# from pyhive import hive
# from impala.dbapi import connect
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib as mpl
plt.rcParams[u'font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus']=False #用来正常显示负号
from datetime import datetime,timedelta
邮件部分
def send_email(title, message, receivers):
# 设置服务器所需信息
# 163邮箱服务器地址
# 设置服务器所需信息
# 163邮箱服务器地址
mail_host = 'XX.163.com'
# 163邮箱端口号
port = 465
# 163用户名
mail_user = 'XX@163.com'
# 密码(部分邮箱为授权码)
mail_pass = 'AABBCC'
# 邮件发送方邮箱地址
sender = 'XX@163.com'
# 邮件主题：拼接当日的时间
current_day = datetime.now().strftime('%Y%m%d')
message['Subject'] = '每日简表_%s'%current_day
# 发送方信息
message['From'] = 'reporter<%s>'%sender
# 接受方信息
reces = ''
for i in range(len(receivers)):
reces = reces+',receiveer%d<%s>'%(i+1,receivers[i])
message['To'] = reces
# 开始发送邮件
try:
# 25端口，非ssl协议
# smtpObj = smtplib.SMTP()
# windows 可行
# smtpObj = smtplib.SMTP_SSL()
# linux 才可行
smtpObj = smtplib.SMTP_SSL(host=mail_host)
# 连接到服务器
smtpObj.connect(mail_host, port)
# 登录到服务器
smtpObj.login(mail_user, mail_pass)
# 发送
smtpObj.sendmail(sender, receivers, message.as_string())
# 退出
smtpObj.quit()
print('success')
except smtplib.SMTPException as e:
print("发送邮件错误:")
print('error', e)
python从mySQL提取
cursor.description方法会将每个字段的字段名,字段类型,字段长度...等等字段的属性列出来.
image.png
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-description.html
def executesql_mysql(sql):
# 连接 mysql 数据库
conn = connect(host='106.15.121.232',
port=3306,
database='datafrog05_adventure',
user='frogdata05',
password='Frogdata!123')
cursor = conn.cursor()
cursor.execute(sql)
results = cursor.fetchall()
# 返回的是list(tuple) 没有表头
# 保存字段名字 为DF添加表头
columns = []
for i in range(len(cursor.description)):
columns_name = cursor.description[i][0].split('.')[-1]
columns.append(columns_name)
table = pd.DataFrame(list(results))
table.columns = columns
cursor.close()
conn.close()
return table
python处理数据&画图
def get_message(test_to_html):
# 转换时间日期为标准格式
test_to_html['create_date'] = test_to_html['create_date'].map(lambda x:
datetime.strptime(x,'%Y-%m-%d'))
# 当日数据
today_info = test_to_html[test_to_html['is_today'] == 1]
today_info_sum = today_info[['sum_amount', 'sum_order']].sum()
#本月数据
tomonth_info = test_to_html[test_to_html['is_current_month'] == 1]
tomonth_info_sum = tomonth_info[['sum_amount', 'sum_order']].sum()
#本季数据
toquarter_info = test_to_html[
test_to_html['is_current_quarter'] == 1]
toquarter_info_sum = toquarter_info[['sum_amount', 'sum_order']].sum()
#本年数据
toyear_info = test_to_html[test_to_html['is_current_year'] == 1]
toyear_info_sum = toyear_info[['sum_amount', 'sum_order']].sum()
table_today = pd.DataFrame({'指标': ['销售额', '订单量'],
'今日': today_info_sum,
'当季': toquarter_info_sum,
'当月': tomonth_info_sum,
'当年': toyear_info_sum}
).reset_index(drop=True)
# picture_time = datetime.now().date() - timedelta(days=15)
picture_time = (datetime.now().date() -
timedelta(days=45)).strftime('%Y-%m-%d')
picture_table = test_to_html[test_to_html['create_date'] >=
picture_time]
picture_table = picture_table.groupby('create_date').sum()
[['sum_amount',
'sum_order','sum_amount_goal','sum_order_goal']]
# plt.show()
# 绘制图形
x = picture_table.index
y1 = picture_table['sum_amount']
y2 = picture_table['sum_order']
y3 = picture_table['sum_amount']/picture_table['sum_amount_goal']
y4 = picture_table['sum_order']/picture_table['sum_order_goal']
fig = plt.figure(figsize=(14, 20))
# 划分子图
ax1 = fig.add_subplot(411) # 等价于fig.add_subplot(4,1,1)
ax2 = fig.add_subplot(412)
ax3 = fig.add_subplot(413)
ax4 = fig.add_subplot(414)
# 开始画图，可以使用 ax1、ax2设置相应的参数
ax1.plot(x, y1, 'g', label='first')
ax1.set_xlabel('时间', fontsize=15)
ax1.set_ylabel('销售额', fontsize=15)
ax1.set_title('过去45天销售趋势', fontsize=25, loc='left')
ax2.plot(x, y2, 'r', label='second')
ax2.set_xlabel('时间', fontsize=15)
ax2.set_ylabel('订单量', fontsize=15)
# 开始画图，可以使用 ax1、ax2设置相应的参数
ax3.plot(x, y3, 'b', label='first')
ax3.set_xlabel('时间', fontsize=15)
ax3.set_ylabel('销售目标达成', fontsize=15)
ax3.axhline(y=1, lw=2, ls="--", color="g") #添加参考线
ax4.plot(x, y4, 'r', label='second')
ax4.set_xlabel('时间', fontsize=15)
ax4.set_ylabel('订单量目标达成', fontsize=15)
ax4.axhline(y=1, lw=2, ls="--", color="g")
plt.xticks(rotation=30)
# x坐标增加15°倾斜
for tick in ax1.get_xticklabels():
tick.set_rotation(15)
for tick in ax2.get_xticklabels():
tick.set_rotation(15)
for tick in ax3.get_xticklabels():
tick.set_rotation(15)
for tick in ax4.get_xticklabels():
tick.set_rotation(15)
img_file = "examples.png"
plt.savefig(img_file)
得到html格式的表格
https://blog.csdn.net/u012111465/article/details/82713561
# 样式添加
# 添加表内容
df_html = table_today.to_html(escape=False, index=False)
head = \
"""
<head> '''设置文档标题和其它在网页中不显示的信息'''
<meta charset="utf-8"> //这里是元信息。里面可以定义网
页的字符格道式和写出网页的详细信息（如。作者。关键词等）
<STYLE TYPE="text/css" MEDIA=screen>
table.dataframe {
border-collapse: collapse;
border: 2px solid #a19da2;
/*居中显示整个表格*/
margin: auto;
}
table.dataframe thead {
border: 2px solid #91c6e1;
background: #f1f1f1;
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: #105de3;
font-family: arial;
text-align: center;
}
table.dataframe td {
text-align: center;
padding: 10px 10px 10px 10px;
}
body {
font-family: 宋体;
}
h1 {
color: #5db446
}
div.header h2 {
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 5px #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: 楷体;
font-size: 20px;
text-align: center;
}
td img {
/*width: 60px;*/
max-width: 300px;
max-height: 300px;
}
</STYLE>
</head>
"""
# head和body是固定格式
# 构造模板的附件（100）
body = \
"""
<body>
<div align="center" class="header">
<!--标题部分的信息-->
<h1 align="center">Adventure Works Cycles 简报</h1>
</div>
<hr>
<div class="content">
<!--正文内容-->
<h2>战绩</h2>
<div>
<h4></h4>
{df_html}
</div>
<div>
<h4></h4>
<center><img src="cid:io"></center>
</div>
<div>
<hr>
<p style="text-align: center">
—— 本次报告完 ——
</p>
</div>
</div>
</body>
""".format(df_html=df_html)
# body中有content的format格式
# content = "<html>" + head + body + "</html>"
content = head + body
html_file = open('t4.html', 'w', encoding='UTF-8', newline='')
html_file.write(content)
html_file.close()
return content,img_file
运行本py文件时 执行以下内容
if __name__ == '__main__':
sql = "SELECT * FROM dw_order_by_day_mdx "
# sql = "show databases"
# test_to_html = executesql_hive(sql)
test_to_html = executesql_mysql(sql)
print(test_to_html)
# 获取要发送的 html 和 图片 信息
content_html,img_file = get_message(test_to_html)
# 目标：设置email要发送的内容，也可以直接就是 MIMEText 的内容，
# 但是下面这样可以追加文本、html、图片等信息
message = MIMEMultipart()
# message = MIMEMultipart('alternative')
# 1、添加文本内容
# content_text = '邮件发送测试中..'
# message.attach(MIMEText(content_text,'plain','utf-8'))
# 2、添加 html 格式的内容
message.attach(MIMEText(content_html,'html','utf-8'))
# 3、添加图片信息
with open(img_file,'rb') as f:
img_info = f.read()
img = MIMEImage(img_info)
img.add_header('Content-ID', 'io')
# body部分有io文件
message.attach(img)
# 最后：设置收件箱地址，可以发邮件了
receivers = ['abai@qq.com','obvrou.163.com']
# 发送邮件
send_email('每日简报',message,receivers)
GIF.gif
- 添加图片的实现原理：
将本地图片加入到邮件附件中:
m_img.add_header('Content-ID', '<id>')用来设置图片id
然后通过邮件html文本引入图片:
<img src="cid:id">
参见：https://blog.csdn.net/qq_38161040/article/details/90140231
添加附件等其他操作 参见：https://blog.csdn.net/qq_33472765/article/details/81022471
扩展标题add_header:
https://www.cnblogs.com/zhangxinqi/p/9113859.html
一次性多张图片发送：
https://www.jb51.net/article/167391.htm
