shillelagh

library1.4.5pypypi✓ verified 21d ago

Shillelagh is a Python library and CLI tool that enables users to query various data sources, including APIs, files, and in-memory objects, using standard SQL. It implements the Python DB API 2.0 specification based on SQLite (using the APSW library) and also provides a SQLAlchemy dialect. The library is actively maintained with frequent releases, with the current version being 1.4.4.

database data

pip install shillelagh

github ↗package ↗docs ↗

INSTALL

IMPORT

SIG · SHILLELAGH

shillelagh

databasepythonv1.4.5

Install

5.4s avg

Import

1273ms

Disk

73MB

Pass rate

10/ 10

Env Coverage10 / 10

glibc

3.9–3.13

musl

3.9–3.13

Install & Compatibility

Where this runs

tested against v1.4.5 · pip install

no network on importno background threads

Install × environment matrix

Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux

musl

py 3.10–3.910 runs

installs and imports cleanly · install 0.0s · import 1.308s · 70MB

glibc

py 3.10–3.910 runs

installs and imports cleanly · install 5.4s · import 1.237s · 72MB

73MB installed

● package 73MB

Code

Verified usage

Verified import paths — ran on the pinned version, not inferred.

connect

✓ from shillelagh.backends.apsw.db import connect

For standard DB API 2.0 connections.

create_engine

✓ from sqlalchemy.engine import create_engine; engine = create_engine('shillelagh://')

For SQLAlchemy usage, the 'shillelagh://' dialect is used.

This quickstart demonstrates how to connect to Shillelagh's DB API 2.0 interface and query a public Google Sheet using SQL. Shillelagh treats the external data source (like the Google Sheet URL) as a virtual table.

import os from shillelagh.backends.apsw.db import connect # Example using a public Google Sheet as a data source # For sensitive data, consider the security warning about credentials in table names. # A public sheet with some example data SHEET_URL = "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0" # Connect to the in-memory database connection = connect(":memory:") cursor = connection.cursor() try: # Query the Google Sheet directly via its URL query = f'SELECT country, SUM(cnt) FROM "{SHEET_URL}" WHERE cnt > 0 GROUP BY country' print(f"Executing query:\n{query}\n") for row in cursor.execute(query): print(row) except Exception as e: print(f"An error occurred: {e}") finally: connection.close()

shillelagh --version

Debug

Known issues

breakingShillelagh v1.3.2 introduced a minimum version requirement for `apsw` (3.43.2.0 or higher) due to an internal API change (`createscalarfunction` renamed to `create_scalar_function`). Older `apsw` versions will cause import errors or runtime failures.

fix

Upgrade your `apsw` package: `pip install --upgrade apsw`

affects: >=1.3.2

gotchaWhen querying resources requiring credentials, Shillelagh may embed sensitive information directly into the virtual table name within the SQL query string. This is not recommended for security reasons, especially when logging queries or exposing them.

fix

Avoid including sensitive credentials directly in table names for production systems. Explore using environment variables or other secure configuration methods to pass credentials to adapters, or ensure strict control over query logging and exposure.

affects: All versions

gotchaPrior to version 1.4.4, date parsing error messages could be unhelpful or lack context, making debugging data ingestion issues difficult.

fix

Upgrade to Shillelagh 1.4.4 or newer for improved date parsing error diagnostics: `pip install --upgrade shillelagh`.

affects: <1.4.4

Errors

Common errors & fixes

sqlite3.OperationalError: No registered driver for scheme 'SCHEME'

The required external dependency for the specified Shillelagh driver (e.g., `requests` for `https`, `gspread` for `gsheets`) is not installed, or the driver is not properly registered.

fix

Install the appropriate extra package for the driver using pip, such as `pip install shillelagh[http]` for HTTP sources or `pip install shillelagh[gsheets]` for Google Sheets.

ModuleNotFoundError: No module named 'apsw'

The `apsw` library, which serves as Shillelagh's underlying SQLite backend, is not installed or not correctly accessible in the Python environment.

fix

Install `apsw` explicitly using pip: `pip install apsw`.

sqlite3.OperationalError: No such table: my_table

The SQL query references a table name that either does not exist in the virtual database, is misspelled, or the underlying data source represented by the table is not correctly configured or accessible via its driver.

fix

Verify the table name against the expected Shillelagh schema or data source, ensure the connection URI is correct, and confirm the data source is valid and accessible.

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:shillelagh

The Shillelagh SQLAlchemy dialect is not properly installed or registered with SQLAlchemy, typically because `shillelagh` itself or its SQLAlchemy extras were not installed.

fix

Ensure `shillelagh` is installed with its SQLAlchemy extras by running `pip install shillelagh[sqlalchemy]`.

Upgrade

Version history

1.4.5latest on PyPI · released Jul 30, 2026

Audit

Dependencies

apswrequiredCore dependency for SQLite backend implementation.

multicornoptionalOptional dependency for the experimental PostgreSQL backend.

sqlglotoptionalOptional dependency for the experimental SQLGlot backend.

Agent activity

14 hits · last 30 days

node

OpenAI (training)

Resources

githubhttps://github.com/betodealmeida/shillelagh ↗

packagehttps://pypi.org/project/shillelagh/ ↗

docshttps://shillelagh.readthedocs.io/ ↗