2021-01-24

如何用python自动发邮件呢?连接公司的hive数据库呢?,并且只发送一个Excel,有多个sheet里面,并且如何做到定时定点的发送呢

'''
@File   :sem客户分析(花满坡）
@author :  hmp
datetime:  2021-01-01 14:52
'''

import datetime
from email.mime.application import MIMEApplication
import pandas as pd
import os
import smtplib  # 邮件模块
from email.mime.multipart import MIMEMultipart
from email.utils import formataddr
from impala.dbapi import connect
import schedule

pd.set_option(
    'display.max_rows',
    100,
    'display.max_columns',
    1000,
    "display.max_colwidth",
    1000,
    'display.width',
    1000)

class SemCustomerAnalysis():
    __files__ = '作业情况.xlsx'  #文件名称
    __sheet__ = ['米堆作业',
                 '平均拨打次数分析表', '销售跟进时效分析表', '渠道效率分析表', '电销数据明细表'
                ] #一个Excel文件里面包含多个sheet
    con = connect(host="123,123,123", user="work", password="8888888888", port=123400,
                  auth_mechanism="PLAIN")
                  #公司hive的账号密码,需要开发者给到

    def __init__(self):
        """
        执行父类的构造方法
        """
        super(SemCustomerAnalysis, self).__init__()

    def get_data(self):
        print('start up!!')
        sql1 = f'''
                         select *
                         from table1
                         
        '''
        #第一个sheet类容,后面每一个sql,都将存放到一个Excel里面

        sql2 = f'''
                    select *
                         from table2
        '''

        sql3 = f'''
                select *
                         from table3

        '''

        sql4 = f'''
                select *
                         from table4
        '''

        sql5 = f'''
                select *
                         from table5
                '''

        writer = pd.ExcelWriter(self.__files__) #过去文件名称,Excel

        sheet_list = self.__sheet__   #Excel里面到每个sheet
        sql_list = [sql1, sql2, sql3, sql4, sql5]

        # dic = {'sheet1':sql1,'sheet2':sql2,'sheet3':sql3,'sheet4':sql4','sheet5':sql5}
        dic = dict(zip(sql_list, sheet_list))
        # print(dic)

        for i in dic:
            res = pd.read_sql_query(i, self.con) #遍历每一个sheet
            # res = pd.read_sql_query(
            #     rewrite_sql(
            #         i,
            #         config.data_config.env),
            #     self.con)
            res.to_excel(excel_writer=writer, sheet_name=dic[i], index=False)
        writer.save()
        writer.close()
        print("文件{}生成成功".format(self.__files__))
        return 'ok'

    def eSend(self):
        self.get_data()
        name = self.__files__
        sender = '123456789@qq.com'    #发送者到QQ账号
        my_pass = 'mavqldnafhnuibdh'   #发送者到QQ邮箱密码,可以用自己的QQ获取
        receiver = [
            "yangyuze@lililio.com","shenchen@lilili.com"
           ]   #接受邮件的人,可以用公司的邮箱
        # receiver = ['jiaoran6795@dingtalk.com']

        try:
            filepath = './' + name
            msg = MIMEMultipart()
            msg['From'] = formataddr(["数据组", sender])
            msg['To'] = ','.join(receiver)
            msg['Subject'] = "作业数据_{}，请注意查收！".format(
                datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
            xlsxpart = MIMEApplication(open(filepath, 'rb').read())
            basename = "{}-作业数据.xlsx".format(
                datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
            xlsxpart.add_header(
                'Content-Disposition',
                'attachment',
                filename=(
                    'gbk',
                    '',
                    basename))
            msg.attach(xlsxpart)
            server = smtplib.SMTP_SSL("smtp.qq.com", 465)
            server.login(sender, my_pass)
            server.sendmail(sender, receiver, msg.as_string())
            server.quit()
            print("邮件发送成功")
            if os.path.exists(filepath):
                os.remove(filepath)
                print("文件已删除")
            else:
                print("没有文件可以供删除")
        except Exception as e:
            print(e)
        return "ok"

def main():
    sem = SemCustomerAnalysis()
    return sem.eSend()

if __name__ == '__main__':
    job = main
    schedule.every().day.at('17:21').do(job) #设置想要发的时间点
    schedule.every().day.at('22:29').do(job)
    # schedule.every().day.at('09:00').do(job)
    # schedule.every().day.at('10:00').do(job)
    # schedule.every().day.at('11:30').do(job)
    # schedule.every().day.at('12:00').do(job)
    # schedule.every().day.at('13:00').do(job)
    while True:
        schedule.run_pending()