🧠 AI SQL Query Auditor

An AI-powered SQL learning tool built with Python, Streamlit, MySQL, and Google Gemini. It lets a user write a SQL query against a real database, runs it, and then uses Gemini AI to explain errors in plain English, suggest corrected SQL, review correct queries, and score query quality — like having a personal SQL tutor sitting next to you.

---

📖 About

AI SQL Query Auditor was built to help beginners learn SQL the way a mentor would — not just by telling them what went wrong, but why, and how to fix it.

Instead of showing a raw, intimidating MySQL error message, the app automatically:

• Reads the live database schema
• Sends the schema, the user's business question, their SQL query, and any MySQL error to Gemini AI
• Returns a simple, beginner-friendly explanation, a corrected query, and a quality score

It started as a two-part hands-on project: first building the core query runner, then layering AI review on top.

---

✨ Features

• 🔌 Connects to a live MySQL database
• 📝 Lets users enter a business question + a SQL query through a web UI
• ▶️ Executes the query and displays results in a table
• ⚠️ Catches and displays SQL errors without crashing
• 🤖 Sends schema + question + query + error to Gemini AI for review
• 💡 AI explains errors in simple English
• 🛠️ AI provides the corrected SQL query with a line-by-line explanation
• ⭐ AI scores query quality out of 10 and flags beginner mistakes
• ✅ Reviews even correct queries and suggests simpler/better alternatives

---

🏗️ Tech Stack

Layer	Technology
Frontend/UI	Streamlit
Backend Language	Python
Database	MySQL (via mysql-connector-python)
AI Model	Google Gemini (gemini-2.5-flash) via google-genai

---

🏛️ Architecture

        Student
           │
           ▼
  Streamlit Application
           │
   Writes SQL Query
           │
    Click "Run Query"
           │
           ▼
   Execute Query in MySQL
     ┌───────────┐
     │           │
  Success     Error Found
     │           │
     └─────┬─────┘
           ▼
   Read Database Schema
           │
           ▼
     Send to Gemini AI
   • Database Schema
   • Business Question
   • Student SQL
   • MySQL Error (if any)
           │
           ▼
      AI SQL Reviewer
   • Explains the error
   • Provides corrected SQL
   • Explains corrections
   • Gives a quality score
   • Suggests improvements

---

📁 Project Structure

AI_SQL_Auditor/
│
├── app.py                  # Streamlit UI + main application logic
├── config.py                # MySQL & Gemini API configuration
├── requirements.txt          # Python dependencies
│
├── utils/
│   ├── db.py                # MySQL connection handler
│   ├── schema.py              # Reads database schema automatically
│   └── gemini.py              # Sends prompts to Gemini and returns response
│
└── prompts/
    └── sql_prompt.txt          # Prompt template used for AI SQL review

---

⚙️ Setup & Installation

1. Prerequisites

• Python 3.x (check Add Python to PATH during install)
• MySQL Community Server + MySQL Workbench
• A free Gemini API key

2. Clone the repository

git clone https://github.com/Anuky-git/AI_SQL_Auditor.git
cd AI_SQL_Auditor

3. Install dependencies

pip install -r requirements.txt

4. Set up the database

Open MySQL Workbench and run:

CREATE DATABASE ai_sql_auditor;
USE ai_sql_auditor;

Then create the Customers, Products, and Orders tables and insert sample data (see /database or setup script if included).

5. Configure credentials

Update config.py with your MySQL credentials and Gemini API key:

MYSQL_HOST = "localhost"
MYSQL_USER = "root"
MYSQL_PASSWORD = "your_mysql_password"
MYSQL_DATABASE = "ai_sql_auditor"

GEMINI_API_KEY = "your_gemini_api_key"

⚠️ Never commit real API keys or passwords to GitHub. Use a .env file or config.py in .gitignore for production.

6. Run the application

streamlit run app.py

The app will open automatically in your browser (usually at http://localhost:8501).

---

🚀 Usage

1. Type a Business Question (e.g., "Show all customers")
2. Write the corresponding SQL Query
3. Click Run Query
4. View the results table
5. Scroll down to see the AI SQL Review — explanation, correction, and score

Example

Business Question: Show all customers

SELECT * FROM Customers;

✅ Query runs successfully → results table is shown → AI confirms the query is correct and gives a score.

Error Example:

SELECT * FROM Customer;

❌ MySQL error (table doesn't exist) → AI explains the mistake in plain English, points out the correct table name is Customers, and shows the corrected query.

---

🖼️ Output / Screenshots

Add screenshots of the running app here so visitors can see it without running it themselves.

![App Home](<img width="960" height="600" alt="homepage" src="https://github.com/user-attachments/assets/2327b5b0-f47d-4c02-b05a-a5509c2cb821" />)
![Query Result](<img width="597" height="378" alt="Result" src="https://github.com/user-attachments/assets/2f8ef031-a168-4c73-9992-8dbfdb39bb33" />
)
![AI Review](<img width="545" height="361" alt="Review" src="https://github.com/user-attachments/assets/dea23a8c-9ac0-4d99-803b-69b641cf2ca6" />
)

---

🎓 Learning Outcomes

• Connecting Python to MySQL with mysql-connector-python
• Building an interactive UI with Streamlit
• Structuring a project into reusable modules (db.py, schema.py, gemini.py)
• Reading a database schema programmatically
• Prompt engineering for an AI code-review use case
• Integrating the Gemini API into a real application
• Handling and displaying errors gracefully in a user-friendly way

---

🤝 Contributing

Contributions, issues, and feature requests are welcome. Feel free to fork this repo and submit a pull request.

📄 License

This project is open source and available under the MIT License.