Skip to Content

Connect Using the SAP HANA Python Interface

Create and debug a Python application that connects to SAP HANA using the SAP HANA client.
You will learn
  • How to install Python and the SAP HANA client Python driver
  • How to create and debug a Python application that queries a SAP HANA database
danielvaDan van LeeuwenSeptember 20, 2022
Created by
danielva
March 29, 2020
Contributors
danielva
thecodester

Prerequisites

  • You have completed the first 3 tutorials in this mission.

In the 2020 Stack Overflow’s annual developer survey, Python ranked 4th in the Most Popular Technologies section. For further information on Python, see Introduction to Python 3.

The following steps create a simple Python app that can connect to and query an SAP HANA database.

  • Step 1

    The first step is to check if Python is installed.

    Enter the commands below.

    Shell
    Copy
    python --version
    python3 --version
    

    If Python is installed, the command will return a value such as Python 3.10.3.

    Details on supported versions of Python for the SAP HANA client for Python can be found at SAP Note 3165810 - SAP HANA Client Supported Platforms.

    If Python is not installed, it can be downloaded from Python downloads.

    On Microsoft Windows, check the box that says Add Python 3.x to PATH as shown below to ensure that the interpreter will be placed in your path.

    python-install

    You should now be able to open a new shell and verify that Python is installed.

  • Step 2

    The standard package installer for Python is pip. The following commands will check the version of pip and attempt to upgrade it to the latest available version.

    Shell
    Copy
    pip --version
    pip3 --version
    pip install --upgrade pip
    

    On Linux or Mac, if you encounter permission issues, one way to solve the issue is to use sudo before the command.


    On Linux, if Python is installed but pip is not, it can be installed on openSUSE using Zypper as shown below.

    Shell (Linux)
    Copy
    zypper install python3-pip
    

    The repository that contains Python packages is PyPI and includes a package for the SAP HANA client for Python.

    hdbcli on PyPI

    Run the following command to download and install the SAP HANA client for Python from PyPI:

    Shell
    Copy
    pip install hdbcli
    

    If an error is displayed that mentions "No matching distribution found for hdbcli, an alternate method is to install it from the SAP HANA client install folder as shown below.

    Shell
    Copy
    cd C:\SAP\hdbclient
    pip install hdbcli-2.12.13.zip
    

    If the install still fails, check 3165810 - SAP HANA Client Supported Platforms to ensure that a supported version of Python installed.


    To upgrade to the latest available version, enter the following command:

    Shell
    Copy
    pip install --no-cache-dir --upgrade hdbcli
    

    To uninstall hdbcli, run the following command:

    Shell
    Copy
    pip uninstall hdbcli
    

    A specific version can be installed using the following command.

    Shell
    Copy
    pip install hdbcli==2.4.167
    

    The list of installed packages can be shown with the following command.

    Shell
    Copy
    pip list
    

    Details of an installed package such as hdbcli can be shown with the following command.

    Shell
    Copy
    pip show hdbcli
    
  • Step 3
    1. In a shell, create a folder named python, enter the newly created directory, and open a file name pythonQuery.py in an editor.

      Shell (Microsoft Windows)
      Copy
      mkdir %HOMEPATH%\HANAClientsTutorial\python
      cd %HOMEPATH%\HANAClientsTutorial\python
      notepad pythonQuery.py
      

      Substitute pico below for your preferred text editor.

      Shell (Linux or Mac)
      Copy
      mkdir -p $HOME/HANAClientsTutorial/python
      cd $HOME/HANAClientsTutorial/python
      pico pythonQuery.py
      
    2. Add the code below to pythonQuery.py.

      Python
      Copy
      #Import your dependencies
      import platform
      from hdbcli import dbapi
      
      #verify the architecture of Python
      print ("Platform architecture: " + platform.architecture()[0])
      
      #Initialize your connection
      conn = dbapi.connect(
          #Option 1, retrieve the connection parameters from the hdbuserstore
          key='USER1UserKey', # address, port, user and password are retrieved from the hdbuserstore
      
          #Option2, specify the connection parameters
          #address='10.7.168.11',
          #port='39015',
          #user='User1',
          #password='Password1',
      
          #Additional parameters
          #encrypt=True, # must be set to True when connecting to HANA as a Service
          #As of SAP HANA Client 2.6, connections on port 443 enable encryption by default (HANA Cloud)
          #sslValidateCertificate=False #Must be set to false when connecting
          #to an SAP HANA, express edition instance that uses a self-signed certificate.
      )
      #If no errors, print connected
      print('connected')
      
      cursor = conn.cursor()
      sql_command = "select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;"
      cursor.execute(sql_command)
      rows = cursor.fetchall()
      for row in rows:
          for col in row:
              print ("%s" % col, end=" ")
          print ("  ")
      cursor.close()
      print("\n")
      
      #Prepared statement example
      sql_command2 = "call HOTEL.SHOW_RESERVATIONS(?,?);"
      parameters = [11, "2020-12-24"]
      cursor.execute(sql_command2, parameters)
      rows = cursor.fetchall()
      for row in rows:
          for col in row:
              print ("%s" % col, end=" ")
          print (" ")
      cursor.close()
      conn.close()
      

      Note that the address, port, UID and PWD will be retrieved from the hdbuserstore.

    3. Run the app.

      Shell
      Copy
      python pythonQuery.py
      

      On some Linux distributions, python refers to a 2.x version of Python. If so, replace python with python3.

      python Query

    The code in pythonQuery.py uses PEP 249 – Python Database API Specification, which defines a set of methods that provide a consistent database interface, independent of the actual database being used.

  • Step 4

    Visual Studio Code provides plugins for Python and can be used to debug an application.

    1. If you have not already done so, download Visual Studio Code.

    2. If you have not already done so, in Visual Studio Code, choose File | Add Folder to Workspace, and then add the HANAClientsTutorial folder.

      Workspace
    3. Open the file pythonQuery.py.

      Python Extension

      Visual Studio Code will recognize the py file extension and will suggest installing the Python extension. Click Install.

    4. Place a breakpoint on line the line for row in rows:.

    5. Select Run | Start Debugging | Python File Debug the currently active Python file.

      Notice that the program stops running at the breakpoint that was set.

      Observe the variable values in the leftmost pane. Step through code.

      VS Code Debugging

    Congratulations! You have now created and debugged a Python application that connects to and queries an SAP HANA database.

    Which of the following statements are true?

Back to top