# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者:cacho_37967865
#博客:https://blog.csdn.net/sinat_37967865
#文件:linuxTop.py
#日期:2019年05月20日
#备注:发起性能测试前,top -b -d 1 > /data/top500.txt将性能信息重定向文件,然后发起性能测试;最后将文件的内容整理后存入到数据库。
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import re
import pymysql


db = pymysql.connect(
 host="localhost",
 user="root",
 password="123456",
 port=3306,
 use_unicode=True,
 charset="utf8",
 database="cacho")

cursor = db.cursor()

def createTable():
 sql1 = 'drop table if exists linux_top;'
 sql2 = 'create table linux_top(top_time VARCHAR(32),binfa_num int,average1 float,average5 float,average15 float,us_cpu VARCHAR(15),' \
 'sy_cpu VARCHAR(15),wa_cpu VARCHAR(15),ni_cpu VARCHAR(15),id_cpu VARCHAR(15),hi_cpu VARCHAR(15),si_cpu VARCHAR(15),' \
 'st_cpu VARCHAR(15),men_total VARCHAR(25),men_used VARCHAR(20),men_free VARCHAR(20),men_buffers VARCHAR(20),PRIMARY KEY (top_time));'
 cursor.execute(sql1)
 cursor.execute(sql2)
 db.commit()


def insert_data(top_time,binfa_num,average1,average5,average15,us_cpu,sy_cpu,wa_cpu,ni_cpu,id_cpu,hi_cpu,si_cpu,st_cpu,men_total,men_used,men_free,men_buffers):
 sql = "insert into linux_top(top_time,binfa_num,average1,average5,average15,us_cpu,sy_cpu,wa_cpu,ni_cpu,id_cpu,hi_cpu,si_cpu,st_cpu,men_total,men_used,men_free,men_buffers) " \
 "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
 % (top_time,binfa_num,average1,average5,average15,us_cpu,sy_cpu,wa_cpu,ni_cpu,id_cpu,hi_cpu,si_cpu,st_cpu,men_total,men_used,men_free,men_buffers)
 try:
 cursor.execute(sql)
 except Exception as e:
 # 发生错误时回滚
 db.rollback()
 print(str(e))
 else:
 db.commit() # 事务提交


def get_auditTime(file,date):
 logs = open(file, 'r' ,encoding= 'utf8')
 load = []
 try:
 for line in logs.readlines():
 line = line.rstrip()
 if line[0:3] == 'top':
 top_time = date + line[6:14]
 #load_average = line[-16:]
 average1 = line[-16:-12]
 average5 = line[-10:-6]
 average15 = line[-4:]
 print(average1,average5,average15)
 row = []
 row.append(top_time)
 row.append(average1)
 row.append(average5)
 row.append(average15)


 elif line[0:6] == 'Cpu(s)':
 line = line.replace('Cpu(s): ','')
 us_cpu = re.findall(r'(\d.*)us',line)[0]
 sy_cpu = re.findall(r'us, *(\d.*)sy',line)[0]
 wa_cpu = re.findall(r'id, *(\d.*)wa', line)[0]
 ni_cpu = re.findall(r'sy, *(\d.*)ni',line)[0]
 id_cpu = re.findall(r'ni, *(\d.*)id', line)[0]
 hi_cpu = re.findall(r'wa, *(\d.*)hi', line)[0]
 si_cpu = re.findall(r'hi, *(\d.*)si', line)[0]
 st_cpu = re.findall(r'si, *(\d.*)st', line)[0]
 row.append(us_cpu)
 row.append(sy_cpu)
 row.append(wa_cpu)
 row.append(ni_cpu)
 row.append(id_cpu)
 row.append(hi_cpu)
 row.append(si_cpu)
 row.append(st_cpu)
 #print(line)
 #print(us_cpu,'-->'+sy_cpu,'-->'+ni_cpu,'-->'+id_cpu,'-->'+wa_cpu,'-->'+hi_cpu,'-->'+si_cpu,'-->'+st_cpu)
 elif line[0:4] == 'Mem:':
 #print(line)
 men_total = re.findall(r' *(\d.*)total',line)[0]
 men_used = re.findall(r'total, *(\d.*)used', line)[0]
 men_free = re.findall(r'used, *(\d.*)free', line)[0]
 men_buffers = re.findall(r'free, *(\d.*)buffers', line)[0]
 row.append(men_total)
 row.append(men_used)
 row.append(men_free)
 row.append(men_buffers)
 #print(men_total, men_used, men_free, men_buffers)
 #print(row)
 load.append(row)
 finally:
 logs.close()
 return load


if __name__ == '__main__':
 createTable()
 top_info = get_auditTime('E:\zenglingwei\\top.txt','2019-08-13 ')
 for info in top_info:
 insert_data(info[0], 3385, float(info[1]), float(info[2]), float(info[3]), info[4], info[5], info[6], info[7], info[8],
 info[9],info[10], info[11], info[12], info[13],info[14],info[15])