Greg
37f8eac932
FastAPI application that ingests CFTC Commitments of Traders data into SQLite and exposes it via a REST API with analytics endpoints (screener, percentile rank, concentration). Includes CLI for historical and weekly data ingestion, Docker setup, and a frontend. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
63 lines
2.3 KiB
Python
63 lines
2.3 KiB
Python
from fastapi import APIRouter, HTTPException, Query
|
|
from typing import Optional
|
|
|
|
from app.db import get_db
|
|
from app.api.models import ReportDateInfo, ReportSnapshotRow
|
|
|
|
router = APIRouter(prefix="/api/reports", tags=["reports"])
|
|
|
|
|
|
@router.get("/dates", response_model=list[ReportDateInfo])
|
|
def get_report_dates(exchange: Optional[str] = Query(None)):
|
|
with get_db() as conn:
|
|
if exchange:
|
|
rows = conn.execute(
|
|
"""
|
|
SELECT r.report_date AS date, COUNT(DISTINCT r.commodity_id) AS commodity_count
|
|
FROM reports r
|
|
JOIN commodities c ON c.id = r.commodity_id
|
|
WHERE c.exchange_abbr = ?
|
|
GROUP BY r.report_date
|
|
ORDER BY r.report_date DESC
|
|
""",
|
|
(exchange,),
|
|
).fetchall()
|
|
else:
|
|
rows = conn.execute(
|
|
"""
|
|
SELECT report_date AS date, COUNT(DISTINCT commodity_id) AS commodity_count
|
|
FROM reports
|
|
GROUP BY report_date
|
|
ORDER BY report_date DESC
|
|
"""
|
|
).fetchall()
|
|
return [ReportDateInfo(**dict(r)) for r in rows]
|
|
|
|
|
|
@router.get("/{date}", response_model=list[ReportSnapshotRow])
|
|
def get_report_snapshot(date: str, exchange: Optional[str] = Query(None)):
|
|
with get_db() as conn:
|
|
sql = """
|
|
SELECT c.cftc_code, c.name AS commodity, c.exchange_abbr AS exchange,
|
|
p.open_interest,
|
|
(p.noncomm_long - p.noncomm_short) AS noncomm_net,
|
|
(p.comm_long - p.comm_short) AS comm_net,
|
|
p.pct_noncomm_long, p.pct_noncomm_short,
|
|
p.traders_total
|
|
FROM positions p
|
|
JOIN reports r ON r.id = p.report_id
|
|
JOIN commodities c ON c.id = r.commodity_id
|
|
WHERE r.report_date = ? AND p.row_type = 'All'
|
|
"""
|
|
params: list = [date]
|
|
if exchange:
|
|
sql += " AND c.exchange_abbr = ?"
|
|
params.append(exchange)
|
|
sql += " ORDER BY c.exchange_abbr, c.name"
|
|
|
|
rows = conn.execute(sql, params).fetchall()
|
|
|
|
if not rows:
|
|
raise HTTPException(status_code=404, detail=f"No data for date {date}")
|
|
return [ReportSnapshotRow(**dict(r)) for r in rows]
|