使用python实现数据库数据导出写入excel并邮件发送

使用python实现数据库数据导出写入excel并邮件发送,代码如下:

#!/usr/bin/python3
#coding:utf-8

import pymysql
import NumberConRateSql
import xlwt
import datetime
import sys
import time
import os
import os.path
from email import encoders
from email.header import Header
from email.mime.text import MIMEText
from email.utils import parseaddr, formataddr
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
import smtplib
import Config

#获取当前时间
now_time = datetime.datetime.now()
#获取昨天时间
yes_time = now_time + datetime.timedelta(days=-1)
#格式输出
yes_time_nyr = yes_time.strftime('%Y-%m-%d')
#curtime = time.strftime("%Y-%m-%d", time.localtime())
outpath = Config.OUTPATH
filename = yes_time_nyr + '.xls'

#定义导出excel函数
def export(table_name,outfile):
    conndb = pymysql.connect(
        host = Config.HOST,
        port = Config.PORT,
        user = Config.USER,
        password = Config.PASS,
        db = Config.DATABASE,
        charset='utf8')
    cursor = conndb.cursor()
    #count = cursor.execute(NumberConRateSql.TEST)
    count = cursor.execute(NumberConRateSql.GET_NUMBER_RATE_SQL)
    #print("查询出" + str(count) + "条记录")
    if count > 0:
        # 重置游标的位置
        cursor.scroll(0,mode='absolute')
        result = cursor.fetchall()
        fields = cursor.description
        #创建文件对象
        workbook = xlwt.Workbook(encoding='utf-8')
        sheet = workbook.add_sheet(table_name,cell_overwrite_ok=True)
        for field in range(0,len(fields)):
            sheet.write(0,field,fields[field][0])
        # 获取并写入数据段信息
        row=1
        col=0
        for row in range(1,len(result)+1):
            for col in range(0,len(fields)):
             sheet.write(row,col,u'%s'%result[row-1][col])
        workbook.save(outpath + outfile)
    else:
        pass
    cursor.close()
    conndb.close()

msg = MIMEMultipart()
msg["Subject"] = Config.TITLE
msg["From"] = Config.SENDER
msg["To"] = Config.RECEIVER
#msg["Cc"] = Config.CC

#定义发送邮件函数
def send_email():
    content = '''您好,附件是测试邮件,请查收。'''
    part = MIMEText(content,'plain','utf-8')
    msg.attach(part)
    if filename:
        file_name = outpath + filename
        part = MIMEText(open(file_name,'rb').read(),'base64','utf-8')
        part["Content-Type"] = 'application/octet-stream'
        #basename = os.path.basename(file_name)
        part["Content-Disposition"] = 'attachment; filename=' + filename
        msg.attach(part)
        s = smtplib.SMTP_SSL(Config.SMTP_SERVER,Config.SMTP_PORT)
        s.login(Config.SENDER, Config.SENDER_PASS)
        s.sendmail(Config.SENDER, Config.RECEIVER, msg.as_string())
        s.quit()
        os.remove(file_name)
    else:
        pass

if __name__=='__main__':
  export('xxxxx',filename)
  send_email()
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页