NEWS / src /database.py
Hetheclif's picture
Upload database.py
f3391c9 verified
Raw History Blame Contribute Delete
5.88 kB
"""
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()