# Local Database Analytics MCP Server

A Python-based Model Context Protocol (MCP) server that connects local databases to AI assistants like Claude. This project demonstrates how to build analytical abstractions on top of raw SQL data using FastMCP, SQLAlchemy, and Pandas.

## Overview

Instead of just giving an LLM raw SQL access, this server provides a structured suite of analytical tools. It allows an AI assistant to natively inspect schemas, run safe queries, aggregate data, detect nulls, compute correlations, and generate time-series summaries—all through natural language.

The repository includes a seed script that generates a realistic local SQLite e-commerce database, allowing you to test the server immediately without needing external database credentials.

## Core Capabilities

- **Read-Only SQL Execution**: Safely execute `SELECT` queries without risking data modification.
- **Statistical Analysis**: Calculate min/max, standard deviation, and Pearson correlations between columns.
- **Time-Series Aggregation**: Group data by day, week, or month to spot trends.
- **Automated Data Quality Checks**: Tools to scan for nulls and anomalies.
- **MCP Prompts**: Pre-built templates for common tasks like generating business summaries and analyzing tables.

## Project Structure

- `server.py` & `mcp_instance.py`: The FastMCP server initialization and entry points.
- `database/`: Contains the SQLAlchemy connection logic and the data seeding script.
- `tools/`: The core of the server. 
  - `query.py`: Basic SQL and schema inspection tools.
  - `analytics.py`: Higher-level Pandas-based statistical tools.
- `resources/`: Exposes database schemas as static MCP resources.
- `prompts/`: Standardized MCP prompts for the AI assistant.
- `tests/`: Pytest suite verifying the functionality of all tools.

## Setup Instructions

1. **Clone the repository**
   ```bash
   git clone https://github.com/manohar135/Local-Database-Analytics-MCP-Server.git
   cd Local-Database-Analytics-MCP-Server
   ```

2. **Set up a virtual environment**
   ```bash
   python -m venv .venv
   source .venv/bin/activate
   pip install -r requirements.txt
   ```

3. **Generate the sample database**
   This script creates a local SQLite database (`data/analytics.db`) and populates it with sample e-commerce data (customers, products, orders).
   ```bash
   python database/seed_data.py
   ```

4. **Run the MCP Inspector**
   FastMCP comes with a built-in UI for testing tools locally without needing Claude Desktop.
   ```bash
   fastmcp dev inspector server.py
   ```

## Testing

The project includes a test suite that uses a temporary SQLite setup to verify the MCP tools.

```bash
pytest tests/ -v
```

## Integration with Claude Desktop

To use this server directly within Claude Desktop, add the following to your `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "local-db-analytics": {
      "command": "/path/to/your/repo/.venv/bin/python",
      "args": ["-m", "fastmcp", "run", "/path/to/your/repo/server.py"]
    }
  }
}
```
*(Replace the paths with the absolute paths to your virtual environment and repository).*