#!/usr/bin/env python

# encoding: utf-8

#@author: 东哥加油!

#@file: log_analyze.py

#@time: 2018/8/23 17:15

import pandas as pd

import re

import time

import datetime

def tj_log_to_excel(from_file_name,to_file_name):

    file=open(from_file_name,'r',encoding='UTF-8')

    columns = ('表名','SELECT 统计','UPDATE 统计','INSERT 统计','DELETE 统计')

    results = []

    results.append(columns)

    dict1 = {}

    print(time.strftime("%H:%M:%S"))

    for (num,line) in enumerate(file):

        line = line.lower()

        l_row = []

        if(re.search(r'delete from ',line)):

            str = re.findall(r"delete from ([a-zA-Z0-9_\.]*)", line)

            str = str[0]

            if(dict1.get(str) == None):

                l_row = [0,0,0,1]

                dict1[str] = l_row

            else:

                v = dict1.get(str)

                v[3] = v[3] + 1

        elif (re.search(r' from ',line)):

            str = re.findall(r" from ([a-zA-Z0-9_\.]*)", line)

            str = str[0]

            if str != '':

                if (dict1.get(str) == None):

                    l_row = [1, 0, 0, 0]

                    dict1[str] = l_row

                else:

                    v = dict1.get(str)

                    v[0] = v[0] + 1

        elif (re.search(r'"update ', line)):

            str = re.findall(r"update ([a-zA-Z0-9_\.]*)", line)

            str = str[0]

            if str != '':

                if (dict1.get(str) == None):

                    l_row = [0, 1, 0, 0]

                    dict1[str] = l_row

                else:

                    v = dict1.get(str)

                    v[1] = v[1] + 1

        elif (re.search(r'"insert into ', line)):

            str = re.findall(r"insert into ([a-zA-Z0-9_\.]*)", line)

            str = str[0]

            if str != '':

                if (dict1.get(str) == None):

                    l_row = [0, 0, 1, 0]

                    dict1[str] = l_row

                else:

                    v = dict1.get(str)

                    v[2] = v[2] + 1

    for key in dict1:

        t_row = []

        t_row.append(key)

        t_row.append(dict1[key][0])

        t_row.append(dict1[key][1])

        t_row.append(dict1[key][2])

        t_row.append(dict1[key][3])

        results.append(t_row)

    df = pd.DataFrame(results)

    df.to_excel(to_file_name)

    print(time.strftime("%H:%M:%S"))

if __name__ == '__main__':

    now_time = datetime.datetime.now()

    step_time = datetime.timedelta(days=1)

    yes_time = now_time - step_time

    pdate = yes_time.strftime('%Y%m%d')

    from_file_name = '/usr/local/mysql-proxy/log/sql_balance.log_'+pdate

    print(from_file_name)

    to_file_name= '/data/shell/sk/sql_balance_'+pdate+'.xls'

    tj_log_to_excel(from_file_name,to_file_name)