xlql 0.3.4

No project description provided

XLQL

A Lightweight Local DBMS for CSV Data Using DuckDB

XLQL is a command-line tool written in Python that allows you to manage databases stored as folders containing CSV files and run SQL queries on them using DuckDB, without the need for a extensive hassle for setting up traditional database server. Also sometime you will just hate working with spreadsheets

It is perfect for quick analysis, structured storage of tabular files, and performing SQL-like operations locally on spreadsheets.

---

Features

• Create and manage databases (as folders) locally.
• Manage tables (CSV files) inside each database.
• Run SQL queries directly on CSV files using DuckDB.
• Pretty-print query results with Tabulate.
• Export query results to multiple formats (CSV, JSON, Parquet).
• Interactive menus for selecting databases/tables via Questionary.
• Fully offline — works without any external DB connection.

---

Installation

pip install xlql

Available Commands

Below are the commands supported in Version 1:

---

1️. createdb

Creates a new database. When running this command for the first time you will need to specify a base db path. Now this is the path where you databases folder will be present inside which your databases will be added as new folders.

xlql createdb

2. listdb

Lists all the databases you have.

xlql listdb

3. dropdb

Drops the selected database. After running the command you will get a menu to choose the database from.

xlql dropdb

4. insert

Inserts a table in the selected database. After you run the command you get a menu to choose the database. Further add the name for the table and absolute path of the csv file.

xlql insert 

5. list

Shows all the tables in mydbname database.

xlql list [mydbname]

6. droptable

Deletes the selected table from the specified database.. After you run the command you the menu to choose the table to be deleted from.

xlql droptable [mydbname]

7. show

Shows the first N rows of specified table. The command can be run in three ways.

• If the db name and table name are not specified user gets a menu for both to choose from.
  • [xlql show]
• If only the table name is not specified user gets a menu to choose table from.
  • [xlql show dbname]
• If both the dbname and table name are specified user gets a prompt to enter the number of rows he want to see.
  • [xlql show dbname tablename]

xlql show [mydbname] [mytablename]

8. desc

Describes the schema of the table. Similar to xlql shpw command this can also run in three scenarios.

• If the db name and table name are not specified user gets a menu for both to choose from.
  • [xlql show]
• If only the table name is not specified user gets a menu to choose table from.
  • [xlql desc dbname]
• If both the dbname and table name are specified command runs fine.
  • [xlql desc dbname tablename]

xlql desc [mydbname] [mytablename]

9. sql

Helps user run SQL queries on the inserted tables. User is supposed to make a .sql file and write their query in that file. Also please use the table name you specified and try to follow general SQL syntax rules for best outcomes.

After running the command xlql sql you will be prompted to answer these question

• Choose the DB.
• Path to the query file.
• If you want to export your data?
• What format you want to export to?

xlql sql

System Architecture Overview

XLQL is designed as a lightweight, file-based database management tool for working with CSV datasets using SQL.
Its architecture prioritizes simplicity, portability, and minimal setup while providing powerful querying capabilities.

---

1. Databases

• A database in XLQL is represented as a directory within the configured base path: {base_path}/databases/{database_name}/
• This design removes the need for complex database servers or installation steps.
• Ensures easy portability and compatibility with version control systems and also customisations.

---

2. Tables

• A table is currently implemented as a CSV file stored inside a database directory: {base_path}/databases/{database_name}/{table_name}.csv

• Standard CSV format ensures universal compatibility and avoids vendor lock-in.

• Future updates will extend support to additional formats (e.g., Excel, Parquet).

---

3. Query Engine

• DuckDB is used as the underlying query processor.
• Executes SQL directly on CSV files without importing data into a separate database.
• Key advantages:
  • Embedded, lightweight, and serverless
  • Full SQL syntax support
  • Optimized for analytical workloads

---

4. Interactive Menus

• Questionary provides interactive selection menus for databases and tables.
• Enhances user experience by eliminating the need to remember complex file names.
• Particularly useful when working with multiple datasets or long file names.

---

5. Result Formatting

• Tabulate is used for clear, structured result presentation in the terminal.
• Produces professional-looking table outputs with multiple style options.
• Ensures readable query results even for large datasets.

---

6. Design Goals

• Minimal Setup – No infrastructure beyond your local file system.
• Portability – Easily copy, share, or version-control entire databases.
• SQL-Powered – Use familiar SQL syntax for data queries.
• Extensibility – Roadmap includes more file format support, quick operations, and an interactive SQL shell.

---

Example Directory Structure

base_path
└── databases/
    ├── sales_db/
    │      ├── orders.csv
    │      └── customers.csv
    └── hr_db/
             ├── employees.csv
             └── payroll.csv

---

TL;DR: It’s simple, fast, easy to setup and gets the job done. And if you ever find yourself explaining to someone that your “database engine” is actually a bunch of folders and CSV files…
Just smile confidently and say: "It’s a design choice and it was made in just 5 hours"