python实现自动化统计数据表内容并发送邮件至指定联系人
一、目录结构和详解
1. Action
该包存放的是封装好的关键字信息。
l db_handler.py文件为连接数据库、获取SQL查询返回的结果对象列表信息、关闭数据库等操作信息；
l writetoexcel.py文件为将SQL查询到的对象录入Excel内的方法；
l set_font_stytl.py文件为设置Excel文本字体样式的方法。
2. Config
该包存放的是配置文件和工程变量以及log日志模块信息。
l db_config.ini为数据库的配置信息，如果数据库相关信息变更，只需要再此处修改即可；
l log模块以及log的配置信息Logger.conf，用来保存日志使用，主要是调试时使用；
l public_data.py存放全局变量信息，用于给不通的包或者py文件提供全局变量信息；
l standa.txt为给SQL提供的源数据信息，查询什么内容均从该文件获知，如果有变更，只需要修改该文件即可。
3. Report
该文件夹存放输出的文件信息。
l Count_result201808xx.xls文件信息为最终需要提供给邮件接收人的测试报告文件；
l Rrsult_count.log文件为log模块写入的日志信息内容；
4. Utils
工程中用到的工程模块功能py脚本文件。
l Config_handler.py文件为解析数据库的通用方法文件；
l FormatTime.py为根据时间封装的一些通用方法；
l SendMail.py为发送邮件的一个方法，主要是构造邮件相关信息以及添加附件。
5. Main_Program
Run_Task.py主程序为一个python定时器方法，定时发送邮件。
二、贴代码
1. Action包
1.1 db_handler.py
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.2 WriteToExcel.py
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.3 Set_font_style.py
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
2. Config包
2.1 db_config.ini
1 [mysqlconf] 2 host=192.168.0.5 3 port=6606 4 user=root 5 password=xxxxxx 6 db_name=xxxxxx
2.2 Log.py
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")
2.3 Logger.conf
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
2.4 public_data.py
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
2.5 standard.txt
1 微信ID,QQ,新浪微博,QQ音乐,腾讯视频,京东商城,淘宝,快手,美团,优酷,爱奇艺,酷狗音乐,QQ浏览器,腾讯新闻,QQ空间,58同城,暴风影音,腾讯微博,搜狐新闻,QQ邮箱,米聊,阿里旺旺,126邮箱,163邮箱,139邮箱,唯品会,天涯论坛,陌陌,大众点评,赶集网,51job 2 18060,1001,18079,7604,7633,21368,21400,3009,21416,7634,7631,7602,3000,23535,18088,21415,7523,18080,3003,20006,1080,1009,13101,20005,13100,21376,20051,1070,23510,21414,23561
3. Report文件夹
3.1 Count_result201808xx.xls
3.2 Result_count.log
4. Utils包
4.1 config_handler.py
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()
4.2 FormatTime.py
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()
4.3 SendMail.py
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()
5. Main_Program主函数
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)
