Python 发邮件及邮件附件

连接mysql库，定时发邮件

#!/usr/bin/env python
# -*- encoding:utf-8  -*-
"""
 @File :mail.py
 @time : 2021/1/15  14:22
 @Author : huixb
 """

import pandas as pd
from sqlalchemy import create_engine, text
import time
import datetime
from io import StringIO

date_now = time.strftime('%Y-%m-%d', datetime.datetime.today().timetuple())
date_now1 = time.strftime('%Y-%m-%d', (datetime.datetime.today()).timetuple())
# 连接配置信息
engine = create_engine('mysql+pymysql://devel_ro:FenBeiHaoZuo@117.121.135.90:3306/库?charset=utf8')
engine1 = create_engine(
    'mysql+pymysql://名:密码=@180.101.195.217:5040/库?charset=utf8',
    encoding='utf-8', convert_unicode=True)
conn = engine.connect()
conn1 = engine1.connect()

sql_1 = """

"""

sql_2 = """

"""

sql_4 = """

"""

df_sql_attached_file_1 = pd.read_sql(sql_4, conn)
s1 = StringIO()
df_sql_attached_file_1.to_csv(s1)

df_sql_1 = pd.read_sql(sql_1, conn)
df_sql_2 = pd.read_sql(sql_2, conn1)

###关闭连接
conn.close()

mailtext = u"""各位好，以下是%s的，请查收！。<br><br>

1-第一类 %s <br>
2-第二类 %s <br>


""" % (date_now1, df_sql_1.to_html(), df_sql_2.to_html())

mailtext = mailtext.replace('class="dataframe"', 'style="border-collapse: collapse"')

import smtplib
from smtplib import SMTP_SSL
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
from email.mime.application import MIMEApplication

mailInfo = {
    "from": "mail@mail.com",
    "to": "mail@mail.com",
    "cc": "",
    "hostname": "smtp.exmail.qq.com",
    "username": "mail@mail.com",
    "password": "password",
    "mailsubject": u"【题目(%s)】" % date_now1,
    "mailtext": u"邮件正文",
    "mailencoding": "utf-8"
}

if __name__ == '__main__':
    msg = MIMEMultipart('alternative')
    htm = MIMEText(mailtext, 'html', 'utf-8')
    msg.attach(htm)

    att1 = MIMEApplication(s1.getvalue().encode('gbk', 'ignore'))
    att1["Content-Type"] = 'application/octet-stream'
    att1.add_header('content-disposition', 'attachment', filename=u'题目.csv')
    msg.attach(att1)

    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()

 

连接hive版

#!/usr/bin/env python
# -*- encoding:utf-8  -*-
"""
 @File :mail.py
 @time : 2021/1/15  14:22
 @Author : huixb
 """

# -*- coding: utf8 -*-
import time
import datetime
import sys, getopt
import subprocess
import traceback
import smtplib
from smtplib import SMTP_SSL
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
from email.mime.application import MIMEApplication
import os
import sys

reload(sys)
sys.setdefaultencoding('utf8')

# date_now = time.strftime('%Y-%m-%d',time.strptime(str(datetime.date.today()),"%Y-%m-%d"))
date_now1 = time.strftime('%Y-%m-%d', (datetime.datetime.today() - datetime.timedelta(days=1)).timetuple())
now = datetime.datetime.now()
delta1 = datetime.timedelta(days=1)
onedayago = now - delta1
date_now = now.strftime('%Y-%m-%d')
date_1dayago = onedayago.strftime('%Y-%m-%d')
# 附件名称
filename = 'sell_ribao%s.csv' % date_now1
### SQL语句

sql_1 = """

"""

##附件头
fileHead = "xx,xx,xx,xx\n".encode(
    "gbk").decode("gbk")


# 获取hiveSql的结果,输入:sql,输出:结果
def getHiveSqlResult(sql):
    result = ''
    cmd = 'hive -e """' + sql.replace('"', "\'") + '"""'
    # print(cmd)
    csvFile2 = ''
    try:
        import codecs
        csvFile2 = codecs.open(filename, 'w', 'gbk')
        csvFile2.write(fileHead)
        p = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE)
        while True:
            buff = p.stdout.readline()
            if buff == b'' and p.poll() != None:
                break
            else:
                tempStr = buff.decode('utf-8')
                if tempStr == '':
                    break;
                ret = tempStr.find("WARN:", 0, len(tempStr))
                if ret != -1:
                    break;
                tempStr = tempStr.replace('\t', ',')
                tempStr = tempStr.encode("gbk").decode("gbk")
                csvFile2.write(tempStr)
        # print(result)
        # return result
    except Exception as e:
        print("message is:%s") % (str(e))
        traceback.print_exc()
    finally:
        csvFile2.close()


# 得到html格式的内容
def getHtmlContent():
    getHiveSqlResult(sql_1)
    mailtext = u"""本邮件是自动邮件，每日发送。<br><br>
    截止%s本月明细。<br>
    数据在附件里。<br>
    <br>""" % date_now1
    # 调整表格格式，文字右对齐
    mailtext = mailtext.replace('class="dataframe"', 'style="border-collapse: collapse"')
    mailtext = mailtext.replace('<td>', '<td align="center">')
    return mailtext


# 发送邮件
def sendMain(recevEmail, ccEmail, sendEmail, sendPasswd, sendEmailHost, sendMailSubject, sendMailContent):
    mailInfo = {
        "from": sendEmail,
        ##"to1" :"xingw@2345.com",
        "to": recevEmail,
        ##"to":"wangxr@2345.com",
        "cc": ccEmail,
        "hostname": sendEmailHost,
        "username": sendEmail,
        "password": sendPasswd,
        "mailsubject": sendMailSubject,
        "mailtext": sendMailContent,
        "mailencoding": "utf-8"
    }
    mailtext = getHtmlContent()
    msg = MIMEMultipart('alternative')
    htm = MIMEText(mailtext, 'html', 'utf-8')
    msg.attach(htm)
    att = MIMEText(open(filename, 'rb').read(), 'base64', 'gbk')
    att["Content-Type"] = 'application/octet-stream'
    att["Content-Disposition"] = 'attachment; filename=%s' % filename
    msg.attach(att)
    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()


def main(argv):
    # 收件人邮箱
    recevEmail = 'xx@xx.com'
    # 抄送人邮箱
    ccEmail = 'xx@xx.com'
    # 发送人邮箱账号
    sendEmail = 'xx@xx.com'
    # 发送人邮箱密码
    sendPasswd = 'xx'
    # 邮件服务器
    sendEmailHost = 'smtp.exmail.qq.com'
    # 标题
    sendMailSubject = ''
    # 正文
    sendMailContent = ''
    if len(argv) < 3:
        print(
            'vip_loan_refuse_rate.py -r <receiver email> -o <cc email> -s <sender email> -p <sender Passwd> -f <sender Host> -t <title> -c <sender content>')
        sys.exit(-1)
    try:
        opts, args = getopt.getopt(argv, "hr:o:s:p:f:t:c:a")
    except getopt.GetoptError:
        print(
            'vip_loan_refuse_rate.py -r <receiver email> -o <cc email> -s <sender email> -p <sender Passwd> -f <sender Host> -t <title> -c <sender content>')
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print(
                'sxj_loan_refuse_rate.py -r <receiver email> -o <cc email> -s <sender email> -p <sender Passwd> -f <sender Host> -t <title> -c <sender content>')
            sys.exit()
        elif opt in ("-r"):
            recevEmail = arg
        elif opt in ("-s"):
            sendEmail = arg
        elif opt in ("-o"):
            ccEmail = arg
        elif opt in ("-p"):
            sendPasswd = arg
        elif opt in ("-f"):
            sendEmailHost = arg
        elif opt in ("-t"):
            sendMailSubject = arg
        elif opt in ("-c"):
            sendMailContent = arg
    if len(sendMailContent) <= 0:
        sendMailContent = getHtmlContent()
        sendMailSubject = u"【xxxx(%s)】" % date_now1
    sendMain(recevEmail, ccEmail, sendEmail, sendPasswd, sendEmailHost, sendMailSubject, sendMailContent)


if __name__ == '__main__':
    main(sys.argv[1:])