README.md

metadata

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

HF URL Gradio APP URL

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.

🌟 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

   git clone <repository-url>
   cd query_mcp_server
   

2. Set Up Virtual Environment

   python -m venv venv
   source venv/bin/activate  # Linux/Mac
   # or
   .\venv\Scripts\activate   # Windows
   

3. Install Dependencies

   pip install -r requirements.txt
   

4. Environment Configuration Create a .env file using the .env.example template:

   cp .env.example .env
   

   Fill in the required environment variables.

🏃‍♂️ Running the Application

1. Start the Application

   python gradio_app.py
   

   or using run.sh

   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:

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