python產生每日報表資料（Excel）並郵件發送

邏輯比較簡單 ，直接上代碼 

定時發送直接使用了win伺服器的定時任務來定時執行指令碼

#coding:utf-8from __future__ import divisionimport pymssql,sys,datetime,xlwt  import smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.header import Headerreload(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 filenamems = 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 yesterdayStartpreCheckCountSuccesSql = "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['訂購的訂單數  成功:'] = orderSucessCountSqlpreCherkL['訂購的訂單數  失敗:'] = orderErrorCountSqlpreCherkL['訂購卡單數:'] = orderKadanSqlpreCherkL['退訂的訂單數  成功:'] = unsubscribeSucessCountSqlpreCherkL['退訂的訂單數  失敗:'] = unsubscribeErrorCountSqlpreCherkL['退訂卡單數:'] = unsubscribeKadanSqlmailMessageText =''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=' + preCerroeFilemessage.attach(att1) att2 = MIMEText(open(ordererroeFile, 'rb').read(), 'base64', 'utf-8')att2["Content-Type"] = 'application/octet-stream'att2["Content-Disposition"] = 'attachment; filename='+ordererroeFilemessage.attach(att2)att3 = MIMEText(open(kadanFile, 'rb').read(), 'base64', 'utf-8')att3["Content-Type"] = 'application/octet-stream'att3["Content-Disposition"] = 'attachment; filename='+kadanFilemessage.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)