python发邮件模板

代码模板

# -*- coding: utf-8 -*-
"""
Created on Thu Apr  9 09:57:43 2020

@author: Administrator
"""

from pyhive import presto
import os
import pandas as pd
import warnings
from smtplib import SMTP_SSL
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import numpy as np
from email.mime.image import MIMEImage
from email.mime.base import MIMEBase
from email import encoders



warnings.filterwarnings("ignore")
######设置工作目录
os.chdir(r'C:\Users\Administrator\Documents\python\贷前监控')

conn = presto.connect(host = '000.00.0.00', port=8888, username='username',catalog='hive')

#流入口径的目标
sql1 = '''
SELECT
	to_char(t1.data_date,'yyyy-mm') month_d
	,t1.data_date
	,DATE_ADD('day',  -DAY_OF_WEEK(t1.data_date) +1 , t1.data_date) week_d
	,t1.product_type
	,case when  t1.IsReapply = '首贷'  then    t1.CHANNEL  else  '复贷' end  as  CHANNEL
		,case when  t1.IsReapply = '首贷'  then    t1.CHANNEL_type  else  '复贷' end  as  CHANNEL_type
	,t1.capital_side
	,t1.IsReapply
	,t1.MOB 
	,case when t1.should_amt <=400 then '400'
			  when t1.should_amt <=600 then '600'
			  when t1.should_amt <=800 then '800'
			  when t1.should_amt <=1200 then '1200'
			  else '>1200' end as   amt_level
	,sum(should_amt)              should_amt
	,sum(over_due_amt)            overdue_amt
	,sum(should_cnt)              should_cnt
	,sum(over_due_cnt)            overdue_cnt
	,sum(t3_repay)                t3
	,sum(t7_repay)                t7
	,sum(t7_acct)                 t7_acct   ---7天未还案件数
	,sum(t7_call)                 t7_call   ---7天未还案件拨打次数
	,sum(t7_con_call)             t7_con_case_cnt ---t7未还案件接通案件
	,sum(t7_con_other_call)       t7_con_other_case_cnt ---t7未还案件三方接通案件
	,sum(t7_con_br_call)       t7_con_br_case_cnt ---t7未还案件本人接通案件
	,sum(t7_call_time)            t7_call_time   ---t7拨打时间
from urge_tmp.temp_hh_repay_lid  t1
left join  urge_tmp.temp_hh_tel_record_dq  t2
on  t1.acct_no = t2.acct_no   and t2.data_date>=t1.data_date
where to_char(t1.data_date,'yyyy-mm')  >= '2019-12'
group by 1,2,3,4,5,6,7,8,9,10
order by 1
'''
df = pd.read_sql_query(sql1, conn)
df.columns = ['月','时间','周','产品','渠道','渠道类型','资金方',
              '首复贷','MOB','amt_level',
              '应还金额','逾期金额','应还笔数','逾期笔数',
'T3回收金额','T7回收金额','T7未还案件','T7未还案件拨打次数','T7未还案件接通量',
'T7未还案件三方接通量','T7未还案件本人接通量','T7未还案件拨打时长']
#df['时间'] = df['时间'].apply(lambda x: x[5:10])
#df['周'] = df['周'].apply(lambda x: x[5:10])

for i in df.columns[10:]:
    df[i] = df[i].astype(float)

def trans_data(df,index):    
    df_1 = df.pivot_table(index= index,
    values=['逾期笔数','逾期金额','T3回收金额','T7回收金额','T7未还案件', 'T7未还案件接通量', 'T7未还案件本人接通量'
    			, 'T7未还案件三方接通量', 'T7未还案件拨打次数', 'T7未还案件拨打时长','应还金额'],aggfunc = 'sum').sort_index(by=index)
    
    df_1['T0'] = df_1['逾期金额']/df_1['应还金额']
    df_1['T3'] = (df_1['逾期金额'] - df_1['T3回收金额'])/df_1['应还金额']
    df_1['T7'] = (df_1['逾期金额'] - df_1['T7回收金额'])/df_1['应还金额']
    df_1['T3催回率'] = df_1['T3回收金额']/df_1['逾期金额']
    df_1['T7催回率'] = df_1['T7回收金额']/df_1['逾期金额']
    df_1['T7触达率'] = df_1['T7未还案件接通量']/df_1['T7未还案件']
    df_1['T7本人触达'] = df_1['T7未还案件本人接通量']/df_1['T7未还案件']
    df_1['T7三方触达'] = df_1['T7未还案件三方接通量']/df_1['T7未还案件']
    df_1['T7案均拨打次数'] = df_1['T7未还案件拨打次数']/df_1['T7未还案件']
    df_1['T7案均拨打时长'] = df_1['T7未还案件拨打时长']/df_1['T7未还案件']
    df_1 = df_1.fillna(0)
    for i in ['T0','T3','T7','T3催回率','T7催回率','T7触达率','T7本人触达','T7三方触达']:
        df_1[i] = df_1[i].apply(lambda x: '%.2f%%'%(x*100))
    for i in ['T7案均拨打次数','逾期笔数','T7案均拨打时长','T7未还案件']:
        df_1[i] = df_1[i].astype(int)
    df_2 = df_1[['逾期笔数','T7未还案件','T0', 'T3', 'T7','T7催回率','T7触碰率','T7本人触碰',
				'T7三方触碰','T7案均拨打次数','T7案均拨打时长']]
    return df_2

df_mob1 = df[df['MOB']=='MOB1']   # mob1
df1_fd = df_mob1[df_mob1['渠道类型'].isin(['其他渠道', '首贷主要渠道'])] # 首贷MOB1
df1_sd = df_mob1[df['首复贷'] == '首贷'] # 复贷MOB1
df1_xqd = df_mob1[df['渠道类型'] == '新渠道'] # 新渠道MOB1
df1_xxl = df_mob1[df['渠道类型'] == '信息流'] # 信息流MOB1

####周
#MOB1复贷
MOB1_fd = trans_data(df1_fd,'周')
MOB1_fd = MOB1_fd.iloc[-6:-1,:]
#MOB1首贷
MOB1_sd = trans_data(df1_sd,'周')
MOB1_sd = MOB1_sd.iloc[-6:-1,:]
#MOB1新渠道
MOB1_xqd = trans_data(df1_xqd,'周')
MOB1_xqd = MOB1_xqd.iloc[-6:-1,:]
#MOB1信息流
MOB1_xxl = trans_data(df1_xxl,'周')
MOB1_xxl = MOB1_xxl.iloc[-6:-1,:]

filename = r'C:\Users\Administrator\Documents\python\贷前监控\detail.xlsx'
df.to_excel(filename,encoding = 'utf-8')


mailtext = u"""
<!DOCTYPE html>
<html>
<meta charset="utf-8">

    <p style="font-size:20px;color:#A7C942;font-family:Microsoft YaHei">各位好,本邮件为贷前T7监控邮件，具体的数据明细详见附件Detail<br></p>
    <style type="text/css">
    table 
      {
      font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;
      width:100%%;
      border-collapse:collapse;
      }
      
<style type="text/css">
table
  {
  font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;
  width:100%%;
  border-collapse:collapse;
  }

table td, table  th
  {
  font-size:12px;
  border:1px solid #98bf21;
  text-align:right;
  padding:2px 3px 2px 3px;
  }

table  th
  {
  font-size:12px;
  text-align:center;
  padding-top:5px;
  padding-bottom:4px;
  background-color:#EAF2D3;
  color:#000000;
  }

table tr.alt td
  {
  color:#000000;
  text-align:right;
  background-color:#EAF2D3;
  }
</style>
       <body>
       <p style="font-size:16px;font-weight:bold">注：以下关于催收的过程指标均是针对T7未还案件的</p>
        <p style="font-size:16px;font-weight:bold">1：复贷MOB1近5周T7表现%s</p>
       
        <!-- img src="cid:fig" alt="dns配置"   -->
       
        <p style="font-size:16px;font-weight:bold">2：首贷MOB1近5周T7表现%s</p>
        <p style="font-size:16px;font-weight:bold">3：新渠道MOB1近5周T7表现%s</p>
        <p style="font-size:16px;font-weight:bold">4：信息流MOB1近5周T7表现%s</p>

         </body>
        <br>
        </html> """ \
        % (MOB1_fd.to_html(index=True),MOB1_sd.to_html(index=True),\
        MOB1_xqd.to_html(index=True),MOB1_xxl.to_html(index=True))
              
mailInfo = {
        "from": "",
        "to":"***.com,****.com",
        "cc": "",
        "hostname": "邮箱配置",
        "username":"****",
        "password":"***",
        "mailsubject":"助贷目标监控",
        "mailtext": u"邮件正文",
        "mailencoding": "utf-8"
    }

if __name__ == '__main__':
    htm = MIMEText(mailtext, 'html', 'utf-8')
    msg = MIMEMultipart()    
    msg.attach(htm) 
    #---------------------设置附件-------------------------#
    file_name = filename
    contype = 'application/octet-stream'
    maintype, subtype = contype.split('/', 1)
    data = open(file_name, 'rb')
    file_msg = MIMEBase(maintype, subtype)
    file_msg.set_payload(data.read( ))
    data.close()
    encoders.encode_base64(file_msg)
    ## 设置附件头
    basename = os.path.basename(file_name)
    file_msg.add_header('Content-Disposition','attachment', filename = basename)
    msg.attach(file_msg)
    #---------------------------附件设置完毕--------------------#
         
try:
        smtp = SMTP_SSL(mailInfo["hostname"])
        smtp.set_debuglevel(0)
        smtp.ehlo(mailInfo["hostname"])
        smtp.login(mailInfo["username"], mailInfo["password"])
        msg["Subject"] = Header(mailInfo["mailsubject"], mailInfo["mailencoding"])
        msg["from"] = mailInfo["from"]
        msg["to"] = mailInfo["to"]
        msg['cc'] = mailInfo["cc"]
        smtp.sendmail(mailInfo["from"], mailInfo["to"].split(","), msg.as_string())
        smtp.quit()
        print('success-test')
except Exception as e:
        print('Exception: ', e)

邮件格式显示