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.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-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
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.4-py2.py3-none-any.whl
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | bc382d4c2cba08d7dbc28901fb53696856615dd2be8a647c3ed9852296aa1054 |
|
| MD5 | c741ff6fd0446b43c118f8010e022dfb |
|
| BLAKE2b-256 | 0f3b1af13f0506d0e03a1123d306183ed084629fb6093b0c0604072962767561 |
