apache日志导入mysql

import mysql.connector
import re

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)
cursor = db.cursor()

# 创建表结构
cursor.execute("""
CREATE TABLE IF NOT EXISTS access_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(15),
    request_time DATETIME,
    request_method VARCHAR(10),
    request_uri VARCHAR(255),
    status_code INT,
    response_size INT
)
""")

# 读取日志文件并插入数据
with open('access.log', 'r') as file:
    for line in file:
        match = re.match(r'(\S+) (\S+) (\S+) \[([\w:/]+\s[+\-]\d{4})\] "(\S+)\s?(\S+)?\s?(HTTP/\d\.\d)?" (\d{3}) (\S+)', line)
        if match:
            ip_address, _, _, request_time, request_method, request_uri, _, status_code, response_size = match.groups()
            cursor.execute("""
            INSERT INTO access_logs (ip_address, request_time, request_method, request_uri, status_code, response_size)
            VALUES (%s, %s, %s, %s, %s, %s)
            """, (ip_address, request_time, request_method, request_uri, status_code, response_size))

# 提交事务
db.commit()

# 关闭连接
cursor.close()
db.close()