python实现自动化统计数据表内容并发送邮件至指定联系人

 1 #encoding=utf-8
 2 import pymysql
 3 from config.Log import *
 4 from config.public_data import *
 5 from utils.config_handler import ConfigParse
 6 
 7 class DB(object):
 8     def __init__(self):
 9         self.db_conf = ConfigParse().get_db_conf()
10         self.conn = pymysql.connect(
11             host = self.db_conf["host"],
12             port = self.db_conf["port"],
13             user = self.db_conf["user"],
14             passwd = self.db_conf["password"],
15             db = self.db_conf["db"],
16             charset = "utf8"
17         )
18         self.cur = self.conn.cursor()
19 
20     def get_api_list(self):
21         sqlStr = get_api_list
22         self.cur.execute(sqlStr)
23         # 返回tuple对象
24         row_3 = self.cur.fetchall()
25         return row_3
26     # #获取上述SQL语句中的检索条件名称（将要成为Excel第一行的表头）。
27     # fields = cursor.description
28     def get_description(self):
29         fields = self.cur.description
30         return fields
31 
32     def get_api_list2(self):
33         sqlStr = get_api_list2
34         self.cur.execute(sqlStr)
35         # 返回tuple对象
36         row_4 = self.cur.fetchall()
37         return row_4
38 
39     def close_connect(self):
40         # 关闭数据连接
41         self.conn.commit()
42         self.cur.close()
43         self.conn.close()
44 
45 if __name__ == '__main__':
46     db = DB()
47     print db.get_api_list()   #row_3
48     print db.get_description()  #fields
49     print db.get_api_list2()   #row_4
50     db.close_connect()

 1 #!/usr/bin/python
 2 # -*- coding: utf-8 -*-
 3 from Action.Set_font_style import *
 4 from Action.db_handler import *
 5 from config.Log import *
 6 from config.public_data import *
 7 import xlwt
 8 import openpyxl
 9 
10 def WriteToExcel():
11     db = DB()
12     # Data = Select_data()
13     Data0 = db.get_api_list()
14     Data2 = db.get_description()
15     Data3 = db.get_api_list2()
16     # Data1 = db.close_connect()
17     logging.info(u"#将字段名写入到EXCEL表头；")
18     workbook = xlwt.Workbook(encoding='utf-8')
19     #创建Excel中的一个sheet，并命名且为可重写状态。
20     sheet = workbook.add_sheet('result_count',cell_overwrite_ok=True)
21     #写第一行和第二行的第一个和第二个单元格
22 
23     tittle = ["用户名","MAC"]
24     for i in range(2):
25         sheet.write(0,i,tittle[i])
26 
27     logging.info(u"#将VnameList中的虚拟身份依次填入Excel中；")
28     for field in range(0,len(VnameList)):
29         # sheet.write(0,field,VnameList[field].encode("utf-8"))
30         sheet.write(0, field+2, VnameList[field])
31     #根据横纵坐标依次录入查询到的信息值。
32     for row in range(1,len(Data0)+1):
33         # for col in range(0,len(Data2)):
34         for col in range(0, len(Data2)):
35             sheet.write(row,col,u'%s'%Data0[row-1][col])
36 
37     #计算合计值
38     for field in range(0,len(Data3[0])):
39         sheet.write(len(Data0)+1,field+2,Data3[0][field])
40     sheet.write_merge(len(Data0)+1,len(Data0)+1,0,1,u'合计',set_style('Times New Roman',220,True))
41 
42     logging.info(u"#将Excel文件保存下来；")
43     logging.info(u"#保存成功！！！\n")
44     workbook.save('../report/Count_result%s.xls'%book_mark.encode("utf-8"))
45     # workbook.save(Excel_result_path + .xls)
46 
47 if __name__ =="__main__":
48     WriteToExcel()

 1 #!/usr/bin/python
 2 # -*- coding: utf-8 -*-
 3 import xlwt
 4 
 5 def set_style(name,height,bold=False):
 6     style = xlwt.XFStyle()  # 初始化样式
 7     font = xlwt.Font()  # 为样式创建字体
 8     font.name = name # 'Times New Roman'
 9     font.bold = bold
10     font.color_index = 4
11     font.height = height
12 
13     style.font = font
14     return style

1 [mysqlconf]
2 host=192.168.0.5
3 port=6606
4 user=root
5 password=xxxxxx
6 db_name=xxxxxx

 1 #!/usr/bin/python
 2 #encoding=utf-8 
 3 import logging.config
 4 from config.public_data import *
 5 
 6 logging.config.fileConfig(Logger_path)
 7 
 8 def debug(message):
 9     logging.debug(message)
10 
11 def warning(message):
12     logging.warning(message)
13 
14 def info(message):
15     logging.info(message)
16 
17 if __name__ == "__main__":
18     logging.info("lalal")
19     logging.warning("ok")
20     logging.debug("fail")

 1 ############################################
 2 [loggers]
 3 keys = root,example01,example02
 4 
 5 [logger_root]
 6 level = DEBUG
 7 handlers = hand01,hand02
 8 
 9 [logger_example01]
10 handlers = hand01,hand02
11 qualname = example01
12 propagate = 0
13 
14 [logger_example02]
15 handlers = hand01,hand03
16 qualname = example02
17 propagate = 0
18 ############################################
19 [handlers]
20 keys = hand01,hand02,hand03
21 
22 [handler_hand01]
23 class = StreamHandler
24 level = DEBUG
25 formatter = form01
26 args = (sys.stderr,)
27 
28 [handler_hand02]
29 class = FileHandler
30 lever = DEBUG
31 formatter = form01
32 args = ('../report/Result_count.log','a')
33 
34 [handler_hand03]
35 class = handlers.RotatingFileHandler
36 lever = INFO
37 formatter = form01
38 args = ('../report/Result_count.log','a',10*1024*1024,5)
39 ############################################
40 [formatters]
41 keys = form01,form02
42 [formatter_form01]
43 format = %(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s
44 datefmt = %Y-%m-%d %H:%M:%S
45 [formatter_form02]
46 format = %(name) -12s: %(levelname) -8s %(message)s
47 datefmt = %Y-$m-%d %H:%M:%S

 1 #!/usr/bin/python
 2 # -*- coding: utf-8 -*-
 3 import os
 4 import datetime
 5 from utils.FormatTime import *
 6 basedir = os.path.dirname(os.path.dirname(__file__))  #项目的根路径
 7 Logger_path = basedir + "\\config\\Logger.conf"  #Logger配置文件的路径
 8 config_path = basedir + "\\config\\db_config.ini"  #数据库配置文件的路径
 9 Excel_result_path = basedir + "\\report\\Count_Result%s"%(date_time())    #生成excel文件的路径
10 excel_path = basedir + "/report/Count_result{datet}".format(datet=date_time())
11 Standard_excel = basedir +  "\\config\\standard.xls"  #标准excel文件的路径
12 path_txt = basedir + "\\config\\standard.txt"   #标准TXT文件的路径
13 
14 today = datetime.datetime.today()  # 获取今天的日期.
15 yesterday = today - datetime.timedelta(days=1)  # 获取昨天的日期.
16 tomorrow = today + datetime.timedelta(days=1)  # 获取明天的日期.
17 today_a = datetime.datetime(today.year, today.month, today.day, 0, 0, 0)  # 获取今天凌晨的时间.
18 yesterday_b = datetime.datetime(yesterday.year, yesterday.month, yesterday.day, 0, 0, 0)  # 获取昨天凌晨的时间.
19 tomorrow_c = datetime.datetime(tomorrow.year, tomorrow.month, tomorrow.day, 0, 0, 0)  # 获取明天凌晨的时间.
20 
21 #格式化时间输出，用于给Excel起名时使用。
22 sheet_time = datetime.datetime.now()
23 book_mark = sheet_time.strftime('%Y%m%d')
24 
25 with open(path_txt,"r") as fp:
26     VnameList = fp.readline().split(",")
27     Vname_Id = fp.readline().split(",")
28 
29 sql_body = ""
30 for id in Vname_Id:
31     sql_body += "count(if(b.ntype='%s',true,null)),"%id
32 
33 get_api_list = "select a.username,a.mac,%sfrom nctermnetlog_if_%s a,ncsnettype b where  a.nettype=b.ntype and stime>unix_timestamp('%s') \
34 and stime<unix_timestamp('%s') group by a.username"%(sql_body[:-1],book_mark[0:6],str(yesterday_b),str(today_a))
35 
36 get_api_list2 = "select %sfrom nctermnetlog_if_%s a,ncsnettype b where  a.nettype=b.ntype and stime>unix_timestamp('%s') \
37 and stime<unix_timestamp('%s')"%(sql_body[:-1],book_mark[0:6],str(yesterday_b),str(today_a))
38 
39 if __name__ == "__main__":
40     print basedir
41     print Logger_path
42     print Excel_result_path
43     print excel_path
44     print config_path
45     print book_mark
46     # print VnameList
47     # print get_api_list
48     print get_api_list2
49     # print sql_body
50     print today

 1 #encoding=utf-8
 2 import ConfigParser
 3 from config.public_data import config_path
 4 
 5 class ConfigParse(object):
 6     def __init__(self):
 7         self.cf = ConfigParser.ConfigParser()
 8 
 9     def get_db_conf(self):
10         self.cf.read(config_path)
11         host = self.cf.get("mysqlconf", "host")
12         port = self.cf.get("mysqlconf", "port")
13         db = self.cf.get("mysqlconf", "db_name")
14         user = self.cf.get("mysqlconf", "user")
15         password = self.cf.get("mysqlconf", "password")
16         return {"host":host,"port": int(port),"db":db,"user":user,"password":password}
17 
18 if __name__ == "__main__":
19     cp = ConfigParse()
20     print cp.get_db_conf()

 1 #!/usr/bin/python
 2 #encoding=utf-8
 3 import time
 4 from datetime import timedelta,date
 5 
 6 
 7 def date_time():
 8     "returns the current time string,format for YYYY-mm-dd HH:MM:SS"
 9     return time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
10 
11 def date_time_slash():
12     "returns the current time string,format for YYYY/mm/dd HH:MM:SS"
13     return time.strftime("%Y/%m/%d %H:%M:%S",time.localtime())
14 
15 def dates():
16     "returns the current time string,format for YYYY-mm-dd"
17     return time.strftime("%Y-%m-%d",time.localtime())
18 
19 def date_slash():
20     "returns the current time string,format for YYYY/mm/dd"
21     return time.strftime("%Y/%m/%d",time.localtime())
22 
23 def date_time():
24     "returns the current time string,format for HH:MM:SS"
25     return time.strftime("%Y%m%d",time.localtime())
26 
27 
28 if __name__=="__main__":
29   
30     print date_time()

 1 #!/usr/bin/python
 2 # -*- coding:utf-8 -*-    
 3 
 4 import smtplib    
 5 from email.mime.multipart import MIMEMultipart    
 6 from email.mime.text import MIMEText
 7 from config.Log import *
 8 from Action.WriteToExcel import *
 9 
10 
11 def SendMail():
12     WriteToExcel()
13     time.sleep(5)
14     smtpserver = 'mail.pronetway.com'
15     username = 'xxx@xxx.com'
16     password='xxx'
17     sender='xxx@xxx.com'
18     logging.info(u"# 登录邮箱服务器成功；")
19 
20     receiver=['xxx@xx.com','xxx@xxx.com','xxx@163.com']
21    
22     #生成今天的日期，格式化输出为年月日；生成昨天的日期，格式化输出为年月日
23     sheet_time = datetime.datetime.now()
24     book_mark = sheet_time.strftime('%Y%m%d')
25     today = datetime.datetime.today()  
26     yesterday = today - datetime.timedelta(days=1)
27     yesterday_b = datetime.datetime(yesterday.year, yesterday.month, yesterday.day, 0, 0, 0)
28     book_mark1 = yesterday_b.strftime('%Y%m%d')
29 
30     logging.info(u"#构造邮件的主题、发件人和收件人信息；")
31     subject ="%s虚拟身份统计结果，请注意查收！"%book_mark1
32     msg = MIMEMultipart('mixed')
33     msg['Subject'] = subject
34     #msg['From'] ="<dpeng_fan@163.com>"
35     msg['From'] ="<fandapeng@pronetway.com>"
36     msg['To'] = ";".join(receiver)
37 
38     logging.info(u"#构造附件；")
39     sendfile=open('../report/Count_result%s.xls'%book_mark,'rb').read()
40     text_att = MIMEText(sendfile, 'base64', 'utf-8')
41     text_att["Content-Type"] = 'application/octet-stream'
42     text_att.add_header('Content-Disposition', 'attachment', filename='Count_result%s.xls'%book_mark1)
43     msg.attach(text_att)
44     logging.info(u"#构造成功，准备发送邮件！")
45 #===============================================================================================================
46 # 发送邮件；考虑到服务器会将邮件作为垃圾邮件处理，导致邮件发送失败，返回554，于是做了死循环，直到发送成功。
47 #===============================================================================================================
48     try:
49         Failure_count =0
50         while True:
51             smtp = smtplib.SMTP()
52             smtp.connect('mail.pronetway.com')
53             smtp.login(username, password)
54             smtp.sendmail(sender, receiver, msg.as_string())
55             #print "Send Success!!!"
56             logging.warning(u"#邮件发送成功!!!")
57             break
58     except Exception as err:
59         print 'Sending Mail Failed:{0}'.format(err)
60         logging.warning('Sending Mail Failed:{0}'.format(err))
61         Failure_count+=1
62         info('Send Failure counts are %s'%Failure_count)
63         # continue
64         #http://help.163.com/09/1224/17/5RAJ4LMH00753VB8.html
65     finally:
66         smtp.quit()
67 if __name__ == "__main__":
68     SendMail()

 1 #!/usr/bin/python
 2 # -*- coding:utf-8 -*-
 3 
 4 from utils.SendMail import *
 5 import datetime
 6 import time
 7 
 8 # def run_Task():
 9 #     SendMail()
10 
11 def timerFun(sched_Timer):
12     flag = 0
13     while True:
14         now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
15         #print now,sched_Timer
16         if now == str(sched_Timer):
17             print "*"*30
18             # run_Task()
19             SendMail()
20             flag = 1
21             time.sleep(60)
22             #break
23         else:
24             if flag==1:
25                 sched_Timer = sched_Timer+datetime.timedelta(days=1)
26                 flag=0
27 
28 if __name__ == "__main__":
29     sched_Timer = datetime.datetime(2018,8,20,10,07,00)
30     print "run the timer task at{0}".format(sched_Timer)
31     timerFun(sched_Timer)