{"id":7749,"library":"sqlalchemy-exasol","title":"SQLAlchemy Exasol Dialect","description":"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.","status":"active","version":"6.1.1","language":"en","source_language":"en","source_url":"https://github.com/exasol/sqlalchemy-exasol","tags":["database","sqlalchemy","exasol","sql","orm","dialect","etl"],"install":[{"cmd":"pip install sqlalchemy-exasol","lang":"bash","label":"Default (WebSocket)"},{"cmd":"pip install \"sqlalchemy-exasol[odbc]\"","lang":"bash","label":"For ODBC connections (includes pyodbc)"}],"dependencies":[{"reason":"Core SQLAlchemy library for ORM and SQL toolkit functionality.","package":"SQLAlchemy"},{"reason":"DBAPI 2.0 compliant driver for connecting to Exasol, used by the dialect.","package":"pyexasol"},{"reason":"Optional dependency required for ODBC-based connections to Exasol.","package":"pyodbc","optional":true}],"imports":[{"note":"Most common entry point for using SQLAlchemy dialects.","symbol":"create_engine","correct":"from sqlalchemy import create_engine"},{"note":"Used for executing raw SQL statements.","symbol":"text","correct":"from sqlalchemy import text"}],"quickstart":{"code":"from sqlalchemy import create_engine, text\nimport os\n\n# Configure connection details from environment variables or provide defaults\nEXASOL_USER = os.environ.get(\"EXASOL_USER\", \"sys\")\nEXASOL_PASSWORD = os.environ.get(\"EXASOL_PASSWORD\", \"exasol\")\nEXASOL_HOST = os.environ.get(\"EXASOL_HOST\", \"localhost\")\nEXASOL_PORT = os.environ.get(\"EXASOL_PORT\", \"8563\")\nEXASOL_SCHEMA = os.environ.get(\"EXASOL_SCHEMA\", \"SYS\")\n\n# Construct the connection string using the default websocket driver\n# For ODBC, use 'exasol+pyodbc://'\nconnection_string = f\"exasol+websocket://{EXASOL_USER}:{EXASOL_PASSWORD}@{EXASOL_HOST}:{EXASOL_PORT}/{EXASOL_SCHEMA}\"\n\ntry:\n    # Create the engine\n    engine = create_engine(connection_string)\n\n    # Test the connection and execute a simple query\n    with engine.connect() as connection:\n        result = connection.execute(text(\"SELECT 1 FROM DUAL\")).scalar()\n        print(f\"Successfully connected to Exasol. Query result: {result}\")\n\n    # Example: Create a table\n    with engine.connect() as connection:\n        connection.execute(text(\"CREATE SCHEMA IF NOT EXISTS MY_SCHEMA\"))\n        connection.execute(text(\"CREATE TABLE IF NOT EXISTS MY_SCHEMA.test_table (id INT, name VARCHAR(255))\"))\n        connection.commit()\n        print(\"Table MY_SCHEMA.test_table created or already exists.\")\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":">=6.0.0"},{"fix":"Upgrade your Python environment to version 3.10 or newer. `sqlalchemy-exasol` 6.1.1 requires Python <3.14,>=3.10.","message":"Support for Python 3.9 was dropped in version 5.2.0, and Python 3.8 support was dropped in 5.1.0.","severity":"deprecated","affected_versions":">=5.1.0 (for 3.8), >=5.2.0 (for 3.9)"},{"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.","message":"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.","severity":"gotcha","affected_versions":"<6.1.1"},{"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.","message":"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.","severity":"deprecated","affected_versions":">=5.0.0"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"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.","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.","error":"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."},{"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.","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.","error":"ModuleNotFoundError: No module named 'pyodbc'"},{"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.","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.","error":"sqlalchemy.exc.InvalidRequestError: Identity map for key (some_class, (some_id,), None) conflicts with an existing identity.  Existing instance: <SomeClass object at 0x...>"},{"fix":"Ensure `sqlalchemy-exasol` is installed: `pip install sqlalchemy-exasol`. If using a custom registration, verify the import path.","cause":"The `sqlalchemy-exasol` package is not installed, or the dialect is not correctly registered within SQLAlchemy.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:exasol"}]}