import os, sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import pandas as pd
from database import get_pg_engine
import os
os.environ["DATABASE_URL"] = "sqlite:///portfolio_db.sqlite3"

engine = get_pg_engine()
tickers = ['AAPL', 'MSFT', 'GOOGL', 'AMZN', 'NVDA', 'META', 'TSLA', 'JPM', 'GS', 'XOM', 'CVX', 'TLT', 'IEF', 'SHY', 'LQD', 'HYG', 'GLD', 'SLV', 'USO', 'SPY', 'QQQ', 'DIA', 'ES=F', 'NQ=F', 'GC=F', 'CL=F']
for t in tickers:
    df = pd.read_sql("SELECT COUNT(*), SUM(CASE WHEN close_price IS NULL THEN 1 ELSE 0 END) as nans FROM daily_prices WHERE ticker=?", engine, params=(t,))
    print(t, df.iloc[0].values)