环境: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()