Making it easy to query APIs via SQL
Project description
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_tableInstallation
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.38.1-r1/apsw-3.38.1-r1.zip \
--global-option=fetch --global-option=--version --global-option=3.38.1 --global-option=--all \
--global-option=build --global-option=--enable-all-extensionsHow 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 countryYou 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
CSV files
Pandas dataframes
GitHub (currently only pull requests, but other endpoints can be easily added)
System information (currently only CPU usage, but other resources 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.
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for shillelagh-1.0.15-py2.py3-none-any.whl
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 954c7fb7247917adadd7153da15ac5aff993f6771c32037450e09a86b90098c3 |
|
| MD5 | 70b701b46b8562c20b890eb29d9861c4 |
|
| BLAKE2b-256 | bac685044c767c602ea911784c15e1b650fc88c0208e7662a992713fb9cf8e64 |
