python 导出数据并发邮件_python实现查询sql后导出到excel并发送邮件

#coding=utf-8

import sys

import xlwt

import pymysql as MySQLdb #这里是python3 如果你是python2.x的话，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 = 'XXXXX'

user = 'xxxx'

pwd = 'xxxxx'

port = 3306

db = 'dbname'

sheet_name = 'report' + time.strftime("%Y-%m-%d")

filename = 'report_' + time.strftime("%Y-%m-%d") + '.xls'

out_path = '/home/report/report_'+ time.strftime("%Y-%m-%d") + '.xls'

print(out_path)

sql = '''select * from xxx'''

def export():

conn = MySQLdb.connect(host,user,pwd,db,charset='utf8')

cursor = conn.cursor()

count = cursor.execute(sql)

print("查询出" + str(count) + "条记录")

#来重置游标的位置

cursor.scroll(0,mode='absolute')

#搜取所有结果

results = cursor.fetchall()

# 获取MYSQL里面的数据字段名称

fields = cursor.description

workbook = xlwt.Workbook() # 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)

_user = "aa@qq.com"

_pwd = "******"

areceiver = "abc@qq.com,cde@qq.com"

acc = "fff@qq.com,eee@qq.com"

#如名字所示Multipart就是分多个部分

msg = MIMEMultipart()

msg["Subject"] =u'【数据统计_' + time.strftime("%Y-%m-%d") + u'】康复1.0系统_运营数据'

msg["From"] = _user

msg["To"] = areceiver

msg["Cc"] = acc

def send_email():

#---这是文字部分---

content = '''Deal all,

附件是康复1.0系统运营数据，请查收！'''

part = MIMEText(content,'plain','utf-8')

msg.attach(part)

#---这是附件部分---

#xls类型附件

file_name = '/home/report/' + 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("Eamil send successfully")

s.close()

#结果测试

if __name__=="__main__":

export()

send_email()