shillelagh

What it is and what it does

Shillelagh is a Python library and CLI that bridges SQL and non-SQL data sources by implementing the Python DB API 2.0 on top of SQLite (via APSW). It lets you write SQL queries against APIs, files, spreadsheets, and in-memory objects as if they were database tables, with support for SELECT, INSERT, UPDATE, and DELETE operations. The library includes adapters for Google Sheets, CSV files, JSON and XML APIs, GitHub, S3, Pandas DataFrames, and several other sources, and you can write custom adapters to add support for new data sources.

You can use Shillelagh three ways: as a Python library with the APSW backend, as a SQLAlchemy dialect for integration with ORMs and query builders, or via a command-line REPL. Queries can combine data from multiple adapters in a single statement—for example, reading timestamps from a Google Sheet and using them to filter weather API results, then writing the output to a CSV file. The library is designed to be both user-friendly for simple queries and extensible for developers adding new adapters.

Use it for:

• Query a Google Sheet directly with SQL without exporting to a database or writing custom parsing code.
• Combine data from multiple APIs and files in a single SQL query without writing ETL glue code.
• Use SQL to filter and aggregate data from REST APIs that don't natively support SQL.
• Prototype data analysis workflows using SQL before migrating to a full database.
• Build a CLI tool that lets non-technical users query APIs and files using familiar SQL syntax.
• Insert or update data in remote spreadsheets and files using SQL INSERT/UPDATE/DELETE statements.

Worth the install?

AI-flagged interpretation of the facts on this page — verify before relying

Shillelagh lets you query APIs, files, spreadsheets, and in-memory objects using SQL, with support for multiple data sources through pluggable adapters and both programmatic and CLI interfaces.

Yes. Shillelagh is actively maintained, has no known vulnerabilities, installs with low friction, and solves a genuine problem: querying diverse data sources with SQL without building custom adapters. It's production-stable (Development Status 5), supports modern Python versions (3.9–3.13), and has a permissive MIT license. Install it if you need SQL access to APIs, spreadsheets, or files; skip it if you're working exclusively with traditional SQL databases.

Install

shillelagh on PyPI

pip install shillelagh

uv add shillelagh

poetry add shillelagh

Installing shillelagh

Before you install

Low friction: pure Python wheel with 9 runtime dependencies including SQLAlchemy, requests, and APSW. Active maintenance with a release 15 days ago and 460 GitHub stars.

License in practice

MIT license (permissive) means you can use, modify, and distribute this package freely in commercial and private projects with minimal restrictions.

Quickstart

pip install shillelagh

from shillelagh.backends.apsw.db import connect

connection = connect(":memory:")
cursor = connection.cursor()
for row in cursor.execute("SELECT * FROM a_table"):
    print(row)

Requires Python 3.9 or later; APSW (included as a runtime dependency) may require a C compiler on some platforms.

Verify before relying

• Performance characteristics when querying large remote datasets or joining across multiple adapters.
• Whether all listed adapters (CSV, Datasette, GSheets, S3, etc.) are included in the base install or require extras.
• Stability and maturity of the experimental Postgres and sqlglot backends mentioned in the description.

Package facts

Evidence: shillelagh-1.4.5-py2.py3-none-any.whl

Tags

More Database packages