Getting Started#

Welcome to PyExasol#

PyExasol is the officially supported Python connector that revolutionizes how users interact with Exasol databases. This powerful tool is specifically designed to handle massive volumes of data with efficiency, providing a performance boost over traditional ODBC/JDBC solutions.

Why Choose PyExasol?#

  • Easy and fast access to Exasol from Python

  • Bulk import and export from/to pandas, parquet , and polars to Exasol`

  • Exasol UDF debugging support

Prerequisites#

  • Exasol >= 8

  • Python >= 3.10

Optional Dependencies#

  • orjson is required for json_lib=orjson to improve JSON parsing performance

  • pandas is required for Importing and Exporting Data functions working with pandas.DataFrame

  • polars is required for Importing and Exporting Data functions working with polars.DataFrame

  • pyarrow is required for Importing and Exporting Data functions working with pyarrow.parquet

  • pproxy is used in the Examples to test an HTTP proxy

  • rapidjson is required for json_lib=rapidjson to improve JSON parsing performance

  • ujson is required for json_lib=ujson to improve JSON parsing performance

Installing#

PyExasol is distributed through PyPI. It can be installed via pip, poetry, or any other compatible dependency management tool:

pip install pyexasol

To install with optional dependencies, use:

pip install pyexasol[<optional-package-name>]

For a list of optional dependencies, see Optional Dependencies.

First Steps#

For a user’s first steps, it is recommended to try out running basic queries and exporting data from an Exasol table well-known Python packages, like pandas or polars.

Note

These examples are written assuming a newly installed or otherwise safe-to-test Exasol database. If that is not the case, it is recommended, in particular with the export examples, to check the API Reference and your to-be-queried table to ensure that the output you will receive is as desired (and not, i.e. millions of rows).

Run basic query#

Note

For more options when running a basic query, check out pyexasol.ExaStatement, which is the returned object from C.execute().

examples/getting_started/basic_query.py#
"""
Getting Started - Run basic queries
"""

import examples._config as config
import pyexasol

# Using a context manager ensures the connection is properly closed
# even if an exception occurs
with pyexasol.connect(
    dsn=config.dsn,
    user=config.user,
    password=config.password,
    websocket_sslopt=config.websocket_sslopt,
) as C:
    with C.execute("SELECT * FROM EXA_ALL_USERS") as stmt:
        print(stmt.fetchone())  # fetch 1 row
        print(stmt.fetchmany(3))  # fetch 3 rows
        print(stmt.fetchall())  # fetch all remaining rows

    # The `is_closed` check is not needed code execution, but it shows the status of the context manager.
    print(stmt.is_closed)
print(C.is_closed)

# You can also iterate through rows directly
with pyexasol.connect(
    dsn=config.dsn,
    user=config.user,
    password=config.password,
    websocket_sslopt=config.websocket_sslopt,
) as C:
    with C.execute("SELECT * FROM EXA_ALL_USERS") as stmt:
        for row in stmt:
            print(row)

Export data into a DataFrame#

Using pandas#

examples/getting_started/export_to_pandas.py#
"""
Getting Started - Export data to pandas DataFrame
"""

# pip install pyexasol[pandas]
import examples._config as config
import pyexasol

with pyexasol.connect(
    dsn=config.dsn,
    user=config.user,
    password=config.password,
    compression=True,
    websocket_sslopt=config.websocket_sslopt,
) as C:
    df = C.export_to_pandas("SELECT * FROM EXA_ALL_USERS")
print(df.head())

Using parquet#

examples/getting_started/export_to_parquet.py#
"""
Getting Started - Export data to parquet file(s)
"""

# pip install pyexasol[pyarrow]
import tempfile
from pathlib import Path

import examples._config as config
import pyexasol

with pyexasol.connect(
    dsn=config.dsn,
    user=config.user,
    password=config.password,
    compression=True,
    websocket_sslopt=config.websocket_sslopt,
) as C:
    # export_to_parquet requires a destination directory, not a query alone
    with tempfile.TemporaryDirectory() as dst:
        C.export_to_parquet(dst=dst, query_or_table="SELECT * FROM EXA_ALL_USERS")
        files = list(Path(dst).glob("*.parquet"))
        print(f"Exported {len(files)} parquet file(s) to {dst}")

Using polars#

examples/getting_started/export_to_polars.py#
"""
Getting Started - Export data to polars DataFrame
"""

# pip install pyexasol[polars]
import examples._config as config
import pyexasol

with pyexasol.connect(
    dsn=config.dsn,
    user=config.user,
    password=config.password,
    compression=True,
    websocket_sslopt=config.websocket_sslopt,
) as C:
    df = C.export_to_polars("SELECT * FROM EXA_ALL_USERS")
print(df.head())

Telemetry#

Exasol software includes services that allow installations to check for available updates and to provide anonymized usage information. These services help ensure that software remains up to date and enable Exasol to better understand how its publicly available software is used in practice, improve product quality, prioritize features, and estimate the size of its active user base. For more information, please refer to Exasol’s Privacy Policy.

To disable telemetry, you can set the environment variable EXASOL_TELEMETRY_DISABLE (any value will work).

Diving Deeper#

The PyExasol documentation covers many topics at different levels of experience: