MCP Data Analyst

MCP Data Analyst

A Model Context Protocol (MCP) server that enables natural language querying of SQL databases using AI. Connect your database and ask questions in plain English - the server will generate and execute SQL queries for you.

Features

• 🤖 Natural Language to SQL: Ask questions in plain English, get SQL results
• 🔌 Multiple Database Support: MySQL, PostgreSQL, MSSQL, MongoDB, SQLite, SSAS (MDX), Elasticsearch (SQL), InfluxDB (InfluxQL)
• 📊 Schema Auto-Discovery: Automatically scans and caches your database schema
• 🛠️ MCP Integration: Works seamlessly with MCP-compatible clients
• ⚡ Efficient: Connection pooling and schema caching for performance
• 🔒 Read-only by Design: Only SELECT-style queries are executed

Query Languages

• SQL: MySQL, PostgreSQL, MSSQL, SQLite, Elasticsearch (SQL API)
• MDX: SSAS
• InfluxQL: InfluxDB

Installation

Prerequisites

• Python 3.12 or higher
• One of: MySQL, PostgreSQL, MSSQL, MongoDB, SQLite, SSAS, Elasticsearch, or InfluxDB
• OpenAI API key (or compatible API endpoint)

Setup

1. Clone the repository:

   cd /path/to/your/workspace

1. Create a virtual environment (recommended):

   python3 -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate

1. Install dependencies:

   pip install -r requirements.txt

1. Configure environment variables:

Copy the example below and create a .env file: ```env

LLM Configuration

LLM_API_KEY=your-api-key-here LLM_MODEL=gpt-3.5-turbo LLM_API_URL=https://api.openai.com/v1

Database Configuration

DB_TYPE=mysql # mysql|postgresql|mssql|mongodb|sqlite|ssas|elasticsearch|influxdb DB_HOST=127.0.0.1 DB_PORT=3306 # 5432 (PostgreSQL), 1433 (MSSQL), 27017 (MongoDB), 2383 (SSAS), 9200 (Elasticsearch), 8086 (InfluxDB) DB_USER=root DB_PASSWORD=your-password DB_NAME=your-database-name # For InfluxDB: database name; for SSAS/Elasticsearch: catalog/index database name

SQLite only

DB_PATH=database.db

## Usage

### Running the MCP Server

Start the server using the standard MCP stdio transport:

python server.py ```

The server will:

1. Validate configuration
2. Connect to your database
3. Build a schema cache
4. Start listening for MCP requests

Available MCP Tools

The server exposes 3 tools that can be called by MCP clients:

1. query_database_with_prompt

Ask questions in natural language and get SQL results.

# Example: "Show me the top 5 customers by total purchases"
{
  "success": true,
  "query": "SELECT c.name, SUM(o.total) as total_purchases FROM customers c...",
  "data": [...]
}

2. get_database_schema

Retrieve the complete database schema.

{
  "success": true,
  "schema": {
    "users": {
      "name": "users",
      "columns": {...}
    }
  }
}

3. build_db_definition

Rebuild the schema cache from the database.

{
  "success": true,
  "message": "Successfully loaded schema for 8 tables",
  "tables": ["users", "orders", "products", ...]
}

Integration with MCP Clients

To use this server with an MCP client (like Claude Desktop), add it to your MCP configuration:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "data-analyst": {
      "command": "python",
      "args": ["/path/to/mcp-data-analyst/server.py"],
      "env": {
        "LLM_API_KEY": "your-key",
        "DB_TYPE": "mysql",
        "DB_HOST": "localhost",
        "DB_NAME": "your_db"
      }
    }
  }
}

Development

Adding a New Database Type

1. Create a new file in DataAnalyst/database/Type/ (e.g., SQLite.py)
2. Extend the BaseDatabase abstract class
3. Implement all required methods: __init__, execute_query, build_definition, close
4. Add the new type to DbTypes enum
5. Update DataAnalyst/database/Type/__init__.py to export your class
6. Update server.py to handle the new database type

Examples

Example 1: Customer Analysis

Query: "Show me the top 10 customers by total order value"

Generated SQL:
SELECT c.customer_name, SUM(o.total_amount) as total_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name
ORDER BY total_value DESC
LIMIT 10;

Example 2: Product Inventory

Query: "Which products are low in stock (less than 10 units)?"

Generated SQL:
SELECT product_name, quantity_in_stock
FROM products
WHERE quantity_in_stock < 10
ORDER BY quantity_in_stock ASC;

Contributing

Contributions are welcome! Please ensure:

1. Code follows PEP 8 style guidelines
2. All functions have type hints and docstrings
3. New database types extend BaseDatabase
4. Changes maintain backward compatibility

Support

For issues, questions, or contributions, please open an issue on the repository.

---

Built with:

• Model Context Protocol (MCP)
• FastMCP
• OpenAI API