python生成每日报表数据(Excel)并邮件发送的实例

#coding:utf-8
from __future__ import division
import pymssql,sys,datetime,xlwt 
import smtplib
from email.mime.text import mimetext
from email.mime.multipart import mimemultipart
from email.header import header
 
reload(sys)
sys.setdefaultencoding("utf-8")
 
 
class mssql:
  def __init__(self,host,user,pwd,db):
    self.host = host
    self.user = user
    self.pwd = pwd
    self.db = db
 
  def __getconnect(self):
    if not self.db:
      raise(nameerror,"")
    self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
    cur = self.conn.cursor()
    if not cur:
      raise(nameerror,"")
    else:
      return cur
 
  def execquery(self,sql):
    cur = self.__getconnect()
    cur.execute(sql)
    reslist = cur.fetchall()
 
    #
    self.conn.close()
    return reslist
 
  def execnonquery(self,sql):
    cur = self.__getconnect()
    cur.execute(sql)
    self.conn.commit()
    self.conn.close()
    
  
  def write_data_to_excel(self,name,sql):
 
    # 将sql作为参数传递调用get_data并将结果赋值给result,(result为一个嵌套元组)
    result = self.execquery(sql)
    # 实例化一个workbook()对象(即excel文件)
    wbk = xlwt.workbook()
    # 新建一个名为sheet1的excel sheet。此处的cell_overwrite_ok =true是为了能对同一个单元格重复操作。
    sheet = wbk.add_sheet('sheet1',cell_overwrite_ok=true)
    # 获取当前日期，得到一个datetime对象如：(2016, 8, 9, 23, 12, 23, 424000)
    today = datetime.date.today()
    yesterday = today - datetime.timedelta(days=1)
    # 将获取到的datetime对象仅取日期如：2016-8-9
    yesterdaytime = yesterday.strftime("%y-%m-%d")
    # 遍历result中的没个元素。
    for i in xrange(len(result)):
      #对result的每个子元素作遍历，
      for j in xrange(len(result[i])):
        #将每一行的每个元素按行号i,列号j,写入到excel中。
        sheet.write(i,j,result[i][j])
    # 以传递的name+当前日期作为excel名称保存。
    filename = name+str(yesterdaytime)+'.xls'
    wbk.save(filename) 
    return filename
 
 
 
ms = mssql(host="122.229.*.*",user="root",pwd="root",db="test")
 
today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
yesterdaystart = yesterday.strftime("%y-%m-%d") + ' 00:00:00'
yesterdayend = yesterday.strftime("%y-%m-%d") + ' 23:59:59'
print yesterdaystart
precheckcountsuccessql = "select count(1) from tb_crmorders where type =1 and result = 'true' and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
precheckuseridsuccessql = "select count(distinct userid) from tb_crmorders where type =1 and result = 'true' and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
precheckcounterrorsql = "select count(1) from tb_crmorders where type =1 and result = 'false' and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
precheckuseriderrorsql = "select count(distinct userid) from tb_crmorders where type =1 and result = 'false' and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
 
ordersucesscountsql = "select count(1) from tb_crmorders where type =2 and action =1 and result = 'true' and notifyresult = 0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
ordererrorcountsql = "select count(1) from tb_crmorders where type =2 and action =1 and result = 'true' and notifyresult is not null and notifyresult != 0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
unsubscribesucesscountsql = "select count(1) from tb_crmorders where type =2 and action =2 and result = 'true' and notifyresult = 0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
unsubscribeerrorcountsql = "select count(1) from tb_crmorders where type =2 and action =2 and result = 'true' and notifyresult is not null and notifyresult != 0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
 
orderkadansql = "select count(1) from tb_crmorders where type =2 and action =1 and result = 'true' and notifyresult is null and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
unsubscribekadansql = "select count(1) from tb_crmorders where type =2 and action =2 and result = 'true' and notifyresult is null and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
precherkkeylist =['crm预校验成功单子数量：','crm预校验成功账号数量：','crm预校验失败单子数量：','crm预校验失败账号数量：','订购的订单数 成功:','订购的订单数 失败:','订购卡单数:','退订的订单数 成功:','退订的订单数 失败:','退订卡单数:']
precherkl = {'crm预校验成功单子数量：' :precheckcountsuccessql ,'crm预校验成功账号数量：' :precheckuseridsuccessql ,'crm预校验失败单子数量：' :precheckcounterrorsql ,'crm预校验失败账号数量：' :precheckuseriderrorsql}
precherkl['订购的订单数 成功:'] = ordersucesscountsql
precherkl['订购的订单数 失败:'] = ordererrorcountsql
precherkl['订购卡单数:'] = orderkadansql
precherkl['退订的订单数 成功:'] = unsubscribesucesscountsql
precherkl['退订的订单数 失败:'] = unsubscribeerrorcountsql
precherkl['退订卡单数:'] = unsubscribekadansql
 
mailmessagetext =''
 
for key in precherkkeylist:
  reslist = ms.execquery(precherkl[key])
  for i in reslist:
    for n in i:
      mailmessagetext = mailmessagetext + key + bytes(n) + '\n' 
 
 
crmorderhandletimesql = "select addtime , notifytime from tb_crmorders where type =2 and action =1 and result = 'true' and notifyresult =0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
crmunsubscribehandletimesql = "select addtime , notifytime from tb_crmorders where type =2 and action =2 and result = 'true' and notifyresult =0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'";
 
crmorderhandle = ms.execquery(crmorderhandletimesql)
ordercount = len(crmorderhandle)
if ordercount != 0:
  totletime = 0
  for temp in crmorderhandle:
    addtime = temp[0]
    notifytime = temp[1]
    
#     adddate = datetime.datetime.strptime(addtime,"%y-%m-%d %h:%m:%s")
#     notifydate =datetime.datetime.strptime(notifytime, "%y-%m-%d %h:%m:%s")
    chazhi = (notifytime - addtime).seconds / 60
    totletime = float(totletime) + float(chazhi)
  mailmessagetext = mailmessagetext + '订购平均处理时长:' + bytes(float(totletime)/ordercount) + '分' + '\n' 
 
crmunsubscribehandle = ms.execquery(crmunsubscribehandletimesql)
subscribecount = len(crmunsubscribehandle)
if subscribecount != 0:
  subscribetotletime = 0
  for temp in crmunsubscribehandle:
    addtime = temp[0]
    notifytime = temp[1]
#     adddate = datetime.datetime.strptime(addtime, "%y-%m-%d %h:%m:%s")
#     notifydate = datetime.datetime.strptime(notifytime, "%y-%m-%d %h:%m:%s")
    chazhi = (notifytime - addtime).seconds / 60
    subscribetotletime = float(subscribetotletime) + float(chazhi)
  mailmessagetext = mailmessagetext + '退订平均处理时长:' + bytes(float(subscribetotletime)/subscribecount) + '分' + '\n' 
mailmessagetext = mailmessagetext + '附件为 ：预校验失败订单，订购/退订失败订单，卡单订单' + '\n' 
 
print mailmessagetext
 
#生成excel文件
 
precheckerrorname = 'precheckerror'
precerroefile = ms.write_data_to_excel(precheckerrorname, "select ordercode,userid,productid,action,msg from tb_crmorders where type =1 and result = 'false' and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'")
 
ordererrorname = 'orderfalse'
ordererroefile = ms.write_data_to_excel(ordererrorname, "select ordercode,userid,productid,action,sg,notifyresult,notifymsg from tb_crmorders where type =2  and result = 'true' and notifyresult is not null and notifyresult != 0 and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'")
 
kadanname = 'nosynchmsg'
kadanfile = ms.write_data_to_excel(kadanname, "select ordercode,userid,productid,sg,action from tb_crmorders where type =2 and result = 'true' and notifyresult is null and addtime between '" + yesterdaystart +"' and '"+yesterdayend + "'")
# 第三方 smtp 服务
mail_host="###@163.com" #设置服务器
mail_user=##"  #用户名
mail_pass="##"  #口令 
 
 
sender = '###@163.com'
receivers = ['##@qq.com'] # 接收邮件，可设置为你的qq邮箱或者其他邮箱
 
#创建一个带附件的实例
message = mimemultipart()
 
message['from'] = header("测试", 'utf-8')
message['to'] = header(" , ".join(receivers), 'utf-8')
 
subject = 'crm订单日数据' + yesterday.strftime('%y-%m-%d')
message['subject'] = header(subject, 'utf-8')
 
#邮件正文内容
message.attach(mimetext(mailmessagetext, 'plain', 'utf-8'))
#设置邮件名片（html格式）
# html = file('qianming.html').read().decode("utf-8")
# message.attach(mimetext(html, 'html', 'utf-8'))
 
# 构造附件1，传送当前目录下的precerroefile 文件
att1 = mimetext(open(precerroefile, 'rb').read(), 'base64', 'utf-8')
att1["content-type"] = 'application/octet-stream'
# 这里的filename可以任意写，写什么名字，邮件中显示什么名字
att1["content-disposition"] = 'attachment; filename=' + precerroefile
message.attach(att1)
 
 
att2 = mimetext(open(ordererroefile, 'rb').read(), 'base64', 'utf-8')
att2["content-type"] = 'application/octet-stream'
att2["content-disposition"] = 'attachment; filename='+ordererroefile
message.attach(att2)
 
 
att3 = mimetext(open(kadanfile, 'rb').read(), 'base64', 'utf-8')
att3["content-type"] = 'application/octet-stream'
att3["content-disposition"] = 'attachment; filename='+kadanfile
message.attach(att3)
 
try:
  smtpobj = smtplib.smtp() 
  smtpobj.connect(mail_host, 25)  # 25 为 smtp 端口号
  smtpobj.login(mail_user,mail_pass) 
  smtpobj.sendmail(sender, receivers, message.as_string())
  print "邮件发送成功"
except smtplib.smtpexception,e:
  print "error: 无法发送邮件" + repr(e)