SQLAlchemy Exasol Dialect

6.1.1 · active · verified Thu Apr 16

SQLAlchemy-Exasol is a dialect for SQLAlchemy that allows Python applications to connect and interact with an Exasol database. It provides support for both WebSocket and ODBC-based connections to Exasol. The current version is 6.1.1, with releases typically occurring every 1-3 months to introduce new features, fix bugs, and maintain compatibility with Python and SQLAlchemy updates.

Common errors

```
sqlalchemy.exc.CompileError: This select() construct can only be used with a `Connection` or `Engine` directly; it cannot be used with an ORM `Session` without first selecting from an ORM-mapped entity.
```
cause Using SQLAlchemy 1.x `select()` syntax with a SQLAlchemy 2.0 `Session` object after upgrading `sqlalchemy-exasol` to version 6.0.0 or later.

fix Update your query patterns to SQLAlchemy 2.0. For direct SQL, use `session.execute(text('SELECT ...'))`. For ORM, ensure `select()` operates on mapped entities or use the `scalar_one()` / `scalars()` methods on the result.
```
ModuleNotFoundError: No module named 'pyodbc'
```
cause Attempting to use an ODBC connection string (e.g., `exasol+pyodbc://`) without having `pyodbc` installed or when `sqlalchemy-exasol` was installed without the `[odbc]` extra.

fix Install the `pyodbc` package by running `pip install pyodbc` or `pip install "sqlalchemy-exasol[odbc]"`. Ensure you have the necessary Exasol ODBC driver installed on your system.
```
sqlalchemy.exc.InvalidRequestError: Identity map for key (some_class, (some_id,), None) conflicts with an existing identity.  Existing instance: <SomeClass object at 0x...>
```
cause This error can occur in ORM sessions on `sqlalchemy-exasol` versions prior to 6.1.1 due to the `get_lastrowid` bug, leading to incorrect ID assignments and identity map conflicts.

fix Upgrade `sqlalchemy-exasol` to version `6.1.1` or newer. Review your ORM code, especially around `session.flush()` and related primary key generation, if the issue persists after upgrade.
```
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:exasol
```
cause The `sqlalchemy-exasol` package is not installed, or the dialect is not correctly registered within SQLAlchemy.

fix Ensure `sqlalchemy-exasol` is installed: `pip install sqlalchemy-exasol`. If using a custom registration, verify the import path.

Warnings

breaking Version 6.0.0 introduced a breaking change by migrating `sqlalchemy-exasol` to the SQLAlchemy 2.0 API and conventions. Code written for SQLAlchemy 1.x will likely fail.
Fix: Update your SQLAlchemy code to use SQLAlchemy 2.0 patterns (e.g., `select()` constructs, session management, `insert().values()`). Refer to SQLAlchemy 2.0 migration guides.
deprecated Support for Python 3.9 was dropped in version 5.2.0, and Python 3.8 support was dropped in 5.1.0.
Fix: Upgrade your Python environment to version 3.10 or newer. `sqlalchemy-exasol` 6.1.1 requires Python <3.14,>=3.10.
gotcha A long-standing bug in the internal `get_lastrowid` function, affecting ORM sessions when flushing values to pass IDs between linked SQLAlchemy tables, was fixed in 6.1.1. This could cause incorrect ID referencing.
Fix: Upgrade to `sqlalchemy-exasol==6.1.1` or newer. If on an older version, carefully re-evaluate ORM flushing logic involving auto-incremented IDs.
deprecated The `pyodbc` and `truodbc` dialects were deprecated in 5.0.0. The `websocket` dialect is now the default and recommended method for connecting to Exasol.
Fix: Prefer `exasol+websocket://` connection strings. If you still require ODBC, install `sqlalchemy-exasol[odbc]` and ensure `pyodbc` is installed and configured correctly, but be aware of its deprecated status.

Install

pip install sqlalchemy-exasol Default (WebSocket)
pip install "sqlalchemy-exasol[odbc]" For ODBC connections (includes pyodbc)

Imports

create_engine
```
from sqlalchemy import create_engine
```
Most common entry point for using SQLAlchemy dialects.
text
```
from sqlalchemy import text
```
Used for executing raw SQL statements.

Quickstart

This quickstart demonstrates how to establish a connection to an Exasol database using `sqlalchemy-exasol` with the default WebSocket driver. It configures connection parameters from environment variables (or defaults), creates an engine, tests the connection with a simple query, and shows how to create a schema and a table.

from sqlalchemy import create_engine, text
import os

# Configure connection details from environment variables or provide defaults
EXASOL_USER = os.environ.get("EXASOL_USER", "sys")
EXASOL_PASSWORD = os.environ.get("EXASOL_PASSWORD", "exasol")
EXASOL_HOST = os.environ.get("EXASOL_HOST", "localhost")
EXASOL_PORT = os.environ.get("EXASOL_PORT", "8563")
EXASOL_SCHEMA = os.environ.get("EXASOL_SCHEMA", "SYS")

# Construct the connection string using the default websocket driver
# For ODBC, use 'exasol+pyodbc://'
connection_string = f"exasol+websocket://{EXASOL_USER}:{EXASOL_PASSWORD}@{EXASOL_HOST}:{EXASOL_PORT}/{EXASOL_SCHEMA}"

try:
    # Create the engine
    engine = create_engine(connection_string)

    # Test the connection and execute a simple query
    with engine.connect() as connection:
        result = connection.execute(text("SELECT 1 FROM DUAL")).scalar()
        print(f"Successfully connected to Exasol. Query result: {result}")

    # Example: Create a table
    with engine.connect() as connection:
        connection.execute(text("CREATE SCHEMA IF NOT EXISTS MY_SCHEMA"))
        connection.execute(text("CREATE TABLE IF NOT EXISTS MY_SCHEMA.test_table (id INT, name VARCHAR(255))"))
        connection.commit()
        print("Table MY_SCHEMA.test_table created or already exists.")

except Exception as e:
    print(f"An error occurred: {e}")

view raw JSON →