How devbm7/text2sql works
Project type and purpose
• Text2SQL is a full-stack, single-page interactive data-query application built with Streamlit (frontend + lightweight Python backend).
• Its purpose is to let users upload a CSV, ask questions in natural language, convert those questions to SQL via an LLM (Ollama), run the SQL against a SQLite database, and display results.
File structure
• Top level:
• README.md – project overview and usage
• main.py – entrypoint for the Streamlit app, orchestrates all stages
• data.csv – example or sample CSV data
• db.sqlite – generated SQLite database file
• notebook.ipynb – exploratory analysis or prototyping
• No deep subdirectories—logic is contained in main.py and imported libraries.
Key components from README
• Frontend: Streamlit interface for file upload, question input, result display
• CSV ingestion → automatic SQLite DB creation
• Question caching to avoid repeated LLM calls
• Ollama LLM service for NL→SQL
• Pandas for data manipulation, LangChain for prompt handling, scikit-learn (used for caching similarity)
Main components
a. Streamlit UI (frontend)
• File uploader widget
• Text input widget for natural-language questions
• Result display table
b. Backend application logic (main.py)
• CSV → SQLite ingestion module
• Cache layer (question → SQL)
• LLM interface module (calls Ollama)
• SQL execution module (runs against SQLite)
• Result formatting & return
c. Data storage
• SQLite database
d. External service
• Ollama LLM server (llama3.1:8b model)
Relationships and data flows
1) User uploads CSV via Streamlit → invoke ingestion module → populate SQLite
2) User enters NL question → cache lookup
• Hit → retrieve cached SQL
• Miss → send prompt to Ollama → receive SQL, store in cache
3) SQL → execute on SQLite → fetch result set
4) Results → format and send back to Streamlit UI → render table
Architectural patterns and important design principles
• Single-page interactive app (UI and backend in one process)
• Modular pipeline: ingestion, caching, LLM translation, query execution
• Separation of concerns: UI vs business logic vs data/persistence vs external LLM
• Use of external microservice (Ollama) for heavy LLM inference
Technologies, frameworks, and libraries
• Streamlit (web UI)
• Python (3.7+) as runtime
• Pandas for CSV handling
• SQLite3 for embedded database
• LangChain-community for prompt management
• Ollama LLM server
• scikit-learn for similarity search (cache)