Shillelagh (ʃɪˈleɪlɪ) 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.engine import create_engine
engine = create_engine("shillelagh://")
connection = engine.connect()
query = "SELECT * FROM a_table"
for row in connection.execute(query):
print(row)
And a command-line utility:
$ shillelagh
sql> SELECT * FROM a_table
Install Shillelagh with pip
:
$ pip install 'shillelagh'
This will install an unofficial APSW package from the Python package index. It's highly recommend to install a newer version:
$ pip install https://github.com/rogerbinns/apsw/releases/download/3.36.0-r1/apsw-3.36.0-r1.zip \
--global-option=fetch --global-option=--version --global-option=3.36.0 --global-option=--all \
--global-option=build --global-option=--enable-all-extensions
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:
- Google Spreadsheets
- WeatherAPI
- Socrata Open Data API
- CSV files
- Pandas dataframes
- Datasette tables
- GitHub (currently only pull requests, but other endpoints can be easily added)
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.