笔记：mysql慢查询日志转换为excel

说明

一个简单的python练习

代码

import time
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import *

# 日志源文件
s_logfile = 'slowlog_10s.log'
# 日志分析表表头
l_wb_header = ('Time', 'User', 'Host', 'Id', 'Query_time', 'Lock_time', 'Rows_sent', 'Rows_examined', 'timestamp',
               'SQLtext')
nowdt = time.strftime("%Y-%m-%d-%H%M%S", time.localtime())
# 日志目标文件
t_logfile = s_logfile + '_' + nowdt + '.xlsx'
# 创建表格
log_wb = openpyxl.Workbook()
log_ws = log_wb.create_sheet('log_analyze', 0)
log_wb.save(t_logfile)


def p2excel(wbfile, l_value, pos_r):
    wb = load_workbook(wbfile)
    ws = wb['log_analyze']
    i = 1
    for a in l_value:
        ws.cell(pos_r, i, a)
        i += 1
    wb.save(wbfile)


# 加入表头
pos_r = 1
p2excel(t_logfile, l_wb_header, pos_r)
file_line_maxnum = 0
file_line_num = 1
l_value = ['', '', '', '', '', '', '', '', '', '']
with open(s_logfile, 'r', encoding='utf-8') as file:
    file_line_maxnum = len(file.readlines())

with open(s_logfile, 'r', encoding='utf-8') as file:
    line = file.readline()
    while line:
        # 处理进度
        print(file_line_num/file_line_maxnum)
        # 处理当前行
        if line[:6] == '# Time':
            if pos_r != 1:
                p2excel(t_logfile, l_value, pos_r)
            l_value = ['', '', '', '', '', '', '', '', '', '']
            l_value[0] = line.split()[2]
            pos_r += 1
            file_line_num += 1
        elif line[:11] == '# User@Host':
            l_value[1] = line.split()[2]
            l_value[2] = line.split()[4].replace('[', '').replace(']', '')
            l_value[3] = line.split()[6]
            file_line_num += 1
        elif line[:12] == '# Query_time':
            l_value[4] = line.split()[2]
            l_value[5] = line.split()[4]
            l_value[6] = line.split()[6]
            l_value[7] = line.split()[8]
            file_line_num += 1
        elif line[:13] == 'SET timestamp':
            l_value[8] = line
            file_line_num += 1
        else:
            l_value[9] = l_value[9] + str(line)
            file_line_num += 1
        line = file.readline()  # 读取下一行