🔍 Code Extractor

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.

Purpose

This endpoint accepts SQL connection parameters and a query via POST request, creates a DataSource object configured for SQL queries, and loads the resulting data into an analysis session. It handles SQL-based data ingestion for statistical analysis workflows, supporting parameterized queries and returning success/error status.

Source Code

def load_sql_data(session_id):
    """Load data from SQL query"""
    try:
        data = request.get_json()
        
        # Create data source from SQL parameters
        data_source = DataSource(
            source_type=DataSourceType.SQL_QUERY,
            sql_connection=data.get('connection_string'),
            sql_query=data.get('query'),
            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 data: {str(e)}")
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

Parameters

Parameter Details

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

Return Value

Returns a Flask JSON response. On success: returns the result dictionary from analysis_service.load_data_for_session() with HTTP 200 status (typically includes 'success': True and data metadata). On failure: returns a JSON object with 'success': False and 'error': <error_message> with HTTP 500 status code.

Dependencies

• flask
• logging
• typing
• 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 (e.g., using requests library)
import requests
import json

session_id = 'abc123-session-id'
url = f'http://localhost:5000/sql_data/{session_id}'

payload = {
    'connection_string': 'postgresql://user:pass@localhost:5432/mydb',
    'query': 'SELECT * FROM sales WHERE region = :region',
    'parameters': {'region': 'North'}
}

response = requests.post(url, json=payload)
result = response.json()

if result.get('success'):
    print('Data loaded successfully')
else:
    print(f"Error: {result.get('error')}")

Best Practices

• Always validate the session_id exists before processing to prevent unauthorized data loading
• Sanitize SQL connection strings and queries to prevent SQL injection attacks
• Consider implementing rate limiting to prevent abuse of database connections
• Add authentication/authorization checks before allowing SQL data loading
• Validate that the connection_string and query are provided in the request body
• Consider adding timeout limits for SQL query execution to prevent long-running queries
• Log sensitive operations but avoid logging connection strings with credentials
• Implement connection pooling in the DataSource or analysis_service to manage database connections efficiently
• Consider adding query result size limits to prevent memory issues with large datasets
• Use environment variables or secure vaults for storing database credentials rather than accepting them directly in requests

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function load_sql_workflow_data 71.6% similar

  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.

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

• function enhanced_sql_workflow 67.9% 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 upload_data 66.9% similar

  Flask route handler that accepts file uploads via POST request, validates the file, saves it with a timestamp, and loads the data into an analysis session.

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

• function analyze_data 61.7% similar

  Flask route handler that initiates an asynchronous data analysis process based on user query, creating a background thread to perform the analysis and returning an analysis ID for progress tracking.

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

• function upload_analysis_dataset 60.9% similar

  Flask API endpoint that handles file upload for data analysis sessions, accepting CSV and Excel files, validating user access, and processing the dataset through a data analysis service.

  From: /tf/active/vicechatdev/vice_ai/new_app.py