Connecting to HANA with pyHANA
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!