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 Cirrus CI - Base Branch Build Status 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.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

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:

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

This version

1.0.5

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.5.tar.gz (2.3 MB view details)

Uploaded Source

Built Distribution

shillelagh-1.0.5-py2.py3-none-any.whl (78.0 kB view details)

Uploaded Python 2 Python 3

File details

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

File metadata

  • Download URL: shillelagh-1.0.5.tar.gz
  • Upload date:
  • Size: 2.3 MB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.7.0 importlib_metadata/4.8.2 pkginfo/1.8.2 requests/2.26.0 requests-toolbelt/0.9.1 tqdm/4.62.3 CPython/3.9.9

File hashes

Hashes for shillelagh-1.0.5.tar.gz
Algorithm Hash digest
SHA256 f15eca71c3e384b753ebf0f5351e7411a890a4f720cf7d6c358d1e18e5290044
MD5 35a44b7751ac485599bbf92154dc1b91
BLAKE2b-256 1f5f2246cf700dcb2f9869496f6c796a11cfa24dbdc16a88ff86d0855bd23805

See more details on using hashes here.

Provenance

File details

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

File metadata

  • Download URL: shillelagh-1.0.5-py2.py3-none-any.whl
  • Upload date:
  • Size: 78.0 kB
  • Tags: Python 2, Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.7.0 importlib_metadata/4.8.2 pkginfo/1.8.2 requests/2.26.0 requests-toolbelt/0.9.1 tqdm/4.62.3 CPython/3.9.9

File hashes

Hashes for shillelagh-1.0.5-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 f900260fd988b9fcbcfc35e49eb45c406d9324012799d9a023d16179e2fee8cb
MD5 e4ba61d86f7daf21ca954836cc8a53e9
BLAKE2b-256 9bf54902e471030932ce8d6ac45c66ebb9dc20f50db834431ab2cb70e8d44aa6

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