从数据库中读取数据，然后插入到excel，发送email

# -*- coding:UTF-8 -*- 
import smtplib
import time
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import MySQLdb
import xlwt
wbk=xlwt.Workbook()
sheet=wbk.add_sheet('sheet1')
row1=1
#设置表头
styleBoldGreen=xlwt.easyxf('font: color-index red,bold on')
styleBoldGreen   = xlwt.easyxf('font: color-index green, bold on')
sheet.write(0,0,u'学号',styleBoldGreen)
sheet.write(0,1,u'姓名',styleBoldGreen)
sheet.write(0,2,u'年龄',styleBoldGreen)
sheet.write(0,3,u'入学时间',styleBoldGreen)


date1=time.strftime('%Y-%m-%d',time.localtime(time.time()))
#连接数据库
conn=MySQLdb.connect(host='*',user='*',passwd='*',db='*',charset='utf8')
cursor1=conn.cursor()
sql = u'select * from order'
cursor1.execute(sql);


#读取数据库中数据
for a,b,c,d in cursor1.fetchall():
    sheet.write(row1,0,a)
    sheet.write(row1,1,b)
    sheet.write(row1,2,c)
    sheet.write(row1,3,d)
    row1=row1+1
wbk.save('E:\\record.xls')
cursor1.close()
conn.commit()
conn.close()


toadd=['*@163.com']
fromadd='*@163.com'
#定义MIMEMultipart对象
msg=MIMEMultipart('alternative')
msg['subject']=date1+'出票记录'
#正文
msg1 = MIMEText(date1 +u'出票记录','plain','utf-8')
#构造附件
att = MIMEText(open('E:\\record.xls', 'rb').read(), 'base64', 'gb2312')
att["Content-Type"] = 'application/octet-stream'
att["Content-Disposition"] = 'attachment; filename="record.xls"'
#添加附件
msg.attach(att)
msg.attach(msg1)


#发送邮件
sever=smtplib.SMTP()
sever.connect('*','25')
sever.starttls()
sever.login('username', 'password')
sever.sendmail(fromadd, toadd, msg.as_string())

sever.quit()

注：python2.7上运行通过

需要安装xlwt,smtplib,MySQLdb模块

若sql查询里有date字段，需要加上如 Date_format(o.create_time,'%Y-%m-%d %H:%i:%s')，否则输出到excel上不是日期格式

连接数据库时conn=MySQLdb.connect(host='*',user='*',passwd='*',db='*',charset='utf8'),一定要加上charset='utf8'，否则带中文的情况下无法运行