SAP / sqlalchemy-hana

SQLAlchemy dialect for SAP HANA =============================== .. image:: https://api.reuse.software/badge/github.com/SAP/sqlalchemy-hana :target: https://api.reuse.software/info/github.com/SAP/sqlalchemy-hana .. image:: https://img.shields.io/badge/code%20style-black-000000.svg :target: https://github.com/psf/black .. image:: https://coveralls.io/repos/github/SAP/sqlalchemy-hana/badge.svg :target: https://coveralls.io/github/SAP/sqlalchemy-hana This dialect allows you to use the SAP HANA database with SQLAlchemy. It uses hdbcli to connect to SAP HANA. Please notice that sqlalchemy-hana isn't an official SAP product and isn't covered by SAP support. Prerequisites ------------- • Python 3.10+ • SQLAlchemy 2.x • _ Install ------- Install from the Python Package Index: .. code-block:: bash $ pip install sqlalchemy-hana Versioning ---------- sqlalchemy-hana follows the semantic versioning standard, meaning that breaking changes will only be added in major releases. Please note, that only the following modules are considered to be part of the public API • sqlalchemy_hana.types • sqlalchemy_hana.errors • sqlalchemy_hana.elements • sqlalchemy_hana.functions For these, only exported members (part of __all__ ) are guaranteed to be stable. Supported HANA Versions/Editions -------------------------------- • SAP HANA Cloud • SAP HANA • SAP HANA, express edition Getting started --------------- If you do not have access to a SAP HANA server, you can also use the _. After installation of sqlalchemy-hana, you can create a engine which connects to a SAP HANA instance. This engine works like all other engines of SQLAlchemy. .. code-block:: python from sqlalchemy import create_engine engine = create_engine('hana://username:password@example.de:30015') Alternatively, you can use HDB User Store to avoid entering connection-related information manually each time you want to establish a connection to an SAP HANA database: .. code-block:: python from sqlalchemy import create_engine engine = create_engine('hana://userkey=my_user_store_key') You can create your user key in the user store using the following command: .. code-block:: hdbuserstore SET In case of a tenant database, you may use: .. code-block:: python from sqlalchemy import create_engine engine = engine = create_engine('hana://user:pass@host/tenant_db_name') Usage ----- Special CREATE TABLE argument ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sqlalchemy-hana provides a special argument called “hana_table_type” which can be used to specify the type of table one wants to create with SAP HANA (i.e. ROW/COLUMN). The default table type depends on your SAP HANA configuration and version. .. code-block:: python t = Table('my_table', metadata, Column('id', Integer), hana_table_type = 'COLUMN') Case Sensitivity ~~~~~~~~~~~~~~~~ In SAP HANA, all case insensitive identifiers are represented using uppercase text. In SQLAlchemy on the other hand all lower case identifier names are considered to be case insensitive. The sqlalchemy-hana dialect converts all case insensitive and case sensitive identifiers to the right casing during schema level communication. In the sqlalchemy-hana dialect, using an uppercase name on the SQLAlchemy side indicates a case sensitive identifier, and SQLAlchemy will quote the name,which may cause case mismatches between data received from SAP HANA. Unless identifier names have been truly created as case sensitive (i.e. using quoted names), all lowercase names should be used on the SQLAlchemy side. LIMIT/OFFSET Support ~~~~~~~~~~~~~~~~~~~~ SAP HANA supports both LIMIT and OFFSET , but it only supports OFFSET in conjunction with LIMIT i.e. in the select statement the offset parameter cannot be set without the LIMIT clause, hence in sqlalchemy-hana if the user tries to use offset without limit, a limit of 2147384648 would be set, this has been done so that the users can smoothly use LIMIT or OFFSET as in other databases that do not have this limitation. 2147384648 was chosen, because it is the maximum number of records per result set. RETURNING Support ~~~~~~~~~~~~~~~~~ Sqlalchemy-hana does not support RETURNING in the INSERT , UPDATE and DELETE statements to retrieve result sets of matched rows from INSERT , UPDATE and DELETE statements because newly generated primary key values are neither fetched nor returned automatically in SAP HANA and SAP HANA does not support the syntax INSERT... RETURNING... . Reflection ~~~~~~~~~~ The sqlalchemy-hana dialect supports all reflection capabilities of SQLAlchemy. The Inspector used for the SAP HANA database is an instance of HANAInspector and offers an additional method which returns the OID (object id) for the given table name. .. code-block:: python from sqlalchemy import create_engine, inspect engine = create_engine("hana://username:password@example.de:30015") insp = inspect(engine) # will be a HANAInspector print(insp.get_table_oid('my_table')) Foreign Key Constraints ~~~~~~~~~~~~~~~~~~~~~~~ In SAP HANA the following UPDATE and DELETE foreign key referential actions are available: • RESTRICT • CASCADE • SET NULL • SET DEFAULT The foreign key referential option NO ACTION does not exist in SAP HANA. The default is RESTRICT . UNIQUE Constraints ~~~~~~~~~~~~~~~~~~ For each unique constraint an index is created in SAP HANA, this may lead to unexpected behavior in programs using reflection. Data types ~~~~~~~~~~ As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with SAP HANA are importable from the top level dialect, whether they originate from sqlalchemy types or from the local dialect. Therefore all supported types are part of the sqlalchemy_hana.types module and can be used from there. sqlalchemy-hana aims to support as many SQLAlchemy types as possible and to fallback to a similar type of the requested type is not supported in SAP HANA. The following table shows the mapping: .. list-table:: :header-rows: 1 • - SQLAlchemy t…