SQLAlchemy dialect for SAP HANA

Warnings

• breaking Migration from `sqlalchemy-hana` 3.x (or older) to 4.x requires `SQLAlchemy 2.x` and `Python 3.10+`. Older `sqlalchemy-hana` versions often targeted `SQLAlchemy 1.x` and might have used the `pyhdb` driver, which is no longer supported by current `sqlalchemy-hana` versions that exclusively use `hdbcli`. This necessitates updating `SQLAlchemy` and potentially adjusting code for `SQLAlchemy 2.x`'s API changes.
  Fix: Upgrade SQLAlchemy to version 2.x and Python to 3.10+. Replace any `pyhdb` driver configurations with `hdbcli`. Review SQLAlchemy's 2.x migration guide for API changes in your application.
• gotcha By default, `sqlalchemy-hana` now uses native boolean types. Older versions represented booleans as integers. For compatibility with existing databases or applications expecting integer representation, `use_native_boolean=False` must be passed to the `create_engine` function.
  Fix: Add `use_native_boolean=False` to your `create_engine` call if you need to maintain integer representation for boolean columns. Example: `create_engine('hana://...', use_native_boolean=False)`.
• gotcha `UPSERT` statements are supported but with limitations. Notably, caching for `UPSERT` operations is explicitly disabled due to implementation details. This may impact performance expectations for bulk `UPSERT` operations and might require alternative strategies or custom SQL.
  Fix: Be aware of potential performance implications for `UPSERT`. Consider using SQLAlchemy's bulk insert/update features with a custom `on_conflict_do_update` clause if more control or specific performance is needed, although a unified implementation is still pending in SQLAlchemy itself.
• gotcha SQL views are not bound to the SQLAlchemy metadata object. Therefore, they cannot be automatically created or dropped with `MetaData.create_all()` or `MetaData.drop_all()`. Views need to be explicitly managed using `CreateView` and `DropView` functions from `sqlalchemy_hana.elements`.
  Fix: Manually create and drop views using `connection.execute(CreateView(my_view))` and `connection.execute(DropView(my_view))`.
• gotcha `sqlalchemy-hana` is an open-source project and is not an official SAP product. As such, it is not covered by SAP support. Users should rely on the project's GitHub repository for issues, community support, and contributions.
  Fix: For support or issues, refer to the project's GitHub issues page: `https://github.com/SAP/sqlalchemy-hana/issues`.

Install

• pip install sqlalchemy-hana Basic installation
• pip install sqlalchemy-hana[alembic] With Alembic support

Imports

• create_engine

  from sqlalchemy import create_engine

• CreateView

  from sqlalchemy_hana.elements import CreateView

  Used for managing SQL views which are not bound to SQLAlchemy metadata.
• DropView

  from sqlalchemy_hana.elements import DropView

  Used for managing SQL views which are not bound to SQLAlchemy metadata.
• types

  from sqlalchemy_hana import types

  Provides SAP HANA specific data types.
• errors

  from sqlalchemy_hana import errors

  Provides SAP HANA specific error handling and wrapper methods.

Quickstart

This quickstart demonstrates how to establish a connection to an SAP HANA database using `sqlalchemy-hana` and execute a simple SQL query. It uses environment variables for connection parameters for security and flexibility. Alternatively, an HDB User Store key can be used with `hana://userkey=my_user_store_key`.

import os
from sqlalchemy import create_engine, text

# Environment variables for connection details (replace with your actual values or user store key)
HANA_USER = os.environ.get('HANA_USER', 'your_username')
HANA_PASSWORD = os.environ.get('HANA_PASSWORD', 'your_password')
HANA_HOST = os.environ.get('HANA_HOST', 'your_hana_host')
HANA_PORT = os.environ.get('HANA_PORT', '30015') # Default port for SAP HANA
HANA_TENANT_DB = os.environ.get('HANA_TENANT_DB', '') # Optional: for tenant databases
HANA_USERKEY = os.environ.get('HANA_USERKEY', '') # Optional: for hdbuserstore key

connection_string = f"hana://{HANA_USER}:{HANA_PASSWORD}@{HANA_HOST}:{HANA_PORT}"
if HANA_TENANT_DB:
    connection_string += f"/{HANA_TENANT_DB}"

if HANA_USERKEY:
    connection_string = f"hana://userkey={HANA_USERKEY}"


try:
    # Create an engine to connect to SAP HANA
    engine = create_engine(connection_string, echo=False)

    # Establish a connection and execute a simple query
    with engine.connect() as connection:
        result = connection.execute(text("SELECT 'Hello from SAP HANA!' AS greeting FROM DUMMY"))
        for row in result:
            print(row.greeting)

    print("Successfully connected to SAP HANA and executed a query.")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure HANA_USER, HANA_PASSWORD, HANA_HOST, HANA_PORT, "
          "and hdbcli are correctly configured or HANA_USERKEY is set.")