import os
import re
import pymysql
# 獲取文件名
my_path = r"C:日志文件夾"
file_names = os.listdir(my_path)
file_list = [os.path.join(my_path, file) for file in file_names]
wait_list = []
for file in file_list:
    with open(file, 'r', encoding='utf-8') as f:
        lines = f.readlines()
        for i in lines:
            pattern = re.compile(
                '\[(?P<time>.*?)\] (?P<ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}) (?P<pro_ip>.*?) (?P<dura_time>\d+) \"(?P<referer>.*?)\" \"(?P<method>.*?) (?P<url>.*?)\" (?P<code>\d+) (?P<size>\d+) (?P<res_size>\d+) (?P<miss>.*?) \"(?P<ua>.*?)\" \"(?P<html_type>.*?)\"')
            gs = pattern.findall(i)
            item_list = gs[0]
            s_time = item_list[0]
            ip = item_list[1]
            pro_ip = item_list[2]
            dura_time = item_list[3]
            referer = item_list[4]
            method = item_list[5]
            url = item_list[6]
            code = item_list[7]
            size = item_list[8]
            res_size = item_list[9]
            miss = item_list[10]
            ua = item_list[11]
            html_type = item_list[12]
            values_str = f"('{s_time}', '{ip}', '{pro_ip}', {int(dura_time)}, '{referer}', '{method}', '{url}', {int(code)}, {int(size)}, {int(res_size)}, '{miss}', '{ua}','{html_type}')"
            wait_list.append(values_str)

def insert_data():
    for i in range(0,int(len(wait_list)/1000+1)):
        items = wait_list[i * 1000:i * 1000 + 1000]
        item_str = ",".join(items)
        inser_sql = f"INSERT INTO ll(s_time, ip, pro_ip, dura_time, referer, method, url,code, size, res_size, miss, ua,html_type) VALUES {item_str}"
        db = pymysql.connect(host='localhost',
                             user='root',
                             password='root',
                             database='logs')
        cursor = db.cursor()
        try:
            cursor.execute(inser_sql)
            db.commit()
        except Exception as e:
            # print(content)
            print(e)
            db.rollback()

select count(id) num,referer from ll GROUP BY referer ORDER BY num desc