shillelagh

Shillelagh (ʃɪˈleɪlɪ) is a Python library and CLI that allows you to query many resources (APIs, files, in memory objects) using SQL. It’s both user and developer friendly, making it trivial to access resources and easy to add support for new ones.

Learn more on the documentation.

The library is an implementation of the Python DB API 2.0 based on SQLite (using the APSW library):

from shillelagh.backends.apsw.db import connect

connection = connect(":memory:")
cursor = connection.cursor()

query = "SELECT * FROM a_table"
for row in cursor.execute(query):
    print(row)

There is also a SQLAlchemy dialect:

from sqlalchemy import text
from sqlalchemy.engine import create_engine

engine = create_engine("shillelagh://")
connection = engine.connect()

query = "SELECT * FROM a_table"
for row in connection.execute(text(query)):
    print(row)

And a command-line utility:

$ shillelagh
🍀> SELECT * FROM a_table

There is also an experimental backend that uses Postgres with the Multicorn2 extension, and another one using sqlglot.

To run the Postgres backend, first install the additional dependencies:

$ pip install 'shillelagh[multicorn]'
$ pip install 'multicorn @ git+https://github.com/pgsql-io/multicorn2.git@v2.5'

Then run:

from shillelagh.backends.multicorn.db import connect

connection = connect(
    user="username",
    password="password",
    host="localhost",
    port=5432,
    database="examples",
)

Or:

from sqlalchemy import create_engine
engine = create_engine("shillelagh+multicorn2://username:password@localhost:5432/examples")

For the sqlglot backend you can use shillelagh+sqlglot:// as the connection string, or import connect() from shillelagh.backends.sqlglot.db.

Why SQL?

Sharks have been around for a long time. They’re older than trees and the rings of Saturn, actually! The reason they haven’t changed that much in hundreds of millions of years is because they’re really good at what they do.

SQL has been around for some 50 years for the same reason: it’s really good at what it does.

Why “Shillelagh”?

Picture a leprechaun hitting APIs with a big stick so that they accept SQL.

How is it different?

Shillelagh allows you to easily query non-SQL resources. For example, if you have a Google Spreadsheet you can query it directly as if it were a table in a database:

SELECT country, SUM(cnt)
FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0"
WHERE cnt > 0
GROUP BY country

You can even run INSERT/DELETE/UPDATE queries against the spreadsheet:

UPDATE "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0"
SET cnt = cnt + 1
WHERE country != 'BR'

Queries like this are supported by adapters. Currently Shillelagh has the following adapters:

Name	Type	URI pattern	Example URI
CSV	File/API	/path/to/file.csv; http(s)://*	/home/user/sample_data.csv
Datasette	API	http(s)://*	https://global-power-plants.datasettes.com/global-power-plants/global-power-plants
Generic JSON	API	http(s)://*	https://api.stlouisfed.org/fred/series?series_id=GNPCA&api_key=XXX&file_type=json#$.seriess[*]
Generic XML	API	http(s)://*	https://api.congress.gov/v3/bill/118?format=xml&offset=0&limit=2&api_key=XXX#.//bill
GitHub	API	https://api.github.com/repos/${owner}/{$repo}/pulls	https://api.github.com/repos/apache/superset/pulls
GSheets	API	https://docs.google.com/spreadsheets/d/${id}/edit#gid=${sheet_id}	https://docs.google.com/spreadsheets/d/1LcWZMsdCl92g7nA-D6qGRqg1T5TiHyuKJUY1u9XAnsk/edit#gid=0
HTML table	API	http(s)://*	https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population
Pandas	In memory	Any variable name (local or global)	my_df
S3	API	s3://bucket/path/to/file	s3://shillelagh/sample_data.csv
Socrata	API	https://${domain}/resource/${dataset-id}.json	https://data.cdc.gov/resource/unsk-b7fc.json
System	API	system://${resource}	system://cpu?interval=2
WeatherAPI	API	https://api.weatherapi.com/v1/history.json?key=${key}&q=${location}	https://api.weatherapi.com/v1/history.json?key=XXX&q=London

There are also 3rd-party adapters:

A query can combine data from multiple adapters:

INSERT INTO "/tmp/file.csv"
SELECT time, chance_of_rain
FROM "https://api.weatherapi.com/v1/history.json?q=London"
WHERE time IN (
  SELECT datetime
  FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=1648320094"
)

The query above reads timestamps from a Google sheet, uses them to filter weather data from WeatherAPI, and writes the chance of rain into a (pre-existing) CSV file.

New adapters are relatively easy to implement. There’s a step-by-step tutorial that explains how to create a new adapter to an API or filetype.

Installation

Install Shillelagh with pip:

$ pip install 'shillelagh'

You also need to install optional dependencies, depending on the adapter you want to use:

$ pip install 'shillelagh[console]'        # to use the CLI
$ pip install 'shillelagh[genericjsonapi]' # for Generic JSON
$ pip install 'shillelagh[genericxmlapi]'  # for Generic XML
$ pip install 'shillelagh[githubapi]'      # for GitHub
$ pip install 'shillelagh[gsheetsapi]'     # for GSheets
$ pip install 'shillelagh[htmltableapi]'   # for HTML tables
$ pip install 'shillelagh[pandasmemory]'   # for Pandas in memory
$ pip install 'shillelagh[s3selectapi]'    # for S3 files
$ pip install 'shillelagh[systemapi]'      # for CPU information

Alternatively, you can install everything with:

$ pip install 'shillelagh[all]'

Release files for shillelagh 1.4.5

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for shillelagh 1.4.5
File	Size	Uploaded
shillelagh-1.4.5.tar.gz	2.5 MB	Jul 30, 2026	Details

Built distribution (wheel)

Table of built distributions (wheels) for shillelagh 1.4.5
File	Interpreter	ABI	Platform	Reset
shillelagh-1.4.5-py2.py3-none-any.whl 130.0 kB Jul 30, 2026	Python 3, Python 2	none	any	Details

Total release size: 2.7 MB

Release files / shillelagh-1.4.5.tar.gz

Download URL	shillelagh-1.4.5.tar.gz
Size	2.5 MB
Tags	Source
SHA-256 checksum How to use checksums	`d22b5f6e357c0d34820179e9d62ac3ed8a7b46f996dd42071c6362e4746a9029`
BLAKE2b-256 checksum How to use checksums	`96a440aa40f38f0aade9185f625406411d933e771ce0349f0dcf7d0954d0e54d`
Upload date	Jul 30, 2026
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/6.2.0 CPython/3.9.25`

Release files / shillelagh-1.4.5-py2.py3-none-any.whl

Download URL	shillelagh-1.4.5-py2.py3-none-any.whl
Size	130.0 kB
Tags	Python 2 Python 3
SHA-256 checksum How to use checksums	`bb78ce2fa9e25241b8675ee172d6bc89f83db73a565ce34cdf34bb7139dc128c`
BLAKE2b-256 checksum How to use checksums	`7f900f4faaf5fbbefe34cf74df42897c57df1e47a79a98b30211ce58c2153958`
Upload date	Jul 30, 2026
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/6.2.0 CPython/3.9.25`