"""Loads parsed raw data into the test DB across relational tables: - fin_equity — Core equity profile and fundamental ratios - fin_equity_major_holders — Major Holders breakdown (4 raw metrics per ticker) - fin_equity_inst_holders — Top Institutional Holders (10 raw rows per ticker) - fin_equity_fund_holders — Top Mutual Fund Holders (10 raw rows per ticker) Pure data storage — no formatted strings or UI computations. """ import logging from typing import Any, Dict, List, Optional, Tuple from db import get_cursor logger = logging.getLogger(__name__) # Core fin_equity columns FIN_EQUITY_COLS = [ "comp_id", "comp_name", "ticker", "equity_type", "eq_exchange", "sector", "industry", "country", "currency", "cusip_no", "isin_no", "figi", "website", "fiscal_yr_end", "description", "employees", "shr_no", "market_cap", "last_pr", "curr_stat", "pe_ratio", "fwd_pe", "beta", "wk52_high", "wk52_low", "div_yield", "eps_ttm", "revenue", "profit_margin", "roe", "debt_to_eq", "free_cashflow", "target_price", "hld_inst_shr_pcnt", "inst_count", "hld_inst_float_pcnt", "insider_pct", "hld_ind_shr_pcnt", "hld_run_dt", "run_hld", "chk_stat", "chk_st_dt", "chk_lst_dt", ] # Major Holders Breakdown (4 raw metric rows) MAJOR_HOLDER_COLS = [ "idfin_equity", "ticker", "metric_key", "metric_label", "val_raw", "run_dt", ] # Top Institutional Holders (10 raw rows, no fmt, no rank) INST_HOLDER_COLS = [ "idfin_equity", "ticker", "holder_name", "shares_held", "report_dt", "pct_held", "value_usd", "pct_change", "run_dt", ] # Top Mutual Fund Holders (10 raw rows, no fmt, no rank) FUND_HOLDER_COLS = [ "idfin_equity", "ticker", "fund_name", "shares_held", "report_dt", "pct_held", "value_usd", "pct_change", "run_dt", ] # Insider Roster (key officers & directors) ROSTER_COLS = [ "idfin_equity", "ticker", "name", "relation", "position_direct", "latest_tx_date", "tx_desc", "position_direct_dt", "run_dt", ] # Insider Transactions (last 2 years, ceiling max 200) INSIDER_TX_COLS = [ "idfin_equity", "ticker", "filer_name", "filer_role", "tx_date", "tx_type", "shares", "value_usd", "ownership", "run_dt", ] # 6-Month Insider Activity & Net Institutional Summary INSIDER_SUMMARY_COLS = [ "idfin_equity", "ticker", "period", "buy_count", "buy_shares", "buy_pct_insider", "sell_count", "sell_shares", "sell_pct_insider", "net_count", "net_shares", "net_pct_insider", "net_inst_shares", "net_inst_pct", "total_insider_shares", "run_dt", ] def _upsert_fin_equity(cur, row: Dict[str, Any]) -> Tuple[str, Optional[int]]: """INSERT or UPDATE a single fin_equity row. Returns ('inserted'|'updated', idfin_equity).""" ticker = row.get("ticker", "?") comp_id = row.get("comp_id", ticker) cur.execute( "SELECT idfin_equity FROM fin_equity WHERE ticker = %s OR comp_id = %s LIMIT 1", (ticker, comp_id), ) existing = cur.fetchone() if existing: idfin = existing["idfin_equity"] update_cols = [c for c in FIN_EQUITY_COLS if c in row] set_clause = ", ".join(f"`{c}` = %s" for c in update_cols) values = [row[c] for c in update_cols] + [idfin] cur.execute(f"UPDATE fin_equity SET {set_clause} WHERE idfin_equity = %s", values) return "updated", idfin else: cols = [c for c in FIN_EQUITY_COLS if c in row] placeholders = ", ".join(["%s"] * len(cols)) col_names = ", ".join(f"`{c}`" for c in cols) values = [row[c] for c in cols] cur.execute( f"INSERT INTO fin_equity ({col_names}) VALUES ({placeholders})", values ) cur.execute( "SELECT idfin_equity FROM fin_equity WHERE ticker = %s OR comp_id = %s LIMIT 1", (ticker, comp_id), ) new_row = cur.fetchone() return "inserted", new_row["idfin_equity"] if new_row else None def _replace_holder_rows( cur, ticker: str, table: str, cols: List[str], rows: List[Dict], idfin_equity: Optional[int], ) -> int: """Delete existing rows for ticker then bulk insert new raw rows with idfin_equity FK.""" cur.execute(f"DELETE FROM `{table}` WHERE ticker = %s", (ticker,)) if not rows: return 0 col_names = ", ".join(f"`{c}`" for c in cols) placeholders = ", ".join(["%s"] * len(cols)) sql = f"INSERT INTO `{table}` ({col_names}) VALUES ({placeholders})" values_list = [] for row in rows: row["idfin_equity"] = idfin_equity values_list.append([row.get(c) for c in cols]) cur.executemany(sql, values_list) return len(rows) def upsert_all( fin_equity_rows: List[Dict], major_rows: List[Dict], inst_rows: List[Dict], fund_rows: List[Dict], roster_rows: List[Dict] = None, tx_rows: List[Dict] = None, summary_rows: List[Dict] = None, ) -> Dict[str, Any]: """Write all data across the 7 relational tables. Returns summary stats dict.""" roster_rows = roster_rows or [] tx_rows = tx_rows or [] summary_rows = summary_rows or [] stats = { "fin_equity": {"inserted": 0, "updated": 0, "failed": 0}, "major_holders": {"inserted": 0, "failed": 0}, "inst_holders": {"inserted": 0, "failed": 0}, "fund_holders": {"inserted": 0, "failed": 0}, "insider_roster": {"inserted": 0, "failed": 0}, "insider_tx": {"inserted": 0, "failed": 0}, "insider_summary": {"inserted": 0, "failed": 0}, } def _by_ticker(rows): d = {} for r in rows: d.setdefault(r["ticker"], []).append(r) return d major_by_ticker = _by_ticker(major_rows) inst_by_ticker = _by_ticker(inst_rows) fund_by_ticker = _by_ticker(fund_rows) roster_by_ticker = _by_ticker(roster_rows) tx_by_ticker = _by_ticker(tx_rows) summary_by_ticker = _by_ticker(summary_rows) with get_cursor(commit=True) as cur: for row in fin_equity_rows: ticker = row.get("ticker", "?") try: action, idfin = _upsert_fin_equity(cur, row) stats["fin_equity"][action] += 1 logger.info("[%s] fin_equity %s (id=%s).", ticker, action, idfin) except Exception as exc: logger.error("[%s] fin_equity error: %s", ticker, exc, exc_info=True) stats["fin_equity"]["failed"] += 1 continue # 1. Major Holders Breakdown (4 raw rows) try: n = _replace_holder_rows( cur, ticker, "fin_equity_major_holders", MAJOR_HOLDER_COLS, major_by_ticker.get(ticker, []), idfin, ) stats["major_holders"]["inserted"] += n logger.info("[%s] major_holders: %d rows.", ticker, n) except Exception as exc: logger.error("[%s] major_holders error: %s", ticker, exc, exc_info=True) stats["major_holders"]["failed"] += 1 # 2. Institutional Holders (10 raw rows) try: n = _replace_holder_rows( cur, ticker, "fin_equity_inst_holders", INST_HOLDER_COLS, inst_by_ticker.get(ticker, []), idfin, ) stats["inst_holders"]["inserted"] += n logger.info("[%s] inst_holders: %d rows.", ticker, n) except Exception as exc: logger.error("[%s] inst_holders error: %s", ticker, exc, exc_info=True) stats["inst_holders"]["failed"] += 1 # 3. Mutual Fund Holders (10 raw rows) try: n = _replace_holder_rows( cur, ticker, "fin_equity_fund_holders", FUND_HOLDER_COLS, fund_by_ticker.get(ticker, []), idfin, ) stats["fund_holders"]["inserted"] += n logger.info("[%s] fund_holders: %d rows.", ticker, n) except Exception as exc: logger.error("[%s] fund_holders error: %s", ticker, exc, exc_info=True) stats["fund_holders"]["failed"] += 1 # 4. Insider Roster (Directory of executives) try: n = _replace_holder_rows( cur, ticker, "fin_equity_insider_roster", ROSTER_COLS, roster_by_ticker.get(ticker, []), idfin, ) stats["insider_roster"]["inserted"] += n logger.info("[%s] insider_roster: %d rows.", ticker, n) except Exception as exc: logger.error("[%s] insider_roster error: %s", ticker, exc, exc_info=True) stats["insider_roster"]["failed"] += 1 # 5. Insider Transactions (Reported last 2 years, ceiling max 200) try: n = _replace_holder_rows( cur, ticker, "fin_equity_insider_tx", INSIDER_TX_COLS, tx_by_ticker.get(ticker, []), idfin, ) stats["insider_tx"]["inserted"] += n logger.info("[%s] insider_tx: %d rows.", ticker, n) except Exception as exc: logger.error("[%s] insider_tx error: %s", ticker, exc, exc_info=True) stats["insider_tx"]["failed"] += 1 # 6. 6-Month Insider Activity & Net Institutional Summary try: n = _replace_holder_rows( cur, ticker, "fin_equity_insider_summary", INSIDER_SUMMARY_COLS, summary_by_ticker.get(ticker, []), idfin, ) stats["insider_summary"]["inserted"] += n logger.info("[%s] insider_summary: %d rows.", ticker, n) except Exception as exc: logger.error("[%s] insider_summary error: %s", ticker, exc, exc_info=True) stats["insider_summary"]["failed"] += 1 return stats