導(dǎo)入包

import smtplib
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart  
# 構(gòu)建郵件頭信息步咪，包括發(fā)件人，接收人益楼，標(biāo)題等
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'] #用來正常顯示中文標(biāo)簽
plt.rcParams['axes.unicode_minus']=False #用來正常顯示負(fù)號(hào)

from datetime import datetime,timedelta

郵件部分

def send_email(title, message, receivers):
    # 設(shè)置服務(wù)器所需信息
    # 163郵箱服務(wù)器地址
    # 設(shè)置服務(wù)器所需信息
    # 163郵箱服務(wù)器地址
    mail_host = 'XX.163.com'
    # 163郵箱端口號(hào)
    port = 465
    # 163用戶名
    mail_user = 'XX@163.com'
    # 密碼(部分郵箱為授權(quán)碼)
    mail_pass = 'AABBCC'
    # 郵件發(fā)送方郵箱地址
    sender = 'XX@163.com'

    # 郵件主題：拼接當(dāng)日的時(shí)間
    current_day = datetime.now().strftime('%Y%m%d')
    message['Subject'] = '每日簡表_%s'%current_day

    # 發(fā)送方信息
    message['From'] = 'reporter<%s>'%sender

    # 接受方信息
    reces = ''
    for i in range(len(receivers)):
        reces = reces+',receiveer%d<%s>'%(i+1,receivers[i])
    message['To'] = reces


    # 開始發(fā)送郵件
    try:
        # 25端口猾漫，非ssl協(xié)議
        # smtpObj = smtplib.SMTP()
        # windows 可行
        # smtpObj = smtplib.SMTP_SSL()
        # linux 才可行
        smtpObj = smtplib.SMTP_SSL(host=mail_host)
        # 連接到服務(wù)器
        smtpObj.connect(mail_host, port)
        # 登錄到服務(wù)器
        smtpObj.login(mail_user, mail_pass)
        # 發(fā)送
        smtpObj.sendmail(sender, receivers, message.as_string())
        # 退出
        smtpObj.quit()
        print('success')
    except smtplib.SMTPException as e:
        print("發(fā)送郵件錯(cuò)誤:")
        print('error', e)

python從mySQL提取

cursor.description方法會(huì)將每個(gè)字段的字段名,字段類型,字段長度...等等字段的屬性列出來.

image.png

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-description.html

def executesql_mysql(sql):
    # 連接 mysql 數(shù)據(jù)庫
    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處理數(shù)據(jù)&畫圖

def get_message(test_to_html):
    # 轉(zhuǎn)換時(shí)間日期為標(biāo)準(zhǔn)格式
    test_to_html['create_date'] = test_to_html['create_date'].map(lambda x: 
datetime.strptime(x,'%Y-%m-%d'))

    # 當(dāng)日數(shù)據(jù)
    today_info = test_to_html[test_to_html['is_today'] == 1]
    today_info_sum = today_info[['sum_amount', 'sum_order']].sum()

    #本月數(shù)據(jù)
    tomonth_info = test_to_html[test_to_html['is_current_month'] == 1]
    tomonth_info_sum = tomonth_info[['sum_amount', 'sum_order']].sum()
    #本季數(shù)據(jù)
    toquarter_info = test_to_html[
test_to_html['is_current_quarter'] == 1]
    toquarter_info_sum = toquarter_info[['sum_amount', 'sum_order']].sum()
    #本年數(shù)據(jù)
    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({'指標(biāo)': ['銷售額', '訂單量'],
                                '今日': today_info_sum,
                                '當(dāng)季': toquarter_info_sum,
                                '當(dāng)月': tomonth_info_sum,
                                '當(dāng)年': 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)  # 等價(jià)于fig.add_subplot(4,1,1)
    ax2 = fig.add_subplot(412)
    ax3 = fig.add_subplot(413)
    ax4 = fig.add_subplot(414)
    # 開始畫圖，可以使用 ax1感凤、ax2設(shè)置相應(yīng)的參數(shù)
    ax1.plot(x, y1, 'g', label='first')
    ax1.set_xlabel('時(shí)間', fontsize=15)
    ax1.set_ylabel('銷售額', fontsize=15)
    ax1.set_title('過去45天銷售趨勢(shì)', fontsize=25, loc='left')
    ax2.plot(x, y2, 'r', label='second')
    ax2.set_xlabel('時(shí)間', fontsize=15)
    ax2.set_ylabel('訂單量', fontsize=15)

    # 開始畫圖悯周，可以使用 ax1、ax2設(shè)置相應(yīng)的參數(shù)
    ax3.plot(x, y3, 'b', label='first')
    ax3.set_xlabel('時(shí)間', fontsize=15)
    ax3.set_ylabel('銷售目標(biāo)達(dá)成', fontsize=15)
    ax3.axhline(y=1, lw=2, ls="--", color="g") #添加參考線
    ax4.plot(x, y4, 'r', label='second')
    ax4.set_xlabel('時(shí)間', fontsize=15)
    ax4.set_ylabel('訂單量目標(biāo)達(dá)成', fontsize=15)
    ax4.axhline(y=1, lw=2, ls="--", color="g")
    plt.xticks(rotation=30)

    # x坐標(biāo)增加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

# 樣式添加
    #  添加表內(nèi)容
    df_html = table_today.to_html(escape=False, index=False)
    head = \
        """
        <head> '''設(shè)置文檔標(biāo)題和其它在網(wǎng)頁中不顯示的信息'''
            <meta charset="utf-8"> //這里是元信息陪竿。里面可以定義網(wǎng)
頁的字符格道式和寫出網(wǎng)頁的詳細(xì)信息（如禽翼。作者。關(guān)鍵詞等）
            <STYLE TYPE="text/css" MEDIA=screen>

                table.dataframe {
                    border-collapse: collapse;
                    border: 2px solid #a19da2;
                    /*居中顯示整個(gè)表格*/
                    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是固定格式

    # 構(gòu)造模板的附件（100）

    body = \
        """
        <body>

        <div align="center" class="header">
            <!--標(biāo)題部分的信息-->
            <h1 align="center">Adventure Works Cycles 簡報(bào)</h1>
        </div>

        <hr>

        <div class="content">
            <!--正文內(nèi)容-->
            <h2>戰(zhàn)績</h2>

            <div>
                <h4></h4>
                {df_html}

            </div>
            <div>
                <h4></h4>
                <center><img src="cid:io"></center>

            </div>
            <div>
            <hr>
            <p style="text-align: center">
                        —— 本次報(bào)告完 ——
                    </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

運(yùn)行本py文件時(shí) 執(zhí)行以下內(nèi)容

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)

    # 獲取要發(fā)送的 html 和 圖片 信息
    content_html,img_file = get_message(test_to_html)


    # 目標(biāo)：設(shè)置email要發(fā)送的內(nèi)容族跛，也可以直接就是 MIMEText 的內(nèi)容闰挡，
    # 但是下面這樣可以追加文本、html礁哄、圖片等信息
    message = MIMEMultipart()
    # message = MIMEMultipart('alternative')

    # 1长酗、添加文本內(nèi)容
    # content_text = '郵件發(fā)送測(cè)試中..'
    # message.attach(MIMEText(content_text,'plain','utf-8'))

    # 2、添加 html 格式的內(nèi)容
    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)


    # 最后：設(shè)置收件箱地址夺脾，可以發(fā)郵件了
    receivers = ['abai@qq.com','obvrou.163.com']
    # 發(fā)送郵件

    send_email('每日簡報(bào)',message,receivers)

GIF.gif

• 添加圖片的實(shí)現(xiàn)原理：
  將本地圖片加入到郵件附件中:
  m_img.add_header('Content-ID', '<id>')用來設(shè)置圖片id
  然后通過郵件html文本引入圖片:
  <img src="cid:id">
  參見：https://blog.csdn.net/qq_38161040/article/details/90140231

添加附件等其他操作 參見：https://blog.csdn.net/qq_33472765/article/details/81022471
擴(kuò)展標(biāo)題add_header:
https://www.cnblogs.com/zhangxinqi/p/9113859.html
一次性多張圖片發(fā)送：
https://www.jb51.net/article/167391.htm