Skip to Content
Technical Articles
Author's profile photo Nicolas Berthier

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!

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sabarna Chatterjee
      Sabarna Chatterjee

      Hi Nicolas,

      I am getting the below error :

      >>> connection = pyhdb.connect(host="sap://vadbirn.nwtrial.od.sap.biz",port=30262,user="SYSTEM",password="XXX")
      Traceback (most recent call last):
        File "<stdin>", line 1, in <module>
        File "/home/sabarna17/.local/lib/python3.7/site-packages/pyhdb/__init__.py", line 30, in connect
          conn.connect()
        File "/home/sabarna17/.local/lib/python3.7/site-packages/pyhdb/connection.py", line 137, in connect
          self._open_socket_and_init_protocoll()
        File "/home/sabarna17/.local/lib/python3.7/site-packages/pyhdb/connection.py", line 66, in _open_socket_and_init_protocoll
          self._socket = socket.create_connection((self.host, self.port), self._timeout)
        File "/usr/lib/python3.7/socket.py", line 707, in create_connection
          for res in getaddrinfo(host, port, 0, SOCK_STREAM):
        File "/usr/lib/python3.7/socket.py", line 748, in getaddrinfo
          for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
      socket.gaierror: [Errno -2] Name or service not known
      

       

      Can you please help me out in this scenario