shelect

I've always wanted a simple commandline tool to be able to run SQL for ad-hoc analysis of data in local files.

So I made shelect, which lets you write SQL to query (and join!) the contents of local JSON / CSV files on disk.

Here's how it can be used:

tl;dr: Write SQL that reads from tables like "./my-file.csv" and shelect does the work of populating that table from data on disk at ./my-file.csv.

Shelect, when given a query as an argument, executes that query as SQL via SQLite.

Before executing your queries, it looks for tables you've accessed, and loads files mapping to those table names. All columns are TEXT columns.

It can also output the tables as CSV / JSON.

$ shelect --help
usage: shelect [-h] [--format {csv,json,table}] [query ...]

Run SELECT queries on local CSV/JSON files using SQLite.

positional arguments:
  query                 SQL SELECT statement referencing local files

options:
  -h, --help            show this help message and exit
  --format, -o {csv,json,table}
                        Output format: table (default if tty), csv (default
                        otherwise), json

Shelect is a python program, so you can install it via pip (or pipx, if you have it).

Via pip:

$ pip3 install shelect

Via pipx:

$ pipx install shelect

The source code can be found here: https://github.com/sufianrhazi/shelect

The python package is on pypi here: https://pypi.org/project/shelect/

In my day to day work, I pretty much live in a commandline terminal. I think there are a whole bunch of missing small tools meant to improve the day to day quality of life if you work in the commandline.

This is one of those tools, I'll be making more.

A few things to keep in mind about shelect:

• It is meant to be a small, simple tool to write ad-hoc SQL against local files that can fit in memory
• It uses SQLite under the hood, so you can use the syntax & functions supported by SQLite
• It treats all data as TEXT data
• JSON is currently limited to an array of key-value objects, where each object must have the same set of keys

I bootstrapped this tool with the help of ChatGPT. After spending an hour or so making the proof-of-concept, I used my actual brain to clean up and better organize the code, make it easily testable, and add some additional nice-to-have features.

Big thanks to SQLite for being a fantastic tool.