Ask a database a question — out loud. A voice-driven Speech-to-SQL system that lets non-technical users query a relational database in plain English and get back interactive charts.
Final project for the Data Management (Mod. B) module of the MSc Data Science programme at the University of Naples Federico II. Awarded 30/30.
The Streamlit dashboard — speak a question, watch the SQL and chart appear.
Auto-generated log-scaled choropleth for "how many customers are in each country?"
You ask a question by voice, upload, or typing. The system:
- Transcribes the audio locally with OpenAI Whisper.
- Generates a T-SQL query with Google Gemini, which is given the live database schema.
- Validates the query with sqlglot — only read-only
SELECTs can reach the database. - Executes it against Microsoft SQL Server.
- Visualizes the result: it picks a chart type from the shape of the data (world map, bar, line, scatter, or metric).
Everything happens in seconds, and there's a CSV export plus a session history.
Voice / Upload / Text
│
▼
Whisper (base.en, local) ── audio → text
│
▼
Google Gemini 2.5 Flash ── text → T-SQL (schema-aware)
│
▼
sqlglot validation ── SELECT-only safety gate
│
▼
SQL Server 2022 ── runs query, returns rows
│
▼
Streamlit dashboard ── table + auto-chart + CSV
Each stage is a separate module (pipeline/), so components can be tested and swapped in isolation. The pipeline is orchestrated by a single PipelineResult object that carries the transcript, generated SQL, resulting DataFrame, timings, and any error up to the UI.
| Layer | Technology |
|---|---|
| Speech recognition | OpenAI Whisper (base.en), local |
| Text-to-SQL | Google Gemini 2.5 Flash (via google-genai) |
| Database | Microsoft SQL Server 2022 |
| DB access | pyodbc + SQLAlchemy |
| SQL safety | sqlglot (T-SQL dialect) |
| Dashboard | Streamlit |
| Visualization | Plotly (choropleth, bar, line, scatter) |
| Data processing | pandas |
| Runtime | Python 3.11 · Windows 11 |
Built on the UCI Online Retail dataset (Chen, 2015) — a real record of ~540,000 transactions from a UK online retailer, December 2010 to December 2011. Chosen for being real, citable, and rich enough to support meaningful joins and aggregations.
The raw file is cleaned by a documented set of rules (null customer IDs, cancellations, non-positive quantities and prices) — retaining 397,884 rows (73.4%) — then normalized into a five-table schema in third normal form:
countries(37) · lookup with regioncustomers(4,338) · linked to a country, with purchase totalsproducts(3,665) ·stock_codekey, description, price, derived categoryinvoices(18,532) · one row per transactioninvoice_items(387,841) · line items linking invoices ↔ products
Cleaning and normalization live in db/loader.py; the schema and keys are defined in db/01_setup_database.sql.
Schema-aware prompting. The prompt is built at startup from INFORMATION_SCHEMA and the system catalog views (schema_introspector.py), so Gemini always sees the real tables, columns, keys, and foreign-key links. If the schema changes, the prompt updates — no code edits needed.
SELECT-only safety gate. Every generated query is parsed by sqlglot in the T-SQL dialect and rejected if it isn't a single SELECT (or a CTE / subquery / union of them). A keyword blocklist also catches INSERT, DROP, EXEC, MERGE, GRANT, and — deliberately — INTO, so SELECT ... INTO can't create tables through a SELECT. Enforced in Python; independent of database permissions.
Retry with model fallback. Gemini calls retry with exponential backoff (2s, 4s), then fall back down a chain of alternate models (gemini-2.5-flash → gemini-2.5-flash-lite → gemini-flash-latest) before reporting failure. Handles free-tier 503 spikes transparently.
Graceful degradation. The optional AI extras (plain-English SQL explanation, follow-up suggestions in enrich.py) return a (value, status) pair so the UI can show why an extra was skipped (ok, no_key, busy, error). If they fail, the core flow is never affected.
Auto-charting from result shape. auto_chart.py classifies each result column as an identifier (like customer_id) or a measure, then picks a visualization: a country + measure becomes a choropleth (log-scaled, because the UK dominates the data), a date + measure becomes a line, a category + measure becomes a bar with tilted labels, two measures become a scatter, a single value becomes a highlighted metric.
- Windows 10/11 (or adapt paths for Linux/macOS)
- Python 3.11
- SQL Server 2022 (Developer edition is free; Windows Authentication)
- ODBC Driver 18 for SQL Server
- ffmpeg on
PATH(for Whisper) - A Google Gemini API key (free tier is enough)
# 1. Clone
git clone https://github.com/anibihakeem/AskTheLedger.git
cd AskTheLedger
# 2. Create a virtual environment
python -m venv .venv
.venv\Scripts\activate # Windows
# source .venv/bin/activate # macOS/Linux
# 3. Install dependencies
pip install -r requirements.txtCreate a .env file in the project root (copy from .env.example):
DB_SERVER=localhost
DB_NAME=Voice2Query
DB_DRIVER=ODBC Driver 18 for SQL Server
GEMINI_API_KEY=your_key_hereIn SSMS, run in order:
db/01_setup_database.sql— creates the database, tables, primary keys, foreign keys.db/02_create_users.sql— creates the read-only application role.
Then populate it:
python db/loader.pyThis downloads the UCI dataset (~22 MB), cleans it, and bulk-loads all 397,884 rows via fast_executemany in FK-safe order (~1–2 minutes).
# Optional: sanity-check the backend
python pipeline/test_pipeline.py
# Launch the dashboard
streamlit run pipeline/app.pyOpens at http://localhost:8501.
AskTheLedger/
├── db/
│ ├── 01_setup_database.sql # schema, keys, indexes
│ ├── 02_create_users.sql # read-only role
│ ├── loader.py # download, clean, normalize, load
│ └── test_connection.py
├── pipeline/
│ ├── app.py # Streamlit dashboard
│ ├── config.py # single source of truth for settings
│ ├── schema_introspector.py # live schema → prompt
│ ├── text_to_sql.py # Gemini call + retry/fallback
│ ├── sql_executor.py # sqlglot validation + pd.read_sql
│ ├── transcriber.py # Whisper wrapper
│ ├── auto_chart.py # shape-aware chart selection
│ ├── enrich.py # optional AI extras (fail-silent)
│ ├── pipeline.py # orchestrator + PipelineResult
│ └── test_pipeline.py # seven representative test queries
├── docs/
│ ├── report.pdf # full project report
│ └── screenshots/ # dashboard images
├── .streamlit/config.toml # theme
├── .env.example
├── requirements.txt
└── README.md
The system is tested against a fixed set of representative questions (see pipeline/test_pipeline.py), each exercising a different SQL feature:
| Question | SQL features |
|---|---|
| Top 5 best-selling products by total quantity | JOIN, SUM, GROUP BY, ORDER BY, TOP |
| How many customers are in each country? | JOIN, COUNT, GROUP BY |
| Average order value by country | JOIN, AVG, GROUP BY |
| The 10 customers who spent the most | JOIN, SUM, GROUP BY, ORDER BY, TOP |
| Which product categories generate the most revenue? | JOIN, SUM, GROUP BY, ORDER BY |
| How many invoices were created in December 2011? | WHERE (date range), COUNT |
| Products that have never been sold | LEFT JOIN / NOT IN subquery |
A few decisions worth calling out, since they shaped the project:
Pipeline architecture over end-to-end. End-to-end speech-to-SQL models exist (SpeechSQLNet, Wav2SQL) but require training data that isn't practical to gather for a course project. A modular pipeline is testable stage-by-stage and more interpretable, at the cost of potential error cascades between stages — which the schema-aware prompt mitigates by anchoring Gemini to real table and column names.
Whisper over Speechmatics. Whisper runs locally: free, offline-capable, and predictable latency. Speechmatics is a strong commercial cloud service with features like speaker diarization, but the cloud dependency and per-minute cost weren't justified for a single-speaker demo.
Gemini + schema-aware prompting over a dedicated text-to-SQL platform (e.g. WrenAI). A hosted general model with a good prompt reaches the required quality with far less infrastructure than standing up a dedicated platform, and integrates in a few lines of code.
SQL Server was a fit, not a superiority claim. Any mature relational database would have worked. SQL Server was chosen for native integration with the SSMS/T-SQL toolchain the course uses, plus the bulk-load and identity features the loader relies on. PostgreSQL would have been equally valid.
- Depends on the hosted Gemini API for text-to-SQL. Retry and fallback mitigate this, but don't eliminate the dependency.
- English-only, to match the dataset. Multilingual support would require a translation layer.
- Ambiguous questions can produce a reasonable but unintended reading — inherent to any natural-language interface.
- Product categories are derived from descriptions by keyword matching, so occasional miscategorization is possible.
pd.read_sqlloads the full result into memory. Fine for the analytical queries here (which aggregate down), but not a streaming solution for very large result sets.
- Database-aware correction of transcription errors, to reduce cascade risk
- Multilingual support via a translation front-end
- Semantic caching of similar questions using embeddings
- Cloud deployment for browser-only access
- Self-hosted open-model fallback (Llama, Mistral) to remove the hosted-API dependency
- Dataset: Chen, D. (2015). Online Retail [Dataset]. UCI Machine Learning Repository. doi:10.24432/C5BW33
- Speech recognition: OpenAI Whisper
- Text-to-SQL: Google Gemini
- SQL parsing: sqlglot
MIT — see LICENSE.