MCP-Driven Data Management System

This repository contains the code for a multi-tiered data management system that allows users to interact with heterogeneous databases (MySQL and PostgreSQL) using natural language. It leverages the FastMCP framework for server-side tool orchestration and integrates a Large Language Model (LLM) for intelligent query processing and response generation. Table of Contents

1. Overview

2. Architecture

3. Key Features

4. Setup and Installation

   • Prerequisites

   • Environment Variables (.env)

   • Database Configuration (Aiven Console for MySQL and Supabase for Postgres)

   • Running the Server

   • Running the Client

5. Usage

6. Troubleshooting

1. Overview

This system provides a conversational interface for performing Create, Read, Update, and Delete (CRUD) operations across multiple databases. It intelligently routes user requests to the appropriate backend tools and presents results in a user-friendly format, including advanced data formatting options.

2. Architecture

The system follows a layered architecture:

Client Layer (client1.py - Streamlit UI): The user-facing web application for natural language interaction.

Application Layer (main1.py - FastMCP Server): The core orchestration layer that hosts database interaction tools, integrates with the LLM, and manages data flow between the client and databases.

AI/LLM Layer (OpenAI API): An external Large Language Model responsible for understanding user intent, selecting tools, extracting arguments, and generating natural language responses.

Database Layer (Aiven console & Supabase - MySQL & PostgreSQL): The persistent storage for customer, product, and sales data.

Conceptual Diagram:

3. Key Features

4. Setup and Installation

Prerequisites

Environment Variables (.env) Create a .env file in the root directory of your project (where main1.py and client1.py reside) and populate it with your database credentials and API keys:

Database Configuration (AWS RDS)

1. Create MySQL on Aiven Console

2. Create PostgreSQL Supabase Instance

Note down their endpoint, IP address, password, host name, username, and DB name!!! Check the .env file format above ☝️

Install Python Dependencies

Navigate to your project directory in the terminal and install the required Python packages:

pip install fastmcp mysql-connector-python psycopg2-binary groq uvicorn pyodbc streamlit pandas pillow openai fastmcp mcp mcp-server python-dotenv asyncio langchain_groq langchain_core plotly

Running the Server

Go to Render and sign up. Then create a Web Service

While setting it up, give your repository's (the one with the server code) public link.

The server (main1.py) hosts the database tools and communicates with the LLM.

nohup python -u main1.py > server.log 2>&1 &

This command will:

Running the Client

The client (client1.py) provides the Streamlit web interface. We are currently running it using the Streamlit cloud.

streamlit run client1.py

This will open the Streamlit application in your web browser.

5. Usage

Once both the server and client are running, you can interact with the system through the Streamlit chat interface.

Sample Prompts:

• "Add a new customer named John Smith with email

• "Update the price of Widget to $29.99"

• "Delete customer Alice Johnson from the database"

• "Create a new product called 'Premium Gadget' priced at $149.99 with description 'High-end gadget'"

• "Change Bob Wilson's email to

• "Show me all sales data with a bar chart of total sales by product"

• "Display customer information with a pie chart showing distribution by first letter of name"

• "List all products with prices and create a histogram of price distribution"

• "Show sales trends over time with a line chart"

• "Show me sales where total price is greater than 50 with a visualization"

• "Display only customer names and emails, excluding other fields"

• "List products with prices between $20 and $50, sorted by price"

• "Show me the highest selling product by quantity"

• "Show me customers who haven't made any purchases yet"

• "Visualize sales distribution by time of day"

• ”Show me the sales data in a visual form”

6. Troubleshooting

-> _mysql_connector.MySQLInterfaceError: Commands out of sync: This often occurs during database seeding if multiple SQL commands are sent too rapidly on the same cursor, or if autocommit is mismanaged. The current seed_databases implementation attempts to mitigate this by separating commands and managing transactions explicitly.

-> If you are unable to use the pyodbc library, depending on the OS of the deployment machine - use the appropriate cmd. Check here

-> For UI issues, check your client1.py file

• Database Connection Errors (e.g., "server not accessible", "timed out"):

  Verify your .env variables are correct (host, port, user, password, URL). Ensure your DB instances are "Available".

• "No Tools Discovered" / LLM Errors:

  Ensure your main1.py is running and accessible at the MCP_SERVER_URL specified in your .env. Verify your GROQ_API_KEY is correct and has sufficient permissions. Check server logs for any errors during tool registration or LLM calls.

• "List Sales" shows no results:

  The Sales table starts empty. You must first use a "record a sale" prompt to add transactions. Confirm that the "record a sale" command returns a success message, indicating the data was inserted. Verify that the customer_id and product_id in your sales records have matching entries in the Customers and ProductsCache tables, respectively.