Spaces:
Sleeping
Sleeping
| 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() |