==========
Shillelagh
==========
.. image:: https://coveralls.io/repos/github/betodealmeida/shillelagh/badge.svg?branch=master
:target: https://coveralls.io/github/betodealmeida/shillelagh?branch=master
.. image:: https://readthedocs.org/projects/shillelagh/badge/?version=latest
:target: https://shillelagh.readthedocs.io/en/latest/?badge=latest
:alt: Documentation Status
.. image:: https://badge.fury.io/py/shillelagh.svg
:target: https://badge.fury.io/py/shillelagh
.. image:: https://img.shields.io/pypi/pyversions/shillelagh
:alt: PyPI - Python Version
.. image:: docs/logo.png
:width: 25 %
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 <https://shillelagh.readthedocs.io/en/latest/>`_.
The library is an implementation of the `Python DB API 2.0 <https://www.python.org/dev/peps/pep-0249/>`_ based on `SQLite <https://sqlite.org/index.html>`_ (using the `APSW <https://rogerbinns.github.io/apsw/>`_ library):
.. code-block:: python
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 <https://www.sqlalchemy.org/>`_ dialect:
.. code-block:: python
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:
.. code-block:: bash
$ shillelagh
sql> SELECT * FROM a_table
There is also an `experimental backend <https://shillelagh.readthedocs.io/en/latest/postgres.html>`_ that uses Postgres with the `Multicorn2 <http://multicorn2.org/>`_ extension. First, install the additional dependencies:
.. code-block:: bash
$ pip install 'shillelagh[multicorn]'
$ pip install 'multicorn @ git+https://github.com/pgsql-io/multicorn2.git@v2.5'
Then run:
.. code-block:: python
from shillelagh.backends.multicorn.db import connect
connection = connect(
user="username",
password="password",
host="localhost",
port=5432,
database="examples",
)
Or:
.. code-block:: python
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 <https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0>`_ you can query it directly as if it were a table in a database:
.. code-block:: sql
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:
.. code-block:: sql
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 <https://shillelagh.readthedocs.io/en/latest/adapters.html>`_. 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:
- `Airtable <https://github.com/cancan101/airtable-db-api>`_
- `GraphQL <https://github.com/cancan101/graphql-db-api>`_
A query can combine data from multiple adapters:
.. code-block:: sql
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 <https://www.weatherapi.com/>`_, 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 <https://shillelagh.readthedocs.io/en/latest/development.html>`_ that explains how to create a new adapter to an API or filetype.
Installation
============
Install Shillelagh with ``pip``:
.. code-block:: bash
$ pip install 'shillelagh'
You also need to install optional dependencies, depending on the adapter you want to use:
.. code-block:: bash
$ 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:
.. code-block:: bash
$ pip install 'shillelagh[all]'
Raw data
{
"_id": null,
"home_page": "https://github.com/betodealmeida/shillelagh/",
"name": "shillelagh",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": null,
"keywords": null,
"author": "Beto Dealmeida",
"author_email": "roberto@dealmeida.net",
"download_url": "https://files.pythonhosted.org/packages/3f/27/c5b39fb41c04e8b943242167bec2de527bee7b1615253165010e2189e9c1/shillelagh-1.3.3.tar.gz",
"platform": "any",
"description": "==========\nShillelagh\n==========\n\n.. image:: https://coveralls.io/repos/github/betodealmeida/shillelagh/badge.svg?branch=master\n :target: https://coveralls.io/github/betodealmeida/shillelagh?branch=master\n.. image:: https://readthedocs.org/projects/shillelagh/badge/?version=latest\n :target: https://shillelagh.readthedocs.io/en/latest/?badge=latest\n :alt: Documentation Status\n.. image:: https://badge.fury.io/py/shillelagh.svg\n :target: https://badge.fury.io/py/shillelagh\n.. image:: https://img.shields.io/pypi/pyversions/shillelagh\n :alt: PyPI - Python Version\n\n.. image:: docs/logo.png\n :width: 25 %\n\nShillelagh (\u0283\u026a\u02c8le\u026al\u026a) 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.\n\nLearn more on the `documentation <https://shillelagh.readthedocs.io/en/latest/>`_.\n\nThe library is an implementation of the `Python DB API 2.0 <https://www.python.org/dev/peps/pep-0249/>`_ based on `SQLite <https://sqlite.org/index.html>`_ (using the `APSW <https://rogerbinns.github.io/apsw/>`_ library):\n\n.. code-block:: python\n\n from shillelagh.backends.apsw.db import connect\n\n connection = connect(\":memory:\")\n cursor = connection.cursor()\n\n query = \"SELECT * FROM a_table\"\n for row in cursor.execute(query):\n print(row)\n\nThere is also a `SQLAlchemy <https://www.sqlalchemy.org/>`_ dialect:\n\n.. code-block:: python\n\n from sqlalchemy.engine import create_engine\n\n engine = create_engine(\"shillelagh://\")\n connection = engine.connect()\n\n query = \"SELECT * FROM a_table\"\n for row in connection.execute(query):\n print(row)\n\nAnd a command-line utility:\n\n.. code-block:: bash\n\n $ shillelagh\n sql> SELECT * FROM a_table\n\nThere is also an `experimental backend <https://shillelagh.readthedocs.io/en/latest/postgres.html>`_ that uses Postgres with the `Multicorn2 <http://multicorn2.org/>`_ extension. First, install the additional dependencies:\n\n.. code-block:: bash\n\n $ pip install 'shillelagh[multicorn]'\n $ pip install 'multicorn @ git+https://github.com/pgsql-io/multicorn2.git@v2.5'\n\nThen run:\n\n.. code-block:: python\n\n from shillelagh.backends.multicorn.db import connect\n\n connection = connect(\n user=\"username\",\n password=\"password\",\n host=\"localhost\",\n port=5432,\n database=\"examples\",\n )\n\nOr:\n\n.. code-block:: python\n\n from sqlalchemy import create_engine\n engine = create_engine(\"shillelagh+multicorn2://username:password@localhost:5432/examples\")\n\nWhy SQL?\n========\n\nSharks 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.\n\nSQL has been around for some 50 years for the same reason: it's really good at what it does.\n\nWhy \"Shillelagh\"?\n=================\n\nPicture a leprechaun hitting APIs with a big stick so that they accept SQL.\n\nHow is it different?\n====================\n\nShillelagh allows you to easily query non-SQL resources. For example, if you have a `Google Spreadsheet <https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0>`_ you can query it directly as if it were a table in a database:\n\n.. code-block:: sql\n\n SELECT country, SUM(cnt)\n FROM \"https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0\"\n WHERE cnt > 0\n GROUP BY country\n\nYou can even run ``INSERT``/``DELETE``/``UPDATE`` queries against the spreadsheet:\n\n.. code-block:: sql\n\n UPDATE \"https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0\"\n SET cnt = cnt + 1\n WHERE country != 'BR'\n\nQueries like this are supported by `adapters <https://shillelagh.readthedocs.io/en/latest/adapters.html>`_. Currently Shillelagh has the following adapters:\n\n============= ============ ========================================================================== =====================================================================================================\n Name Type URI pattern Example URI\n============= ============ ========================================================================== =====================================================================================================\n CSV File/API ``/path/to/file.csv``; ``http(s)://*`` ``/home/user/sample_data.csv``\n Datasette API ``http(s)://*`` ``https://global-power-plants.datasettes.com/global-power-plants/global-power-plants``\n Generic JSON API ``http(s)://*`` ``https://api.stlouisfed.org/fred/series?series_id=GNPCA&api_key=XXX&file_type=json#$.seriess[*]``\n Generic XML API ``http(s)://*`` ``https://api.congress.gov/v3/bill/118?format=xml&offset=0&limit=2&api_key=XXX#.//bill``\n GitHub API ``https://api.github.com/repos/${owner}/{$repo}/pulls`` ``https://api.github.com/repos/apache/superset/pulls``\n GSheets API ``https://docs.google.com/spreadsheets/d/${id}/edit#gid=${sheet_id}`` ``https://docs.google.com/spreadsheets/d/1LcWZMsdCl92g7nA-D6qGRqg1T5TiHyuKJUY1u9XAnsk/edit#gid=0``\n HTML table API ``http(s)://*`` ``https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population``\n Pandas In memory Any variable name (local or global) ``my_df``\n S3 API ``s3://bucket/path/to/file`` ``s3://shillelagh/sample_data.csv``\n Socrata API ``https://${domain}/resource/${dataset-id}.json`` ``https://data.cdc.gov/resource/unsk-b7fc.json``\n System API ``system://${resource}`` ``system://cpu?interval=2``\n WeatherAPI API ``https://api.weatherapi.com/v1/history.json?key=${key}&q=${location}`` ``https://api.weatherapi.com/v1/history.json?key=XXX&q=London``\n============= ============ ========================================================================== =====================================================================================================\n\nThere are also 3rd-party adapters:\n\n- `Airtable <https://github.com/cancan101/airtable-db-api>`_\n- `GraphQL <https://github.com/cancan101/graphql-db-api>`_\n\nA query can combine data from multiple adapters:\n\n.. code-block:: sql\n\n INSERT INTO \"/tmp/file.csv\"\n SELECT time, chance_of_rain\n FROM \"https://api.weatherapi.com/v1/history.json?q=London\"\n WHERE time IN (\n SELECT datetime\n FROM \"https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=1648320094\"\n )\n\nThe query above reads timestamps from a Google sheet, uses them to filter weather data from `WeatherAPI <https://www.weatherapi.com/>`_, and writes the chance of rain into a (pre-existing) CSV file.\n\nNew adapters are relatively easy to implement. There's a `step-by-step tutorial <https://shillelagh.readthedocs.io/en/latest/development.html>`_ that explains how to create a new adapter to an API or filetype.\n\nInstallation\n============\n\nInstall Shillelagh with ``pip``:\n\n.. code-block:: bash\n\n $ pip install 'shillelagh'\n\nYou also need to install optional dependencies, depending on the adapter you want to use:\n\n.. code-block:: bash\n\n $ pip install 'shillelagh[console]' # to use the CLI\n $ pip install 'shillelagh[genericjsonapi]' # for Generic JSON\n $ pip install 'shillelagh[genericxmlapi]' # for Generic XML\n $ pip install 'shillelagh[githubapi]' # for GitHub\n $ pip install 'shillelagh[gsheetsapi]' # for GSheets\n $ pip install 'shillelagh[htmltableapi]' # for HTML tables\n $ pip install 'shillelagh[pandasmemory]' # for Pandas in memory\n $ pip install 'shillelagh[s3selectapi]' # for S3 files\n $ pip install 'shillelagh[systemapi]' # for CPU information\n\nAlternatively, you can install everything with:\n\n.. code-block:: bash\n\n $ pip install 'shillelagh[all]'\n",
"bugtrack_url": null,
"license": "mit",
"summary": "Making it easy to query APIs via SQL",
"version": "1.3.3",
"project_urls": {
"Documentation": "https://shillelagh.readthedocs.io/",
"Homepage": "https://github.com/betodealmeida/shillelagh/"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "772664fbe52dea01595ae6449fb9d1e2fd0908595f1f04d8e003ac3114e371ee",
"md5": "7c655b9a9568046e18b0367d1f9ae235",
"sha256": "07eae597ed47198a3f67bdc01295eeb47f6811ab51fa02d75c873c121d63886e"
},
"downloads": -1,
"filename": "shillelagh-1.3.3-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "7c655b9a9568046e18b0367d1f9ae235",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": ">=3.9",
"size": 115836,
"upload_time": "2024-12-02T01:22:10",
"upload_time_iso_8601": "2024-12-02T01:22:10.522009Z",
"url": "https://files.pythonhosted.org/packages/77/26/64fbe52dea01595ae6449fb9d1e2fd0908595f1f04d8e003ac3114e371ee/shillelagh-1.3.3-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "3f27c5b39fb41c04e8b943242167bec2de527bee7b1615253165010e2189e9c1",
"md5": "3ff001bd73662a22725ac62a010ce439",
"sha256": "cf2da0da8ac2903ec74569b68b9d8d7802145fbfeeedd20aee3d758a89d4f9c7"
},
"downloads": -1,
"filename": "shillelagh-1.3.3.tar.gz",
"has_sig": false,
"md5_digest": "3ff001bd73662a22725ac62a010ce439",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 2519292,
"upload_time": "2024-12-02T01:22:13",
"upload_time_iso_8601": "2024-12-02T01:22:13.397738Z",
"url": "https://files.pythonhosted.org/packages/3f/27/c5b39fb41c04e8b943242167bec2de527bee7b1615253165010e2189e9c1/shillelagh-1.3.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-12-02 01:22:13",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "betodealmeida",
"github_project": "shillelagh",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"lcname": "shillelagh"
}
JSON
