or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

adapters.mdcli-interface.mddatabase-api.mdindex.mdsqlalchemy-integration.mdtype-system.md
tile.json

tessl/pypi-shillelagh

Making it easy to query APIs via SQL

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/shillelagh@1.4.x

To install, run

npx @tessl/cli install tessl/pypi-shillelagh@1.4.0

index.mddocs/

Shillelagh

Shillelagh is a comprehensive Python library that enables querying diverse data sources using standard SQL syntax. Built on SQLite with APSW, it implements the Python DB API 2.0 specification and provides SQLAlchemy dialects for seamless integration. The library features an extensible adapter system supporting multiple data sources including CSV files, Google Sheets, GitHub APIs, JSON/XML endpoints, HTML tables, Pandas DataFrames, S3 objects, and system metrics.

Package Information

  • Package Name: shillelagh
  • Language: Python
  • Installation: pip install shillelagh

Core Imports

import shillelagh

For database connections:

from shillelagh.backends.apsw.db import connect

For SQLAlchemy integration:

from sqlalchemy import create_engine

# Create engine with shillelagh dialect
engine = create_engine("shillelagh://")

Basic Usage

from shillelagh.backends.apsw.db import connect

# Connect to shillelagh (in-memory database)
connection = connect(":memory:")
cursor = connection.cursor()

# Query a CSV file
cursor.execute("""
    SELECT * FROM "https://example.com/data.csv"
    WHERE column1 > 100
    ORDER BY column2
""")

# Fetch results
rows = cursor.fetchall()
for row in rows:
    print(row)

connection.close()

Architecture

Shillelagh's architecture follows a layered approach:

  • DB API Layer: Standard Python database interface (Connection, Cursor)
  • Backend System: Multiple SQL backends (APSW/SQLite, Multicorn, SQLGlot)
  • Adapter Framework: Extensible plugin system for data sources
  • Type System: Robust field types with automatic inference
  • Query Engine: SQL parsing, optimization, and execution

This design allows SQL queries against any supported data source while maintaining compatibility with standard Python database tools and ORMs.

Capabilities

Database Connection and Querying

Standard Python DB API 2.0 interface for executing SQL queries against various data sources. Supports transactions, cursors, and all standard database operations.

def connect(path, adapters=None, adapter_kwargs=None, safe=False, isolation_level=None, apsw_connection_kwargs=None, schema="main"):
    """Create a database connection."""

class Connection:
    def cursor(self): ...
    def execute(self, operation, parameters=None): ...
    def close(self): ...
    def commit(self): ...
    def rollback(self): ...

class Cursor:
    def execute(self, operation, parameters=None): ...
    def fetchone(self): ...
    def fetchmany(self, size=None): ...
    def fetchall(self): ...
    def close(self): ...

Database API

Data Source Adapters

Extensible system for connecting to various data sources including APIs, files, and in-memory data. Each adapter translates SQL operations to source-specific queries.

class Adapter:
    def supports(self, uri, fast=True, **kwargs): ...
    def parse_uri(self, uri): ...
    def get_columns(self): ...
    def get_data(self, bounds=None, order=None, **kwargs): ...

Adapters

Type System and Field Definitions

Comprehensive type system for data conversion and validation with automatic type inference and custom field definitions.

class Field:
    def parse(self, value): ...
    def format(self, value): ...
    def quote(self, value): ...

class Boolean(Field): ...
class Integer(Field): ...
class Float(Field): ...
class String(Field): ...
class DateTime(Field): ...

Type System

SQLAlchemy Integration

Multiple SQLAlchemy dialects for integration with ORMs and SQL frameworks, supporting different execution modes and safety levels.

# SQLAlchemy dialects available:
# - shillelagh (default APSW dialect)
# - shillelagh.safe (safe mode dialect)
# - gsheets (Google Sheets specific)
# - metricflow (dbt MetricFlow)

SQLAlchemy Integration

Command Line Interface

Interactive SQL shell with syntax highlighting, completion, and configuration support for querying data sources from the command line.

def main():
    """Main CLI entry point."""

CLI Interface

SQL Functions

Custom SQL functions available in queries for utility operations, metadata inspection, and data manipulation.

def upgrade(target_version):
    """Upgrade shillelagh to specified version."""

def sleep(seconds):
    """Sleep for specified number of seconds."""

def get_metadata(uri):
    """Get metadata about a data source."""

def version():
    """Get current shillelagh version."""

def date_trunc(value, unit):
    """Truncate datetime to specified unit (year, quarter, month, week, day, hour, minute, second)."""

Exception Hierarchy

class Warning(Exception):
    """Important warnings like data truncations while inserting."""

class Error(Exception):
    """Base exception class."""

class InterfaceError(Error):
    """Errors related to the database interface."""

class DatabaseError(Error):
    """Errors related to the database."""

class DataError(DatabaseError):
    """Errors due to problems with processed data."""

class OperationalError(DatabaseError):
    """Errors related to database operation."""

class IntegrityError(DatabaseError):
    """Raised when relational integrity is affected."""

class InternalError(DatabaseError):
    """Raised when database encounters internal error."""

class ProgrammingError(DatabaseError):
    """Raised for programming errors."""

class NotSupportedError(DatabaseError):
    """Raised when method or API is not supported."""

class ImpossibleFilterError(Error):
    """Raised when condition impossible to meet is found."""

class UnauthenticatedError(InterfaceError):
    """Raised when user needs to authenticate."""