{"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":"python","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,"search_vec":"'1.4.4':63 '2.0':35,74 'access':67 'activ':53 'also':45 'api':19,34,65,73 'apsw':42 'base':37 'cli':8 'current':60 'data':16,66 'db':33,72 'db-api':71 'dialect':49 'enabl':11 'file':20 'frequent':56 'implement':30 'in-memori':22 'includ':18 'librari':6,43,51 'maintain':54 'memori':24 'object':25 'provid':46 'python':5,32 'queri':14 'releas':57 'shillelagh':1,2 'sourc':17 'specif':36 'sql':28,64 'sqlalchemi':48,75 'sqlite':39,68 'standard':27 'tabl':70 'tool':9 'use':26,40 'user':12 'various':15 'version':61 'virtual':69","created_at":"2026-04-12T14:06:34.142093+00:00","updated_at":"2026-04-16T21:41:59.554385+00:00","problems":[{"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.","cause":"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.","error":"sqlite3.OperationalError: No registered driver for scheme 'SCHEME'"},{"fix":"Install `apsw` explicitly using pip: `pip install apsw`.","cause":"The `apsw` library, which serves as Shillelagh's underlying SQLite backend, is not installed or not correctly accessible in the Python environment.","error":"ModuleNotFoundError: No module named 'apsw'"},{"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.","cause":"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.","error":"sqlite3.OperationalError: No such table: my_table"},{"fix":"Ensure `shillelagh` is installed with its SQLAlchemy extras by running `pip install shillelagh[sqlalchemy]`.","cause":"The Shillelagh SQLAlchemy dialect is not properly installed or registered with SQLAlchemy, typically because `shillelagh` itself or its SQLAlchemy extras were not installed.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:shillelagh"}],"ecosystem":"pypi","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":"1.4.5","cli_name":"shillelagh","cli_version":"Traceback (most recent call last):","type":"library","homepage":null,"github":"https://github.com/betodealmeida/shillelagh","docs":"https://shillelagh.readthedocs.io/","changelog":null,"pypi":"https://pypi.org/project/shillelagh/","npm":null,"openapi_spec":null,"status_page":null,"smithery":null,"categories":["database","data"],"base_url":null,"auth_type":null,"provenance":{"verified_status":"passing","verified_at":"2026-06-28","last_verified":"2026-08-30","next_check":"2026-07-28","install_tag":null}}