Connecting Python Application to SAP HANA (XSA)

In my last blog post, Getting Started with Python on SAP HANA XSA, I covered how to set up Python application development for XSA, from deploying the Python runtime to using it for a simple Hello World application. In this post, I will explain how to connect your application to the HANA Database and make simple SQL queries. I will use the same pyapp application from the previous post. If you have not followed along with the previous post, you can download the code from this link.

Binding Application to XSA Services

You can bind different XSA services to your Python application by specifying them as dependencies in the manifest.yml file. This is useful when you are trying to connect your application to a database or external APIs that you will be sending HTTP requests to. In this blog, I’ll illustrate an example of connecting to a SAP HANA database and executing a simple SQL query. From a command prompt, execute the xs create-service command to create a service called hdi-db, which will provide connectivity to your HANA database as shown below:

xs create-service hana hdi-shared hdi-db

To bind hdi-db to your application, specify it as a dependency for pyapp in the manifest.yml file as shown below:

>>> manifest.yml
---
applications:
- name: pyapp
  host: pyapp
  path: .
  command: python server.py
  services:
   - hdi-db

The service, hdi-db, has now been bound to your pyapp application. This means that during deployment (when you execute xs push), XSA will incorporate details, such connection properties and credentials, about hdi-db within pyapp’s environment variables. You can access these environment variables using the cfenv library and connect to the hdi-db service within your code. Modify the server.py file as shown:

>>> server.py
import os
from flask import Flask
from cfenv import AppEnv     

app = Flask(__name__)
env = AppEnv()		#used to obtain environment variables

port = int(os.environ.get('PORT', 3000))
hana = env.get_service(name='hdi-db')	#connect to hdi-db service

@app.route('/')
def hello():
	return "Hello World"

if __name__ == '__main__':
app.run(port=port)

Update the requirements.txt file to specify the dependency on cfenv library.

>>> requirements.txt
Flask==0.12.2
cfenv==0.5.3

And done! The hdi-db service has been bound your pyapp application and is ready to be used within your code. Exciting!!

Connecting Application to SAP HANA

Before connecting your application to SAP HANA, make sure you install the SAP provided packages for Python that will be used in the application. Download the XS PYTHON 1 zip file from this link to a local directory called sap_dependencies. I would recommend saving it in a place you can access easily as you will be installing packages from this directory to your application for almost any application.

Create a new folder called vendor in the pyapp directory. This folder holds all Python packages that your application depends on, similar to the node_modules folder used on the Web IDE for SAP HANA. Update the requirements.txt file to include the SAP HANA Database client library hdbcli as well. You will use this library to connect to SAP HANA later.

>>> requirements.txt
Flask==0.12.2
cfenv==0.5.3
hdbcli

Now, download the packages listed in the requirements.txt file into the vendor folder.

pip download -d C:\path\to\vendor -r C:\path\to\requirements.txt --find-links C:\path\to\sap_dependencies

It is important to note here that some of the packages are platform specific, so if you download them using a Windows machine, like I did, the Windows-compatible versions will be installed. This can cause issues in deploying your application since SAP HANA runs on Linux and requires Linux-compatible versions. For instance, the package hdbcli is platform specific. If you execute the pip download command from Windows, the Windows-compatible version would be installed. This is not the correct version for SAP HANA, and you will get an error if you use this package to deploy your application. To work around this, you can manually transfer the Linux specific hdbcli package from the XS PYTHON 1 zip file saved in sap_dependencies folder to the vendor folder of the application.

Alternatively, you can install the packages on your Linux machine and transfer them over to Windows using an FTP (i.e. FileZilla). If you are using Linux completely for all of development, you don’t need to worry, pip download will install the appropriate packages for your system.

Once all the dependencies have been installed, you can move on to incorporate SAP HANA connectivity in your application logic. First, you need import the dbapi module from hdbcli library and establish a connection to the HANA database using the connect() method. You can use the hana variable which you initialized in the previous section to pull database credentials from the hdi-db service binding. Finally, you can verify the connection by executing a SQL query to retrieve and display the current time. The code snippet below outlines these changes for the server.py file.

>>> server.py
import os
from flask import Flask
from cfenv import AppEnv
from hdbcli import dbapi

app = Flask(__name__)		
env = AppEnv()		

port = int(os.environ.get('PORT', 3000))
hana = env.get_service(name='hdi-db')	

@app.route('/')
def hello():
    #connect to DB using credentials from hdi-db service
    conn = dbapi.connect(address=hana.credentials['host'],
                         port= int(hana.credentials['port']),
                         user = hana.credentials['user'],
                         password = hana.credentials['password'],
                         CURRENTSCHEMA=hana.credentials['schema'])
    cursor = conn.cursor()

    #execute SQL query
    cursor.execute("select CURRENT_UTCTIMESTAMP from DUMMY", {})      
    ro = cursor.fetchone()        #get the first result
    cursor.close()
    conn.close()        #close DB connection

    #return query results
    return "Current time is: " + str(ro["CURRENT_UTCTIMESTAMP"])  

if __name__ == '__main__':
    app.run(port=port)

You can now deploy and run this application from command prompt as shown.

cd C:\path\to\pyapp
xs push pyapp

Et voila! You’ve now been connected to the SAP HANA Database! Open the pyapp application URL that is displayed once your application gets deployed. If you have done everything correctly, you should get the following output:

That’s it! You should now be able to make database queries and incorporate results within your Python application. I will cover database access in greater detail with more examples in another blog post coming up. Keep an eye out on the comments section to get the link to the post once it is published.

Setting Up Logging

As your application logic gets longer and more complicated, it would a good idea to set up logging as well to be able to debug. In order to do so, you need to import the sap_cf_logging library. Following the same steps as earlier, insert “sap_cf_logging==3.0.0” to your requirements.txt file and run the pip download command to download the library to the vendor folder from the sap_dependencies folder.

The code snippet below shows how you can set up logging and use some of its functionality within your server.py file.

>>> server.py
import os
from flask import Flask
from cfenv import AppEnv
from hdbcli import dbapi

import logging
from cf_logging import flask_logging

app = Flask(__name__)		
env = AppEnv()		

flask_logging.init(app, logging.INFO)
logger = logging.getLogger('route.logger')

port = int(os.environ.get('PORT', 3000))
hana = env.get_service(name='hdi-db')	

@app.route('/')
def hello():
    conn = dbapi.connect(address=hana.credentials['host'],
                         port= int(hana.credentials['port']),
                         user = hana.credentials['user'],
                         password = hana.credentials['password'],
                         CURRENTSCHEMA=hana.credentials['schema'])
    
    # check if database connection has been established successfully
    # and output to application logs
    if conn.isconnected():
        logger.info('Connection to database successful')      
    else:
        logger.info('Unable to connect to database')

    cursor = conn.cursor()
    cursor.execute("select CURRENT_UTCTIMESTAMP from DUMMY", {})
    ro = cursor.fetchone()
    cursor.close()
    conn.close()

    return "Current time is: " + str(ro["CURRENT_UTCTIMESTAMP"])

if __name__ == '__main__':
    app.run(port=port)

There you have it! Your application should be much easier to debug with logging enabled. You can access logs for any application by executing the xs logs from the command prompt as shown below.

xs logs <app_name> --recent

Hopefully, this blog helps you advance in your journey to using Python with XSA. In my next blog, I will cover details on how to set up user authentication and authorization using XSA with Python. You can access the blog at this link. As always, if you run into any errors while following along with this blog or have other feedback, feel free to comment below!