AI-Powered Retail Database Assistant
Text-to-SQL Project — Complete Explanation
By: Abhimanyu Kumar, Amit Kumar & Ankit Pandey | CSE
Project Overview
This is a web application where a user types a question in plain English (like "What did Amit buy last
month?") and the system automatically converts it to an SQL query, runs it on a database, and shows the
results with charts. It combines AI (LLM), a relational database (SQLite), and a web UI (Streamlit) into one
complete application.
Project Files — What Each Does
1. [Link] — Database Setup
This file creates the database from scratch using SQLite, a lightweight file-based database (stored as
[Link]).
4 Tables are created:
Table Columns / Purpose
Products product_id, product_name, category, price, stock
Customers customer_id, customer_name, phone, city, customer_type
Orders order_id, customer_id, order_date — links customers to purchases
OrderItems item_id, order_id, product_id, quantity — individual items per order
Foreign Keys link the tables together (e.g., Orders.customer_id references Customers.customer_id). This
is a relational database design. Sample data — 10 products and 5 customers (Ankit, Amit, Abhimanyu,
Rahul, Ravi) — is inserted using INSERT INTO statements.
2. llm_sql.py — The AI Brain (Text → SQL Conversion)
This is the most important file. It converts plain English questions into SQL using two approaches:
Approach 1: Rule-Based SQL (rule_based_sql function)
Handles common patterns without AI — like "What did Amit buy?" or "Show today's orders". Uses simple
if/elif checks and SQLite date functions like strftime() to filter by today / this month / last month. This is fast
and 100% accurate for known patterns.
Approach 2: LLM (AI) via Ollama (generate_sql function)
If the rule-based system can't handle the question, it sends a prompt to a local AI model called LLaMA 3,
running via Ollama (a tool to run AI models locally on your computer). The prompt includes the database
schema (table structure), rules, and few-shot examples so the AI knows exactly how to write correct
SQLite queries.
Self-Correction (fix_sql function)
If the generated SQL fails, this function sends the broken SQL + the error message back to the AI and
asks it to fix it — up to 2 retry attempts automatically.
clean_sql function
Strips markdown formatting (like ```sql) from the AI's response and extracts just the clean SELECT query
using Python's re (regex) module.
3. query_runner.py — Runs SQL Queries & Fetches Data
Handles all read operations from the database:
• run_query(sql) — Executes any SELECT query safely. Blocks non-SELECT queries (no
DELETE/DROP allowed from the Q&A section).
• get_dashboard_data() — Returns total products, customers, orders, and total revenue for the
dashboard.
• get_customer_names() — Gets all customer names — used to detect names in user questions.
• get_all_orders() — Returns full order history with customer, product, quantity, and amount.
• get_low_stock_products() — Returns products with stock ≤ 5 for the low-stock alert banner.
4. admin_functions.py — Admin Write Operations
Handles all write operations (INSERT, UPDATE, DELETE):
• add_product() — Adds a new product. If it already exists, updates the stock instead.
• add_customer() — Inserts a new customer into the Customers table.
• update_stock() — Directly sets a new stock quantity for a product by product_id.
• delete_product() — Deletes a product, but prevents deletion if it exists in any order history (referential
integrity).
• create_order() — Creates a new order — checks stock first, then reduces stock automatically after
placing the order.
• delete_order() — Deletes one order and restores the stock back to the product.
• delete_all_orders() — Deletes all orders and fully restores stock for every product.
5. [Link] — The Main Web Application (Streamlit UI)
This is the frontend + controller of the entire app, built using Streamlit. It ties all other modules together
and provides the user interface.
• Pronoun Resolution — Remembers the last customer mentioned. So if you ask "What did Amit buy?"
then "What did he buy last month?", it replaces "he" with "Amit" automatically using Python's re (regex)
module.
• Admin Panel (Sidebar) — A dropdown lets admins Add/Delete products, Add customers, Create/Delete
orders, and View all orders — all from the sidebar without typing SQL.
• Dashboard — Shows 4 key metrics: Total Products, Total Customers, Total Orders, Total Revenue
using [Link](). Also shows a low stock warning if any product has ≤ 5 units.
• Q&A Section — User picks a sample question or types their own. The app detects names, resolves
pronouns, generates SQL, displays it, runs it, shows results as a table, and lets the user download as
CSV.
• Interactive Charts — Uses Plotly Express to generate Bar, Line, or Pie charts from query results. User
can choose X-axis, Y-axis, and chart type dynamically.
Libraries & Technologies Used
Library / Tool Purpose
Streamlit Builds the entire web UI — buttons, inputs, tables, charts, sidebar, metrics
SQLite3 File-based database ([Link]) — no server needed; built into Python
Pandas Converts SQL results into DataFrames (tables), enables CSV export
Plotly Express Creates interactive Bar, Line, and Pie charts from query results
Ollama + LLaMA 3 Local AI model that converts English questions into SQL queries
Requests Sends HTTP POST requests to Ollama's local API (localhost:11434)
re (Regex) Handles pronoun replacement (he/she/they → customer name)
datetime Records the exact date and time when orders are created
Database Schema (Table Relationships)
The four tables are linked using Foreign Keys, forming a classic retail order management schema:
Customers ■■(1:many)■■■ Orders ■■(1:many)■■■ OrderItems ■■■(many:1)■■ Products
• Each Customer can have many Orders
• Each Order can have many OrderItems
• Each OrderItem references one Product
• Revenue = [Link] × [Link]
How It All Works Together (Flow)
Ste
p What Happens File Responsible
1 User types a question in English [Link]
2 Customer name detected; pronouns resolved (he/she → name) [Link]
3 Rule-based check — if pattern matches, SQL is returned directly llm_sql.py
4 If no rule matches, prompt is sent to LLaMA 3 AI via Ollama llm_sql.py
5 Generated SQL is cleaned and displayed on screen llm_sql.py
6 SQL is executed on [Link] (SELECT only) query_runner.py
7 If SQL errors, fix_sql() asks AI to self-correct (up to 2 retries) llm_sql.py
8 Results shown as table; CSV download + Plotly chart offered [Link]
Key Concepts to Know for Presentation
• Text-to-SQL
The core idea of the project — converting natural language questions into SQL queries automatically using
AI.
• LLM (Large Language Model)
LLaMA 3 is the AI model used. It was trained on large amounts of text and code, so it understands how to
write SQL given a schema and examples.
• Ollama
A tool that lets you run AI models like LLaMA 3 locally on your own computer without needing internet or
cloud services.
• Few-Shot Prompting
The technique used in the prompt — giving the AI a few examples (Q&A pairs) so it learns the pattern and
generates correct SQL.
• Self-Correction / Retry Logic
If the AI's SQL fails, the error is fed back to the AI so it can fix its own mistake — up to 2 attempts.
• Foreign Key / Relational DB
Tables are linked via foreign keys so you can JOIN them — e.g., finding which customer bought which
product.
• Session State (Streamlit)
Streamlit's st.session_state stores data between user interactions — used here to remember the last
customer name for pronoun resolution.
• Streamlit
A Python library that turns Python scripts into interactive web apps instantly — no HTML/CSS/JavaScript
needed.
Major Project | Abhimanyu Kumar · Amit Kumar · Ankit Pandey | CSE