使用python 连接hive，并且发送邮箱

工作中，我们可能会遇到需要每天给领导发送邮箱，数据来源是hive，并且格式基本一致，那么下面这些代码就可是使用得到
# coding: utf-8
import  sys
reload(sys)
sys.setdefaultencoding('utf-8')
import time
import datetime
import smtplib
import os
from pyhive import hive
import numpy as np
import pandas as pd
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.header import Header
import logging

#连接hive数据库，读取原始数据
#try_except_else 语句,如果在try子句执行时没有发生异常，Python将执行else语句后的语句
def sqldf(sql,username='xxx',password='xxx',host ='xxx',port = xxx,database = 'xxx', auth="xxx"):
    _conn_status = True
    _max_retries_count = 1   # 设置最大重试次数
    _conn_retries_count = 1  # 初始重试次数
    _conn_retry_time = 1    # 重试等待时间为30秒
    while _conn_status and _conn_retries_count <= _max_retries_count:
        try:
            conn = hive.Connection(username = username, password = password, database = database,host = host, port = port, auth=auth)
            df = pd.read_sql(sql, con = conn)
            _conn_status = False  # 如果conn成功则_status为设置为False则退出循环，返回db连接对象
        except Exception as e:
            print('Error is:', e)
            _conn_retries_count += 1
            time.sleep(_conn_retry_time)
        else:
            return(df)
        finally:
            conn.close()


#选择需要展示的列
def get_show_column(df,show_column):
  df = df[show_column]
  return df
#得到展示表格，本次数用不着的
def get_table(df):
  table0=" "
  for i in range(len(df.index)):  #行数
      td=" "
      for j in range(len(df.columns)):  #列数
          if i%2==0:  #即是说行数是偶数
              td=td+'<td style="text-align:right;border-width: 1px;padding: 8px;border-style: solid;border-color: #333333;background-color: #ffffff;color:#000000">' + str(df.iloc[i,j]) + "</td>"
          else:
              td=td+'<td style="text-align:right;border-width: 1px;padding: 8px;border-style: solid;border-color: #333333;background-color: #F5F5F5;color:#000000">' + str(df.iloc[i,j]) + "</td>"
      table0 = table0+ "<tr>" + td + "</tr>"+"\n"
  return table0




#设置报表数据日期

date=time.strftime("%Y%m%d")

# 设置smtplib所需的参数
smtpserver = 'xxx'  #比如是QQ邮箱
username = 'xxx' #邮箱账号
password = 'xxx' #邮箱密码  不是我们平时说的哪种密码，而是认证的xxx
sender = 'xxx' #发件人邮箱
to_receiver=['xxx'] #收件人邮箱,多个人就是要list
cc_reciver=['xxx']  #抄送邮箱
#bcc_reciver=['xxx'] #密送邮箱


receiver = cc_reciver  + to_receiver
msg = MIMEMultipart('mixed')
msg['Subject'] = 'xxx'+date  #邮件标题
msg['From'] = 'xxx'    #邮件发件人
msg['To'] = ";".join(to_receiver)
msg['Cc'] = ";".join(cc_reciver)
#msg['Bcc'] = ";".join(bcc_reciver)
logger=logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)
handler=logging.FileHandler("./log.txt")
handler.setLevel(logging.DEBUG)
formatter=logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
console=logging.StreamHandler()
console.setLevel(logging.DEBUG)
logger.addHandler(handler)
logger.addHandler(console)
begin_time=datetime.datetime.now()
logger.info("-----开始XX情况统计 {begin_time}------")



#附件
logger.info("开始sql附件 "+str(datetime.datetime.now()))
sql_fujian='''
select distinct * from dm.cgm_xindai2_cuishou 
'''
df_yuqihuankuan_cust_e =sqldf(sql_fujian)
column = [xxx]  #是是sql每列的需要显示的字段
df_yuqihuankuan_cust_e.columns=column
logger.info("开始df_yuqihuankuan_cust "+str(datetime.datetime.now()))





#邮件正文构造
html = """\
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>XX标题</title>
</head>
<body>
<div id="container">各位好:<br>
  <br>
  <p>xxx</p>  #即是你正文的内容
  <br>
  <br>
</div>
</body>
</html>
"""

text_html = MIMEText(html,'html','utf-8')
msg.attach(text_html)

#构造附件
logger.info("构造附件中... "+str(datetime.datetime.now()))
fname="xxx_"+date+".xlsx"
writer=pd.ExcelWriter(fname)
df_yuqihuankuan_cust_e.to_excel(writer,sheet_name='xxx')
writer.save()

sendfile=open(fname,'rb').read()
text_att = MIMEText(sendfile, 'base64', 'utf-8')
text_att["Content-Type"] = 'application/octet-stream'
text_att.add_header('Content-Disposition', 'attachment', filename=fname)
msg.attach(text_att)

# 发送邮件
logger.info("准备发送... "+str(datetime.datetime.now()))
smtp = smtplib.SMTP()
smtp.connect('mail.sui.com')
smtp.login(username, password)
smtp.sendmail(sender, receiver, msg.as_string())
smtp.quit()
os.remove(fname)
logger.info("完成... "+str(datetime.datetime.now()-begin_time))
posted on 2020-08-31 10:49 小小喽啰阅读(585) 评论(0) 编辑收藏举报
使用python 连接hive，并且发送邮箱

搜索

常用链接

最新随笔

积分与排名

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论
