🔍 Code Extractor

Demonstrates a SmartStat SQL workflow by loading a database schema, initializing a SQL query generator, and generating SQL queries from natural language requests with detailed output and metadata.

Purpose

This is a demonstration/testing function that showcases the complete workflow of the SmartStat SQL query generation system. It loads a database schema from a JSON file, creates a SQLQueryGenerator instance, and processes multiple example natural language queries to demonstrate how user requests are converted into SQL queries. The function provides detailed console output with emojis for visual clarity, showing the schema loading process, query generation results, explanations, and metadata for each example query. It serves as both a testing tool and documentation of the intended workflow for integration with the SmartStat Flask application.

Source Code

def main():
    print("🚀 SmartStat SQL Workflow Demonstration\n")
    
    # Load the database schema
    print("📋 Loading database schema...")
    try:
        schema = DatabaseSchema.from_json("database_schema_20251003_120434.json")
        print(f"✅ Loaded schema for: {schema.database_name}")
        print(f"   Description: {schema.description}")
        print(f"   Tables: {len(schema.complete_table_list)}")
        print()
    except Exception as e:
        print(f"❌ Error loading schema: {e}")
        return
    
    # Initialize the SQL query generator
    print("🔧 Initializing SQL query generator...")
    query_generator = SQLQueryGenerator(schema)
    print("✅ Query generator ready\n")
    
    # Example queries to demonstrate the workflow
    example_queries = [
        "Show me recent laboratory requests with sample information from the last month",
        "Get customer statistics including number of requests and most common tests",
        "Find bacteriology results with antibiotic sensitivity data",
        "List veterinarians and their associated practices with contact information",
        "Show analysis groups and their associated individual analyses"
    ]
    
    print("🧪 Generating SQL queries for example analysis requests:\n")
    
    for i, user_query in enumerate(example_queries, 1):
        print(f"📝 Example {i}: {user_query}")
        print("-" * 80)
        
        try:
            # Generate SQL query
            sql_query, metadata = query_generator.generate_sql_query(user_query, max_rows=100)
            
            print(f"💡 Explanation: {metadata['explanation']}")
            print("\n🔍 Generated SQL Query:")
            print("```sql")
            print(sql_query)
            print("```")
            
            print(f"\n📊 Metadata:")
            print(f"   Database: {metadata['database_name']}")
            print(f"   Max rows: {metadata['max_rows']}")
            print(f"   Generated at: {metadata['generated_at']}")
            
        except Exception as e:
            print(f"❌ Error generating query: {e}")
        
        print("\n" + "="*80 + "\n")
    
    print("🎯 Workflow Summary:")
    print("1. User provides natural language analysis request")
    print("2. AI analyzes request against database schema")
    print("3. Appropriate SQL query is generated")
    print("4. Query is executed to retrieve relevant data")
    print("5. Data continues through normal analysis pipeline")
    print("\n✨ Ready to integrate with SmartStat Flask application!")

Return Value

This function does not return any value (implicitly returns None). It performs side effects by printing demonstration output to the console. If the schema file cannot be loaded, the function returns early without processing queries.

Dependencies

• sql_query_generator

Required Imports

from sql_query_generator import SQLQueryGenerator
from sql_query_generator import DatabaseSchema
from sql_query_generator import ConnectionConfig

Usage Example

# Ensure the schema file exists in the current directory
# database_schema_20251003_120434.json

from sql_query_generator import SQLQueryGenerator, DatabaseSchema, ConnectionConfig

def main():
    print("🚀 SmartStat SQL Workflow Demonstration\n")
    
    print("📋 Loading database schema...")
    try:
        schema = DatabaseSchema.from_json("database_schema_20251003_120434.json")
        print(f"✅ Loaded schema for: {schema.database_name}")
        print(f"   Description: {schema.description}")
        print(f"   Tables: {len(schema.complete_table_list)}")
        print()
    except Exception as e:
        print(f"❌ Error loading schema: {e}")
        return
    
    print("🔧 Initializing SQL query generator...")
    query_generator = SQLQueryGenerator(schema)
    print("✅ Query generator ready\n")
    
    example_queries = [
        "Show me recent laboratory requests with sample information from the last month"
    ]
    
    print("🧪 Generating SQL queries for example analysis requests:\n")
    
    for i, user_query in enumerate(example_queries, 1):
        print(f"📝 Example {i}: {user_query}")
        print("-" * 80)
        
        try:
            sql_query, metadata = query_generator.generate_sql_query(user_query, max_rows=100)
            print(f"💡 Explanation: {metadata['explanation']}")
            print("\n🔍 Generated SQL Query:")
            print("sql")
            print(sql_query)
            print("")
        except Exception as e:
            print(f"❌ Error generating query: {e}")

if __name__ == "__main__":
    main()

Best Practices

• Ensure the database schema JSON file exists and is properly formatted before running this function
• The function expects a specific schema file name ('database_schema_20251003_120434.json') - modify this if using a different schema file
• This is a demonstration function intended for testing and documentation purposes, not for production use
• Error handling is implemented for schema loading and query generation, but errors are only printed to console
• The function uses a hardcoded list of example queries - customize these based on your specific use case
• The max_rows parameter is set to 100 for all queries - adjust this based on expected data volume
• Console output uses emoji characters for visual clarity - ensure your terminal supports UTF-8 encoding
• The function demonstrates the complete workflow but does not actually execute the generated SQL queries against a database

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function main_v61 92.4% similar

  Demonstrates the SmartStat SQL Workflow by loading a database schema, initializing a SQL query generator, and generating SQL queries from natural language requests for various laboratory data analysis scenarios.

  From: /tf/active/vicechatdev/smartstat/demo_sql_workflow.py

• function demonstrate_sql_workflow 84.7% similar

  Demonstrates the enhanced SQL workflow for the SmartStat system by loading configurations, initializing SQL query generator, testing natural language to SQL conversion, and displaying schema analysis.

  From: /tf/active/vicechatdev/smartstat/demo_enhanced_sql_workflow.py

• function demonstrate_sql_workflow_v1 83.9% similar

  Demonstrates the enhanced SQL workflow for the SmartStat system by loading configurations, initializing the SQL query generator, testing natural language to SQL conversion, and displaying schema analysis.

  From: /tf/active/vicechatdev/full_smartstat/demo_enhanced_sql_workflow.py

• function demo_analysis_workflow 71.7% similar

  Demonstrates a complete end-to-end statistical analysis workflow using the SmartStat system, including session creation, data loading, natural language query processing, analysis execution, and result interpretation.

  From: /tf/active/vicechatdev/full_smartstat/demo.py

• function demo_statistical_agent 64.8% similar

  Demonstrates the capabilities of a statistical agent by testing query interpretation on sample data with various statistical analysis queries.

  From: /tf/active/vicechatdev/full_smartstat/demo.py