---
title: GenAI Text2SQL Analytics Assistant
emoji: 🤖
colorFrom: blue
colorTo: indigo
sdk: streamlit
sdk_version: "1.45.1"
app_file: app.py
pinned: false
license: mit
---

# 🤖 GenAI Text2SQL Analytics Assistant

An advanced Generative AI-powered analytics assistant that converts natural language questions into executable SQL queries and retrieves insights from a live relational database.

Built using LangChain, Groq LLMs, SQLAlchemy, SQLite, and Streamlit.

---

# 🚀 Features

- Natural Language to SQL Conversion
- Live Database Query Execution
- AI-Powered Business Analytics
- SQL Query Transparency
- Interactive Analytics Dashboard
- CSV Export Functionality
- Automatic SQL Cleaning & Validation
- Multi-table SQL Reasoning
- Conversational Analytics Experience
- Real-time Data Insights

---

# 🧠 How It Works

The application does not train a custom AI model.

Instead, it uses a Large Language Model (LLM) to:
1. Understand the user's business question
2. Generate optimized SQL queries dynamically
3. Execute queries on a live SQLite database
4. Return analytics results and business insights

---

# 🏗️ Architecture

```text
User Query
    ↓
LLM (Groq + LangChain)
    ↓
SQL Query Generation
    ↓
SQLite Database Execution
    ↓
Analytics Results
    ↓
AI Business Summary + Visualization
```

---

# 🛠️ Tech Stack

| Category | Technology |
|---|---|
| LLM Provider | Groq |
| Framework | LangChain |
| Database | SQLite |
| ORM | SQLAlchemy |
| Frontend | Streamlit |
| Data Generation | Faker |
| Data Processing | Pandas |
| Language | Python |

---

# 📂 Project Structure

```bash
TEXT2SQL-GENAI/
│
├── agents/
│   ├── sql_agent.py
│   └── langchain_sql_agent.py
│
├── database/
│   ├── create_db.py
│   ├── seed_data.py
│   └── ecommerce.db
│
├── prompts/
│   └── sql_prompt.py
│
├── utils/
│   ├── sql_utils.py
│   └── query_engine.py
│
├── app.py
├── requirements.txt
├── .env
└── README.md
```

---

# 🗄️ Database Schema

The project uses a relational e-commerce database containing:

- Customers
- Products
- Employees
- Orders
- Order Items

The database was populated with synthetic business data using Faker.

---

# 📊 Example Questions

```text
Top 5 products by revenue
```

```text
Which employee handled the most orders?
```

```text
Average order value by city
```

```text
Show revenue by product category
```

```text
Top customers by spending
```

---

# 🔥 Advanced Features Implemented

## ✅ Schema-Aware Prompting
Injected database schema directly into prompts to improve SQL accuracy.

## ✅ SQL Cleaning
Automatically removes markdown formatting and cleans generated SQL.

## ✅ Error Handling & Retry Logic
Detects invalid SQL queries and retries with correction prompts.

## ✅ LangChain SQL Agent
Implemented autonomous SQL reasoning using LangChain SQL Agent.

## ✅ AI Business Summaries
Generates human-readable business insights from query results.

## ✅ Interactive Analytics Dashboard
Built using Streamlit with:
- Data tables
- SQL visibility
- CSV export
- Charts and visualizations

---

# 📈 Sample Analytics Capabilities

- Revenue Analysis
- Customer Segmentation
- Product Performance
- Sales Trends
- Employee Performance Metrics
- Business Intelligence Reporting

---

# ⚙️ Installation

## 1️⃣ Clone Repository

```bash
git clone <your_repo_url>
cd TEXT2SQL-GENAI
```

---

## 2️⃣ Create Virtual Environment

### Windows

```bash
python -m venv venv
venv\Scripts\activate
```

---

## 3️⃣ Install Dependencies

```bash
pip install -r requirements.txt
```

---

## 4️⃣ Configure Environment Variables

Create `.env`

```env
GROQ_API_KEY=your_api_key_here
```

---

# ▶️ Running The Project

## Step 1 — Create Database

```bash
python database/create_db.py
```

---

## Step 2 — Seed Database

```bash
python database/seed_data.py
```

---

## Step 3 — Run Streamlit App

```bash
streamlit run app.py
```

---

# 🌟 Key Learning Outcomes

- Generative AI Application Development
- Prompt Engineering
- LangChain Agent Workflows
- SQL Query Generation
- Database Integration with LLMs
- Conversational Analytics Systems
- Streamlit Dashboard Development
- AI-powered Business Intelligence

---

# 🎯 Future Improvements

- PostgreSQL Support
- Authentication System
- Query History
- Role-Based Access Control
- Real-time Streaming Responses
- Data Upload & Auto Analysis
- Multi-turn Conversational Memory
- Advanced Chart Visualizations

---

# 📌 Important Note

This project does not fine-tune or train an LLM.

Instead, it demonstrates:
- LLM orchestration
- schema-aware prompting
- live SQL generation
- autonomous database querying
- AI analytics workflows

which closely reflects real-world enterprise GenAI systems.

---

# 👨‍💻 Author

Mohd Faizanullah