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

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.4.tar.gz (287.1 kB view details)

Uploaded Source

Built Distribution

shillelagh-1.0.4-py2.py3-none-any.whl (70.0 kB view details)

Uploaded Python 2 Python 3

File details

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

File metadata

  • Download URL: shillelagh-1.0.4.tar.gz
  • Upload date:
  • Size: 287.1 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.4.tar.gz
Algorithm Hash digest
SHA256 3be1152765c0c88bb576d5e27b6cc511e45a17b8f01ed10f6b78aa56d82d09f5
MD5 2ec1eb52d646ddcb569728be43cbdcb2
BLAKE2b-256 1ed42346225b4408e9cef19ff04c6d2334079510b97f7e1be405f97f2e54843d

See more details on using hashes here.

Provenance

File details

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

File metadata

  • Download URL: shillelagh-1.0.4-py2.py3-none-any.whl
  • Upload date:
  • Size: 70.0 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.4-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 bc382d4c2cba08d7dbc28901fb53696856615dd2be8a647c3ed9852296aa1054
MD5 c741ff6fd0446b43c118f8010e022dfb
BLAKE2b-256 0f3b1af13f0506d0e03a1123d306183ed084629fb6093b0c0604072962767561

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