pyexasol Exasol Python Driver

2.1.0 · active · verified Sun Mar 29

pyexasol is the officially supported Python connector for Exasol, designed for high-performance data handling with low overhead, fast HTTP transport, and compression. It provides an API for parallel data stream processing, offering significant performance improvements over ODBC/JDBC solutions, especially with `pandas`, `parquet`, and `polars`. The library is actively maintained with a regular release cadence, often seeing monthly or bi-monthly updates.

Warnings

breaking Python 3.9 support was dropped in PyExasol v2.0.0. Projects using Python 3.9 or older must upgrade their Python version to >=3.10 to use PyExasol v2.0.0 or later.
Fix: Upgrade your Python environment to 3.10 or newer.
breaking The `export_params['with_column_names']` parameter for export functions (e.g., `export_to_pandas`) now strictly requires a boolean value. Prior to v2.0.0, its mere presence would be interpreted as `True` regardless of the assigned value.
Fix: Ensure `export_params['with_column_names']` is explicitly set to `True` or `False`.
breaking From PyExasol v1.0.0, strict certificate verification became the default behavior for `pyexasol.connect()` and `ExaConnection`. This changes the default `websocket_sslopt=None` from effectively `{'cert_reqs': ssl.CERT_NONE}` to `{'cert_reqs': ssl.CERT_REQUIRED}`, potentially causing `SSL: CERTIFICATE_VERIFY_FAILED` errors for users not explicitly configuring SSL options. Version 1.0.1 introduced a partial mitigation for fingerprint users.
Fix: For production, provide valid SSL certificates via `websocket_sslopt={'ca_certs': 'path/to/cert.pem'}` or use server fingerprints. For development/testing on untrusted networks, consider `websocket_sslopt={'check_hostname': False, 'verify_mode': 0}` (use with caution and never in production).
gotcha When using `export_to_parquet`, the destination directory (dst) by default must be empty or not exist. If it exists and contains files, an exception may be raised unless `callback_params['existing_data_behavior']` is set to 'overwrite_or_ignore' or 'delete_matching'.
Fix: Ensure the `dst` directory is empty, doesn't exist, or explicitly set `callback_params={'existing_data_behavior': 'overwrite_or_ignore'}` (or 'delete_matching') when calling `export_to_parquet`.
gotcha When exporting data to pandas using `export_to_pandas`, data types might not be perfectly preserved due to differences in Exasol and pandas/NumPy type systems (e.g., Exasol's exact decimals or large integers may map to floats or objects in pandas, potentially losing precision). The underlying mechanism uses CSV export/import.
Fix: For critical data type preservation, inspect column types after import. Use `callback_params` to pass custom `dtype` arguments to `pandas.read_csv` if necessary, or use `stmt.fetchall()` and manually construct a DataFrame with explicit dtypes.
gotcha PyExasol v2.1.0 improved error reporting for `import_from_callback` and `export_to_callback` by wrapping exceptions from various internal threads. Earlier versions might have provided less clear error messages during data transfer failures, making debugging harder.
Fix: Upgrade to v2.1.0 or later for enhanced error diagnostics in import/export operations involving callbacks. For older versions, examine logs from HTTP and SQL threads for more context.

Install

pip install pyexasol Install core library
pip install pyexasol[pandas,pyarrow,polars,orjson] Install with common optional dependencies

Imports

ExaConnection
```
from pyexasol import ExaConnection
```
While `pyexasol.connect()` is a common entry point, `ExaConnection` is the primary class for direct interaction and better type hinting.

Quickstart

This quickstart demonstrates how to establish a connection to an Exasol database, execute DDL and DML statements, and fetch results. It highlights the use of `ExaConnection` and basic SQL operations. For local testing without valid SSL certificates, you might need to adjust `websocket_sslopt` as noted in the code comments. Credentials should ideally be managed via environment variables or a secure configuration.

import os
from pyexasol import ExaConnection

# Replace with your Exasol connection details or environment variables
EXASOL_HOST = os.environ.get('EXASOL_HOST', '127.0.0.1')
EXASOL_PORT = os.environ.get('EXASOL_PORT', '8563')
EXASOL_USER = os.environ.get('EXASOL_USER', 'sys')
EXASOL_PASSWORD = os.environ.get('EXASOL_PASSWORD', 'exasol')

try:
    # Connect to the Exasol database
    # For production, ensure proper SSL options and fingerprint are used
    # For local/testing without certs, add: websocket_sslopt={'check_hostname': False, 'verify_mode': 0}
    # See warnings for v1.0.0 regarding strict certificate verification defaults.
    con = ExaConnection(
        dsn=f"{EXASOL_HOST}:{EXASOL_PORT}",
        user=EXASOL_USER,
        password=EXASOL_PASSWORD
    )
    print("Successfully connected to Exasol.")

    # Execute a DDL statement
    con.execute("CREATE SCHEMA IF NOT EXISTS MY_SCHEMA")
    con.execute("OPEN SCHEMA MY_SCHEMA")

    # Execute a DML statement
    con.execute("CREATE OR REPLACE TABLE my_table (id INT, name VARCHAR(100))")
    con.execute("INSERT INTO my_table VALUES (1, 'Alice'), (2, 'Bob')")

    # Fetch data
    stmt = con.execute("SELECT * FROM my_table ORDER BY id")
    results = stmt.fetchall()
    print("Fetched results:", results)

    # Fetch data into a pandas DataFrame (requires `pyexasol[pandas]`)
    # import pandas as pd
    # df = con.export_to_pandas("SELECT * FROM my_table")
    # print("Fetched into DataFrame:\n", df)

except Exception as e:
    print(f"An error occurred: {e}")
finally:
    if 'con' in locals() and con.is_connected():
        con.close()
        print("Connection closed.")

view raw JSON →