python生成每日报表数据(Excel)并邮件发送的实例
逻辑比较简单,直接上代码
定时发送直接使用了win服务器的定时任务来定时执行脚本
#coding:utf-8 from__future__importdivision importpymssql,sys,datetime,xlwt importsmtplib fromemail.mime.textimportMIMEText fromemail.mime.multipartimportMIMEMultipart fromemail.headerimportHeader reload(sys) sys.setdefaultencoding("utf-8") classMSSQL: def__init__(self,host,user,pwd,db): self.host=host self.user=user self.pwd=pwd self.db=db def__GetConnect(self): ifnotself.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() ifnotcur: raise(NameError,"") else: returncur defExecQuery(self,sql): cur=self.__GetConnect() cur.execute(sql) resList=cur.fetchall() # self.conn.close() returnresList defExecNonQuery(self,sql): cur=self.__GetConnect() cur.execute(sql) self.conn.commit() self.conn.close() defwrite_data_to_excel(self,name,sql): #将sql作为参数传递调用get_data并将结果赋值给result,(result为一个嵌套元组) result=self.ExecQuery(sql) #实例化一个Workbook()对象(即excel文件) wbk=xlwt.Workbook() #新建一个名为Sheet1的excelsheet。此处的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中的没个元素。 foriinxrange(len(result)): #对result的每个子元素作遍历, forjinxrange(len(result[i])): #将每一行的每个元素按行号i,列号j,写入到excel中。 sheet.write(i,j,result[i][j]) #以传递的name+当前日期作为excel名称保存。 filename=name+str(yesterdaytime)+'.xls' wbk.save(filename) returnfilename 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' printyesterdayStart preCheckCountSuccesSql="selectcount(1)FROMtb_crmordersWHEREtype=1andresult='true'andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; preCheckUseridSuccesSql="selectcount(DISTINCTuserid)FROMtb_crmordersWHEREtype=1andresult='true'andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; preCheckCountErrorSql="selectcount(1)FROMtb_crmordersWHEREtype=1andresult='false'andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; preCheckUseridErrorSql="selectcount(DISTINCTuserid)FROMtb_crmordersWHEREtype=1andresult='false'andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; orderSucessCountSql="selectcount(1)FROMtb_crmordersWHEREtype=2andaction=1andresult='true'andnotifyresult=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; orderErrorCountSql="selectcount(1)FROMtb_crmordersWHEREtype=2andaction=1andresult='true'andnotifyresultISNOTNULLandnotifyresult!=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; unsubscribeSucessCountSql="selectcount(1)FROMtb_crmordersWHEREtype=2andaction=2andresult='true'andnotifyresult=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; unsubscribeErrorCountSql="selectcount(1)FROMtb_crmordersWHEREtype=2andaction=2andresult='true'andnotifyresultISNOTNULLandnotifyresult!=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; orderKadanSql="selectcount(1)FROMtb_crmordersWHEREtype=2andaction=1andresult='true'andnotifyresultISNULLandaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; unsubscribeKadanSql="selectcount(1)FROMtb_crmordersWHEREtype=2andaction=2andresult='true'andnotifyresultISNULLandaddtimeBETWEEN'"+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='' forkeyinpreCherkKeyList: reslist=ms.ExecQuery(preCherkL[key]) foriinreslist: fornini: mailMessageText=mailMessageText+key+bytes(n)+'\n' crmOrderHandleTimeSql="selectaddtime,notifytimeFROMtb_crmordersWHEREtype=2andaction=1andresult='true'andnotifyresult=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; crmunsubscribeHandleTimeSql="selectaddtime,notifytimeFROMtb_crmordersWHEREtype=2andaction=2andresult='true'andnotifyresult=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'"; crmOrderHandle=ms.ExecQuery(crmOrderHandleTimeSql) orderCount=len(crmOrderHandle) iforderCount!=0: totleTime=0 fortempincrmOrderHandle: 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) ifsubscribeCount!=0: subscribetotleTime=0 fortempincrmunsubscribeHandle: 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' printmailMessageText #生成excel文件 preCheckErrorname='preCheckError' preCerroeFile=ms.write_data_to_excel(preCheckErrorname,"selectordercode,userid,productid,action,msgFROMtb_crmordersWHEREtype=1andresult='false'andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'") orderErrorname='orderFalse' ordererroeFile=ms.write_data_to_excel(orderErrorname,"selectordercode,userid,productid,action,sg,notifyresult,notifymsgFROMtb_crmordersWHEREtype=2andresult='true'andnotifyresultISNOTNULLandnotifyresult!=0andaddtimeBETWEEN'"+yesterdayStart+"'and'"+yesterdayEnd+"'") kadanname='noSynchMsg' kadanFile=ms.write_data_to_excel(kadanname,"selectordercode,userid,productid,sg,actionFROMtb_crmordersWHEREtype=2andresult='true'andnotifyresultISNULLandaddtimeBETWEEN'"+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"邮件发送成功" exceptsmtplib.SMTPException,e: print"Error:无法发送邮件"+repr(e)
以上这篇python生成每日报表数据(Excel)并邮件发送的实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。