Skip to main content

Making it easy to query APIs via SQL

Project description

https://coveralls.io/repos/github/betodealmeida/shillelagh/badge.svg?branch=master Documentation Status https://badge.fury.io/py/shillelagh.svg PyPI - Python Version

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

Installation

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-extensions

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

  • WeatherAPI

  • Socrata Open Data API

  • CSV files

  • Pandas dataframes

  • Datasette tables

  • 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.

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

shillelagh-1.0.14.tar.gz (2.3 MB view details)

Uploaded Source

Built Distribution

shillelagh-1.0.14-py2.py3-none-any.whl (80.8 kB view details)

Uploaded Python 2 Python 3

File details

Details for the file shillelagh-1.0.14.tar.gz.

File metadata

  • Download URL: shillelagh-1.0.14.tar.gz
  • Upload date:
  • Size: 2.3 MB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.0 CPython/3.9.13

File hashes

Hashes for shillelagh-1.0.14.tar.gz
Algorithm Hash digest
SHA256 8c26010c84dc6d1cb188bf55d2229ad82ed41ce2ba0e50453e09138fcbdbfbbd
MD5 fb0de0e7594ca8c375540b5e485c8c9f
BLAKE2b-256 beb5131c091be2dc5f63095314089801791b8dd53c9babc9cff9beab0084b5d9

See more details on using hashes here.

Provenance

File details

Details for the file shillelagh-1.0.14-py2.py3-none-any.whl.

File metadata

File hashes

Hashes for shillelagh-1.0.14-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 fdd471d6b3d91a6021aa2832131612b1132ed358396a1dbcc571b9a042b51e72
MD5 7500745c4f98b7fc34f2f3859391f9cf
BLAKE2b-256 821a4a08f1ec924f965071b5d8693e9c7110b9315bd9867a21d98202c374655a

See more details on using hashes here.

Provenance

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page