import sqlalchemy as sa from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime, Text, Boolean, text from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from datetime import datetime import json import pandas as pd Base = declarative_base() class StockData(Base): __tablename__ = 'stock_data' id = Column(Integer, primary_key=True) symbol = Column(String(20), nullable=False) date = Column(DateTime, nullable=False) open_price = Column(Float) high_price = Column(Float) low_price = Column(Float) close_price = Column(Float) volume = Column(Integer) created_at = Column(DateTime, default=datetime.now) class TechnicalIndicators(Base): __tablename__ = 'technical_indicators' id = Column(Integer, primary_key=True) symbol = Column(String(20), nullable=False) date = Column(DateTime, nullable=False) rsi = Column(Float) macd = Column(Float) macd_signal = Column(Float) bb_upper = Column(Float) bb_middle = Column(Float) bb_lower = Column(Float) ema_20 = Column(Float) sma_20 = Column(Float) sma_50 = Column(Float) stoch_k = Column(Float) williams_r = Column(Float) atr = Column(Float) created_at = Column(DateTime, default=datetime.now) class TradingSignals(Base): __tablename__ = 'trading_signals' id = Column(Integer, primary_key=True) symbol = Column(String(20), nullable=False) signal_date = Column(DateTime, nullable=False) signal = Column(String(10)) # BUY, SELL, HOLD confidence = Column(Float) live_price = Column(Float) predicted_return = Column(Float) model_version = Column(String(50)) created_at = Column(DateTime, default=datetime.now) class ModelMetrics(Base): __tablename__ = 'model_metrics' id = Column(Integer, primary_key=True) symbol = Column(String(20), nullable=False) model_type = Column(String(50)) accuracy = Column(Float) precision_score = Column(Float) recall_score = Column(Float) training_date = Column(DateTime, default=datetime.now) feature_count = Column(Integer) training_samples = Column(Integer) is_active = Column(Boolean, default=True) class DatabaseManager: def __init__(self): # SQLite persistent database for better data retention self.engine = create_engine('sqlite:///stock_trading.db', echo=False) try: # Create all tables Base.metadata.create_all(self.engine) Session = sessionmaker(bind=self.engine) self.session = Session() # Verify tables were created with self.engine.connect() as conn: result = conn.execute(text("SELECT name FROM sqlite_master WHERE type='table'")) tables = [row[0] for row in result] print(f"Database tables available: {tables}") except Exception as e: print(f"Database initialization error: {str(e)}") raise e def save_stock_data(self, symbol, data_df): """Save stock OHLCV data to database""" try: for index, row in data_df.iterrows(): stock_record = StockData( symbol=symbol, date=index, open_price=float(row['Open']), high_price=float(row['High']), low_price=float(row['Low']), close_price=float(row['Close']), volume=int(row['Volume']) ) self.session.merge(stock_record) self.session.commit() return True except Exception as e: self.session.rollback() print(f"Error saving stock data: {str(e)}") return False def save_technical_indicators(self, symbol, indicators_df): """Save technical indicators to database""" try: for index, row in indicators_df.iterrows(): indicator_record = TechnicalIndicators( symbol=symbol, date=index, rsi=float(row.get('RSI', 0)) if pd.notna(row.get('RSI', 0)) else None, macd=float(row.get('MACD', 0)) if pd.notna(row.get('MACD', 0)) else None, macd_signal=float(row.get('MACD_signal', 0)) if pd.notna(row.get('MACD_signal', 0)) else None, bb_upper=float(row.get('BB_upper', 0)) if pd.notna(row.get('BB_upper', 0)) else None, bb_middle=float(row.get('BB_middle', 0)) if pd.notna(row.get('BB_middle', 0)) else None, bb_lower=float(row.get('BB_lower', 0)) if pd.notna(row.get('BB_lower', 0)) else None, ema_20=float(row.get('EMA_20', 0)) if pd.notna(row.get('EMA_20', 0)) else None, sma_20=float(row.get('SMA_20', 0)) if pd.notna(row.get('SMA_20', 0)) else None, sma_50=float(row.get('SMA_50', 0)) if pd.notna(row.get('SMA_50', 0)) else None, stoch_k=float(row.get('Stoch_K', 0)) if pd.notna(row.get('Stoch_K', 0)) else None, williams_r=float(row.get('Williams_R', 0)) if pd.notna(row.get('Williams_R', 0)) else None, atr=float(row.get('ATR', 0)) if pd.notna(row.get('ATR', 0)) else None ) self.session.merge(indicator_record) self.session.commit() return True except Exception as e: self.session.rollback() print(f"Error saving technical indicators: {str(e)}") return False def save_trading_signal(self, symbol, signal, confidence, live_price, predicted_return=None, model_version="v1.0"): """Save trading signal to database""" try: signal_record = TradingSignals( symbol=symbol, signal_date=datetime.now(), signal=signal, confidence=confidence, live_price=live_price, predicted_return=predicted_return, model_version=model_version ) self.session.add(signal_record) self.session.commit() return True except Exception as e: self.session.rollback() print(f"Error saving trading signal: {str(e)}") return False def save_model_metrics(self, symbol, model_type, accuracy, precision, recall, feature_count, training_samples): """Save model performance metrics to database""" try: # Deactivate old models for this symbol old_models = self.session.query(ModelMetrics).filter_by(symbol=symbol, is_active=True).all() for model in old_models: model.is_active = False # Add new model metrics metrics_record = ModelMetrics( symbol=symbol, model_type=model_type, accuracy=accuracy, precision_score=precision, recall_score=recall, feature_count=feature_count, training_samples=training_samples, is_active=True ) self.session.add(metrics_record) self.session.commit() return True except Exception as e: self.session.rollback() print(f"Error saving model metrics: {str(e)}") return False def get_latest_stock_data(self, symbol, limit=100): """Get latest stock data from database""" try: records = self.session.query(StockData).filter_by(symbol=symbol)\ .order_by(StockData.date.desc()).limit(limit).all() data = [] for record in records: data.append({ 'Date': record.date, 'Open': record.open_price, 'High': record.high_price, 'Low': record.low_price, 'Close': record.close_price, 'Volume': record.volume }) return pd.DataFrame(data).set_index('Date').sort_index() except Exception as e: print(f"Error getting stock data: {str(e)}") return pd.DataFrame() def get_trading_signals_history(self, symbol, limit=50): """Get trading signals history for a symbol""" try: records = self.session.query(TradingSignals).filter_by(symbol=symbol)\ .order_by(TradingSignals.signal_date.desc()).limit(limit).all() signals = [] for record in records: signals.append({ 'Date': record.signal_date, 'Signal': record.signal, 'Confidence': record.confidence, 'Live Price': record.live_price, 'Model Version': record.model_version }) return pd.DataFrame(signals) except Exception as e: print(f"Error getting trading signals: {str(e)}") return pd.DataFrame() def get_model_performance(self, symbol): """Get current model performance metrics""" try: record = self.session.query(ModelMetrics).filter_by(symbol=symbol, is_active=True).first() if record: return { 'model_type': record.model_type, 'accuracy': record.accuracy, 'precision': record.precision_score, 'recall': record.recall_score, 'training_date': record.training_date, 'feature_count': record.feature_count, 'training_samples': record.training_samples } return None except Exception as e: print(f"Error getting model performance: {str(e)}") return None def get_database_stats(self): """Get database statistics""" try: stats = { 'total_stock_records': self.session.query(StockData).count(), 'total_indicators': self.session.query(TechnicalIndicators).count(), 'total_signals': self.session.query(TradingSignals).count(), 'active_models': self.session.query(ModelMetrics).filter_by(is_active=True).count(), 'symbols_tracked': len(self.session.query(StockData.symbol).distinct().all()) } return stats except Exception as e: print(f"Error getting database stats: {str(e)}") return {} def close(self): """Close database connection""" self.session.close()