Загрузка данных

import sqlite3
import http.server
import re
import oracledb
import threading
import time

PP_DSN = "localhost:1521/XE"
PP_USER = "monitoring"
PP_PASS = "monitor123"
LOCAL_DB = "errors.db"
WEB_PORT = 8080

conn = sqlite3.connect(LOCAL_DB, check_same_thread=False)
conn.execute("""CREATE TABLE IF NOT EXISTS errors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pp_name TEXT DEFAULT 'ПП-Тест',
    error_code TEXT,
    message_text TEXT,
    table_name TEXT,
    occurred_at TIMESTAMP,
    is_new BOOLEAN DEFAULT 1)""")
conn.commit()

ERROR_DICT = {
    "ORA-00001": {"name": "Нарушение уникальности", "cause": "Дубликат значения", "fix": "1. Проверить SEQUENCE\n2. Найти дубликат: SELECT empno, COUNT(*) FROM emp GROUP BY empno HAVING COUNT(*) > 1\n3. Удалить дубликат"},
    "ORA-00942": {"name": "Таблица не существует", "cause": "Нет прав или удалена", "fix": "1. Проверить имя\n2. GRANT SELECT ON ..."},
}

def collect():
    try:
        c = oracledb.connect(user=PP_USER, password=PP_PASS, dsn=PP_DSN)
        cur = c.cursor()
        cur.execute("SELECT originating_timestamp, message_text FROM V$DIAG_ALERT_EXT WHERE message_text LIKE '%ORA-%' ORDER BY originating_timestamp DESC FETCH FIRST 20 ROWS ONLY")
        for ts, msg in cur:
            code = re.search(r'ORA-\d{5}', msg)
            code = code.group(0) if code else "UNKNOWN"
            tbl = re.search(r'\(([A-Za-z]+)\.([A-Za-z_]+)\)', msg)
            tbl = f"{tbl.group(1)}.{tbl.group(2)}" if tbl else "не определена"
            if not conn.execute("SELECT id FROM errors WHERE message_text=? AND occurred_at=?", [msg, ts]).fetchone():
                conn.execute("INSERT INTO errors (error_code,message_text,table_name,occurred_at) VALUES (?,?,?,?)", [code, msg, tbl, ts])
                conn.commit()
                print(f"[+] {code} в {tbl}")
        c.close()
    except Exception as e:
        print(f"[-] {e}")

class H(http.server.BaseHTTPRequestHandler):
    def do_GET(self):
        self.send_response(200); self.send_header('Content-type', 'text/html; charset=utf-8'); self.end_headers()
        errs = conn.execute("SELECT * FROM errors ORDER BY occurred_at DESC LIMIT 50").fetchall()
        html = f"<html><head><meta charset='utf-8'><title>ЦУМ</title><style>body{{font-family:Arial;margin:20px;background:#f5f5f5}}.card{{background:white;border-radius:8px;padding:15px;margin:10px 0;border-left:4px solid #ff9800}}.code{{font-weight:bold;color:#d32f2f}}.fix{{background:#e8f5e9;padding:10px;border-radius:4px;margin-top:10px;white-space:pre-line}}</style></head><body><h2>ЦУМ — Ошибки Oracle</h2><p>Ошибок: {len(errs)}</p>"
        for e in errs:
            info = ERROR_DICT.get(e[2], {"name": e[2], "fix": "Добавьте в словарь"})
            html += f"<div class='card'><span class='code'>{e[2]}</span> — <b>{info['name']}</b><br><small>{e[5]} | {e[4]}</small><div class='fix'><b>Как исправить:</b><br>{info['fix']}</div></div>"
        html += "</body></html>"
        self.wfile.write(html.encode())

if __name__ == '__main__':
    print("[*] Первый сбор..."); collect()
    threading.Thread(target=lambda: [time.sleep(30), collect()], daemon=True).start()
    print(f"[*] http://localhost:{WEB_PORT}")
    http.server.HTTPServer(('0.0.0.0', WEB_PORT), H).serve_forever()