Python generates daily report data (Excel) and sends mail

Logic is simpler, directly on the code

Timed delivery uses the win server's timed task to execute the script regularly

#coding: Utf-8 __future__ Import Division import PYMSSQL,SYS,DATETIME,XLWT Import smtplib from Email.mime.text Impor T mimetext from Email.mime.multipart import mimemultipart from Email.header import header reload (SYS) sys.setdefaultencod
        ING ("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.__getconnec T () Cur.execute (SQL) Self.conn.commit () self.conn.close () def write_data_to_excel (Self,name,sql): # Pass the SQL as a parameter to call Get_data and assign the results to result, which (result is a nested tuple) outcome = self. ExecQuery (SQL) # instantiates a workbook () object (that is, an Excel file) wbk = XLWT. Workbook () # Create a new Excel sheet named Sheet1.
        The CELL_OVERWRITE_OK =true here is to be able to repeat operations on the same cell.
        Sheet = wbk.add_sheet (' Sheet1 ', cell_overwrite_ok=true) # Get the current date, get a DateTime object such as: (2016, 8, 9, 23, 12, 23, 424000) Today = Datetime.date.today () yesterday = Today-datetime.timedelta (Days=1) # takes the DateTime object fetched to date as
        : 2016-8-9 yesterdaytime = Yesterday.strftime ("%y-%m-%d") # traverses no elements in result.
                For i in xrange (len (Result)): #对result的每个子元素作遍历, for J in Xrange (Len (result[i))):
                #将每一行的每个元素按行号i, the column number J, is written to Excel.
        Sheet.write (I,j,result[i][j]) # saves as an Excel name by passing the name+ current date.
      filename = Name+str (yesterdaytime) + '. xls '  Wbk.save (filename) return filename ms = MSSQL (host= "122.229.*.*", user= "root", pwd= "root", db= "test") 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 = ' Sele CT count (1) from Tb_crmorders WHERE type =1 and result = ' true ' and Addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yester
Dayend + "'"; 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 '" + ye
Sterdaystart + "' 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)" Tb_crmorders WHERE type =2 and action =1 and result = ' true ' and Notifyresu
lt = 0 and Addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yesterdayend +" ";  Ordererrorcountsql = "SELECT count (1)" 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)" Tb_crmorders WHERE type =2 and action =2 and result = ' true ' and Noti
Fyresult = 0 and Addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yesterdayend +" "; Unsubscribeerrorcountsql = "SELECT count (1)" Tb_crmorders WHERE type =2 and action =2 and result = ' true ' and notify

The result is not NULL and Notifyresult!= 0 and Addtime BETWEEN ' + Yesterdaystart + ' ' and ' ' +yesterdayend + ' '; Orderkadansql = "SELECT count (1) 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)" Tb_crmorders WHERE type =2 and action =2 and result = ' true ' and Notifyresul
T is NULL and addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yesterdayend + '"; Precherkkeylist =[' CRM pre-validation Success List number: ', ' CRM pre-check number of successful accounts: ', ' CRM failed list number: ', ' CRM failed account number: ', ' Order number of orders successfully: ', ' Order number of orders failed: ', ' Order card singular: ', ' unsubscribe number of orders successfully: ', ' Unsubscribe number failed: ', ' unsubscribe card singular: '] precherkl = {' CRM pre-check Success List quantity: ':p recheckcountsuccessql, ' CRM pre-validation successful account number: ':p re Checkuseridsuccessql, ' CRM failed list number: ':p recheckcounterrorsql, ' CRM pre-validation failed account number: ':p recheckuseriderrorsql} precherkl[' Order number Successful: ' = Ordersucesscountsql precherkl[' Order number failed: ' = Ordererrorcountsql precherkl[' order card singular: ' = Orderkadansql PreC herkl[' Unsubscribe order number succeeded: ' = unsubscribesucesscountsql precherkl[' Unsubscribe number failed: ' = unsubscribeerrorcountsql precherkl[' unsubscribe card singular: ' = 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 ' A
nd notifyresult =0 and addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yesterdayend +" "; Crmunsubscribehandletimesql = "Select Addtime, notifytime from tb_crmorders WHERE type =2 and action =2 and result = ' TR

UE ' and Notifyresult =0 and Addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yesterdayend +" "; Crmorderhandle = Ms. ExecQuery (crmorderhandletimesql) OrderCount = Len (crmorderhandle) if ordercount!= 0:totletime = 0 for temp in CR Morderhandle:addtime = temp[0] Notifytime = temp[1] # adddate = Datetime.datetime.strpti
        Me (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 + ' order average processing length: ' + bytes (float (totletime)/orderc Ount) + ' min ' + ' \ n ' crmunsubscribehandle = Ms. ExecQuery (crmunsubscribehandletimesql) Subscribecount = Len (crmunsubscribehandle) if Subscribecount!= Letime = 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 (subscribetotl etime) + float (chazhi) Mailmessagetext = Mailmessagetext + ' Unsubscribe average length of processing: ' + bytes (float (subscribetotletime)/subscribecoun T) + ' min ' + ' \ n ' mailmessagetext = mailmessagetext + ' attachment is: Pre-check failed order, order/Unsubscribe failed order, card order ' + ' \ n ' Print Mailmessagetext #生成exce L File 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 (or Dererrorname, "Select Ordercode,userid,productid,action,sg,notifyresult,notifymsg from Tb_crmorders WHERE type =2 and R Esult = ' true ' and notifyresult are not NULL and Notifyresult!= 0 and Addtime BETWEEN ' "+ Yesterdaystart +" ' and ' "+yest Erdayend + "'") Kadanname = ' nosynchmsg ' kadanfile = Ms.write_data_to_excel (Kadanname, "select Ordercode,userid,producti D,sg,action from Tb_crmorders WHERE type =2 and result = ' true ' and Notifyresult is NULL and Addtime BETWEEN ' "+ yesterd   Aystart + "' and '" +yesterdayend + "") # third-party SMTP service mail_host= "###@163.com" #设置服务器 mail_user=## "#用户名 mail_pass=" # # #口令 sender = ' ###@163.com ' receivers = [' # # @qq. com '] # receive mail, can be set to your QQ mailbox or other mailbox #创建一个带附件的实例 message = Mimemultipart () message[' from '] = HeaDer ("Test", ' utf-8 ') message[' to '] = Header (",". Join (receivers), ' utf-8 ') Subject = ' CRM Order Date data ' + yesterday.strftime ('%Y -%m-%d ') message[' Subject '] = Header (Subject, ' utf-8 ') #邮件正文内容 Message.attach (mimetext, ' mailmessagetext ', ' Utf-8 ') #设置邮件名片 (HTML format) # HTML = file (' qianming.html '). Read (). Decode ("Utf-8") # Message.attach (HTML, ' HTML ', ' Utf-8 ') # constructs annex 1, which transmits precerroefile files in the current directory ATT1 = mimetext (open (Precerroefile, ' RB '). Read (), ' base64 ', ' Utf-8 ') att1["Co Ntent-type "] = ' application/octet-stream ' # here the filename can be arbitrarily written, what name is written, what name is displayed in the message att1[" content-disposition "] = ' attachment; Filename= ' + precerroefile Message.attach (att1) att2 = Mimetext (open (Ordererroefile, ' RB '). Read (), ' base64 ', ' Utf-8 ' tt2["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 is the SMTP port number Smtpobj.login (Mail_user,mail_pass) Smtpobj.sendmai L (sender, receivers, message.as_string ()) print "Mail sent successfully" except Smtplib.


 Smtpexception,e:print "Error: Unable to send mail" + repr (E)