{"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":"python","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,"search_vec":"'-249':19 '0.2.12':8 '2':48 '7':39 '8':41 'access':25 'activ':11 'address':58 'cluster':35 'compat':44,56 'dbapi':3,6,17,62 'dialect':22 'elasticsearch':2,5,32,38,60 'elasticsearch-dbapi':1,4 'enabl':23 'issu':59 'librari':13 'made':52 'maintain':55 'mode':45 'opensearch':34,47,61 'oper':30 'pep':18 'period':53 'provid':15 'python':12 'queri':28 'query-on':27 'releas':50 'sql':24,64 'sqlalchemi':21,63 'support':37 'version':7 'via':43 'x':40,42,49","created_at":"2026-04-12T03:36:07.730073+00:00","updated_at":"2026-04-16T14:47:51.392902+00:00","problems":[{"fix":"Ensure `elasticsearch-dbapi` is installed in the active Python environment: `pip install elasticsearch-dbapi`. If using an existing environment, a reinstallation or environment refresh might be needed to register the dialect properly.","cause":"This error occurs when SQLAlchemy cannot find the registered dialect for 'elasticsearch+http', typically because `elasticsearch-dbapi` or its dependencies are not correctly installed or registered with SQLAlchemy.","error":"NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:elasticsearch.http"},{"fix":"Verify the Elasticsearch/OpenSearch cluster is running and accessible from the client machine. Double-check the connection string's host and port. Example: `engine = create_engine(\"elasticsearch+http://localhost:9200/\")`.","cause":"This is a generic connection error indicating that `elasticsearch-dbapi` was unable to establish a connection to the Elasticsearch or OpenSearch cluster, often due to incorrect host/port, network issues, or the cluster being unavailable.","error":"OperationalError: Error connecting to Elasticsearch:"},{"fix":"Ensure the Elasticsearch/OpenSearch cluster has the SQL plugin enabled and is running a compatible version (Elasticsearch 7.x/8.x or OpenSearch 2.x) that supports POST requests to the `/_sql` endpoint. Also, check the cluster's configuration for SQL API settings. If using OpenSearch, consider adding `v2=true` to the connection string for SQL v2 compatibility if applicable: `engine = create_engine(\"opensearch+http://localhost:9200/?v2=true\")`.","cause":"This error typically indicates that the connected Elasticsearch or OpenSearch cluster either does not support the SQL REST API at the `/_sql` endpoint or is an older version that expects a different HTTP method (e.g., GET) for SQL queries, while `elasticsearch-dbapi` uses POST.","error":"TransportError(405, 'Incorrect HTTP method for uri [/_sql/] and method [POST], allowed: [GET, PUT, DELETE, HEAD]')"},{"fix":"Provide valid authentication credentials in your connection string or `connect_args`. For basic authentication, include username and password: `engine = create_engine(\"elasticsearch+http://user:password@localhost:9200/\")`. For Elasticsearch 8.x with API key, refer to `elasticsearch-py` documentation for API key usage or ensure `ELASTIC_CLIENT_APIVERSIONING=1` is set in your environment if using compatibility mode.","cause":"This error signifies that the connection attempt to Elasticsearch or OpenSearch failed due to missing or incorrect authentication credentials, meaning the cluster requires a username and password (or API key) which were not provided or were invalid.","error":"AuthenticationException(401, 'security_exception', 'missing authentication credentials for REST request')"},{"fix":"Downgrade or upgrade `elasticsearch-py` to a version compatible with `elasticsearch-dbapi==0.2.12`. For example, `elasticsearch-dbapi` 0.2.1 requires `elasticsearch-py<7.14`, so you might need to run `pip install elasticsearch-py=='7.13.0'` (or another compatible version) before or after installing `elasticsearch-dbapi`.","cause":"This is a dependency conflict where a version of the `elasticsearch-py` client library already installed (or being installed) is incompatible with the version range required by `elasticsearch-dbapi`.","error":"Elasticsearch-py X.Y.Z conficts with the elasticsearch-dbapi-0.2.1 which requires Elasticsearch-py