python发送带图片和参数的html格式的邮件

#coding:utf-8
#!/usr/local/python3.6/bin/python3
import pandas as pd
import pyodbc
import matplotlib.pyplot as plt
import waterfall_chart
import numpy as np
from datetime import datetime, timedelta
import smtplib
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart 
from email.header import Header
import os

cnxn = pyodbc.connect( 'DRIVER={SQL Server};SERVER=;DATABASE=;UID=;PWD=')#DRIVER={ODBC Driver 17 for SQL Server}
now = datetime.now()
current_wn = now.isocalendar()[1]
last_week = now - timedelta(weeks=1)
last_wn = last_week.isocalendar()[1]

query_a = f"SELECT * FROM table1 where date in ({current_wn},{last_wn})"
data_a = pd.read_sql_query(query_a, cnxn)
query_b = f"SELECT * FROM table2 where date in ({current_wn},{last_wn})"
data_b = pd.read_sql_query(query_b, cnxn)
query_c = f"SELECT * FROM table3 where date in ({current_wn},{last_wn})"
data_c = pd.read_sql_query(query_c, cnxn)

dimentsions_a = ['Region','aSource','Series_of_Interest']
dimentsions_b = ['Region','Data_Source','Series_of_Interest']
dimentsions_c = ['Vehicle_Type','Region','Series_of_Interest']

def fun1(dimentsions,data,cata):
    for title in dimentsions.split(','):
        df = data.groupby(['date', title])['Id'].nunique().reset_index()
        Last_week=df[df['date']==last_wn]['Id'].sum()
        new_row = pd.DataFrame({title: ['Initial'], 'Increase': [Last_week]})
        pivot_df = df.pivot(index=title, columns='date', values='Id').reset_index()
        pivot_df[[current_wn,last_wn]] = pivot_df[[current_wn,last_wn]].fillna(0)
        pivot_df['Increase'] = pivot_df[current_wn] - pivot_df[last_wn]
        Volatility = round(pivot_df['Increase'].std() / pivot_df['Increase'].mean(),4)
        dict_Volatility[dimentsions]=Volatility
        df6 = pivot_df.loc[np.abs(pivot_df['Increase']).nlargest(6).index]
        df6 = pd.concat([new_row,df6], ignore_index=True)
        #df6 = new_row.append(df6)
        rows = len(pivot_df)
        if rows>6:
            other_val = pivot_df.drop(df6.index)['Increase'].sum()
            other_df = pd.DataFrame({title: ['Others'], 'Increase': [other_val]})
            df6 = pd.concat([df6, other_df], ignore_index=True)
        labels = df6[title]
        values = df6['Increase']
        waterfall_chart.plot(labels, values, Title=title+' Week'+str(current_wn), net_label='Total', formatting="{:,.1f}")
        filename = cata+'_'+title + '_Week_' + str(current_wn) + '.png'
        plt.savefig(filename, bbox_inches='tight')
        plt.clf()

dict_Volatility = {}
for i in dimentsions_a:
    try:
        fun1(i,data_a,'a')
    except Exception as e:
        print("Exception:"+str(repr(e)))
        
a_dict_Volatility= {k: v for k, v in sorted(dict_Volatility.items(), key=lambda item: item[1],reverse=True)}
a_1 = list(a_dict_Volatility.keys())[0]
a_1_value = dict_Volatility[a_1]
a_2 = list(a_dict_Volatility.keys())[1]
a_2_value = dict_Volatility[a_2]
a_3 = list(a_dict_Volatility.keys())[2]
a_3_value = dict_Volatility[a_3]

dict_Volatility = {}
for i in dimentsions_b:
    try:
        fun1(i,data_b,'b')
    except Exception as e:
        print("Exception:"+str(repr(e)))

b_dict_Volatility= {k: v for k, v in sorted(dict_Volatility.items(), key=lambda item: item[1],reverse=True)}
b_1 = list(b_dict_Volatility.keys())[0]
b_1_value = dict_Volatility[b_1]
b_2 = list(b_dict_Volatility.keys())[1]
b_2_value = dict_Volatility[b_2]
b_3 = list(b_dict_Volatility.keys())[2]
b_3_value = dict_Volatility[b_3]

dict_Volatility = {}        
for i in dimentsions_c:
    try:
        fun1(i,data_c,'c')
    except Exception as e:
        print("Exception:"+str(repr(e)))
        
c_dict_Volatility= {k: v for k, v in sorted(dict_Volatility.items(), key=lambda item: item[1],reverse=True)}
c_1 = list(c_dict_Volatility.keys())[0]
c_1_value = dict_Volatility[c_1]
c_2 = list(c_dict_Volatility.keys())[1]
c_2_value = dict_Volatility[c_2]
c_3 = list(c_dict_Volatility.keys())[2]
c_3_value = dict_Volatility[c_3]

mail_msg = """
<html>
<meta charset="UTF-8">
<head>
    <title>CRM Data Analysis</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2 user-scalable = yes">
</head>
    <body>
        <h3>Growth of sales figures in Germany last week compared to the previous week</h3>
        <h5>1. as data (%s：%s；%s：%s；%s：%s):</h5>
        <table><tr>
          <td><img src="cid:imagea_Region" contenteditable="false"/></td>
          <td><img src="cid:imagea_aSource" contenteditable="false"/></td>
          <td><img src="cid:imagea_Series_of_Interest" contenteditable="false"/></td>
          </tr></table>
        <h5>2. b data (%s：%s；%s：%s；%s：%s):</h5>
        <table><tr>
          <td><img src="cid:imageb_Region"  contenteditable="false"/></td>
          <td><img src="cid:imageb_Data_Source"  contenteditable="false"/></td>
          <td><img src="cid:imageb_Series_of_Interest"  contenteditable="false"/></td>
          </tr></table>
        <h5>3. cs data (%s：%s；%s：%s；%s：%s):</h5>
        <table><tr>
          <td><img src="cid:imagec_Vehicle_Type"  contenteditable="false"/></td>
          <td><img src="cid:imagec_Region"  contenteditable="false"/></td>
          <td><img src="cid:imagec_Series_of_Interest" contenteditable="false"/></td>
          </tr></table>          
    </body>
"""%(a_1,a_1_value,a_2,a_2_value,a_3,a_3_value,
     b_1,b_1_value,b_2,b_2_value,b_3,b_3_value,
     c_1,c_1_value,c_2,c_2_value,c_3,c_3_value)

tolist = ['****@qq.com','****@qq.com']# 收件人
smtpHost='smtp.office365.com'
smtpPort='587'
msg = MIMEMultipart('related')
msg["From"] = "****@qq.com"  #发件人
subject="xxxxxx"
msg["Subject"] = Header(subject, 'utf-8').encode()  #邮件主题
msg['To'] = ", ".join(tolist)  #收件人
#self.msg["Cc"] = ", ".join(tolist_cc) #抄送人
msgtext = MIMEText(mail_msg,_subtype='html',_charset='utf-8')
msg.attach(msgtext)

#添加图片
#dimentsions_as = ['Region','aSource','Series_of_Interest']
#dimentsions_b = ['Region','Data_Source','Series_of_Interest']
#dimentsions_c = ['Vehicle_Type','Region','Series_of_Interest']
for i in dimentsions_a:
    fp = open('a_'+i+'_Week_' + str(current_wn) + '.png', 'rb')
    img = MIMEApplication(fp.read())
    img.add_header('Content-Disposition', 'attachment', filename='<imagea_'+i+'>')
    msg.attach(img)
    fp.close()

#添加图片
for i in dimentsions_b:
    fp = open('b_'+str(i)+'_Week_' + str(current_wn) + '.png', 'rb')
    img = MIMEApplication(fp.read())
    img.add_header('Content-Disposition', 'attachment',  filename='<imageb_'+str(i)+'>')
    msg.attach(img)
    fp.close()

#添加图片
for i in dimentsions_c:
    fp = open('c_'+i+'_Week_' + str(current_wn) + '.png', 'rb')
    img = MIMEApplication(fp.read())
    img.add_header('Content-Disposition', 'attachment', filename='<imagec_'+i+'>')
    msg.attach(img)
    fp.close()

smtp = smtplib.SMTP(smtpHost,smtpPort)
smtp.set_debuglevel(True)
smtp.ehlo()
smtp.starttls()

smtp.login("****@qq.com","123456")#邮箱名和密码
smtp.sendmail(msg["From"],tolist, msg.as_string())
#添加抄送人 smtp.sendmail(msg["From"],tolist+tolist_cc, msg.as_string())

for i in dimentsions_a:
    os.remove('a_'+i+'_Week_' + str(current_wn) + '.png')

for i in dimentsions_b:
    os.remove('b_'+str(i)+'_Week_' + str(current_wn) + '.png')

for i in dimentsions_c:
    os.remove('c_'+str(i)+'_Week_' + str(current_wn) + '.png')