Accessing Data using REST APIs with Python on XSA

My previous blogs have been focused on setting up Python on HANA XSA, connecting applications to HANA Databases, and configuring security and routing settings for applications. In this blog, I am going to delve deeper into accessing a HANA Database within a Python application and creating REST APIs to query data.

I am going to start with the same pyapp project from my previous blogs for which you can find the code at this link. Just to review, the project consists of an application router which takes care of the front-end user interface and acts as a reverse proxy. There is also a Python application named core-py which displays the current time when opened. The myuaa service is used within the application router to set up central user authorization and authentication while the hdi-db service allows connectivity to the HANA Database. New in this blog is the database module which is used to design and deploy all database artifacts.

Creating HANA Database Module

Similar to the core-py Python application, you can incorporate a HANA Database module within your XSA project as well. This allows you to create a local data model with tables, calculation views, stored procedures and functions, etc. which you can utilize within your Python application(s).

There are two ways for developing the database module – one is using the SAP Web IDE for SAP HANA and the other is using a simple text editor. I prefer the Web IDE as it provides graphical user interface for designing database artifacts especially calculation views. I am going to cover both methods briefly. However, I already have a database module that you can download from this link into a new directory named core-db within your pyapp project. Feel free to skip to the “Deploying Database Module” section below if you don’t need details on designing the data model.

Developing on SAP WebIDE for SAP HANA

In case you don’t have the WebIDE installed. Open the WebIDE on your web browser. If you don’t know the url for it, you can execute the following in the command prompt to get it.

xs target -s SAP
xs app webide --urls

The figure below shows the homepage of the WebIDE with the major sections highlighted.

You can import or clone your pyapp project here to develop the database module. Right click on Workspace and select Import if you want to import from your local file system or Git > Clone Repository if you want to clone from your Github. If you are importing, make sure you compress (zip) the project folder that you want to import beforehand. The project should now be visible in your Workspace.

Create an mta.yaml file in the pyapp directory and paste the following code into it.

>>> mta.yaml

ID: pyapp

_schema-version: '2.1'

version: 0.0.1

Make a new directory named .che in the pyapp directory followed by a file named project.json inside this directory. Paste the following code into the project.json file.

>>> project.json

{

"type": "mta",

"builders": {

"default": "mtabuilder",

"configs": {}

},

"runners": {

"configs": {}

}

"mixinTypes": []

}

These settings allow you to create a HANA Database module and deploy it from the Web IDE. Right click on “pyapp” and select New > SAP HANA Database Module. Name the module core_db, click Next, clear the Namespace field which should be pre-filled with the project name, and click Finish. You should now have an empty database module that looks like the following.

The package.json file specifies the node modules that are required in deploying the database module. The src folder contains all the SQL and other files for database artifacts development. The mta.yaml file has a new module and resource inserted for your database module. Notice how there are both manifest.yml and mta.yaml files in your project. The mta file is used by the Web IDE for when you build and run the database module for testing. The manifest file is ignored by the Web IDE and is only used when you deploy your project from the CLI using xs push once you are done developing the database artifacts.

Anyway, once you are done developing and testing the database artifacts, you need to download the database module into your local pyapp project directory and deploy it using xs push from the command prompt. Right click on “ core_db” from the project explorer and select “Export”. This downloads a zipped file containing your database module which you can unzip in the appropriate directory in your XSA project.

Developing on a Text Editor

If you prefer to do all database development on the same text editor as you are using for Python instead, you can totally do that as well! You would first create a directory for your database module in the project directory followed by a directory called src in the database module directory as well as a package.json file. In the src directory, you can put all your files in appropriate sub-directories (data, functions, synonyms, procedures, etc.). You would also need to create a file named .hdiconfig in the src directory and fill it with code from this link. This file is only for configuration and does not need to be modified. In the package.json file, specify dependency on the hdi-deploy node module which is needed to deploy the database.

>>> package.json

{

    "name": "deploy",

    "dependencies": {

        "@sap/hdi-deploy": "3.5.1"

    },

    "scripts": {

        "start": "node node_modules/@sap/hdi-deploy/deploy.js"

    }

}

Deploying Database Module

Modify manifest.yml in the pyapp directory to include the new core-db module. Remember, the manifest file is used by XSA to deploy all your applications and bind them to services as specified. Make sure you add the core-db module to the beginning of the file as the database needs to be deployed and running for the other applications using the database to be deployed properly. Specify the “no-route” option to be true since the database module only provides back-end functionality for your other applications and does not need to be assigned a route of its own. Bind the hdi-db service to the database so that the deployer knows what container to target. Specify a target HDI container for your database which in this case is hdi-db. Your manifest.yml file should now look like the following.

>>> manifest.yml

---

applications:

- name: core-db

  path: ./core-db/

  no-route: true

  services:

   - hdi-db

  env:

    TARGET_CONTAINER: hdi-db

...

Before you deploy the database module, you need to install the node module dependencies in the src directory. Navigate to the directory and execute npm install. This should create a new node modules directory with the required modules. Note: You have to do this step even if you downloaded the database files from the link I provided earlier.

You are finally ready to deploy the database module!!! Let’s do it! From command prompt, navigate to the pyapp directory, log in to XSA, confirm you are in the development space, and push the core-db module.

cd C:\path\to\pyapp

xs-admin-login

xs target -s development

xs push core-db

The output should look similar to the figure below if the module is deployed correctly. Note that there is no URL for this module as we specified “no route” to be true in the manifest.yml file.

You should now be able to view the hdi-db container and all the artifacts you have deployed from the SAP HANA Database Explorer. To get to the Database Explorer, execute xs app hrtt-service –urls from the command prompt. Make sure you are in the SAP space. Open the returned URL in your web browser. Log in if prompted. Click on the “+” button and select hdi-db from the list. This should add the hdi-db container to the list of databases.

You can even open the SQL Console to execute any SQL command by right clicking on “hdi-db” and selecting “Open SQL Console”. This is especially useful in checking that your Python application is querying data properly.

Database Access

Now that your database is up and running, you are ready to make SQL queries from your Python application. In this blog, I’ll demonstrate examples of reading data using GET requests. In Part 2 of this blog, I’ll explain how to write and modify data within the HANA Database using REST APIs (PUT, POST, and DELETE).

Reading Data

Let’s start with simply reading data from hdi-db. First, modify the server.py file in the core-py directory as shown below. Note that in the snippet below, I have split establishing database connection and checking user authorization into separate functions of their own as they will be used multiple times within the REST APIs. I have also modified the hello() function to query and show information about the technical and application users for the hdi-db container rather than the current time. Just to review, HDI containers have two separate users, the technical user is the local object owner while the application user is the external user accessing the database. In addition, the output from the hello() function is now an html formatted string.

>>> server.py

import os

from flask import Flask

from cfenv import AppEnv

from hdbcli import dbapi

import logging

from cf_logging import flask_logging

from sap import xssec

from flask import abort

from flask import request

import json

from flask import make_response

from flask import jsonify

#create instance of flask app

app = Flask(__name__)

env = AppEnv()

#set up logging

flask_logging.init(app, logging.INFO)

logger = logging.getLogger('route.logger')

#assign port for flask app

app_port = int(os.environ.get('PORT', 3000))

#get env variables for bound services

hana = env.get_service(name='hdi-db')

uaa_service = env.get_service(name='myuaa').credentials

#used to establish connection with HANA DB

def connectDB(serviceName):

    service = env.get_service(name=serviceName)

    conn = dbapi.connect(address=service.credentials['host'],

                         port= int(service.credentials['port']),

                         user = service.credentials['user'],

                         password = service.credentials['password'],

                         CURRENTSCHEMA=service.credentials['schema'])

    return conn

#used to check if user is authorized

def checkAuth(header):

    if 'authorization' not in request.headers:

        return False

    

    access_token = header.get('authorization')[7:]

    security_context = xssec.create_security_context(access_token, uaa_service)

    isAuthorized = security_context.check_scope('openid')

    if not isAuthorized:

        return False

    return True

@app.route('/')

def hello():

    #authorize user

    logger.info('Authorization successful') if checkAuth(request.headers) else abort(403)

    #establish db connection

    conn = connectDB('hdi-db')

    logger.info('Database connection successful: ' + str(conn.isconnected()))

                        

    cursor = conn.cursor()

    

    #query user information from hdi-db

    cursor.execute('SELECT CURRENT_USER FROM DUMMY')

    techUser = cursor.fetchone()['CURRENT_USER']

    cursor.execute('SELECT SESSION_CONTEXT(\'APPLICATIONUSER\') "APPLICATION_USER" FROM "DUMMY"')

    appUser = cursor.fetchone()['APPLICATION_USER']

    

    #html output

    output = '''

        <h1>Welcome to SAP HANA!</h1>

        <p>Technical User: %s</p>

        <p>Application User: %s</p>

        ''' % (techUser, appUser)

    cursor.close()

    conn.close()

                

    return output

if __name__ == '__main__':

    app.run(port=app_port)

Add a new route handler in server.py for “/viewProduct” URL which allows users to view products saved in the database given a specific category or a number of products. The @app.route() decorator specifies the URL and the HTTP method that the users can use to access the URL. In this case, GET method is sufficient as the user only needs to read data.

The code below checks if the user has provided a category or number of products in the GET request or not. If a category is provided, it executes a SELECT statement to query products in that category. If a number is provided, it calls a view (saved in the database) to query the specified number of products. The query results are then displayed as a list of JSON objects.

@app.route('/viewProduct', methods=['GET'])

def viewProduct():

    #authorize user

    logger.info('Authorization successful') if checkAuth(request.headers) else abort(403)

    #establish db connection

    conn = connectDB('hdi-db')

    logger.info('Database connection successful: ' + str(conn.isconnected()))

    cursor = conn.cursor()

    #check if the user has specified any parameters

    if (bool(request.args)):

        #check if user has provided number of results or category

        if 'category' in request.args:

            #if category is provided, query all products from category

            params = request.args['category'].split(',')

            query = 'SELECT * FROM "Product.Products" WHERE '

            for item in params:

                query += "CATEGORY=? OR "

            query = query[:-3]

        

        #if number is provided, query that number of results

        elif 'number' in request.args:

            if (request.args['number'] == 'all'):

                query = 'SELECT * FROM "MDViews.ProductView"'

                params = None

            else:

                query = 'SELECT * FROM "MDViews.ProductView" LIMIT ?'

                params = request.args['number']

        cursor.execute(query, params)

        #format results in to a list of JSON objects

        results = []

        i = 0

        for row in cursor.fetchall():

            i = i + 1

            results.append(json.dumps({str(i): str(row)}))

        #send response

        return make_response(jsonify({"results" : results}), 200)

    #if no parameters specified

    else:

        return make_response(jsonify({"Error": "No category specified."}), 404)

The three digit numbers (200, 404, etc.) in the return statements above are HTTP response codes which are used to specify the status of the HTTP request. 

Congratulations! You have created your first REST API using Python on XSA. You can go ahead and push the pyapp project to XSA by executing xs push from the pyapp directory. Everything should get deployed properly and the URL for the web application should be outputted once it is up and running. Open the web application on your browser.  Log in if prompted and the output should be “hello world”. Edit the URL from “/index.html” to “/core-py”. The output should be similar to the figure below.

Insert “/viewProduct” after “/core-py” and the output should be as follows.

Pass in some categories (i.e. Notebooks, Desktops, etc.) with the URL and the output should be a list of products in those categories. This list should display only a few details about the products.

Pass in an integer for number of products to query and the output should be as follows. This list should contain more details about the products than before as it is querying data from a different master table.