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

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

shillelagh-1.0.0rc3.tar.gz (155.4 kB view details)

Uploaded Source

Built Distribution

shillelagh-1.0.0rc3-py2.py3-none-any.whl (58.3 kB view details)

Uploaded Python 2 Python 3

File details

Details for the file shillelagh-1.0.0rc3.tar.gz.

File metadata

  • Download URL: shillelagh-1.0.0rc3.tar.gz
  • Upload date:
  • Size: 155.4 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.4.1 importlib_metadata/4.6.0 pkginfo/1.7.0 requests/2.25.1 requests-toolbelt/0.9.1 tqdm/4.61.1 CPython/3.9.4

File hashes

Hashes for shillelagh-1.0.0rc3.tar.gz
Algorithm Hash digest
SHA256 27e72f4a2b06c02ee464cc217f1409d788738ef4de241c32ee7f62f368303d49
MD5 fade159ee21e2888ff6b783339629d0a
BLAKE2b-256 c6d23488f28d17441e277834da13e83eaea67408809745f5fda2aa4755f14bca

See more details on using hashes here.

Provenance

File details

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

File metadata

  • Download URL: shillelagh-1.0.0rc3-py2.py3-none-any.whl
  • Upload date:
  • Size: 58.3 kB
  • Tags: Python 2, Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.4.1 importlib_metadata/4.6.0 pkginfo/1.7.0 requests/2.25.1 requests-toolbelt/0.9.1 tqdm/4.61.1 CPython/3.9.4

File hashes

Hashes for shillelagh-1.0.0rc3-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 927925c08879777438a86c3081aab4566866f42ec9164159b797c5e5c7d8e1fe
MD5 718e91c044b72eb0a08885d73fb5a1e0
BLAKE2b-256 1bf88384abd7937411461ff2a6d20d1defa63d2a6053d852220833d446f5a3f9

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