Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import pandas as pd | |
| import sqlite3 | |
| import plotly.express as px | |
| import plotly.graph_objects as go | |
| import os | |
| def main(): | |
| st.set_page_config(page_title="Stock Trading Database Console", layout="wide") | |
| st.title("📊 Stock Trading Database Console") | |
| st.markdown("### SQLite Database Management & Analytics") | |
| # Database file info | |
| db_file = "stock_trading.db" | |
| if os.path.exists(db_file): | |
| size = os.path.getsize(db_file) | |
| st.success(f"Database: {db_file} ({size/1024:.1f} KB)") | |
| else: | |
| st.warning(f"Database file {db_file} not found. Data will be created when you use the main app.") | |
| return | |
| # Connect to database | |
| try: | |
| conn = sqlite3.connect(db_file) | |
| st.success("Database connection established") | |
| 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", "Tables", "Stock Data", "Trading Signals", "Model Metrics", "SQL Query"] | |
| ) | |
| if section == "Overview": | |
| show_overview(conn) | |
| elif section == "Tables": | |
| show_tables(conn) | |
| elif section == "Stock Data": | |
| show_stock_data(conn) | |
| elif section == "Trading Signals": | |
| show_trading_signals(conn) | |
| elif section == "Model Metrics": | |
| show_model_metrics(conn) | |
| elif section == "SQL Query": | |
| show_sql_interface(conn) | |
| conn.close() | |
| def show_overview(conn): | |
| """Show database overview""" | |
| st.header("Database Overview") | |
| # Get table information | |
| tables_query = "SELECT name FROM sqlite_master WHERE type='table'" | |
| tables = pd.read_sql(tables_query, conn) | |
| if not tables.empty: | |
| st.subheader("Available Tables") | |
| st.dataframe(tables) | |
| # Table statistics | |
| col1, col2, col3, col4 = st.columns(4) | |
| try: | |
| stock_count = pd.read_sql("SELECT COUNT(*) as count FROM stock_data", conn).iloc[0]['count'] | |
| with col1: | |
| st.metric("Stock Records", stock_count) | |
| except: | |
| with col1: | |
| st.metric("Stock Records", 0) | |
| try: | |
| signals_count = pd.read_sql("SELECT COUNT(*) as count FROM trading_signals", conn).iloc[0]['count'] | |
| with col2: | |
| st.metric("Trading Signals", signals_count) | |
| except: | |
| with col2: | |
| st.metric("Trading Signals", 0) | |
| try: | |
| indicators_count = pd.read_sql("SELECT COUNT(*) as count FROM technical_indicators", conn).iloc[0]['count'] | |
| with col3: | |
| st.metric("Technical Indicators", indicators_count) | |
| except: | |
| with col3: | |
| st.metric("Technical Indicators", 0) | |
| try: | |
| models_count = pd.read_sql("SELECT COUNT(*) as count FROM model_metrics", conn).iloc[0]['count'] | |
| with col4: | |
| st.metric("Model Metrics", models_count) | |
| except: | |
| with col4: | |
| st.metric("Model Metrics", 0) | |
| else: | |
| st.info("No tables found. Use the main app to generate data.") | |
| def show_tables(conn): | |
| """Show table schemas""" | |
| st.header("Table Schemas") | |
| # Get all tables | |
| tables_query = "SELECT name FROM sqlite_master WHERE type='table'" | |
| tables = pd.read_sql(tables_query, conn) | |
| for table_name in tables['name']: | |
| st.subheader(f"Table: {table_name}") | |
| # Get table schema | |
| schema_query = f"PRAGMA table_info({table_name})" | |
| schema = pd.read_sql(schema_query, conn) | |
| st.dataframe(schema) | |
| # Get sample data | |
| try: | |
| sample_query = f"SELECT * FROM {table_name} LIMIT 3" | |
| sample = pd.read_sql(sample_query, conn) | |
| if not sample.empty: | |
| st.write("Sample Data:") | |
| st.dataframe(sample) | |
| except: | |
| st.write("No sample data available") | |
| st.markdown("---") | |
| def show_stock_data(conn): | |
| """Show stock OHLCV data""" | |
| st.header("Stock Data Analysis") | |
| try: | |
| # Get unique symbols | |
| symbols_query = "SELECT DISTINCT symbol FROM stock_data ORDER BY symbol" | |
| symbols = pd.read_sql(symbols_query, conn) | |
| if not symbols.empty: | |
| selected_symbol = st.selectbox("Select Stock Symbol:", symbols['symbol'].tolist()) | |
| # Get data for selected symbol | |
| data_query = f""" | |
| SELECT * FROM stock_data | |
| WHERE symbol = '{selected_symbol}' | |
| ORDER BY date DESC | |
| LIMIT 100 | |
| """ | |
| stock_data = pd.read_sql(data_query, conn) | |
| if not stock_data.empty: | |
| st.dataframe(stock_data) | |
| # Create candlestick chart | |
| fig = go.Figure(data=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'] | |
| )) | |
| fig.update_layout(title=f"{selected_symbol} - Candlestick Chart") | |
| st.plotly_chart(fig, use_container_width=True) | |
| # Volume chart | |
| fig_vol = px.bar(stock_data, x='date', y='volume', title=f"{selected_symbol} - Volume") | |
| st.plotly_chart(fig_vol, use_container_width=True) | |
| else: | |
| st.info(f"No data found for {selected_symbol}") | |
| else: | |
| st.info("No stock data available. Train a model in the main app to generate data.") | |
| except Exception as e: | |
| st.error(f"Error loading stock data: {str(e)}") | |
| def show_trading_signals(conn): | |
| """Show trading signals""" | |
| st.header("Trading Signals Analysis") | |
| try: | |
| signals_query = "SELECT * FROM trading_signals ORDER BY signal_date DESC LIMIT 100" | |
| signals = pd.read_sql(signals_query, conn) | |
| if not signals.empty: | |
| st.dataframe(signals) | |
| # Signal distribution | |
| col1, col2 = st.columns(2) | |
| with col1: | |
| signal_counts = signals['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, x='confidence', title="Confidence Distribution") | |
| st.plotly_chart(fig_conf, use_container_width=True) | |
| # Signals by symbol | |
| if 'symbol' in signals.columns: | |
| symbol_signals = signals.groupby('symbol')['signal'].count().reset_index() | |
| fig_bar = px.bar(symbol_signals, x='symbol', y='signal', title="Signals by Symbol") | |
| st.plotly_chart(fig_bar, use_container_width=True) | |
| else: | |
| st.info("No trading signals found. Generate signals in the main app.") | |
| except Exception as e: | |
| st.error(f"Error loading trading signals: {str(e)}") | |
| def show_model_metrics(conn): | |
| """Show model performance metrics""" | |
| st.header("Model Performance Metrics") | |
| try: | |
| metrics_query = "SELECT * FROM model_metrics ORDER BY training_date DESC" | |
| metrics = pd.read_sql(metrics_query, conn) | |
| if not metrics.empty: | |
| st.dataframe(metrics) | |
| # Performance charts | |
| col1, col2 = st.columns(2) | |
| with col1: | |
| fig_acc = px.bar(metrics, x='symbol', y='accuracy', title="Model Accuracy") | |
| st.plotly_chart(fig_acc, use_container_width=True) | |
| with col2: | |
| fig_prec = px.bar(metrics, x='symbol', y='precision_score', title="Model Precision") | |
| st.plotly_chart(fig_prec, use_container_width=True) | |
| else: | |
| st.info("No model metrics found. Train models in the main app.") | |
| except Exception as e: | |
| st.error(f"Error loading model metrics: {str(e)}") | |
| def show_sql_interface(conn): | |
| """SQL query interface""" | |
| st.header("SQL Query Interface") | |
| # Quick queries | |
| st.subheader("Quick Queries") | |
| col1, col2, col3 = st.columns(3) | |
| with col1: | |
| if st.button("Show All Tables"): | |
| query = "SELECT name FROM sqlite_master WHERE type='table'" | |
| result = pd.read_sql(query, conn) | |
| st.dataframe(result) | |
| with col2: | |
| if st.button("Latest Signals"): | |
| try: | |
| query = "SELECT * FROM trading_signals ORDER BY created_at DESC LIMIT 10" | |
| result = pd.read_sql(query, conn) | |
| st.dataframe(result) | |
| except Exception as e: | |
| st.error(f"Error: {str(e)}") | |
| with col3: | |
| if st.button("Stock Summary"): | |
| try: | |
| query = """ | |
| SELECT symbol, COUNT(*) as records, | |
| MIN(date) as first_date, MAX(date) as last_date | |
| FROM stock_data | |
| GROUP BY symbol | |
| """ | |
| result = pd.read_sql(query, conn) | |
| st.dataframe(result) | |
| except Exception as e: | |
| st.error(f"Error: {str(e)}") | |
| # Custom SQL | |
| st.subheader("Custom SQL Query") | |
| sql_query = st.text_area( | |
| "Enter SQL Query:", | |
| value="SELECT * FROM sqlite_master WHERE type='table';", | |
| height=100 | |
| ) | |
| if st.button("Execute Query"): | |
| try: | |
| if sql_query.strip(): | |
| result = pd.read_sql(sql_query, conn) | |
| st.dataframe(result) | |
| st.success(f"Query executed successfully. {len(result)} rows returned.") | |
| except Exception as e: | |
| st.error(f"Query error: {str(e)}") | |
| if __name__ == "__main__": | |
| main() |