SmartDB-Assistant 🤖💬

Talk to your MySQL database in plain English — AI chatbot with self-healing SQL generation, schema-aware context, and read-only security enforcement.

A locally-hosted AI-powered Natural Language to SQL assistant built with Streamlit, Ollama (Qwen 2.5 7B), and SQLAlchemy. Ask questions in plain English, and SmartDB-Assistant translates them into secure SQL queries, executes them against your MySQL database, and returns clean, human-readable answers — all running 100% locally with no API keys or cloud dependencies.

---

🏗️ Project Architecture & Files

database chatbot/
├── app.py              # Streamlit UI and application controller
├── db_interface.py     # Database abstraction layer (SQLAlchemy + MySQL)
├── sql_agent.py        # LLM interface for SQL generation & validation
├── test_schema.py      # Quick schema verification script
├── requirements.txt    # Python package dependencies
├── .gitignore          # Git ignore rules
├── README.md           # This file
└── tests/
    ├── test_db_interface.py   # Unit tests for database connector
    └── test_sql_agent.py      # Unit tests for SQL agent

File Descriptions

File	Purpose
app.py	The Streamlit user interface. Manages chat history, expands short-form queries, calls the SQL agent, executes queries with self-correction (up to 2 retries), and displays human-friendly answers.
db_interface.py	The database abstraction layer. Contains a generic SQLAlchemyConnector that connects to MySQL via PyMySQL. Handles schema introspection (tables, columns, types) and foreign key relationships. Database connection settings are configured directly in this file.
sql_agent.py	The core LLM interface using Ollama (qwen2.5:7b). Contains functions for: relevance classification, table selection (for large schemas), SQL generation, SQL cleaning/validation (blocks non-SELECT statements), and SQL self-correction on error.
test_schema.py	A quick standalone script to verify the database connection and print the extracted schema.
requirements.txt	Python packages: streamlit, ollama, sqlalchemy, pymysql, pytest, flake8, black.
tests/	Automated unit tests using pytest. Tests use temporary in-memory SQLite databases and mocked LLM calls — no external dependencies needed.

---

💻 Hardware Recommendations

Running local models smoothly on an AMD Ryzen 7 CPU and NVIDIA RTX 3050 GPU (4 GB VRAM):

1. GPU Acceleration: The qwen2.5:7b model requires ~4.7 GB VRAM. Ollama handles this by performing partial offloading — most layers run on the GPU, the rest fall back to the CPU.
2. Setup:
   • Install the official NVIDIA Drivers and CUDA Toolkit on Windows.
   • Install the native Windows build of Ollama. It automatically detects the RTX 3050 and uses CUDA acceleration (~20-30 tokens/second).
3. Performance Tips:
   • Close other GPU-intensive apps (games, editors) while using the chatbot.
   • The first query after starting Ollama may be slower due to model loading. Subsequent queries are fast.

---

⚙️ How to Run the Project

1. Start Ollama and Pull the Model

ollama pull qwen2.5:7b

2. Set Up Python Environment

python -m venv venv
# On Windows:
venv\Scripts\activate
# On macOS/Linux:
source venv/bin/activate

pip install -r requirements.txt

3. Configure the Database Connection

Open db_interface.py and edit the configuration variables at the top of the file:

# ==========================================
# 🔧 DATABASE CONFIGURATION — Edit these values for your MySQL database
# ==========================================
DB_TYPE = "mysql"
DB_HOST = "localhost"
DB_PORT = "3306"
DB_USER = "your_username"
DB_PASSWORD = "your_password"
DB_NAME = "your_database"
# ==========================================

Replace your_username, your_password, and your_database with your actual MySQL credentials and database name.

4. Verify the Connection (Optional)

python test_schema.py

This prints the detected tables and columns to confirm the connection works.

5. Launch the Chatbot

streamlit run app.py

The chatbot opens at http://localhost:8501.

---

🧪 Running Automated Tests

pytest

All tests use temporary SQLite databases and mocked LLM calls — no external database or Ollama instance required.

---

🛡️ Key Features

1. SQL Self-Correction Loop

If the generated SQL query fails (e.g. wrong column name or join), the system catches the error, passes the failed SQL and error message back to the LLM, and asks for a correction. It retries up to 2 times automatically.

2. Strict Security Filter

Generated SQL is inspected for forbidden commands (DELETE, UPDATE, DROP, ALTER, INSERT, etc.) before execution, guaranteeing read-only access.

3. Smart Table Selection

For large databases (>15 tables), the LLM first selects only the relevant tables, keeping the schema context small and generation fast.

4. Short-Form Query Expansion

Queries with 3 or fewer words (e.g. "customers count") are automatically expanded into descriptive questions for better SQL generation.

5. Conversation Context

The chatbot maintains a rolling conversation history, allowing follow-up questions like "and what about last year?" to reference previous queries.

---

📦 Tech Stack

Technology	Role
Python 3.10+	Core language
Streamlit	Web-based chat UI
Ollama	Local LLM inference engine
Qwen 2.5 7B	Language model for SQL generation
SQLAlchemy	Database abstraction & schema introspection
PyMySQL	MySQL database driver
pytest	Automated testing framework

---

📄 License

This project is for educational and internal use.