Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import pandas as pd | |
| from database import DatabaseManager | |
| from sqlalchemy import text | |
| import plotly.express as px | |
| import plotly.graph_objects as go | |
| from datetime import datetime, timedelta | |
| def database_console(): | |
| """Database console interface for viewing and managing stock data""" | |
| st.title("📊 Database Console - Stock Trading Data") | |
| st.markdown("### SQLite Database Management") | |
| # Show database file location | |
| st.info("Database file: stock_trading.db (persistent storage)") | |
| # Initialize database | |
| try: | |
| if 'console_db' not in st.session_state: | |
| from database import DatabaseManager | |
| st.session_state.console_db = DatabaseManager() | |
| db = st.session_state.console_db | |
| st.success("Database connection established") | |
| # Show database file size if it exists | |
| import os | |
| if os.path.exists('stock_trading.db'): | |
| size = os.path.getsize('stock_trading.db') | |
| st.metric("Database Size", f"{size / 1024:.1f} KB") | |
| except Exception as e: | |
| st.error(f"Database connection failed: {str(e)}") | |
| return | |
| # Sidebar navigation | |
| st.sidebar.header("Database Navigation") | |
| section = st.sidebar.selectbox( | |
| "Select Section:", | |
| ["Overview", "Stock Data", "Technical Indicators", "Trading Signals", "Model Metrics", "SQL Query"] | |
| ) | |
| if section == "Overview": | |
| show_database_overview(db) | |
| elif section == "Stock Data": | |
| show_stock_data(db) | |
| elif section == "Technical Indicators": | |
| show_technical_indicators(db) | |
| elif section == "Trading Signals": | |
| show_trading_signals(db) | |
| elif section == "Model Metrics": | |
| show_model_metrics(db) | |
| elif section == "SQL Query": | |
| show_sql_interface(db) | |
| def show_database_overview(db): | |
| """Show database overview and statistics""" | |
| st.header("Database Overview") | |
| col1, col2, col3, col4 = st.columns(4) | |
| try: | |
| # Get table counts | |
| stock_count = db.session.execute(text("SELECT COUNT(*) FROM stock_data")).scalar() | |
| indicator_count = db.session.execute(text("SELECT COUNT(*) FROM technical_indicators")).scalar() | |
| signal_count = db.session.execute(text("SELECT COUNT(*) FROM trading_signals")).scalar() | |
| model_count = db.session.execute(text("SELECT COUNT(*) FROM model_metrics")).scalar() | |
| with col1: | |
| st.metric("Stock Records", stock_count) | |
| with col2: | |
| st.metric("Technical Indicators", indicator_count) | |
| with col3: | |
| st.metric("Trading Signals", signal_count) | |
| with col4: | |
| st.metric("Model Records", model_count) | |
| except Exception as e: | |
| st.warning(f"Error getting counts: {str(e)}") | |
| # Initialize tables if they don't exist | |
| if st.button("Initialize Database Tables"): | |
| initialize_database_tables(db) | |
| # Recent activity | |
| st.subheader("Recent Activity") | |
| try: | |
| recent_signals = pd.read_sql(""" | |
| SELECT symbol, signal, confidence, live_price, created_at | |
| FROM trading_signals | |
| ORDER BY created_at DESC | |
| LIMIT 10 | |
| """, db.engine) | |
| if not recent_signals.empty: | |
| st.dataframe(recent_signals) | |
| else: | |
| st.info("No recent trading signals found") | |
| except Exception as e: | |
| st.info("No trading signals table found yet") | |
| def show_stock_data(db): | |
| """Show stock OHLCV data""" | |
| st.header("Stock Data (OHLCV)") | |
| # Stock selection | |
| symbol = st.selectbox( | |
| "Select Stock Symbol:", | |
| ["RELIANCE.NS", "TCS.NS", "INFY.NS", "HDFCBANK.NS", "ICICIBANK.NS"] | |
| ) | |
| try: | |
| # Get stock data | |
| query = """ | |
| SELECT * FROM stock_data | |
| WHERE symbol = :symbol | |
| ORDER BY date DESC | |
| LIMIT 100 | |
| """ | |
| stock_data = pd.read_sql(query, db.engine, params={"symbol": symbol}) | |
| if not stock_data.empty: | |
| st.dataframe(stock_data) | |
| # Create price chart | |
| fig = go.Figure() | |
| fig.add_trace(go.Candlestick( | |
| x=stock_data['date'], | |
| open=stock_data['open_price'], | |
| high=stock_data['high_price'], | |
| low=stock_data['low_price'], | |
| close=stock_data['close_price'], | |
| name=symbol | |
| )) | |
| fig.update_layout(title=f"{symbol} - Price Chart", xaxis_title="Date", yaxis_title="Price (₹)") | |
| st.plotly_chart(fig, use_container_width=True) | |
| else: | |
| st.info(f"No stock data found for {symbol}") | |
| if st.button(f"Load {symbol} Data"): | |
| load_sample_data(db, symbol) | |
| except Exception as e: | |
| st.error(f"Error loading stock data: {str(e)}") | |
| def show_technical_indicators(db): | |
| """Show technical indicators data""" | |
| st.header("Technical Indicators") | |
| symbol = st.selectbox( | |
| "Select Stock:", | |
| ["RELIANCE.NS", "TCS.NS", "INFY.NS", "HDFCBANK.NS", "ICICIBANK.NS"], | |
| key="tech_symbol" | |
| ) | |
| try: | |
| query = """ | |
| SELECT * FROM technical_indicators | |
| WHERE symbol = :symbol | |
| ORDER BY date DESC | |
| LIMIT 50 | |
| """ | |
| tech_data = pd.read_sql(query, db.engine, params={"symbol": symbol}) | |
| if not tech_data.empty: | |
| st.dataframe(tech_data) | |
| # Create indicators chart | |
| col1, col2 = st.columns(2) | |
| with col1: | |
| if 'rsi' in tech_data.columns: | |
| fig_rsi = px.line(tech_data, x='date', y='rsi', title='RSI') | |
| st.plotly_chart(fig_rsi, use_container_width=True) | |
| with col2: | |
| if 'macd' in tech_data.columns: | |
| fig_macd = px.line(tech_data, x='date', y='macd', title='MACD') | |
| st.plotly_chart(fig_macd, use_container_width=True) | |
| else: | |
| st.info(f"No technical indicators found for {symbol}") | |
| except Exception as e: | |
| st.error(f"Error loading technical indicators: {str(e)}") | |
| def show_trading_signals(db): | |
| """Show trading signals""" | |
| st.header("Trading Signals") | |
| try: | |
| # Get all signals | |
| signals_data = pd.read_sql(""" | |
| SELECT * FROM trading_signals | |
| ORDER BY signal_date DESC | |
| LIMIT 100 | |
| """, db.engine) | |
| if not signals_data.empty: | |
| st.dataframe(signals_data) | |
| # Signal distribution | |
| col1, col2 = st.columns(2) | |
| with col1: | |
| signal_counts = signals_data['signal'].value_counts() | |
| fig_pie = px.pie(values=signal_counts.values, names=signal_counts.index, | |
| title="Signal Distribution") | |
| st.plotly_chart(fig_pie, use_container_width=True) | |
| with col2: | |
| fig_conf = px.histogram(signals_data, x='confidence', title="Confidence Distribution") | |
| st.plotly_chart(fig_conf, use_container_width=True) | |
| else: | |
| st.info("No trading signals found") | |
| except Exception as e: | |
| st.error(f"Error loading trading signals: {str(e)}") | |
| def show_model_metrics(db): | |
| """Show model performance metrics""" | |
| st.header("Model Performance Metrics") | |
| try: | |
| metrics_data = pd.read_sql(""" | |
| SELECT * FROM model_metrics | |
| ORDER BY training_date DESC | |
| """, db.engine) | |
| if not metrics_data.empty: | |
| st.dataframe(metrics_data) | |
| # Performance charts | |
| col1, col2 = st.columns(2) | |
| with col1: | |
| fig_acc = px.bar(metrics_data, x='symbol', y='accuracy', | |
| title="Model Accuracy by Symbol") | |
| st.plotly_chart(fig_acc, use_container_width=True) | |
| with col2: | |
| fig_prec = px.bar(metrics_data, x='symbol', y='precision_score', | |
| title="Model Precision by Symbol") | |
| st.plotly_chart(fig_prec, use_container_width=True) | |
| else: | |
| st.info("No model metrics found") | |
| except Exception as e: | |
| st.error(f"Error loading model metrics: {str(e)}") | |
| def show_sql_interface(db): | |
| """SQL query interface""" | |
| st.header("SQL Query Interface") | |
| # Predefined queries | |
| st.subheader("Quick Queries") | |
| col1, col2, col3 = st.columns(3) | |
| with col1: | |
| if st.button("Show All Tables"): | |
| try: | |
| tables = db.engine.execute(text("SELECT name FROM sqlite_master WHERE type='table'")).fetchall() | |
| st.write("Tables:", [table[0] for table in tables]) | |
| except Exception as e: | |
| st.error(f"Error: {str(e)}") | |
| with col2: | |
| if st.button("Latest Signals"): | |
| try: | |
| result = pd.read_sql("SELECT * FROM trading_signals ORDER BY created_at DESC LIMIT 5", db.engine) | |
| st.dataframe(result) | |
| except Exception as e: | |
| st.error(f"Error: {str(e)}") | |
| with col3: | |
| if st.button("Stock Summary"): | |
| try: | |
| result = pd.read_sql(""" | |
| SELECT symbol, COUNT(*) as records, | |
| MIN(date) as first_date, MAX(date) as last_date | |
| FROM stock_data | |
| GROUP BY symbol | |
| """, db.engine) | |
| st.dataframe(result) | |
| except Exception as e: | |
| st.error(f"Error: {str(e)}") | |
| # Custom SQL query | |
| st.subheader("Custom SQL Query") | |
| sql_query = st.text_area( | |
| "Enter SQL Query:", | |
| value="SELECT * FROM trading_signals LIMIT 10;", | |
| height=100 | |
| ) | |
| if st.button("Execute Query"): | |
| try: | |
| if sql_query.strip(): | |
| result = pd.read_sql(sql_query, db.engine) | |
| st.dataframe(result) | |
| st.success(f"Query executed successfully. {len(result)} rows returned.") | |
| except Exception as e: | |
| st.error(f"Query error: {str(e)}") | |
| def initialize_database_tables(db): | |
| """Initialize database tables with sample data""" | |
| try: | |
| from database import Base | |
| Base.metadata.create_all(db.engine) | |
| st.success("Database tables initialized successfully") | |
| st.rerun() | |
| except Exception as e: | |
| st.error(f"Error initializing tables: {str(e)}") | |
| def load_sample_data(db, symbol): | |
| """Load sample data for testing""" | |
| try: | |
| import yfinance as yf | |
| from technical_indicators import TechnicalIndicators | |
| # Download stock data | |
| ticker = yf.Ticker(symbol) | |
| data = ticker.history(period="1mo") | |
| if not data.empty: | |
| # Save stock data | |
| db.save_stock_data(symbol, data) | |
| # Calculate and save technical indicators | |
| tech = TechnicalIndicators() | |
| data_with_indicators = tech.calculate_all_indicators(data) | |
| db.save_technical_indicators(symbol, data_with_indicators) | |
| st.success(f"Sample data loaded for {symbol}") | |
| st.rerun() | |
| else: | |
| st.error(f"Could not fetch data for {symbol}") | |
| except Exception as e: | |
| st.error(f"Error loading sample data: {str(e)}") | |
| if __name__ == "__main__": | |
| database_console() |