[Python]查询mysql导出结果至Excel并发送邮件

环境:Linux +python2.7+mysql5.6

1、提前安装xlwt(excel写入操作模块),MySQLdb(mysql操作模块)

2、脚本如下:

#!/usr/bin/python
#coding: utf-8
import sys
import xlwt
import MySQLdb
import datetime
import time
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
import os.path
 
host = 'localhost'
user = 'user'
pwd = 'Passwd'
port = 3306
db = 'database'
sheet_name = 'report' + time.strftime("%Y-%m-%d")
filename = 'report_' + time.strftime("%Y-%m-%d") + '.xls'
out_path = '/data/monitor/temp/report_'+ time.strftime("%Y-%m-%d") + '.xls'
print(out_path)
sql = 'Select * from sys_user;'
def export():
    conn = MySQLdb.connect(host,user,pwd,db,charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute(sql)
    print("查询出" + str(count) + "条记录")
    if count>0:
        #来重置游标的位置
        cursor.scroll(0,mode='absolute')
        #搜取所有结果
        results = cursor.fetchall()
        # 获取MYSQL里面的数据字段名称
        fields = cursor.description
        workbook = xlwt.Workbook(encoding = 'utf-8') # workbook是sheet赖以生存的载体。
        sheet = workbook.add_sheet(sheet_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(results)+1):
            for col in range(0,len(fields)):
                sheet.write(row,col,u'%s'%results[row-1][col])
        workbook.save(out_path)
    else:
        print("无数据")
 
_user = "XXXX@qq.com"
_pwd = "passwd"
areceiver = "xxxxx@139.com"
acc = "xxxxx@139.com"
 
#如名字所示Multipart就是分多个部分
msg = MIMEMultipart()
msg["Subject"] =u'【数据统计_' + time.strftime("%Y-%m-%d") + u'】'
msg["From"] = _user
msg["To"] = areceiver
msg["Cc"] = acc
 
def send_email():
    conn = MySQLdb.connect(host,user,pwd,db,charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute(sql)
    #---这是文字部分---
    content = '''Deal all,\n附件是系统每日统计情况,请查收!
    总计结果数为:'''+str(count)
 
    part = MIMEText(content,'plain','utf-8')
    msg.attach(part)
    if count>0:
        #---这是附件部分---
        #xls类型附件
        file_name = '/data/monitor/temp/' + filename
        part = MIMEText(open(file_name,'rb').read(), 'base64', 'gb2312')
        part["Content-Type"] = 'application/octet-stream'
        basename = os.path.basename(file_name)
        part["Content-Disposition"] = 'attachment; filename=%s' % basename.encode('gb2312')
        msg.attach(part)
        s = smtplib.SMTP("smtp.qq.com", timeout=30)#连接smtp邮件服务器,端口默认是25
        s.login(_user, _pwd)#登陆服务器
        s.sendmail(_user, areceiver.split(',') + acc.split(','), msg.as_string())#发送邮件
        print("Email send successfully")
        s.close()
    else:
        print("nothing to send!")
#调用函数
if __name__=="__main__":
    export()
    send_email()




原文链接:https://www.2cto.com/database/201708/663940.html


  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值