Download src/database.py from Hetheclif/NEWS: direct link, hf CLI and curl.
- Browser
- Download file 5.88 kB
-
https://huggingface.co/spaces/Hetheclif/NEWS/resolve/main/src/database.py
- Command line
-
hf download hf://spaces/Hetheclif/NEWS/src/database.py
-
curl -L -o database.py https://huggingface.co/spaces/Hetheclif/NEWS/resolve/main/src/database.py
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 | |
| # ------------------------------------------------------------------ | |
| 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() | |