Making it easy to query APIs via SQL

Navigation

Verified details

These details have been verified by PyPI
Maintainers
Avatar for roberto from gravatar.com roberto

Unverified details

These details have not been verified by PyPI
Project links
Meta
  • License: MIT License (mit)
  • Author: Beto Dealmeida
  • Requires: Python >=3.9
  • Provides-Extra: all, console, dbtmetricflowapi, docs, genericjsonapi, genericxmlapi, githubapi, gsheetsapi, holidaysmemory, htmltableapi, multicorn, pandasmemory, s3selectapi, systemapi, testing
Classifiers

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 docs/logo.png

Shillelagh (ʃɪˈleɪlɪ) is a Python library and CLI that allows you to query many resources (APIs, files, in memory objects) using SQL. It’s both user and developer friendly, making it trivial to access resources and easy to add support for new ones.

Learn more on the documentation.

The library 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

There is also an experimental backend that uses Postgres with the Multicorn2 extension. First, install the additional dependencies:

$ pip install 'shillelagh[multicorn]'
$ pip install 'multicorn @ git+https://github.com/pgsql-io/multicorn2.git@v2.5'

Then run:

from shillelagh.backends.multicorn.db import connect

connection = connect(
    user="username",
    password="password",
    host="localhost",
    port=5432,
    database="examples",
)

Or:

from sqlalchemy import create_engine
engine = create_engine("shillelagh+multicorn2://username:password@localhost:5432/examples")

Why SQL?

Sharks have been around for a long time. They’re older than trees and the rings of Saturn, actually! The reason they haven’t changed that much in hundreds of millions of years is because they’re really good at what they do.

SQL has been around for some 50 years for the same reason: it’s really good at what it does.

Why “Shillelagh”?

Picture a leprechaun hitting APIs with a big stick so that they accept SQL.

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:

Name

Type

URI pattern

Example URI

CSV

File/API

/path/to/file.csv; http(s)://*

/home/user/sample_data.csv

Datasette

API

http(s)://*

https://global-power-plants.datasettes.com/global-power-plants/global-power-plants

Generic JSON

API

http(s)://*

https://api.stlouisfed.org/fred/series?series_id=GNPCA&api_key=XXX&file_type=json#$.seriess[*]

Generic XML

API

http(s)://*

https://api.congress.gov/v3/bill/118?format=xml&offset=0&limit=2&api_key=XXX#.//bill

GitHub

API

https://api.github.com/repos/${owner}/{$repo}/pulls

https://api.github.com/repos/apache/superset/pulls

GSheets

API

https://docs.google.com/spreadsheets/d/${id}/edit#gid=${sheet_id}

https://docs.google.com/spreadsheets/d/1LcWZMsdCl92g7nA-D6qGRqg1T5TiHyuKJUY1u9XAnsk/edit#gid=0

HTML table

API

http(s)://*

https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population

Pandas

In memory

Any variable name (local or global)

my_df

S3

API

s3://bucket/path/to/file

s3://shillelagh/sample_data.csv

Socrata

API

https://${domain}/resource/${dataset-id}.json

https://data.cdc.gov/resource/unsk-b7fc.json

System

API

system://${resource}

system://cpu?interval=2

WeatherAPI

API

https://api.weatherapi.com/v1/history.json?key=${key}&q=${location}

https://api.weatherapi.com/v1/history.json?key=XXX&q=London

There are also 3rd-party 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.

Installation

Install Shillelagh with pip:

$ pip install 'shillelagh'

You also need to install optional dependencies, depending on the adapter you want to use:

$ pip install 'shillelagh[console]'        # to use the CLI
$ pip install 'shillelagh[genericjsonapi]' # for Generic JSON
$ pip install 'shillelagh[genericxmlapi]'  # for Generic XML
$ pip install 'shillelagh[githubapi]'      # for GitHub
$ pip install 'shillelagh[gsheetsapi]'     # for GSheets
$ pip install 'shillelagh[htmltableapi]'   # for HTML tables
$ pip install 'shillelagh[pandasmemory]'   # for Pandas in memory
$ pip install 'shillelagh[s3selectapi]'    # for S3 files
$ pip install 'shillelagh[systemapi]'      # for CPU information

Alternatively, you can install everything with:

$ pip install 'shillelagh[all]'

Project details

Verified details

These details have been verified by PyPI
Maintainers
Avatar for roberto from gravatar.com roberto

Unverified details

These details have not been verified by PyPI
Project links
Meta
  • License: MIT License (mit)
  • Author: Beto Dealmeida
  • Requires: Python >=3.9
  • Provides-Extra: all, console, dbtmetricflowapi, docs, genericjsonapi, genericxmlapi, githubapi, gsheetsapi, holidaysmemory, htmltableapi, multicorn, pandasmemory, s3selectapi, systemapi, testing
Classifiers

Release history Release notifications | RSS feed

This version

1.3.2

1.3.1

1.3.0

1.2.28

1.2.27

1.2.26

1.2.25

1.2.24

1.2.23

1.2.22

1.2.21

1.2.20

1.2.19

1.2.18

1.2.17

1.2.16

1.2.15

1.2.14

1.2.13

1.2.12

1.2.11

1.2.10

1.2.9

1.2.8

1.2.7

1.2.6

1.2.5

1.2.4

1.2.3

1.2.2

1.2.1

1.2.0

1.1.5

1.1.4

1.1.3

1.1.2

1.1.1

1.1.0

1.0.16

1.0.15

1.0.14

1.0.13

1.0.12

1.0.11

1.0.10

1.0.9

1.0.8

1.0.7

1.0.6

1.0.5

1.0.4

1.0.3

1.0.2

1.0.1

1.0.0

1.0.0rc5 pre-release

1.0.0rc4 pre-release

1.0.0rc3 pre-release

1.0.0rc2 pre-release

1.0.0rc1 pre-release

0.8.1

0.8.0

0.7.4

0.7.3.2

0.7.3.1

0.7.3

0.7.2

0.7.1

0.7.0

0.6.1

0.6.0

0.5.2

0.5.1

0.5.0

0.4.3

0.4.2

0.4.1

0.4

0.3.1

0.3

0.2.1

0.2

0.1

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

Uploaded Source

Built Distribution

shillelagh-1.3.2-py2.py3-none-any.whl (115.9 kB view details)

Uploaded Python 2 Python 3

File details

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

File metadata

  • Download URL: shillelagh-1.3.2.tar.gz
  • Upload date:
  • Size: 2.5 MB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/5.1.1 CPython/3.9.20

File hashes

Hashes for shillelagh-1.3.2.tar.gz
Algorithm Hash digest
SHA256 da07f678f53120195144b06c8f526d56665cf42c2bc90f8cfa12b5d071043a7d
MD5 7604dff59118f03844d6c405ebba4a95
BLAKE2b-256 3f7374202a3b725727becc12bac459de1f095940ff6f716b76baaf883e26c94f

See more details on using hashes here.

File details

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

File metadata

  • Download URL: shillelagh-1.3.2-py2.py3-none-any.whl
  • Upload date:
  • Size: 115.9 kB
  • Tags: Python 2, Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/5.1.1 CPython/3.9.20

File hashes

Hashes for shillelagh-1.3.2-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 99296b7a1c2d8db4718980ffc53df9b5a93d7dec3d7b8cfce29fc0f2f2896c08
MD5 c157181a8d78ad6fab6a6ac488ff8e83
BLAKE2b-256 b69c316a0199669a77961a59bbf2d34038ce88f8c0e1ba12a3bff07049ac0a83

See more details on using hashes here.

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