具体参看如下代码
import requests,codecs import pandas as pd import os import smtplib,os,json from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText from email.mime.application import MIMEApplication from openpyxl import load_workbook import readConfig as Rc import time from apscheduler.schedulers.blocking import BlockingScheduler import pgSqlQuery as Pq def mailWrite(filepath,sheet): '''写邮件，读取excel文件内容作为邮件正文''' df2 = pd.read_excel(filepath, sheet_name=sheet) # 设置html存放目录 htmlfiles = 'pgResultHtml' if not os.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) # 返回文件内容 return htmlContent def sendEmail(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 in resultFileList: # 获取结果文件的绝对路径 resultFilePath = os.path.join(targetPath, resultFileName) # ---这是文字部分--- # 邮件正文内容 # 打开文件 # filepath设置详细的文件地址 filepath = resultFilePath # 打开结果exel文件 wb = load_workbook(filepath) # 获取excel sheet名称 sheets = wb.sheetnames # 定义一个存放读取结果的dict htmlData = {} for sheet in sheets: # 调用excel转化html 函数 content = mailWrite(filepath,sheet) # 根据sheet页存储各个sheet的内容 htmlData[sheet] = content contents = '' # 遍历取出各个sheet的内容 for ct in htmlData.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 def pgQuery(text): global i # 设置文件名 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)) def pgQueryTask(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()