SAP HANA Python Client (hdbcli)

Warnings

• gotcha Unlike PEP 249, `hdbcli` has `autocommit` turned ON by default. This means changes are committed automatically after each DML statement (INSERT, UPDATE, DELETE) unless explicitly managed.
  Fix: Be aware of the autocommit behavior. If explicit transaction control is desired, you must manage transactions manually (e.g., using `conn.begin()`, `conn.commit()`, `conn.rollback()`).
• gotcha When connecting to SAP HANA Cloud, the port number is typically 443, and encryption is always enabled by default. For on-premise HANA tenant databases, the port is often 3NN13, and for single-tenant 3NN15 (where NN is the SAP instance number). Ensure correct port and encryption settings for your specific HANA instance.
  Fix: Consult your SAP HANA database's connection details. Adjust the `port` parameter in `dbapi.connect()` accordingly. For HANA Cloud, `encryption=True` is often the default or required.
• gotcha The `hdbcli` package is distributed under the SAP Developer License Agreement, which is a proprietary license. Review the license terms for usage restrictions and compliance.
  Fix: Refer to the SAP Developer License Agreement (linked from PyPI and documentation) for full details.
• deprecated The installation of `hdbcli` via `.tar.gz` sdist is deprecated. Version 2.28 and later now default to `.whl` (wheel) files for installation. The `tar.gz` sdist will be removed in future versions.
  Fix: Always use `pip install hdbcli` to ensure installation from the preferred `.whl` distribution. Avoid manual installation from `.tar.gz` files where possible.
• gotcha There are known limitations for the 32-bit Windows driver, specifically regarding the maximum length of LOB columns and the maximum rowcount that can be returned (both limited to 2,147,483,647).
  Fix: If working with very large data volumes, use a 64-bit Python environment on Windows.
• gotcha Community reports indicate that specific `hdbcli` versions might exhibit connection issues with certain SAP HANA database versions, sometimes requiring version pinning for compatibility across different HANA instances.
  Fix: If encountering unexpected connection failures, try pinning to a known working `hdbcli` version or consult SAP support and documentation for compatibility matrices between `hdbcli` and your SAP HANA database version.

Install

• pip install hdbcli Install latest version

Imports

• dbapi

  from hdbcli import dbapi

  The primary interface for connecting to SAP HANA databases following PEP 249 is exposed via the 'dbapi' module within hdbcli.

Quickstart

This quickstart demonstrates how to establish a connection to an SAP HANA database using `hdbcli`, execute a simple `SELECT` statement, create a table, insert data, and fetch results. It uses environment variables for secure credential management. Remember to set `HANA_HOST`, `HANA_PORT`, `HANA_USER`, and `HANA_PASSWORD` in your environment.

import os
from hdbcli import dbapi

# Retrieve credentials from environment variables for security
host = os.environ.get('HANA_HOST', 'your_hana_host.com')
port = os.environ.get('HANA_PORT', '30015') # Example for single-tenant, adjust as needed
user = os.environ.get('HANA_USER', 'YOUR_USER')
password = os.environ.get('HANA_PASSWORD', 'YOUR_PASSWORD')

try:
    # Establish a connection to the SAP HANA database
    # For HANA Cloud, port is typically 443 with encryption=True (default).
    # For on-premise, adjust port (e.g., 3NN13/3NN15) and encryption.
    conn = dbapi.connect(address=host, port=int(port), user=user, password=password)
    print(f"Successfully connected to HANA at {host}:{port}")

    # Create a cursor object
    cursor = conn.cursor()

    # Execute a simple query
    cursor.execute("SELECT CURRENT_UTCTIMESTAMP FROM DUMMY")

    # Fetch the result
    result = cursor.fetchone()
    print(f"Current UTC Timestamp from HANA: {result[0]}")

    # Execute an update/insert (example: creating a table and inserting data)
    try:
        cursor.execute("DROP TABLE MY_TEST_TABLE_HDBCLI")
        print("Dropped existing MY_TEST_TABLE_HDBCLI.")
    except dbapi.Error as e:
        if "Cannot drop table" not in str(e): # Ignore if table doesn't exist
            print(f"Error dropping table: {e}")

    cursor.execute("CREATE TABLE MY_TEST_TABLE_HDBCLI (ID INTEGER PRIMARY KEY, NAME VARCHAR(255))")
    print("Created MY_TEST_TABLE_HDBCLI.")

    sql_insert = "INSERT INTO MY_TEST_TABLE_HDBCLI (ID, NAME) VALUES (?, ?)"
    cursor.execute(sql_insert, (1, 'Item One'))
    cursor.execute(sql_insert, (2, 'Item Two'))
    print("Inserted two rows.")

    # Fetch inserted data
    cursor.execute("SELECT * FROM MY_TEST_TABLE_HDBCLI")
    rows = cursor.fetchall()
    print("Data in MY_TEST_TABLE_HDBCLI:")
    for row in rows:
        print(row)

    # Commit the transaction (if autocommit is off, which it isn't by default in hdbcli)
    # conn.commit() # Not strictly necessary if autocommit is on (default in hdbcli)

except dbapi.Error as e:
    print(f"HANA Database error: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")
finally:
    # Close the cursor and connection
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'conn' in locals() and conn:
        conn.close()
        print("Connection closed.")