欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-29 14:38:18
逻辑比较简单 ,直接上代码  定时发送直接使用了win服务器的定时任务来定时执行脚本 #coding:utf-8 from __future__ i...

逻辑比较简单 ,直接上代码 

定时发送直接使用了win服务器的定时任务来定时执行脚本

#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)
 
 

以上这篇python生成每日报表数据(excel)并邮件发送的实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。