🔍 Code Extractor

function load_sql_workflow_data

Maturity: 55

Flask route handler that processes user natural language queries by leveraging an LLM to generate and execute SQL queries against a database, returning the results for a specific session.

File:
/tf/active/vicechatdev/full_smartstat/app.py
Lines:
193 - 230
Complexity:
moderate

Purpose

This endpoint implements a SQL workflow where users can submit natural language queries that are automatically converted to SQL by an LLM, executed against a configured database, and loaded into an analysis session. It supports optional schema file and connection configuration parameters, falling back to defaults when not provided. This enables non-technical users to query databases using natural language while maintaining session-based data management.

Source Code

def load_sql_workflow_data(session_id):
    """Load data using new SQL workflow: user query -> LLM generates SQL -> execute"""
    try:
        data = request.get_json()
        
        # Extract parameters
        user_query = data.get('user_query', '')
        schema_file = data.get('schema_file', '')  # Optional - will use default if empty
        connection_config = data.get('connection_config', '')  # Optional - will use default if empty
        
        if not user_query:
            return jsonify({
                'success': False,
                'error': 'User query is required'
            }), 400
        
        # Schema file and connection config are now optional (defaults will be used)
        
        # Create data source for SQL workflow
        data_source = DataSource(
            source_type=DataSourceType.SQL_WORKFLOW,
            user_query=user_query,
            schema_file=schema_file,
            connection_config=connection_config,
            parameters=data.get('parameters', {})
        )
        
        # Load data for session
        result = analysis_service.load_data_for_session(session_id, data_source)
        
        return jsonify(result)
        
    except Exception as e:
        logger.error(f"Error loading SQL workflow data: {str(e)}")
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

Parameters

Name Type Default Kind
session_id - - positional_or_keyword

Parameter Details

session_id: String identifier for the user's analysis session. This is extracted from the URL path and used to associate the loaded data with a specific session context. Must be a valid session ID that exists in the system.

Return Value

Returns a Flask JSON response tuple. On success (200): {'success': True, ...additional result data from analysis_service.load_data_for_session()}. On validation error (400): {'success': False, 'error': 'User query is required'}. On server error (500): {'success': False, 'error': <error message string>}. The response is a tuple of (jsonified_dict, http_status_code).

Dependencies

  • flask
  • logging
  • pathlib
  • pandas
  • werkzeug

Required Imports

from flask import Flask, request, jsonify
import logging
from models import DataSource, DataSourceType
from services import StatisticalAnalysisService

Usage Example

# Client-side usage example (JavaScript/Python requests)
import requests

# Prepare the request payload
payload = {
    'user_query': 'Show me all customers who made purchases in the last 30 days',
    'schema_file': 'path/to/schema.json',  # Optional
    'connection_config': 'path/to/db_config.json',  # Optional
    'parameters': {'limit': 100}  # Optional additional parameters
}

# Make POST request to the endpoint
response = requests.post(
    'http://localhost:5000/sql_workflow/abc123-session-id',
    json=payload,
    headers={'Content-Type': 'application/json'}
)

# Handle response
if response.status_code == 200:
    result = response.json()
    if result['success']:
        print('Data loaded successfully:', result)
    else:
        print('Error:', result['error'])
else:
    print('HTTP Error:', response.status_code, response.json())

Best Practices

  • Always validate that user_query is provided before processing, as it is the only required parameter
  • The function gracefully handles missing schema_file and connection_config by using system defaults
  • Proper error handling with try-except ensures all exceptions are caught and returned as JSON responses
  • HTTP status codes are correctly used: 400 for validation errors, 500 for server errors, 200 for success
  • The session_id should be validated to ensure it corresponds to an existing session before processing
  • Consider implementing rate limiting for this endpoint to prevent abuse of LLM resources
  • Log all errors for debugging and monitoring purposes using the logger instance
  • Ensure the analysis_service.load_data_for_session method properly handles SQL workflow data sources
  • The parameters dictionary allows for extensibility without changing the API contract
  • Consider adding authentication/authorization checks before processing sensitive database queries
  • Validate and sanitize user_query input to prevent potential injection attacks at the LLM level
  • Monitor LLM token usage and costs when processing user queries

Tags

flask api-endpoint sql-workflow llm-integration natural-language-query database data-loading session-management rest-api error-handling json-api

Similar Components

AI-powered semantic similarity - components with related functionality:

  • function enhanced_sql_workflow 77.3% similar

    Flask route handler that initiates an enhanced SQL workflow with iterative optimization, executing data extraction and analysis in a background thread while providing real-time progress tracking.

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

  • function load_sql_data 71.6% similar

    Flask route handler that loads data from a SQL database using a provided connection string and query, creating a data source for a specific analysis session.

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

  • function get_field_suggestions 62.9% similar

    Flask route handler that processes POST requests to generate field suggestions based on a user's natural language request by leveraging an enhanced SQL workflow system.

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

  • function main_v62 61.8% 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 60.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
← Back to Browse