🔍 Code Extractor

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.

Purpose

This function serves as a demonstration and testing tool for the SmartStat SQL workflow system. It showcases the complete pipeline from loading a database schema (Poulpharm_labosoft.json) to generating SQL queries from natural language requests. The function demonstrates five example use cases including laboratory requests, customer statistics, bacteriology results, veterinarian information, and analysis groups. It's designed to validate the integration between natural language processing and SQL query generation before deployment in 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("Poulpharm_labosoft.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, including schema information, generated SQL queries, explanations, and metadata for each example query. The function may return early (None) if the schema fails to load.

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 Poulpharm_labosoft.json exists in current directory
# Set any required environment variables (e.g., OPENAI_API_KEY)

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("Poulpharm_labosoft.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",
        "Get customer statistics including number of requests and most common tests"
    ]
    
    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("")
            
            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")

if __name__ == "__main__":
    main()

Best Practices

• Ensure the Poulpharm_labosoft.json schema file exists and is valid before running this function
• Set up required API keys (likely OpenAI) as environment variables before execution
• This function is intended for demonstration and testing purposes, not production use
• The function uses exception handling to gracefully handle schema loading errors and query generation failures
• The max_rows parameter is set to 100 to limit result sets during demonstration
• Review the generated SQL queries before executing them against a production database
• The function prints extensive output with emojis for readability - consider redirecting output if logging to files
• Each example query demonstrates different aspects of the database schema (requests, statistics, results, contacts, analysis groups)

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function main_v61 92.4% similar

  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.

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

• function demonstrate_sql_workflow_v1 88.2% 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 demonstrate_sql_workflow 88.2% 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 demo_analysis_workflow 75.8% 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.3% 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