Kestrel Python API Server - Dynamic Script-Based Architecture

A C# Kestrel web server that serves as a completely dynamic API server. Instead of creating specific C# methods, it automatically discovers and executes Python scripts based on URL paths. The C# layer is completely agnostic and can execute any SQL command generated by Python scripts.

🏗️ Architecture Overview

HTTP Request → Dynamic Controller → Python Script Discovery → SQL Generation → Generic SQL Service → Database → Response

Key Features

• Zero C# Endpoint Methods: No specific methods needed in C# controllers
• Automatic Script Discovery: URL paths automatically map to Python scripts
• Dynamic Routing: /api/get-user/3 automatically executes get-user.py with parameter 3
• Flexible Input: Supports URL parameters, JSON body, and form data
• SQL Generation: Python scripts generate SQL commands dynamically
• Generic Execution: C# service executes any SQL command with parameters
• Extensible: Add new endpoints by simply creating new Python scripts

How It Works

1. URL to Script Mapping

The system automatically maps URL paths to Python scripts:

URL Path	Python Script	Example
/api/get-users	get-users.py	Get all users
/api/get-user/123	get-user.py	Get user with ID 123
/api/create-user	create-user.py	Create new user
/api/update-user/456	update-user.py	Update user with ID 456
/api/delete-user/789	delete-user.py	Delete user with ID 789
/api/search-users	search-users.py	Search users by query
/api/user-stats	user-stats.py	Get user statistics

2. Request Processing Flow

1. HTTP Request arrives at any URL under /api/
2. Dynamic Controller extracts path and HTTP method
3. Script Discovery converts path to Python filename (removes numeric parts)
4. Python Script receives request data (path, method, query, headers, body, path_params)
5. SQL Generation based on request data and parameters
6. C# SQL Service executes generated SQL with parameters
7. Response returned as JSON

3. Python Script Input Format

Each Python script receives a standardized request object:

{
  "path": "get-user/123",
  "method": "GET",
  "query": {"param1": "value1"},
  "headers": {"content-type": "application/json"},
  "body": {"field1": "value1"},
  "path_params": ["123"]
}

4. Standardized Response Format

All responses use a consistent 4-field JSON format:

{
  "error": "string | empty",
  "message": "string", 
  "data": "object | array | null",
  "timestamp": "ISO 8601 datetime"
}

Response Examples:

• Success: {"error": "", "message": "Users retrieved successfully", "data": [...], "timestamp": "2024-12-14T17:30:45.123Z"}
• Error: {"error": "Script not found", "message": "Script execution failed", "data": null, "timestamp": "2024-12-14T17:30:45.123Z"}

Project Structure

KestrelPythonApi/
├── Controllers/
│   └── DynamicController.cs     # Single dynamic controller (no specific methods)
├── Services/
│   ├── PythonService.cs         # IronPython integration service
│   └── SqlService.cs            # Generic SQL execution service
├── PythonScripts/              # Auto-discovered Python scripts
│   ├── get-users.py             # Auto-mapped from /api/get-users
│   ├── get-user.py              # Auto-mapped from /api/get-user/*
│   ├── create-user.py           # Auto-mapped from /api/create-user
│   ├── update-user.py           # Auto-mapped from /api/update-user/*
│   ├── delete-user.py           # Auto-mapped from /api/delete-user/*
│   ├── search-users.py          # Auto-mapped from /api/search-users
│   └── user-stats.py            # Auto-mapped from /api/user-stats
├── Program.cs                  # Application entry point
├── appsettings.json            # Configuration
└── README.md                   # This file

Usage Examples

1. Get All Users

curl -X GET "http://localhost:5000/api/get-users"

2. Get User by ID

curl -X GET "http://localhost:5000/api/get-user/123"

3. Create User (JSON)

curl -X POST "http://localhost:5000/api/create-user" \
	  -H "Content-Type: application/json" \
	  -d '{
	    "name": "John Doe",
	    "email": "john@example.com",
	    "phone": "123-456-7890"
	  }'

4. Create User (Form Data)

curl -X POST "http://localhost:5000/api/create-user" \
	  -F "name=Jane Smith" \
	  -F "email=jane@example.com" \
	  -F "phone=098-765-4321"

5. Update User

curl -X PUT "http://localhost:5000/api/update-user/1" \
	  -H "Content-Type: application/json" \
	  -d '{
	    "name": "John Updated",
	    "email": "john.updated@example.com",
	    "phone": "111-222-3333"
	  }'

6. Delete User

curl -X DELETE "http://localhost:5000/api/delete-user/1"

7. Search Users

curl -X GET "http://localhost:5000/api/search-users?name=john"
curl -X GET "http://localhost:5000/api/search-users?email=example.com"

8. Get User Statistics

curl -X GET "http://localhost:5000/api/user-stats"

Adding New Endpoints

Simple Steps

1. Create Python Script: Add new .py file to PythonScripts/ folder
2. Access Immediately: No C# changes needed - script is auto-discovered

Example: Add /api/hello-world endpoint

1. Create PythonScripts/hello-world.py:

import json
import sys

def process_request(request_data):
	return {
		"success": True,
		"data": {"message": "Hello from Python!"},
		"message": "Generated greeting"
	}

if __name__ == "__main__":
	request_data = json.loads(sys.argv[1]) if len(sys.argv) > 1 else {}
	result = process_request(request_data)
	print(json.dumps(result))

2. Use Immediately:

curl -X GET "http://localhost:5000/api/hello-world"

Python Script Development

Best Practices

1. Always use parameterized queries to prevent SQL injection
2. Extract path parameters from request_data['path_params'] array
3. Handle query strings via request_data['query'] dictionary
4. Read request body from request_data['body'] dictionary
5. Return structured response with success, sql, parameters fields
6. Include response format hints for proper HTTP responses

Script Template

import json
import sys

def process_request(request_data):
	"""
	Process request and generate SQL command
	"""
	try:
		# Extract data from request
		method = request_data.get('method', 'GET')
		path_params = request_data.get('path_params', [])
		query_params = request_data.get('query', {})
		body = request_data.get('body', {})
		
		# Your logic here
		sql = "SELECT * FROM table WHERE condition = @param"
		parameters = {"@param": "value"}
		
		return {
			"success": True,
			"sql": sql,
			"parameters": parameters,
			"operation": "SELECT",
			"message": "Generated SQL successfully"
		}
	except Exception as e:
		return {
			"success": False,
			"error": str(e),
			"message": "Failed to generate SQL"
		}

if __name__ == "__main__":
	# Read request data from command line argument
	if len(sys.argv) > 1:
		request_data = json.loads(sys.argv[1])
	else:
		request_data = {}
	
	result = process_request(request_data)
	print(json.dumps(result))

Advanced Features

Dynamic SQL Generation

Python scripts can generate complex SQL:

• Dynamic WHERE clauses based on input parameters
• JOIN operations across multiple tables
• Aggregation queries with GROUP BY and HAVING
• Subqueries and CTEs (Common Table Expressions)
• Conditional logic for different scenarios

Response Formatting

Python scripts can control HTTP responses:

• Default: Returns data with 200 OK
• "created": Returns 201 Created with Location header
• "no_content": Returns 204 No Content

Error Handling

• Input validation in Python before SQL generation
• SQL error handling in C# service layer
• HTTP status codes properly mapped to error types

Setup and Run

Prerequisites

• .NET 8.0 SDK
• IronPython packages (included in project)

Build and Run

# Navigate to project directory
cd KestrelPythonApi

# Restore dependencies
dotnet restore

# Build project
dotnet build

# Run application
dotnet run

The server will start on http://localhost:5000

Access Swagger UI

Open your browser and navigate to http://localhost:5000 to see interactive API documentation.

Configuration

The application can be configured via appsettings.json:

{
  "ConnectionStrings": {
	"DefaultConnection": "Data Source=app.db"
  },
  "PythonScripts": {
	"Path": "PythonScripts"
  },
  "Logging": {
	"LogLevel": {
	  "Default": "Information",
	  "Microsoft.AspNetCore": "Warning"
	}
  }
}

Security Features

• SQL Injection Prevention: All queries use parameterized statements
• Input Validation: Python scripts validate input before SQL generation
• Error Handling: Comprehensive error handling prevents information leakage
• CORS Configuration: Configurable CORS policies for cross-origin requests

Performance Considerations

• Script Discovery: Fast filename-based lookup, no scanning required
• Connection Management: Each SQL execution uses its own connection
• Parameter Binding: Efficient parameter binding prevents SQL parsing overhead
• Memory Management: Proper disposal of database connections and commands
• Async Operations: All database operations are asynchronous

Benefits of This Architecture

1. Zero C# Code for New Endpoints: Just add Python scripts
2. Maximum Flexibility: Python can generate any SQL logic
3. Rapid Development: No compilation needed for new endpoints
4. Dynamic Business Logic: Complex logic can be implemented in Python
5. Database Agnostic: Easy to switch database providers
6. Hot Deployment: Update Python scripts without restarting server
7. Separation of Concerns: C# handles infrastructure, Python handles business logic

This architecture provides the ultimate flexibility for API development while maintaining security, performance, and type safety through the generic C# SQL execution layer.