• [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,
    附件是系统每日统计情况,请查收!
        总计结果数为:'''+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()
    

      

  • 相关阅读:
    边推改革边上“保险” 央行“双降”两大亮点带来哪些变化
    今天走势将冲高回落后重新回归下跌周期
    小心!资本正在流出中国:国际收支表里被遗漏的-2547亿美元
    价格改革确立时间表和路线图 六大重点领域破题
    避免在办公室体重上升的三大良策
    别再说自己有多忙
    沪指可能展开一波3个交易日的调整
    专车新政博弈 垄断行业改革样本
    JS和CS互访【后台前台代码调用JavaScript变量以及JavaScript调用代码变量】
    net9:图片文件转换成二进制流存入SQL数据库,以及从数据库中读取二进制流输出文件
  • 原文地址:https://www.cnblogs.com/jzhg/p/8404678.html
Copyright © 2020-2023  润新知