AI-NLP_SQL_LLM: Natural Language to SQL Query Generator

🚀 Project Overview

AI-NLP_SQL_LLM is an advanced Natural Language Processing (NLP) system that converts human language queries into SQL statements using Large Language Models (LLMs). The system leverages LangChain framework and OpenAI's GPT models to provide intelligent database querying capabilities through conversational interfaces.

✨ Key Features

• Natural Language to SQL Conversion: Transform plain English questions into executable SQL queries
• Multi-Database Support: Compatible with MySQL, MSSQL, and other SQL databases
• Few-Shot Learning: Enhanced accuracy through example-based learning
• Semantic Similarity: Intelligent example selection using vector embeddings
• Conversational Memory: Maintains context across multiple queries
• Table Schema Awareness: Automatically identifies relevant database tables
• Structured Output: Returns formatted, human-readable responses

🏗️ Architecture

Core Components

1. Language Model Integration

   • OpenAI GPT-3.5-turbo for natural language understanding
   • LangChain framework for orchestration
   • Custom prompt engineering for SQL generation

2. Database Layer

   • SQLDatabase connector for MySQL integration
   • Dynamic table schema detection
   • Query execution and result processing

3. Memory & Context Management

   • ChatMessageHistory for conversation tracking
   • Context-aware follow-up question handling

4. Vector Database

   • ChromaDB for semantic similarity search
   • Example selection based on query similarity

📋 Prerequisites

System Requirements

• Python 3.8+
• MySQL Server (or compatible database)
• OpenAI API key
• LangChain API key (optional, for tracing)

Environment Setup

1. Database Configuration

   • MySQL server running
   • Database with sample data (customers, employees, orders, etc.)
   • Proper user permissions

2. API Keys

   • OpenAI API key for GPT model access
   • LangChain API key for tracing (optional)

🛠️ Installation

1. Clone the Repository

git clone https://github.com/manoharpavuluri/AI_NLP_SQL_LLM.git
cd AI_NLP_SQL_LLM

2. Install Dependencies

pip install -r requirements.txt

3. Environment Configuration

Create a .env file with your configuration:

# Database Configuration
MYSQL_DB_USER=your_username
MYSQL_DB_PASS=your_password
MYSQL_DB_HOST=localhost
MYSQL_DB_NAME=your_database

# API Keys
OPENAI_API_KEY=your_openai_api_key
LANGCHAIN_API_KEY=your_langchain_api_key
LANGCHAIN_TRACING_V2=true

🚀 Usage

Basic Usage

1. Start the Jupyter Notebook

   jupyter notebook AI_SQL_LLM.ipynb

2. Mount Google Drive (if using Google Colab)

   • The notebook automatically mounts Google Drive
   • Place your .env file in the specified directory

3. Run the Cells

   • Execute cells sequentially to set up the environment
   • The system will connect to your database and initialize the LLM

Example Queries

# Basic query
question = "What is the price of '1968 Ford Mustang'?"
response = chain.invoke({"question": question})

# Complex query with aggregations
question = "How many customers have an order count greater than 5?"
response = chain.invoke({"question": question})

# Follow-up questions (with memory)
question = "Can you list their names?"
response = chain.invoke({"question": question, "messages": history.messages})

🔧 Technical Implementation

1. SQL Query Generation Chain

# Core query generation
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
generate_query = create_sql_query_chain(llm, mysql_db)

2. Few-Shot Learning Implementation

# Example-based learning
examples = [
    {
        "input": "List all customers in France with a credit limit over 20,000.",
        "query": "SELECT * FROM customers WHERE country = 'France' AND creditLimit > 20000;"
    }
]

3. Semantic Similarity Selection

# Vector-based example selection
vectorstore = Chroma()
example_selector = SemanticSimilarityExampleSelector.from_examples(
    examples,
    OpenAIEmbeddings(),
    vectorstore,
    k=2
)

4. Table Schema Detection

# Automatic table relevance detection
table_chain = create_extraction_chain_pydantic(Table, llm, system_message=table_details_prompt)

📊 Database Schema

The system works with a sample database containing the following tables:

• customers - Customer information and details
• employees - Employee records
• offices - Office locations
• orderdetails - Order line items
• orders - Order headers
• payments - Payment transactions
• productlines - Product categories
• products - Product catalog

🔍 Advanced Features

1. Conversational Memory

• Maintains context across multiple queries
• Enables follow-up questions like "Can you list their names?"
• Uses ChatMessageHistory for conversation tracking

2. Intelligent Table Selection

• Automatically identifies relevant tables for queries
• Uses Pydantic models for structured extraction
• Reduces query complexity and improves performance

3. Enhanced Prompting

• Custom system prompts for MySQL expertise
• Few-shot examples for better accuracy
• Semantic similarity for example selection

🧪 Testing and Validation

Query Examples

# Test various query types
test_queries = [
    "How many products are there?",
    "What is the highest payment amount?",
    "List customers in France with credit limit over 20,000",
    "How many customers have more than 5 orders?"
]

for query in test_queries:
    response = chain.invoke({"question": query})
    print(f"Q: {query}")
    print(f"A: {response}\n")

🔒 Security Considerations

1. API Key Management

   • Store API keys in environment variables
   • Never commit keys to version control
   • Use secure key management services

2. Database Security

   • Use dedicated database users with minimal permissions
   • Implement connection pooling
   • Regular security audits

3. Input Validation

   • Validate user inputs before processing
   • Implement query result limits
   • Monitor for SQL injection attempts

🚨 Troubleshooting

Common Issues

1. Database Connection Errors

   • Verify database credentials
   • Check network connectivity
   • Ensure database server is running

2. API Key Issues

   • Validate OpenAI API key
   • Check API quota and billing
   • Verify key permissions

3. Memory Issues

   • Clear conversation history periodically
   • Monitor vector database size
   • Implement cleanup routines

📈 Performance Optimization

1. Query Optimization

   • Use table selection to reduce scope
   • Implement query caching
   • Monitor query execution times

2. Memory Management

   • Regular cleanup of conversation history
   • Optimize vector database operations
   • Implement connection pooling

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

📄 License

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

🙏 Acknowledgments

• LangChain team for the excellent framework
• OpenAI for providing the GPT models
• The open-source community for various dependencies

📞 Support

For questions, issues, or contributions:

• Create an issue on GitHub
• Contact the maintainer
• Check the documentation

---

Note: This system is designed for educational and development purposes. Always test thoroughly before using in production environments.