📊 Sales Dashboard Agent

Core Source Code & Natural Language to Code Implementation

Python 3.8+ Gemini 2.0 Flash TinyDB Code-as-Plan

🔍 About This Code Showcase

This curated code snippet demonstrates how the Sales Dashboard Agent converts natural language business questions into executable Python analytics code using the "code-as-plan" pattern.

Full deployment scripts, API keys, and proprietary prompt engineering are omitted for security. This showcase highlights the core natural language processing, code generation, and safe execution sandbox.

📖 Core Algorithm: Natural Language to Code Engine

The foundation of the Sales Dashboard Agent is its ability to understand business questions and generate executable Python code dynamically:

📄 sales_dashboard_agent.py

import google.generativeai as genai
from tinydb import TinyDB, Query
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from typing import Dict, Any
import re

class SalesDashboardAgent:
    """
    Natural language to code AI agent for business analytics.
    Converts plain English questions into executable Python code
    using TinyDB for data access and Matplotlib for visualizations.
    """

    def __init__(self, gemini_api_key: str, db: TinyDB):
        genai.configure(api_key=gemini_api_key)
        self.model = genai.GenerativeModel('gemini-2.0-flash-exp')
        self.db = db
        self.orders_tbl = db.table('orders')

        # Get sample data for prompt context
        self.sample_records = self.orders_tbl.all()[:3]

        # Safe execution namespace (read-only operations only)
        self.safe_globals = {
            'Query': Query,
            'orders_tbl': self.orders_tbl,
            'sum': sum,
            'len': len,
            'max': max,
            'min': min,
            'sorted': sorted,
            'defaultdict': lambda: None,  # Import on demand
            'plt': plt,
            'sns': sns,
            'pd': pd
        }

    def sales_dashboard_agent(self, user_question: str) -> Dict[str, Any]:
        """
        Main agent function that processes natural language queries.

        Args:
            user_question: Business question in plain English
                          e.g., "What were total sales in November 2024?"

        Returns:
            Dictionary with answer, generated code, and visualization (if any)
        """

        print(f"📊 Processing question: {user_question}")

        # Step 1: Generate Python code from natural language
        generated_code = self._generate_analytics_code(user_question)

        print(f"\n🔧 Generated Code:\n{generated_code}\n")

        # Step 2: Execute code in sandboxed environment
        execution_result = self._execute_code_safely(generated_code)

        # Step 3: Extract answer and return results
        return {
            'question': user_question,
            'generated_code': generated_code,
            'answer': execution_result.get('answer_text', 'No answer generated'),
            'has_visualization': execution_result.get('has_chart', False),
            'success': execution_result.get('success', False)
        }
                

🧠 Code Generation Engine (Prompt Engineering)

The code generation engine uses carefully crafted prompts with data schema, examples, and constraints to guide Gemini AI:

📄 code_generator.py

def _generate_analytics_code(self, user_question: str) -> str:
    """
    Generate executable Python code from natural language question.
    Uses Gemini AI with structured prompt including schema and examples.
    """

    # Build comprehensive prompt with context
    prompt = f"""
You are a Python code generator for sales data analytics using TinyDB.

DATABASE SCHEMA:
Table: orders_tbl
Fields:
- Order_ID (int): Unique order identifier
- Order_Date (str): Date in YYYY-MM-DD format
- Customer_Name (str): Customer name
- City (str): Customer city
- State (str): Customer state
- Region (str): Geographic region (East, West, Centre, South)
- Country (str): Country (e.g., "United States")
- Category (str): Product category (Electronics, Clothing, Accessories, etc.)
- Sub_Category (str): Detailed product sub-category
- Product_Name (str): Specific product name
- Quantity (int): Number of units ordered
- Unit_Price (float): Price per unit in USD
- Revenue (float): Total revenue (Quantity × Unit_Price)
- Profit (float): Profit from order

SAMPLE DATA:
{self._format_sample_data(self.sample_records)}

USER QUESTION:
{user_question}

AVAILABLE TOOLS:
- TinyDB Query object: Query()
- orders_tbl: TinyDB table with search(), all(), get() methods
- Python builtins: sum(), len(), max(), min(), sorted()
- collections.defaultdict for aggregations
- matplotlib.pyplot (plt) for charts
- seaborn (sns) for enhanced visualizations

CODE GENERATION RULES:

1. QUERY PATTERNS:
   - Filter by date: Item.Order_Date >= '2024-11-01'
   - Filter by category: Item.Category == 'Electronics'
   - Multiple conditions: (Item.Region == 'West') & (Item.Revenue > 1000)

2. AGGREGATION PATTERNS:
   - Total revenue: sum(order['Revenue'] for order in orders_tbl.all())
   - Count orders: len(orders_tbl.all())
   - Group by category:
     from collections import defaultdict
     category_revenue = defaultdict(float)
     for order in orders_tbl.all():
         category_revenue[order['Category']] += order['Revenue']

3. VISUALIZATION (if requested):
   - Bar chart example:
     plt.figure(figsize=(10, 6))
     plt.bar(regions, revenues, color='steelblue')
     plt.title('Revenue by Region', fontsize=14, fontweight='bold')
     plt.xlabel('Region')
     plt.ylabel('Revenue ($)')
     plt.show()

4. MANDATORY OUTPUT:
   - ALWAYS set answer_text variable with human-friendly summary
   - Example: answer_text = f"Total revenue in Nov 2024: ${{revenue:,.2f}}"
   - Include relevant metrics and context

5. SAFETY CONSTRAINTS:
   - READ-ONLY operations only (no insert, update, delete)
   - No file system access
   - No network requests
   - No dangerous imports

GENERATE PYTHON CODE:
Write clean, efficient code that answers the question.
Remember to set answer_text with the final answer!
"""

    # Call Gemini to generate code
    response = self.model.generate_content(prompt)
    generated_code = self._extract_code_from_response(response.text)

    return generated_code

def _extract_code_from_response(self, response_text: str) -> str:
    """Extract Python code from markdown code blocks."""

    # Look for ```python code blocks
    code_pattern = r'```python\n(.*?)\n```'
    matches = re.findall(code_pattern, response_text, re.DOTALL)

    if matches:
        return matches[0].strip()

    # Fallback: return entire response if no code blocks found
    return response_text.strip()
                

🔒 Safe Code Execution Sandbox

The execution sandbox ensures code runs safely with read-only access and controlled namespace:

📄 safe_executor.py

def _execute_code_safely(self, code: str) -> Dict[str, Any]:
    """
    Execute generated code in sandboxed environment.

    Security features:
    - Controlled namespace (safe_globals only)
    - No dangerous imports
    - Read-only database access
    - Exception handling
    """

    # Prepare safe execution namespace
    exec_namespace = self.safe_globals.copy()

    # Add defaultdict if needed
    if 'defaultdict' in code:
        from collections import defaultdict
        exec_namespace['defaultdict'] = defaultdict

    # Initialize result variables
    exec_namespace['answer_text'] = "No answer generated"
    has_chart = False

    try:
        # Validate code safety before execution
        if self._is_code_safe(code):
            # Execute code in controlled namespace
            exec(code, exec_namespace)

            # Check if visualization was created
            if 'plt.show()' in code:
                has_chart = True

            return {
                'success': True,
                'answer_text': exec_namespace.get('answer_text'),
                'has_chart': has_chart
            }
        else:
            return {
                'success': False,
                'answer_text': "⚠️ Code contains unsafe operations",
                'has_chart': False
            }

    except Exception as e:
        print(f"❌ Execution error: {e}")
        return {
            'success': False,
            'answer_text': f"Error executing code: {str(e)}",
            'has_chart': False
        }

def _is_code_safe(self, code: str) -> bool:
    """
    Validate code for safety before execution.
    Blocks dangerous operations like file I/O, network calls, etc.
    """

    # Blocked operations (blacklist)
    dangerous_patterns = [
        'open(',           # File operations
        'import os',       # OS access
        'import sys',      # System access
        'import subprocess', # Shell commands
        'eval(',          # Dynamic code execution
        'exec(',          # Dynamic code execution
        '__import__',     # Dynamic imports
        '.insert(',       # Database mutations
        '.update(',       # Database mutations
        '.delete(',       # Database mutations
        'requests.',      # Network requests
        'urllib',         # Network requests
    ]

    for pattern in dangerous_patterns:
        if pattern in code:
            print(f"⚠️ Blocked dangerous operation: {pattern}")
            return False

    return True
                

📊 Example: Revenue Analysis Query

Here's a complete example showing how the agent processes a business question:

📄 example_query.py

# USER ASKS:
user_question = "What were our total sales in November 2024?"

# AGENT GENERATES THIS CODE:
generated_code = """
Item = Query()

# Filter orders for November 2024
nov_orders = orders_tbl.search(
    (Item.Order_Date >= '2024-11-01') &
    (Item.Order_Date <= '2024-11-30')
)

# Calculate total revenue
nov_revenue = sum(order['Revenue'] for order in nov_orders)
order_count = len(nov_orders)

# Format answer
answer_text = f"November 2024: ${nov_revenue:,.2f} from {order_count} orders."
"""

# CODE EXECUTES SAFELY:
result = agent.sales_dashboard_agent(user_question)

# OUTPUT:
print(result['answer'])
# "November 2024: $1,234,567.89 from 2,845 orders."
                

📈 Example: Visualization Request

When users request charts, the agent generates complete Matplotlib code:

📄 visualization_example.py

# USER ASKS:
user_question = "Show me a bar chart of total revenue by region"

# AGENT GENERATES THIS CODE:
generated_code = """
import matplotlib.pyplot as plt

# Aggregate revenue by region
region_revenue = {}
for order in orders_tbl.all():
    region = order['Region']
    revenue = order['Revenue']
    region_revenue[region] = region_revenue.get(region, 0) + revenue

# Prepare data for plotting
regions = list(region_revenue.keys())
revenues = list(region_revenue.values())

# Create bar chart
plt.figure(figsize=(10, 6))
plt.bar(regions, revenues, color=['#3B82F6', '#10B981', '#F59E0B', '#EF4444'])
plt.title('Total Revenue by Region', fontsize=14, fontweight='bold')
plt.xlabel('Region', fontsize=12)
plt.ylabel('Revenue ($)', fontsize=12)
plt.grid(axis='y', alpha=0.3)

# Format y-axis as currency
ax = plt.gca()
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:,.0f}'))

plt.tight_layout()
plt.show()

# Generate summary
max_region = max(region_revenue.items(), key=lambda x: x[1])
answer_text = f"West region leads with ${max_region[1]:,.2f} in total revenue."
"""

# CODE EXECUTES AND DISPLAYS CHART:
result = agent.sales_dashboard_agent(user_question)

# OUTPUT:
# - Bar chart displayed in notebook
# - Answer: "West region leads with $2,345,678.90 in total revenue."
                

⚙️ Technical Implementation Notes

Key Algorithms & Innovations

Code-as-Plan Pattern: AI generates executable code instead of just answers for full transparency
Prompt Engineering: Comprehensive prompts with schema, examples, and constraints guide code generation
Safe Execution Sandbox: Controlled namespace prevents dangerous operations (file I/O, network, mutations)
TinyDB Integration: In-memory document database enables fast analytics on order data
Auto-Visualization: Detects chart requests and generates complete Matplotlib code
Error Handling: Robust exception handling with user-friendly error messages

Why This Approach Works

Transparency: Users see exact code generated, can verify and learn from it
Flexibility: Handles arbitrary business questions without pre-defined queries
Fast Iteration: 1-3 seconds from question to answer with visualization
Safety First: Sandboxed execution prevents data corruption or security issues
Educational Value: Users learn Python and data analytics from generated code

Supported Query Types

Aggregations: Total revenue, profit, quantity, average order value
Filtering: By date range, region, category, customer, product
Grouping: Revenue by region/category/product/customer
Top N: Best-selling products, top customers, highest profit items
Calculations: Profit margins, growth rates, percentage comparisons
Visualizations: Bar charts, line charts, scatter plots, tables

Security Features

Blacklist Validation: Blocks dangerous operations before execution
Read-Only Database: No insert, update, or delete operations allowed
Controlled Imports: Only approved libraries (pandas, matplotlib, TinyDB)
No File Access: Cannot read/write files or access filesystem
No Network Access: Cannot make HTTP requests or external calls
Exception Isolation: Errors don't crash main application

🚀 Try Live Demo 📖 View Full Project Details