fetch_api_data.py

import sqlite3
import requests
import pandas as pd

BASE_URL = "https://pocketportfolio.app/api/tickers"
TICKERS = ["FUN", "AAPL", "GOOGL", "AMZN", "MSFT", "TSLA", "NVDA"]
DB_PATH = "stocks.db"

def extract(ticker):
    url = f"{BASE_URL}/{ticker}/json"
    try:
        response = requests.get(url)
        response.raise_for_status()
        data = response.json()
        print(f"  [{ticker}] fetched {len(data.get('data', []))} rows")
        return data
    except requests.exceptions.RequestException as e:
        print(f"  [{ticker}] request failed: {e}")
        return {}

def transform(json_data):
    transformed = []
    stock_data = json_data.get("data", [])
    symbol = json_data.get("symbol", "UNKNOWN")
    for row in stock_data:
        transformed.append({
            "symbol": symbol,
            "date": row["date"],
            "open": row["open"],
            "high": row["high"],
            "low": row["low"],
            "close": row["close"],
            "volume": row["volume"]
        })
    return transformed

def load(all_rows):
    if not all_rows:
        print("No data to load.")
        return

    df = pd.DataFrame(all_rows)
    conn = sqlite3.connect(DB_PATH)

    df.to_sql("prices", conn, if_exists="replace", index=False)

    count = conn.execute("SELECT COUNT(*) FROM prices").fetchone()[0]
    conn.close()
    print(f"Done. {count} total rows in stocks.db")

def main():
    all_rows = []
    for ticker in TICKERS:
        print(f"Fetching {ticker}...")
        json_data = extract(ticker)
        rows = transform(json_data)
        all_rows.extend(rows)

    load(all_rows)

if __name__ == "__main__":
    main()