定时执行任务，发送带excel附件及excel内容展示邮件

importrequests,codecs
importpandas as pd
importos

importsmtplib,os,json
from email.mime.multipart importMIMEMultipart
from email.mime.text importMIMEText
from email.mime.application importMIMEApplication

from openpyxl importload_workbook
importreadConfig as Rc



importtime
from apscheduler.schedulers.blocking importBlockingScheduler


importpgSqlQuery as Pq






defmailWrite(filepath,sheet):
    '''写邮件，读取excel文件内容作为邮件正文'''df2 = pd.read_excel(filepath, sheet_name=sheet)
    #设置html存放目录
    htmlfiles = 'pgResultHtml'
    if notos.path.exists(htmlfiles):
        os.mkdir(htmlfiles)
    htmlName = sheet + '.html'htmlPath =os.path.join(htmlfiles,htmlName)
    #生成html结果文件
    with codecs.open(htmlPath, 'w', 'utf-8') as html_file:
        html_file.write(df2.to_html(header=True, index=False))
    #打开并读取html结果文件内容
    with open(html_file.name,'r') as html_f:
        htmlContent =html_f.read()
        #print(htmlContent)
    #返回文件内容
    returnhtmlContent


defsendEmail(fileName):
    '''发送带附近及展示附件内容邮件'''rc =Rc.ReadConfig()
    Smtp_Server = rc.getEmailInfo('Smtp_Server')
    sender = rc.getEmailInfo('Smtp_Sender')
    
    pwd = rc.getEmailInfo('Password')
    receiver = rc.getEmailInfo('Pre_Receiver')
    Msg_Title = rc.getEmailInfo('Msg_Title')
    Text_description = rc.getEmailInfo('Text_description')

    Receiver =[]
    for receiver in receiver.split(','):
        Receiver.append(receiver)
    _user =sender
    _pwd =pwd
    _to =Receiver
    print(_to)
    #如名字所示Multipart就是分多个部分
    msg =MIMEMultipart()
    msg["Subject"] =Msg_Title
    msg["From"] =_user
    msg["To"] = ",".join(_to)
    print(msg['To'])

    #---这是附件部分---
currentPath =os.getcwd()
    sqldataFile = os.path.join(currentPath, 'sqlDataFiles')

    targetPath =os.path.join(sqldataFile, fileName)
    #获取目标目录下的邮件附件
    resultFileList =os.listdir(targetPath)

    #发送多个附件的邮件，这里发送指定目录下所有类型一致的文件
    for resultFileName inresultFileList:
        #获取结果文件的绝对路径
        resultFilePath =os.path.join(targetPath, resultFileName)

        #---这是文字部分---

        #邮件正文内容

        #打开文件
        #filepath设置详细的文件地址
        filepath =resultFilePath
        #打开结果exel文件
        wb =load_workbook(filepath)
        #获取excel sheet名称
        sheets =wb.sheetnames
        #定义一个存放读取结果的dict
        htmlData ={}

        for sheet insheets:
            #调用excel转化html 函数
            content =mailWrite(filepath,sheet)
            #根据sheet页存储各个sheet的内容
            htmlData[sheet] =content

        contents = ''
        #遍历取出各个sheet的内容
        for ct inhtmlData.keys():
            contents += (ct + ':
' +htmlData[ct])
        #设置邮件正文
        Text_description = Text_description +contents
        html = MIMEText(Text_description, 'html', 'utf-8')
        msg.attach(html)

        #设置邮件附件
        with open(resultFilePath, 'rb') as f:
            part =MIMEApplication(f.read())
            part.add_header('Content-Disposition', 'attachment', filename=resultFileName)
            msg.attach(part)

    s = smtplib.SMTP(Smtp_Server, 25)  #连接smtp邮件服务器,端口默认是25
    s.login(_user, _pwd)  #登陆服务器
    s.sendmail(_user, _to, msg.as_string())  #发送邮件
s.close()




#设置初始值
i =46

defpgQuery(text):

    
    globali
    #设置文件名
    weekFileName = 'W' +str(i)
    #执行pg 查询，传递文件名，告知执行哪个文件下的sql
    sqlquery =Pq.PgSqlQuery(weekFileName)
    sqlquery.readSql()

    t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    #因为每周执行一次，每周执行目录不同，所以这里累加1，到下一个目录下
    i = i+ 1
    #发送邮件
sendEmail(weekFileName)
    print('{} --- {}'.format(text, t))





defpgQueryTask(text):
    '''定时任务，保证当前headers 持续有效'''currentPath =os.getcwd()

    rc =Rc.ReadConfig()
    payload = rc.getPgSQLInfo('payload')
    payload =json.loads(payload)
    url = rc.getPgSQLInfo('url')
    headers = rc.getPgSQLInfo('headers')
    headers =json.loads(headers)

    sqlPath = os.path.join(currentPath,'sql.txt')


    response = requests.request("POST", url, data=payload, headers=headers)

    print(response)
    print(response.json())
    t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    print('{} --- {}'.format(text, t))


scheduler =BlockingScheduler()

#设置每周5 上午 10：30执行一次
scheduler.add_job(pgQuery, 'cron', day_of_week=2, hour=16,minute=32, args=['pgQuery'])
scheduler.add_job(pgQueryTask, 'interval', hours = 1,minutes = 56, args=['pgQueryTask'])
scheduler.start()