python实现读取数据库生成Excel然后发送邮件

# -*- coding:UTF-8 -*-
from app import app,dirpath
from flask import request,render_template,flash,abort,url_for,redirect,session,Flask,g
from sqlalchemy import Column, String
from app.DB import db
import cx_Oracle
import logging
import logging.config
import time,datetime
import sys
import os
import xlwt
from django.http import StreamingHttpResponse
from flask import jsonify,send_from_directory
import platform

"""
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication

"""


@app.route("/query" ,methods=['GET','POST'])
def query():
    return  render_template('/query.html')

@app.route("/download" ,methods=['GET','POST'])
def download():
    print("----",request.method)
    error = None
    if request.method == 'POST':

        jqbh = str( request.form['jqbhxz'].strip() )
        ksrq = str( request.form['ksrqxz'].strip() )
        jsrq = str( request.form['jsrqxz'].strip() )

        if jqbh =='' :
            error = '核心板编号不能为空！'
            return render_template('/query.html', error=error)
        if ksrq =='' :
            error = '开始时间不能为空！'
            return render_template('/query.html', error=error)
        if jsrq =='' :
            error = '结束时间不能为空！'
            return render_template('/query.html', error=error)

        a=ksrq
        b=jsrq
        a_ = datetime.datetime.strptime(a,'%Y-%m-%d')
        b_ = datetime.datetime.strptime(b,'%Y-%m-%d')
        c = b_ - a_
        if (c.days>3):
            error = '结束日期 - 开始日期不能大于3天，请重新输入！'
            return render_template('/query.html', error=error)

#        filename = "XZWJ"+jqbh+".txt"

        filename = "MX"+jqbh+".xls"
        os_platfrom=platform.platform()
        fname=''
        if os_platfrom.startswith('Window'):
            fname = dirpath + "\\" +filename
        else:
            fname = dirpath + "/" +filename


        scwj(ksrq,jsrq,jqbh ,fname)
#        sendmail(fname)

    return send_from_directory(dirpath,filename,as_attachment=True)


"""
后台查询数据库生成下载文件
"""
def scwj( ksrq,jsrq,jqbh ,filename):

        res = None
        conn = db.connect()

        columnName = ['发票代码', '发票号码', 'kprq', 'jqbh', 'ghdwmc', 'xhdwmc','zyspmc','zhsl','hjje','se','jshj','bz','发票状态']
        style1= xlwt.XFStyle()
        style1.num_format_str= 'yyyy-mm-dd'
        wb = xlwt.Workbook(encoding='utf-8')
        sheet =wb.add_sheet('电子发票明细', cell_overwrite_ok=True)

        for i in range(len(columnName)):    #插入第一行字段名称
            sheet.write(0, i, columnName[i])

        try:
            res = conn.execute(
                        "select fpdm, fphm , kprq ,dqsj,jqbh , ghdwmc , xhdwmc , zyspmc , zhsl , hjje , se  jshj, bz , fpzt  "
                        "from t_bdx_dzfpmx t  "
                        "where t.jqbh =:jqbh  "
                        "and to_char(t.dqsj,'YYYY-MM-DD')>=:ksrq  "
                        "and to_char(t.dqsj,'YYYY-MM-DD')<=:jsrq  " ,{'jqbh': jqbh,'ksrq': ksrq,'jsrq': jsrq} )

            rows = res.fetchall()
            for row in range(1,len(rows)+1):
                for col in range(0,len(columnName)):
                    sheet.write(row,col,rows[row-1][col])

            wb.save( filename )
        except Exception as e:
            conn.close()
            return "异常"
        finally:
            conn.close()  # 调用Connection.close()释放连接，将数据库连接放回连接池，而不是真的关闭连接



"""
def sendmail(file):
    i=0
    username = '*****@163.com'
    password = '******'
    sender = username
    receivers_list=["773120370@qq.com","yaqi2288@163.com","zhangyaqi@e-chinalife.com"]

    for i in range(len(receivers_list)):
        receivers = ','.join([receivers_list[i]])
        # 如名字所示： Multipart就是多个部分
        msg = MIMEMultipart()
        msg['Subject'] = '系统静态参数'
        msg['From'] = sender
        msg['To'] = receivers

        # 下面是文字部分，也就是纯文本
        puretext = MIMEText('附件是提取的系统静态参数，请查阅！')
        msg.attach(puretext)

        # 首先是xlsx类型的附件
        #jpgpart = MIMEApplication(open('/home/mypan/1949777163775279642.jpg', 'rb').read())
        xlsxpart = MIMEApplication(open(file, 'rb').read())
        xlsxpart.add_header('Content-Disposition', 'attachment', filename=file)
        msg.attach(xlsxpart)

        ##  下面开始真正的发送邮件了
        try:
            client = smtplib.SMTP()
            client.connect('smtp.163.com')
            client.login(username, password)
            client.sendmail(sender, receivers, msg.as_string())
            client.quit()
            print ('带有各种附件的邮件发送成功！',receivers_list[i]+ '  '+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
        except smtplib.SMTPRecipientsRefused:
            print ('Recipient refused')
        except smtplib.SMTPAuthenticationError:
            print ('Auth error')
        except smtplib.SMTPSenderRefused:
            print ('Sender refused')
        except smtplib.SMTPException as e:
            return "异常"
        time.sleep(3)
        i=i+1
"""