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)并邮件发送的实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。