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 encoderswarnings.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 = '''
SELECTto_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_2df_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"""


各位好,本邮件为贷前T7监控邮件，具体的数据明细详见附件Detail
注：以下关于催收的过程指标均是针对T7未还案件的
1：复贷MOB1近5周T7表现%s
2：首贷MOB1近5周T7表现%s
3：新渠道MOB1近5周T7表现%s
4：信息流MOB1近5周T7表现%s

 """ \% (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 = filenamecontype = '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)

邮件格式显示

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！