README.md

---
title: PostgreSQL Query Agent with Visualization
emoji: 🤯
colorFrom: pink
colorTo: yellow
sdk: docker
pinned: false
short_description: PostgreSQL Query Agent with Visualization
---

[HF URL](https://huggingface.co/spaces/intellica/talk2data)
[Gradio APP URL](https://intellica-talk2data.hf.space/)


# Natural Language SQL Query Agent with Visualization

A smart and interactive PostgreSQL query system that translates natural language requests into SQL queries, executes them, and generates visualizations using PandasAI. Built with modern technologies including LangChain, FastMCP, and Gradio.

![Architecture](resources/visualization_demo.png)

## 🌟 Features

- **Natural Language to SQL**: Convert plain English questions into SQL queries
- **Interactive Chat Interface**: User-friendly Gradio web interface
- **Smart Visualization**: Automated chart generation based on query results
- **Conversation Memory**: Maintains context across multiple queries
- **Database Schema Understanding**: Intelligent handling of database structure
- **Multiple LLM Support**: Compatible with both OpenAI and Google's Gemini models

## 🏗️ Architecture

The project is structured into several key components:

### 1. Query Processing Layer
- **LangChain Client** (`langchain_mcp_client.py`):
  - Manages LLM interactions for query understanding
  - Handles conversation flow and context
  - Integrates with MCP tools
  - Supports multiple LLM providers (OpenAI/Gemini)

### 2. Database Layer
- **PostgreSQL MCP Server** (`postgre_mcp_server.py`):
  - Manages PostgreSQL connections and query execution
  - Implements connection pooling for efficiency
  - Provides database schema information
  - Handles query result processing

### 3. Visualization Layer
- **PandasAI Integration** (`pandasai_visualization.py`):
  - Intelligent chart generation from query results
  - Support for multiple chart types
  - Automated visualization selection
  - Exports charts to `exports/charts/` directory

### 4. User Interface
- **Gradio Web Interface** (`gradio_app.py`):
  - Clean and intuitive chat interface
  - Real-time query processing
  - Visualization display
  - Interactive session management

### 5. Memory Management
- **Conversation Store** (`memory_store.py`):
  - Maintains conversation history
  - Implements singleton pattern for global state
  - Enables contextual query understanding

## 🚀 Getting Started

### Prerequisites

- Python 3.11 or lower
- PostgreSQL database 
- Access to either OpenAI API or Google Gemini API

### Installation

1. **Clone the Repository**
   ```bash
   git clone <repository-url>
   cd query_mcp_server
   ```

2. **Set Up Virtual Environment**
   ```bash
   python -m venv venv
   source venv/bin/activate  # Linux/Mac
   # or
   .\venv\Scripts\activate   # Windows
   ```

3. **Install Dependencies**
   ```bash
   pip install -r requirements.txt
   ```

4. **Environment Configuration**
   Create a `.env` file using the .env.example template:
   ```bash
   cp .env.example .env
   ```
   Fill in the required environment variables.

## 🏃‍♂️ Running the Application

1. **Start the Application**
   ```bash
   python gradio_app.py
   ```
   or using run.sh
   ```bash
   chmod +x run.sh
   ./run.sh
   ```

2. **Access the Interface**
   - Open your browser and navigate to `http://localhost:7860`
   - Start querying your database using natural language!

## 🧪 Testing

To test the visualization component independently:
```bash
python pandasai_visualization.py
```
This will generate sample visualizations to verify the PandasAI setup.

## 📁 Project Structure
```
query_mcp_server/
├── gradio_app.py           # Web interface
├── langchain_mcp_client.py # LLM integration
├── postgre_mcp_server.py   # Database handler
├── pandasai_visualization.py # Visualization logic
├── memory_store.py         # Conversation management
├── exports/               
│   └── charts/            # Generated visualizations
└── resources/             # Static resources
```

## 🛠️ Contributing

1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

## 📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

## ✨ Acknowledgments

- LangChain for the powerful LLM framework
- PandasAI for intelligent visualization capabilities
- Gradio for the intuitive web interface
- FastMCP for efficient database communication