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_table
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:
Google Spreadsheets
CSV files
A query can combine data from multiple adapters:
INSERT INTO "csv:///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.
Project details
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.0rc3-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 927925c08879777438a86c3081aab4566866f42ec9164159b797c5e5c7d8e1fe |
|
MD5 | 718e91c044b72eb0a08885d73fb5a1e0 |
|
BLAKE2b-256 | 1bf88384abd7937411461ff2a6d20d1defa63d2a6053d852220833d446f5a3f9 |