484 lines
14 KiB
Python
484 lines
14 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
新闻智能跟踪系统 - SQLite 数据访问层
|
|
原生 sqlite3，无 ORM，结构与其它项目保持一致。
|
|
"""
|
|
import sqlite3
|
|
import json
|
|
import os
|
|
from datetime import datetime
|
|
|
|
import config
|
|
|
|
_SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS sources (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
type TEXT DEFAULT '',
|
|
url TEXT DEFAULT '',
|
|
description TEXT DEFAULT '',
|
|
weight REAL DEFAULT 1.0,
|
|
enabled INTEGER DEFAULT 1,
|
|
status TEXT DEFAULT 'ok', -- ok / error
|
|
last_fetch TEXT DEFAULT '',
|
|
last_count INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
source_id INTEGER DEFAULT 0,
|
|
title TEXT NOT NULL,
|
|
url TEXT DEFAULT '',
|
|
author TEXT DEFAULT '',
|
|
content TEXT DEFAULT '',
|
|
summary TEXT DEFAULT '',
|
|
domain TEXT DEFAULT '', -- 分类：AI模型与算法 / 芯片与硬件 ...
|
|
entities TEXT DEFAULT '[]', -- JSON 数组：涉及的公司/实体
|
|
importance INTEGER DEFAULT 0, -- LLM 深度评分 1-10
|
|
relevance INTEGER DEFAULT 0, -- 兴趣相关度 0-100
|
|
total_score INTEGER DEFAULT 0, -- 综合分 0-100
|
|
is_important INTEGER DEFAULT 0,
|
|
analysis TEXT DEFAULT '', -- LLM 分析结论(为什么重要)
|
|
llm_status TEXT DEFAULT 'pending', -- pending / done / skipped / error
|
|
notified INTEGER DEFAULT 0, -- 是否已实时邮件通知
|
|
status TEXT DEFAULT 'new', -- new / summarized
|
|
published_at TEXT DEFAULT '',
|
|
collected_at TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS keywords (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
keyword TEXT NOT NULL,
|
|
weight INTEGER DEFAULT 5,
|
|
enabled INTEGER DEFAULT 1
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS domains (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
weight INTEGER DEFAULT 5,
|
|
enabled INTEGER DEFAULT 1
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS companies (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
enabled INTEGER DEFAULT 1
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
type TEXT DEFAULT 'realtime', -- realtime / summary
|
|
subject TEXT DEFAULT '',
|
|
count INTEGER DEFAULT 0,
|
|
article_ids TEXT DEFAULT '[]',
|
|
status TEXT DEFAULT 'ok',
|
|
detail TEXT DEFAULT '',
|
|
sent_at TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_collected ON articles(collected_at);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_score ON articles(total_score);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(llm_status);
|
|
"""
|
|
|
|
|
|
def get_conn():
|
|
conn = sqlite3.connect(config.DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
return conn
|
|
|
|
|
|
def init_db():
|
|
os.makedirs(config.DATA_DIR, exist_ok=True)
|
|
conn = get_conn()
|
|
conn.executescript(_SCHEMA)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def now_str():
|
|
return datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|
|
|
|
|
# ---------------- settings ----------------
|
|
def get_setting(key, default=None):
|
|
conn = get_conn()
|
|
row = conn.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
|
|
conn.close()
|
|
if row is None:
|
|
return default
|
|
try:
|
|
return json.loads(row["value"])
|
|
except Exception:
|
|
return row["value"]
|
|
|
|
|
|
def set_setting(key, value):
|
|
conn = get_conn()
|
|
conn.execute(
|
|
"INSERT INTO settings(key,value) VALUES(?,?) "
|
|
"ON CONFLICT(key) DO UPDATE SET value=excluded.value",
|
|
(key, json.dumps(value, ensure_ascii=False)),
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def get_all_settings():
|
|
conn = get_conn()
|
|
rows = conn.execute("SELECT key,value FROM settings").fetchall()
|
|
conn.close()
|
|
out = {}
|
|
for r in rows:
|
|
try:
|
|
out[r["key"]] = json.loads(r["value"])
|
|
except Exception:
|
|
out[r["key"]] = r["value"]
|
|
return out
|
|
|
|
|
|
# ---------------- sources ----------------
|
|
def list_sources(only_enabled=False):
|
|
conn = get_conn()
|
|
sql = "SELECT * FROM sources"
|
|
if only_enabled:
|
|
sql += " WHERE enabled=1"
|
|
sql += " ORDER BY id"
|
|
rows = conn.execute(sql).fetchall()
|
|
conn.close()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def get_source(sid):
|
|
conn = get_conn()
|
|
row = conn.execute("SELECT * FROM sources WHERE id=?", (sid,)).fetchone()
|
|
conn.close()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def add_source(name, type_, url, desc, weight=1.0):
|
|
conn = get_conn()
|
|
cur = conn.execute(
|
|
"INSERT INTO sources(name,type,url,description,weight,created_at) VALUES(?,?,?,?,?,?)",
|
|
(name, type_, url, desc, weight, now_str()),
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.lastrowid
|
|
|
|
|
|
def update_source(sid, **fields):
|
|
allowed = {"name", "type", "url", "description", "weight", "enabled"}
|
|
sets, vals = [], []
|
|
for k, v in fields.items():
|
|
if k in allowed:
|
|
sets.append(f"{k}=?")
|
|
vals.append(v)
|
|
if not sets:
|
|
return
|
|
vals.append(sid)
|
|
conn = get_conn()
|
|
conn.execute(f"UPDATE sources SET {','.join(sets)} WHERE id=?", vals)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def delete_source(sid):
|
|
conn = get_conn()
|
|
conn.execute("DELETE FROM sources WHERE id=?", (sid,))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def update_source_fetch(sid, status="ok", count=0):
|
|
conn = get_conn()
|
|
conn.execute(
|
|
"UPDATE sources SET status=?, last_fetch=?, last_count=? WHERE id=?",
|
|
(status, now_str(), count, sid),
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
# ---------------- articles ----------------
|
|
def article_exists(url):
|
|
conn = get_conn()
|
|
row = conn.execute("SELECT id FROM articles WHERE url=?", (url,)).fetchone()
|
|
conn.close()
|
|
return row is not None
|
|
|
|
|
|
def add_article(a):
|
|
conn = get_conn()
|
|
cur = conn.execute(
|
|
"""INSERT INTO articles(source_id,title,url,author,content,summary,domain,entities,
|
|
importance,relevance,total_score,is_important,analysis,llm_status,status,
|
|
published_at,collected_at)
|
|
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
|
|
(a.get("source_id", 0), a["title"], a.get("url", ""), a.get("author", ""),
|
|
a.get("content", ""), a.get("summary", ""), a.get("domain", ""),
|
|
json.dumps(a.get("entities", []), ensure_ascii=False),
|
|
a.get("importance", 0), a.get("relevance", 0), a.get("total_score", 0),
|
|
a.get("is_important", 0), a.get("analysis", ""), a.get("llm_status", "pending"),
|
|
a.get("status", "new"), a.get("published_at", ""), now_str()),
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.lastrowid
|
|
|
|
|
|
def update_article(aid, **fields):
|
|
allowed = {"summary", "domain", "entities", "importance", "relevance",
|
|
"total_score", "is_important", "analysis", "llm_status", "notified", "status"}
|
|
sets, vals = [], []
|
|
for k, v in fields.items():
|
|
if k in allowed:
|
|
if k == "entities":
|
|
v = json.dumps(v, ensure_ascii=False)
|
|
sets.append(f"{k}=?")
|
|
vals.append(v)
|
|
if not sets:
|
|
return
|
|
vals.append(aid)
|
|
conn = get_conn()
|
|
conn.execute(f"UPDATE articles SET {','.join(sets)} WHERE id=?", vals)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def get_article(aid):
|
|
conn = get_conn()
|
|
row = conn.execute(
|
|
"SELECT a.*, s.name AS source_name FROM articles a "
|
|
"LEFT JOIN sources s ON a.source_id=s.id WHERE a.id=?", (aid,)).fetchone()
|
|
conn.close()
|
|
if not row:
|
|
return None
|
|
d = dict(row)
|
|
try:
|
|
d["entities"] = json.loads(d["entities"] or "[]")
|
|
except Exception:
|
|
d["entities"] = []
|
|
return d
|
|
|
|
|
|
def list_articles(**filters):
|
|
"""支持 source_id/domain/is_important/llm_status/min_score/q/date_from/order/limit/offset"""
|
|
sql = ("SELECT a.*, s.name AS source_name FROM articles a "
|
|
"LEFT JOIN sources s ON a.source_id=s.id WHERE 1=1")
|
|
args = []
|
|
if filters.get("source_id"):
|
|
sql += " AND a.source_id=?"
|
|
args.append(filters["source_id"])
|
|
if filters.get("domain"):
|
|
sql += " AND a.domain=?"
|
|
args.append(filters["domain"])
|
|
if filters.get("is_important") is not None:
|
|
sql += " AND a.is_important=?"
|
|
args.append(1 if filters["is_important"] else 0)
|
|
if filters.get("llm_status"):
|
|
sql += " AND a.llm_status=?"
|
|
args.append(filters["llm_status"])
|
|
if filters.get("min_score"):
|
|
sql += " AND a.total_score>=?"
|
|
args.append(filters["min_score"])
|
|
if filters.get("q"):
|
|
sql += " AND (a.title LIKE ? OR a.content LIKE ?)"
|
|
args += [f"%{filters['q']}%", f"%{filters['q']}%"]
|
|
if filters.get("date_from"):
|
|
sql += " AND a.published_at>=?"
|
|
args.append(filters["date_from"])
|
|
order = filters.get("order", "a.collected_at DESC")
|
|
sql += f" ORDER BY {order}"
|
|
if filters.get("limit"):
|
|
sql += " LIMIT ?"
|
|
args.append(filters["limit"])
|
|
if filters.get("offset"):
|
|
sql += " OFFSET ?"
|
|
args.append(filters["offset"])
|
|
conn = get_conn()
|
|
rows = conn.execute(sql, args).fetchall()
|
|
conn.close()
|
|
out = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
try:
|
|
d["entities"] = json.loads(d["entities"] or "[]")
|
|
except Exception:
|
|
d["entities"] = []
|
|
out.append(d)
|
|
return out
|
|
|
|
|
|
def count_articles(**filters):
|
|
sql = "SELECT COUNT(*) c FROM articles a WHERE 1=1"
|
|
args = []
|
|
if filters.get("domain"):
|
|
sql += " AND a.domain=?"
|
|
args.append(filters["domain"])
|
|
if filters.get("is_important") is not None:
|
|
sql += " AND a.is_important=?"
|
|
args.append(1 if filters["is_important"] else 0)
|
|
conn = get_conn()
|
|
c = conn.execute(sql, args).fetchone()["c"]
|
|
conn.close()
|
|
return c
|
|
|
|
|
|
def article_stats():
|
|
conn = get_conn()
|
|
total = conn.execute("SELECT COUNT(*) c FROM articles").fetchone()["c"]
|
|
important = conn.execute("SELECT COUNT(*) c FROM articles WHERE is_important=1").fetchone()["c"]
|
|
pending_llm = conn.execute("SELECT COUNT(*) c FROM articles WHERE llm_status='pending'").fetchone()["c"]
|
|
notified = conn.execute("SELECT COUNT(*) c FROM articles WHERE notified=1").fetchone()["c"]
|
|
conn.close()
|
|
return {"total": total, "important": important, "pending_llm": pending_llm, "notified": notified}
|
|
|
|
|
|
def latest_articles_for_summary(window_hours=24):
|
|
"""汇总窗口内（含未通知、未汇总的）资讯"""
|
|
conn = get_conn()
|
|
rows = conn.execute(
|
|
"SELECT a.*, s.name AS source_name FROM articles a "
|
|
"LEFT JOIN sources s ON a.source_id=s.id "
|
|
"WHERE a.published_at >= datetime('now','localtime','-%d hours') "
|
|
"AND a.total_score>=50 ORDER BY a.total_score DESC LIMIT 200" % int(window_hours)
|
|
).fetchall()
|
|
conn.close()
|
|
out = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
try:
|
|
d["entities"] = json.loads(d["entities"] or "[]")
|
|
except Exception:
|
|
d["entities"] = []
|
|
out.append(d)
|
|
return out
|
|
|
|
|
|
def pending_llm_articles(limit=10):
|
|
conn = get_conn()
|
|
rows = conn.execute(
|
|
"SELECT a.*, s.name AS source_name FROM articles a "
|
|
"LEFT JOIN sources s ON a.source_id=s.id "
|
|
"WHERE a.llm_status='pending' ORDER BY a.total_score DESC LIMIT ?", (limit,)
|
|
).fetchall()
|
|
conn.close()
|
|
out = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
try:
|
|
d["entities"] = json.loads(d["entities"] or "[]")
|
|
except Exception:
|
|
d["entities"] = []
|
|
out.append(d)
|
|
return out
|
|
|
|
|
|
# ---------------- 兴趣画像 ----------------
|
|
def list_keywords():
|
|
conn = get_conn()
|
|
rows = conn.execute("SELECT * FROM keywords ORDER BY weight DESC").fetchall()
|
|
conn.close()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def add_keyword(kw, weight=5):
|
|
conn = get_conn()
|
|
conn.execute("INSERT OR IGNORE INTO keywords(keyword,weight) VALUES(?,?)", (kw, weight))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def delete_keyword(kid):
|
|
conn = get_conn()
|
|
conn.execute("DELETE FROM keywords WHERE id=?", (kid,))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def list_domains():
|
|
conn = get_conn()
|
|
rows = conn.execute("SELECT * FROM domains ORDER BY weight DESC").fetchall()
|
|
conn.close()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def add_domain(name, weight=5):
|
|
conn = get_conn()
|
|
conn.execute("INSERT OR IGNORE INTO domains(name,weight) VALUES(?,?)", (name, weight))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def delete_domain(did):
|
|
conn = get_conn()
|
|
conn.execute("DELETE FROM domains WHERE id=?", (did,))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def list_companies():
|
|
conn = get_conn()
|
|
rows = conn.execute("SELECT * FROM companies ORDER BY id").fetchall()
|
|
conn.close()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def add_company(name):
|
|
conn = get_conn()
|
|
conn.execute("INSERT OR IGNORE INTO companies(name) VALUES(?)", (name,))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def delete_company(cid):
|
|
conn = get_conn()
|
|
conn.execute("DELETE FROM companies WHERE id=?", (cid,))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
# ---------------- 通知日志 ----------------
|
|
def add_log(type_, subject, count, article_ids, status="ok", detail=""):
|
|
conn = get_conn()
|
|
conn.execute(
|
|
"INSERT INTO notification_log(type,subject,count,article_ids,status,detail,sent_at) "
|
|
"VALUES(?,?,?,?,?,?,?)",
|
|
(type_, subject, count, json.dumps(article_ids, ensure_ascii=False), status, detail, now_str()),
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def list_logs(limit=50):
|
|
conn = get_conn()
|
|
rows = conn.execute(
|
|
"SELECT * FROM notification_log ORDER BY id DESC LIMIT ?", (limit,)).fetchall()
|
|
conn.close()
|
|
out = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
try:
|
|
d["article_ids"] = json.loads(d["article_ids"] or "[]")
|
|
except Exception:
|
|
d["article_ids"] = []
|
|
out.append(d)
|
|
return out
|
|
|
|
|
|
def clear_old_articles(days=30):
|
|
conn = get_conn()
|
|
conn.execute(
|
|
"DELETE FROM articles WHERE collected_at < datetime('now','localtime','-%d days')" % days)
|
|
conn.commit()
|
|
conn.close()
|