Spaces:
Running
Running
File size: 3,593 Bytes
d961738 | 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 149 150 151 152 153 154 155 156 157 | from pathlib import Path
import sqlite3
from typing import Optional
import pandas as pd
from fastapi import FastAPI, Query
from fastapi.middleware.cors import CORSMiddleware
app = FastAPI(title="Green Energy News API", version="1.0.0")
# SQLite stays in the same repo position
BASE_DIR = Path(__file__).resolve().parent.parent
DB_PATH = BASE_DIR / "data" / "news.db"
app.add_middleware(
CORSMiddleware,
allow_origins=["*"], # tighten later if needed
allow_credentials=False,
allow_methods=["*"],
allow_headers=["*"],
)
def get_connection() -> sqlite3.Connection:
return sqlite3.connect(DB_PATH)
@app.get("/health")
def health():
return {
"status": "ok",
"db_exists": DB_PATH.exists(),
"db_path": str(DB_PATH),
}
@app.get("/labels")
def get_labels():
conn = get_connection()
query = """
SELECT DISTINCT label
FROM classified_articles
WHERE label IS NOT NULL
ORDER BY label
"""
df = pd.read_sql_query(query, conn)
conn.close()
return df["label"].dropna().tolist()
@app.get("/sources")
def get_sources():
conn = get_connection()
query = """
SELECT DISTINCT source
FROM classified_articles
WHERE source IS NOT NULL
ORDER BY source
"""
df = pd.read_sql_query(query, conn)
conn.close()
return df["source"].dropna().tolist()
@app.get("/summary/daily-actions")
def daily_actions(
start_date: Optional[str] = None,
end_date: Optional[str] = None,
):
conn = get_connection()
query = """
SELECT
date(published_at) AS day,
label,
COUNT(*) AS count
FROM classified_articles
WHERE 1=1
"""
params = []
if start_date:
query += " AND date(published_at) >= date(?)"
params.append(start_date)
if end_date:
query += " AND date(published_at) <= date(?)"
params.append(end_date)
query += """
GROUP BY date(published_at), label
ORDER BY day ASC, label ASC
"""
df = pd.read_sql_query(query, conn, params=params)
conn.close()
return df.to_dict(orient="records")
@app.get("/articles")
def get_articles(
label: Optional[str] = None,
source: Optional[str] = None,
start_date: Optional[str] = None,
end_date: Optional[str] = None,
search: Optional[str] = None,
limit: int = Query(50, ge=1, le=500),
offset: int = Query(0, ge=0),
):
conn = get_connection()
query = """
SELECT
article_id,
title,
description,
clean_text,
label,
raw_label,
source,
url,
published_at,
classified_at
FROM classified_articles
WHERE 1=1
"""
params = []
if label:
query += " AND label = ?"
params.append(label)
if source:
query += " AND source = ?"
params.append(source)
if start_date:
query += " AND date(published_at) >= date(?)"
params.append(start_date)
if end_date:
query += " AND date(published_at) <= date(?)"
params.append(end_date)
if search:
query += " AND (lower(title) LIKE ? OR lower(description) LIKE ?)"
pattern = f"%{search.lower()}%"
params.extend([pattern, pattern])
query += " ORDER BY published_at DESC LIMIT ? OFFSET ?"
params.extend([limit, offset])
df = pd.read_sql_query(query, conn, params=params)
conn.close()
return df.to_dict(orient="records") |