python3根据Elasticsearch（es）每天线上更新的日志，做成excel统计表并定时邮箱发送附件
程序是来实现 es中上线更新量的统计
代码实现需要个shell脚本，需要的同学可以问我要
用到几个包介绍一下
xlwt和xlrd，都需要下载，在命令行下(win,linux,mac都可以) pip3 install xxx xxx是要安装的包，都是处理excel的包,一个生成一个读取
yagmail 是个很好的发送邮件的包，使用方便3行就可以，支持上传附件
截图信息：
excel截图
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 17/8/29 上午9:13
# @Author : lee
# @File : excel_update.py
# @Software: PyCharm
# 说明: code后有'#'的时测试时加的或者需要修改的code
# 两个参数， 天数和邮件 发送多人可以用list形式 例如 ['xx@xx.com','xxx@xx.com']
import os
import xlwt # 导入写excel的包
import xlrd # 读excel
import sys
import yagmail
import datetime
class update(object):
def __init__(self,day,name):
self.name = name
self.day = int(day)
self.dict_date = {}
self.dict_date = {}
self.list_day_of_urgent_update = []
self.list_alltime = []
def get_datetime(self): # 获取当前日期和30天内的所欲日期放入list
oneday = datetime.timedelta(days = 1)
nowday = datetime.date.today()
self.nowday = nowday
for i in range(1, self.day+1):
theday = nowday - oneday * i
update_day = theday.strftime('%Y-%m-%d')
self.list_alltime.append(update_day)
if theday.strftime('%A') == 'Friday' or theday.strftime('%A') == 'Saturday' or theday.strftime('%A') == 'Sunday':
# print(theday.strftime('%A'))
self.list_day_of_urgent_update.append(update_day)
#print(self.list_alltime)
def open_log(self): # 打开对应目录下的日志文件 生成两个文件 保存到当前目录
for i in self.list_alltime:
day_of_update = open('log/%s.log' % i, 'r') #改到服务器 每天产生数据包存放位置
# for i in day_of_update:
# print(i)
day_of_all_update = open('log/day_of_all_update.log', 'a')
for line in day_of_update:
day_of_all_update.writelines(line)
day_of_all_urgent = open('log/day_of_all_urgent.log', 'a')
if len(self.list_day_of_urgent_update) != 0:
for j in self.list_day_of_urgent_update:
day_of_urgent = open('log/%s.log' % j, 'r') #这里也是
for line in day_of_urgent:
day_of_all_urgent.writelines(line)
day_of_all_update.close()
day_of_all_urgent.close()
def os_shell(self): # 调用os 处理整理好的日志文件，统计数量后规则化输出
os.system('sh runduck.sh day_of_all_update.log > day_of_all_update.txt')
os.system('sh runduck.sh day_of_all_urgent.log > day_of_all_urgent.txt')
def head_style(self):
style = xlwt.XFStyle()
font = xlwt.Font()
font.height =280
style.font = font
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER #水平居中
style.alignment = alignment
return style
def body_style(self):
style = xlwt.XFStyle()
font = xlwt.Font()
font.height =280
style.font = font
return style
def write_first(self): # 将数据规则化输出到excel中
date_file = open('day_of_all_update.txt')
counter = 0
for line in date_file:
if '------' in line :
pass
else:
self.dict_date[counter] = line.replace('\n','')
#print(dict_date[counter])
counter += 1
wb = xlwt.Workbook()
sh = wb.add_sheet('昨日更新')
sh.write_merge(0,0,0,5, '昨日更新')
sh.write(1,0,'一级目录更新内容')
sh.write(1,1,'合计')
sh.write(1,2,'二级目录更新内容')
sh.write(1,3,'更新次数')
sh.write(1,4,'紧急更新次数')
sh.write(1,5,'失败次数')
counter1 = 1
for i in self.dict_date:
if '一级目录更新内容' in self.dict_date[i]:
sh.write(counter1+1,0,self.dict_date[i+1].split(' ')[0])
sh.write(counter1+1,1,self.dict_date[i+1].split(' ')[2])
sh.write(counter1+1,2,self.dict_date[i+3].split(' ')[0])
sh.write(counter1+1,3,self.dict_date[i+3].split(' ')[2])
counter1 += 1
pass
elif '执行了更新' in self.dict_date[i] and self.dict_date[i-1] != '一级目录更新内容'and self.dict_date[i-1] != '二级目录更新内容' :
sh.write(counter1+1,2,self.dict_date[i].split(' ')[0])
sh.write(counter1+1,3,self.dict_date[i].split(' ')[2])
counter1 += 1
wb.save('middle.xls')
date_file.close()
'''
函数的作用是找不第二级目录的内容和排序，
因为xlwt这个包不支持直接读取刚生成的excel文件，
只能用另外一个包xlrd 读取并获得二级目录的数据和
'''
def read_first(self):
workbook = xlrd.open_workbook(r'middle.xls')
sheet2 = workbook.sheet_by_name('昨日更新')
cols = sheet2.col_values(2)
#print(cols)
return cols
def write_second(self): # 根据获得的二级目录位置信息，再次执行一遍，可以写入紧急更行的信息
date_file = open('day_of_all_update.txt')
date_urgent_log = open('day_of_all_urgent.txt')
dict_urgent = {}
counter2 = 0
for line in date_urgent_log:
if '------' in line :
pass
else:
dict_urgent[counter2] = line.replace('\n','')
#print(dict_date[counter])
counter2 += 1
# for i in dict_urgent:
# print(dict_urgent[i])
counter3 = 0
for line in date_file:
if '------' in line :
pass
else:
self.dict_date[counter3] = line.replace('\n','')
#print(dict_date[counter])
counter3 += 1
wb = xlwt.Workbook()
sh = wb.add_sheet('%s天内更新信息'%self.day)
first_col=sh.col(0) #xlwt中是行和列都是从0开始计算的
th_col=sh.col(2)
# sec_col=sh.col(0)
first_col.width=256*20
th_col.width = 256*20
if len(self.list_day_of_urgent_update) != 0:
sh.write_merge(0,0,0,5, '%s天内更新信息'%self.day,self.head_style())
else:
sh.write_merge(0,0,0,3, '%s天内更新信息'%self.day,self.head_style())
#sh.horz = xlwt.Alignment.HORZ_CENTER
sh.write(1,0,'一级目录更新内容',self.body_style())
sh.write(1,1,'合计',self.body_style())
sh.write(1,2,'二级目录更新内容',self.body_style())
sh.write(1,3,'更新次数',self.body_style())
if len(self.list_day_of_urgent_update) != 0:
sh.write(1,4,'紧急更新次数',self.body_style())
sh.write(1,5,'失败次数',self.body_style())
counter1 = 1
# for i in self.dict_date: #
# print(self.dict_date[i]) #
for i in self.dict_date:
if '一级目录更新内容' in self.dict_date[i]:
sh.write(counter1+1,0,self.dict_date[i+1].split(' ')[0],self.body_style())
sh.write(counter1+1,1,self.dict_date[i+1].split(' ')[2],self.body_style())
sh.write(counter1+1,2,self.dict_date[i+3].split(' ')[0],self.body_style())
sh.write(counter1+1,3,self.dict_date[i+3].split(' ')[2],self.body_style())
counter1 += 1
pass
elif '执行了更新' in self.dict_date[i] and self.dict_date[i-1] != '一级目录更新内容'and self.dict_date[i-1] != '二级目录更新内容' :
sh.write(counter1+1,2,self.dict_date[i].split(' ')[0],self.body_style())
sh.write(counter1+1,3,self.dict_date[i].split(' ')[2],self.body_style())
counter1 += 1
#sh.write(33,33,'二级目录')
counter_l = 0
if len(self.list_day_of_urgent_update) != 0:
for i in self.read_first():
#print(i)
for j in dict_urgent:
if i != '':
if dict_urgent[j] != "一级目录更新内容" and dict_urgent[j] != "二级目录更新内容":
# print(dict_urgent[j].replace('\n', '').split(' ')[0])
if i == dict_urgent[j].replace('\n', '').split(' ')[0]:
sh.write(counter_l,4,dict_urgent[j].replace('\n','').split(' ')[2],self.body_style())
counter_l += 1
wb.save('update.xls') # 产物文件
date_file.close()
def markdown(self): # 根据获得的二级目录位置信息，再次执行一遍，可以写入紧急更行的信息
date_file = open('day_of_all_update.txt')
date_urgent_log = open('day_of_all_urgent.txt')
dict_urgent = {}
counter2 = 0
for line in date_urgent_log:
if '------' in line :
pass
else:
dict_urgent[counter2] = line.replace('\n','')
#print(dict_date[counter])
counter2 += 1
# for i in dict_urgent:
# print(dict_urgent[i])
counter3 = 0
for line in date_file:
if '------' in line :
pass
else:
self.dict_date[counter3] = line.replace('\n','')
#print(dict_date[counter])
counter3 += 1
wb = xlwt.Workbook()
sh = wb.add_sheet('%s天内更新信息'%self.day)
first_col=sh.col(0) #xlwt中是行和列都是从0开始计算的
th_col=sh.col(2)
# sec_col=sh.col(0)
first_col.width=256*20
th_col.width = 256*20
if len(self.list_day_of_urgent_update) != 0:
sh.write_merge(0,0,0,5, '%s天内更新信息'%self.day,self.head_style())
else:
sh.write_merge(0,0,0,3, '%s天内更新信息'%self.day,self.head_style())
#sh.horz = xlwt.Alignment.HORZ_CENTER
sh.write(1,0,'一级目录更新内容',self.body_style())
sh.write(1,1,'合计',self.body_style())
sh.write(1,2,'二级目录更新内容',self.body_style())
sh.write(1,3,'更新次数',self.body_style())
if len(self.list_day_of_urgent_update) != 0:
sh.write(1,4,'紧急更新次数',self.body_style())
sh.write(1,5,'失败次数',self.body_style())
counter1 = 1
# for i in self.dict_date: #
# print(self.dict_date[i]) #
for i in self.dict_date:
if '一级目录更新内容' in self.dict_date[i]:
sh.write(counter1+1,0,self.dict_date[i+1].split(' ')[0],self.body_style())
sh.write(counter1+1,1,self.dict_date[i+1].split(' ')[2],self.body_style())
sh.write(counter1+1,2,self.dict_date[i+3].split(' ')[0],self.body_style())
sh.write(counter1+1,3,self.dict_date[i+3].split(' ')[2],self.body_style())
counter1 += 1
pass
elif '执行了更新' in self.dict_date[i] and self.dict_date[i-1] != '一级目录更新内容'and self.dict_date[i-1] != '二级目录更新内容' :
sh.write(counter1+1,2,self.dict_date[i].split(' ')[0],self.body_style())
sh.write(counter1+1,3,self.dict_date[i].split(' ')[2],self.body_style())
counter1 += 1
#sh.write(33,33,'二级目录')
counter_l = 0
if len(self.list_day_of_urgent_update) != 0:
for i in self.read_first():
#print(i)
for j in dict_urgent:
if i != '':
if dict_urgent[j] != "一级目录更新内容" and dict_urgent[j] != "二级目录更新内容":
# print(dict_urgent[j].replace('\n', '').split(' ')[0])
if i == dict_urgent[j].replace('\n', '').split(' ')[0]:
sh.write(counter_l,4,dict_urgent[j].replace('\n','').split(' ')[2],self.body_style())
counter_l += 1
wb.save('update.xls') # 产物文件
date_file.close()
def detele(self): # 调用os 将产生的临时文件删除
os.system('rm -rf log/day_all.log')
os.system('rm -rf log/day_all02.log')
os.system('rm -rf log/day_of_all_update.log')
os.system('rm -rf log/day_of_all_urgent.log')
os.system('rm -rf day_of_all_update.txt')
os.system('rm -rf day_of_all_urgent.txt')
def send_mail(self):
yag = yagmail.SMTP(user='xx@xx.com', password='yonyou@1988', host='smtp.xx.com', port='465')
body = "附件：一二级目录更新情况"
if self.name == 'noc':
self.name = ['xx@xx.com','xx@xx.com']
self.name = [self.name]
# yag.send(to='xx@xx.com', subject='工作文件', contents=[body, 'middle.xls'])
yag.send(to=self.name, subject="%s--%s天内更新情况" % (self.nowday, self.day), contents=[body, 'update.xls'])
print("给%s成功发送邮件" % self.name)
if __name__ == '__main__':
item = update(sys.argv[1],sys.argv[2])
# item = update(1)
item.get_datetime()
item.open_log()
item.os_shell()
item.write_first()
item.read_first()
item.write_second()
item.detele()
item.send_mail()
