Connecting to HANA with pyHANA

Hi gurus!

As a SAP technical architect by trade and Python developer at heart, I have been wanting to try out the capabilities of pyHANA for a while now. pyHANA is an open source-maintained fork of the PyHDB project, a 100% Python client for HANA, based on the SAP HANA SQL Command Network Protocol.

In this post, I will detail, step by step, how to connect pyHANA to a HANA database and fetch a few records from the TABLES table. Let’s go!

First, let’s setup SAP HANA Studio so that we can write Python code with it. I am aware that SAP HANA Studio is not the best IDE for writing Python, and I personally prefer vi and PyCharm, but I thought it would make sense to work from the official HANA IDE for this. Why not?

(In this post, I am using SAP HANA Studio 2.3.41.)

In HANA Studio, let’s install pyDev. Go to Help > Install New Software.

Then Work with: http://www.pydev.org/updates. Select all and install all the PyDev packages.

When it’s all installed, open the PyDev perspective.

Then create a new PyDev project.

Time to install pyHANA!

After making sure that git is set in your PATH, use the following command to pip install it (in my case I am using Anaconda):

pip install git+https://github.com/hpi-epic/pyhana.git

(base) C:\Users\b89557>pip install git+https://github.com/hpi-epic/pyhana.git
Collecting git+https://github.com/hpi-epic/pyhana.git
Cloning https://github.com/hpi-epic/pyhana.git to c:\users\b89557\appdata\local\temp\pip-req-build-opvwg5yr
Building wheels for collected packages: pyhdb
Running setup.py bdist_wheel for pyhdb ... done
Stored in directory: C:\Users\b89557\AppData\Local\Temp\pip-ephem-wheel-cache-10jp7gk7\wheels\67\c9\e1\3ef865c755599b471c10500b98e0a5bde2e2a721ad73a53029
Successfully built pyhdb
Installing collected packages: pyhdb
Found existing installation: pyhdb 0.3.4
Uninstalling pyhdb-0.3.4:
Successfully uninstalled pyhdb-0.3.4
Successfully installed pyhdb-0.3.4.dev0

Then in HANA Studio, create a .py Python file within your project and run the following code. The function pyhdb.connect creates a new database session and returns an instance of the Connection class. As a port, you must select 3<instance number>15; if your instance number is 00, your port should be 30015.

import pyhdb
connection = pyhdb.connect(
    host="yourhostname",
    port="yourport",
    user="youruser",
    password="yourpassword"
)

Then, with the cursor method of your connection object, you can create an instance of Cursor that is able to query the database using SQL.

cursor = connection.cursor()
cursor.execute("SELECT TABLE_NAME FROM TABLES")
print(cursor.fetchmany(10))
connection.close()

And then you get:

[('CS_ATTRIBUTE_MAPPING_',), ('CS_CONCAT_ATTRIBUTES_',), ('CS_JOIN_PHYSICAL_INDEXES_',), ('CS_KEY_FIGURES_',), ('CS_VIEW_ATTRIBUTES_',), ('CS_COLUMNS_',), ('CS_CALC_INDEXES_',), ('CS_HIERARCHY_DEFINITIONS_',), ('CS_JOIN_INDEXES_',), ('CS_TABLES_',)]

That’s all folks! We connected pyHANA to our HANA database and run a simple SQL query on it. Watch this space for more content about leveraging the power of Python and the HANA platform!