QueryBuilder

QueryBuilder is a modular backend system and CLI tool that allows you to run read-only SQL queries on CSV/Excel files locally, without setting up a traditional database. It provides a CLI-first approach, with Streamlit GUI planned for future releases.

This README focuses on the backend and CLI usage; GUI is pending.

---

1. Overview

QueryBuilder allows users to:

• Load CSV/Excel files into a local SQL database
• Execute read-only SQL queries
• Inspect tables and schemas
• Export query results to CSV or Excel

The backend is engine-agnostic, currently supporting SQLite, with DuckDB planned. Query execution and connection handling are fully implemented using raw drivers with custom ORM-like wrappers, connection pooling, and resource management.

Key Features

• CLI-first usage, with static and interactive modes
• Engine-agnostic backend
• Read-only query validation
• Table and schema inspection
• Export query results
• Explicit cleanup of resources
• Modular, extensible architecture

---

2. Backend Architecture

Project Structure

backend/
│
├── main.py               # Entry point and QuerySession
├── __init__.py
│
├── core/                 # Core utilities, constants, logging, exceptions
│   ├── config.py
│   ├── constants.py
│   ├── exceptions.py
│   ├── logger.py
│   └── utils.py
│
├── engines/              # Database engines
│   ├── base_engine/
│   │   ├── loader.py     # Abstract EngineLoader
│   │   ├── queries.py    # Abstract EngineQuery
│   │   └── __init__.py
│   │
│   └── sqlite/
│       ├── loader.py     # SQLite EngineLoader implementation
│       ├── queries.py    # SQLite EngineQuery implementation
│       └── __init__.py
│
├── loader/               # File loaders
│   ├── loader.py         # FileLoader base + CSV/Excel loaders
│   ├── loader_helper.py  # Utilities for cleaning table/column names
│   └── __init__.py
│
└── query_builder/        # QueryBuilder wrapper
    ├── query.py
    ├── query_helper.py   # Regex patterns for query validation
    └── __init__.py

frontend/
│
├── cli/
│   ├── cli_static.py     # Static CLI using argparse
│   ├── constants.py      # Supported engines
│   ├── qb_cli.py         # Interactive CLI
│   └── __init__.py
│
└── gui/
    └── run.py            # Streamlit GUI (pending update)

---

Core Modules

Module	Responsibility
core/config.py	Configuration (DB mappings, defaults)
core/constants.py	Constants (DB names, validation)
core/exceptions.py	Custom exceptions (FileTypeNotSupportedError, DataBaseEngineNotFound)
core/logger.py	Centralized logging
core/utils.py	General utility functions

Engines

• EngineLoader: Manages connections, connection pooling, and cleanup
• EngineQuery: Executes queries and fetches results
• SQLite implementation available; DuckDB planned
• Backend uses raw drivers, not ORM; ORM-like wrappers created internally

Loader

• FileLoader (abstract): Loads files into database, tracks table names
• FileLoaderCsv: Loads single CSV into table table
• FileLoaderExcel: Loads each sheet into separate table, supports password-protected Excel files

QueryBuilder

• Wraps EngineQuery, validates queries (read-only, SELECT only)
• Returns pandas DataFrame
• Supports table listing, schema inspection, explain plans

Main (QuerySession)

• Unified interface for CLI or GUI

• Manages engine, loader, query builder

• Methods:

  • execute(query)
  • tables()
  • schema(table_name)
  • explain(query)
  • export(last_result, out_file)
  • cleanup()

---

3. CLI Usage

3.1 Static CLI (argparse)

Run queries directly from command line without entering interactive shell.

python -m frontend.cli.cli_static [OPTIONS]

Options

Option	Alias	Description
--file	-f	Path to CSV/Excel file
--query	-q	SQL query to execute
--db_engine	-db	SQL engine to use (sqlite, duckdb, auto)
--cleanup	-	Cleanup database after execution
--store	-s	Save query results to result_set.csv

Examples

python -m frontend.cli.cli_static -f data.csv -q"SELECT * FROM table LIMIT 10" -db sqlite
python -m frontend.cli.cli_static -f data.csv -q"SELECT * FROM table" -s
python -m frontend.cli.cli_static -f data.csv -q"SELECT * FROM table" --cleanup

3.2 Interactive CLI (qb_cli)

Provides terminal-style environment for dynamic queries, schema exploration, and exports.

python -m frontend.cli.qb_cli

Commands

Command	Description
open <file> <db>	Load CSV/Excel file using specified DB engine (use quotes for spaces)
tables	List all loaded tables
schema [table]	Show schema for table (or all tables if omitted)
select <sql>	Execute SQL query
explain <sql>	Show query plan
export <file>	Export last query result
cleanup	Cleanup session and database
clear	Clear terminal
help	Show commands
`exit	quit`	Exit CLI

Notes

• Only read-only queries allowed (SELECT, SHOW, DESCRIBE)
• Tracks last query result for exporting
• Always use cleanup() to free resources

---

4. Pending / Future Work

• Full DuckDB integration
• Streamlit GUI with engine selector, query explorer, schema explorer, and export
• Multi-file joins
• Optimization for large datasets
• Interactive CLI enhancements

---

5. References

• System Documentation

• Backend Technical Documentation

• CLI User Guide