{"id":3986,"library":"elasticsearch-dbapi","title":"elasticsearch-dbapi","description":"elasticsearch-dbapi (version 0.2.12) is an active Python library that provides a DBAPI (PEP-249) and SQLAlchemy dialect, enabling SQL access for query-only operations on Elasticsearch and OpenSearch clusters. It supports Elasticsearch 7.x, 8.x (via compatibility mode), and OpenSearch 2.x. Releases are made periodically to maintain compatibility and address issues.","status":"active","version":"0.2.12","language":"en","source_language":"en","source_url":"https://github.com/preset-io/elasticsearch-dbapi","tags":["elasticsearch","opensearch","dbapi","sqlalchemy","sql"],"install":[{"cmd":"pip install elasticsearch-dbapi","lang":"bash","label":"Install stable release"}],"dependencies":[{"reason":"Required for connecting to Elasticsearch clusters.","package":"elasticsearch"},{"reason":"Required for connecting to OpenSearch clusters.","package":"opensearch-py"},{"reason":"Required for using the SQLAlchemy dialect.","package":"SQLAlchemy"}],"imports":[{"symbol":"connect","correct":"from es.elastic.api import connect"},{"note":"Used with the 'elasticsearch+http://' or 'odelasticsearch+https://' dialect.","symbol":"create_engine","correct":"from sqlalchemy.engine import create_engine"}],"quickstart":{"code":"import os\nfrom es.elastic.api import connect\n\n# IMPORTANT: For Elasticsearch 8.x, set ELASTIC_CLIENT_APIVERSIONING=1\n# in your environment or before connecting to enable compatibility mode.\n# os.environ['ELASTIC_CLIENT_APIVERSIONING'] = '1'\n\n# Configure host and port. Adjust as necessary for your setup.\n# For OpenSearch, a common port is 19200.\nES_HOST = os.environ.get('ES_HOST', 'localhost')\nES_PORT = int(os.environ.get('ES_PORT', 9200))\n\ntry:\n    # Connect to Elasticsearch/OpenSearch via DBAPI\n    conn = connect(host=ES_HOST, port=ES_PORT)\n    curs = conn.cursor()\n\n    # Execute a simple SQL query\n    curs.execute(\"SELECT 1\")\n    result = curs.fetchall()\n    print(f\"Connection successful. Query 'SELECT 1' returned: {result}\")\n\n    # Example: List available tables (indices)\n    curs.execute(\"SHOW TABLES\")\n    tables = curs.fetchall()\n    print(f\"First 5 available tables (indices): {tables[:5]}...\")\n\n    curs.close()\n    conn.close()\n    print(\"Successfully connected, queried, and closed connection.\")\nexcept Exception as e:\n    print(f\"Failed to connect or query Elasticsearch/OpenSearch: {e}\")\n    print(\"Please ensure your Elasticsearch/OpenSearch instance is running and accessible at \")\n    print(f\"'{ES_HOST}:{ES_PORT}'. For Elasticsearch 8.x, ensure ELASTIC_CLIENT_APIVERSIONING is set.\")","lang":"python","description":"Demonstrates connecting to an Elasticsearch/OpenSearch instance using the DBAPI interface, executing a basic SQL query, and listing available indices (tables). It highlights the critical environment variable for Elasticsearch 8.x compatibility."},"warnings":[{"fix":"Set `os.environ['ELASTIC_CLIENT_APIVERSIONING'] = '1'` in your application or as a system environment variable before initiating connections.","message":"To connect to Elasticsearch 8.x, the `ELASTIC_CLIENT_APIVERSIONING` environment variable must be set to `1` in your Python application. Failing to do so will result in connection or query errors due to API incompatibilities.","severity":"breaking","affected_versions":"All versions when connecting to Elasticsearch 8.x"},{"fix":"Add `v2=True` to the `connect` function call or `create_engine` URL if using SQLAlchemy (e.g., `elasticsearch+http://localhost:9200/?v2=true`).","message":"This library defaults to complying with SQL v1. If your Elasticsearch/OpenSearch SQL endpoint requires v2, you must pass `v2=true` as a query parameter in the connection string (e.g., `connect(host='localhost', v2=True)`).","severity":"gotcha","affected_versions":"All versions"},{"fix":"Avoid using array type columns or querying indices with leading dots or specific dot patterns where possible. For AWS ES, review `GROUP BY` usage to ensure it targets keyword fields. Consider using aliases for problematic index names.","message":"There are known limitations including lack of support for array type columns (which are excluded by SQLAlchemy's `get_columns`) and issues with indexes whose names start with a dot ('.'). Specific limitations also exist for AWS ES/OpenDistro, such as only being able to `GROUP BY` keyword fields and problems with indices containing dots (e.g., 'audit_log.2021.01.20').","severity":"gotcha","affected_versions":"All versions"},{"fix":"Adjust the `fetch_size` parameter during connection (e.g., `connect(host='localhost', fetch_size=50000)`) to a higher value if you expect more results. Be mindful of memory consumption with very large fetch sizes.","message":"The maximum number of rows fetched by a single query is limited to 10000 by default. This can lead to truncated results for larger datasets.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always review the breaking changes documentation for the specific Elasticsearch/OpenSearch version you are targeting and the corresponding `elasticsearch-py` or `opensearch-py` client versions when upgrading your cluster or clients.","message":"The `elasticsearch-dbapi` library relies on the underlying `elasticsearch-py` and `opensearch-py` clients. Breaking changes in major versions of Elasticsearch or OpenSearch, or in their official Python clients, can indirectly impact the functionality or require configuration adjustments in `elasticsearch-dbapi` even if `elasticsearch-dbapi` itself doesn't have a breaking change.","severity":"gotcha","affected_versions":"Dependent on underlying client/Elasticsearch/OpenSearch versions"}],"env_vars":null,"last_verified":"2026-04-11T00:00:00.000Z","next_check":"2026-07-10T00:00:00.000Z"}