{"id":4763,"library":"shillelagh","title":"Shillelagh","description":"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.","status":"active","version":"1.4.4","language":"en","source_language":"en","source_url":"https://github.com/betodealmeida/shillelagh/","tags":["sql","api","data access","sqlite","virtual tables","db-api-2.0","sqlalchemy"],"install":[{"cmd":"pip install shillelagh","lang":"bash","label":"Basic Installation"},{"cmd":"pip install 'shillelagh[multicorn]'","lang":"bash","label":"PostgreSQL Multicorn Backend (Optional)"}],"dependencies":[{"reason":"Core dependency for SQLite backend implementation.","package":"apsw","optional":false},{"reason":"Optional dependency for the experimental PostgreSQL backend.","package":"multicorn","optional":true},{"reason":"Optional dependency for the experimental SQLGlot backend.","package":"sqlglot","optional":true}],"imports":[{"note":"For standard DB API 2.0 connections.","symbol":"connect","correct":"from shillelagh.backends.apsw.db import connect"},{"note":"For SQLAlchemy usage, the 'shillelagh://' dialect is used.","symbol":"create_engine","correct":"from sqlalchemy.engine import create_engine; engine = create_engine('shillelagh://')"}],"quickstart":{"code":"import os\nfrom shillelagh.backends.apsw.db import connect\n\n# Example using a public Google Sheet as a data source\n# For sensitive data, consider the security warning about credentials in table names.\n\n# A public sheet with some example data\nSHEET_URL = \"https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0\"\n\n# Connect to the in-memory database\nconnection = connect(\":memory:\")\ncursor = connection.cursor()\n\ntry:\n    # Query the Google Sheet directly via its URL\n    query = f'SELECT country, SUM(cnt) FROM \"{SHEET_URL}\" WHERE cnt > 0 GROUP BY country'\n    print(f\"Executing query:\\n{query}\\n\")\n    for row in cursor.execute(query):\n        print(row)\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\nfinally:\n    connection.close()","lang":"python","description":"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."},"warnings":[{"fix":"Upgrade your `apsw` package: `pip install --upgrade apsw`","message":"Shillelagh 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.","severity":"breaking","affected_versions":">=1.3.2"},{"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.","message":"When 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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Upgrade to Shillelagh 1.4.4 or newer for improved date parsing error diagnostics: `pip install --upgrade shillelagh`.","message":"Prior to version 1.4.4, date parsing error messages could be unhelpful or lack context, making debugging data ingestion issues difficult.","severity":"gotcha","affected_versions":"<1.4.4"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}