File size: 5,879 Bytes
f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 f8cc540 f3391c9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | """
Automotive Intelligence Dashboard — Database Module (raw sqlite3)
"""
from __future__ import annotations
import hashlib
import json
import logging
import sqlite3
import threading
from datetime import datetime, timedelta
from typing import Any, Dict, List, Optional
from config import config
logger = logging.getLogger(__name__)
class NewsDatabase:
"""Thread-safe SQLite store for automotive news."""
def __init__(self, db_path: Optional[str] = None) -> None:
self._path = str(db_path or config.database_path)
self._local = threading.local()
self._ensure_schema()
def _conn(self) -> sqlite3.Connection:
"""Get a thread-local connection."""
if not hasattr(self._local, "conn") or self._local.conn is None:
conn = sqlite3.connect(self._path, check_same_thread=False)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA busy_timeout=5000")
self._local.conn = conn
return self._local.conn
# ------------------------------------------------------------------
# Schema
# ------------------------------------------------------------------
def _ensure_schema(self) -> None:
c = self._conn()
c.execute("""CREATE TABLE IF NOT EXISTS news (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title_en TEXT, title_zh TEXT,
summary_en TEXT, summary_zh TEXT,
content TEXT, url TEXT, source TEXT,
language TEXT, category TEXT,
published_at TEXT, fetched_at TEXT,
companies TEXT, roles TEXT, content_hash TEXT UNIQUE
)""")
c.execute("CREATE INDEX IF NOT EXISTS idx_news_source ON news(source)")
c.execute("CREATE INDEX IF NOT EXISTS idx_news_pub ON news(published_at)")
c.execute("CREATE INDEX IF NOT EXISTS idx_news_hash ON news(content_hash)")
c.execute("""CREATE TABLE IF NOT EXISTS crawl_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source TEXT, articles_fetched INTEGER, articles_new INTEGER,
status TEXT, error TEXT,
started_at TEXT, completed_at TEXT
)""")
c.commit()
# ------------------------------------------------------------------
# Helpers
# ------------------------------------------------------------------
@staticmethod
def _hash(content: str) -> str:
return hashlib.sha256(content.encode("utf-8", errors="replace")).hexdigest()
# ------------------------------------------------------------------
# Write
# ------------------------------------------------------------------
def upsert_article(self, article: Dict[str, Any]) -> bool:
title = (article.get("title_en") or "").strip()
if not title:
return False
content = (article.get("content") or article.get("summary_en") or "").strip()
h = self._hash(content or title)
c = self._conn()
cur = c.execute("SELECT id FROM news WHERE content_hash = ?", (h,))
if cur.fetchone():
return False
c.execute(
"""INSERT INTO news (title_en, title_zh, summary_en, summary_zh, content,
url, source, language, category, published_at, fetched_at,
companies, roles, content_hash)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
(title,
article.get("title_zh") or "",
(article.get("summary_en") or "")[:500],
(article.get("summary_zh") or "")[:300],
content[:2000],
article.get("url", ""),
article.get("source", "unknown"),
article.get("language", "en"),
article.get("category", "general"),
article.get("published_at", datetime.now().isoformat()),
datetime.now().isoformat(),
json.dumps(article.get("companies", []), ensure_ascii=False),
json.dumps(article.get("roles", []), ensure_ascii=False),
h))
c.commit()
return True
# ------------------------------------------------------------------
# Read
# ------------------------------------------------------------------
def get_news(self, start_date=None, limit=100, company=None, role=None, category=None):
c = self._conn()
rows = [dict(r) for r in c.execute(
"SELECT * FROM news ORDER BY published_at DESC LIMIT ?", (limit,))]
if start_date:
rows = [r for r in rows if (r.get("published_at") or "") >= start_date]
if role and role != "全部":
rows = [r for r in rows if role in (r.get("roles") or "[]")]
if company and company != "全部":
rows = [r for r in rows if company in (r.get("companies") or "[]")]
if category and category != "全部":
rows = [r for r in rows if (r.get("category") or "").lower() == category.lower()]
return rows
def get_recent_count(self, hours=24):
cutoff = (datetime.now() - timedelta(hours=hours)).isoformat()
c = self._conn()
results = {}
for r in c.execute("SELECT source, COUNT(*) as cnt FROM news WHERE fetched_at >= ? GROUP BY source", (cutoff,)):
results[r["source"]] = r["cnt"]
return results
def total_count(self) -> int:
c = self._conn()
return c.execute("SELECT COUNT(*) FROM news").fetchone()[0]
def log_crawl(self, source, fetched, new, status="ok", error=""):
c = self._conn()
now = datetime.now().isoformat()
c.execute(
"INSERT INTO crawl_log (source, articles_fetched, articles_new, status, error, started_at, completed_at) VALUES (?,?,?,?,?,?,?)",
(source, fetched, new, status, error, now, now))
c.commit()
|