Build a Python App on BTP Cloud Foundry: Read, Filter, and Download Table Data from HANA Cloud DB

[KarthikKumar31]

This blog demonstrates creation of a simple Python application on the BTP Cloud Foundry Environment to read, filter by columns, and download data from a table which is present in the HANA Cloud DB.

Step 1: Installation of Cloud Foundry CLI

Install Cloud Foundry CLI. The Cloud Foundry CLI can be found at  https://tools.hana.ondemand.com/#cloud-cpcli

Step 2: New Directory Creation

Create a new directory for the application

mkdir new-hana-app 
cd new-hana-app

Step 3: Installation of Python

In the newly created directory new-hana-app, install Python and create & activate a Python virtual environment

python3 -m venv venv
source venv\Scripts\activate

Step 4: Installation of Libraries

Inside the environment, install below necessary libraries for HANA Cloud connectivity using the below command in command prompt

pip install SQLAlchemy hdbcli Flask pandas xlsxwriter

Step 5: Creation of Python Script

Create Python Script inside the new-hana-app directory, create a file app.py (This is an example, replace it with the appropriate name), add the below python code that will handle the connection to HANA Cloud & data retrieval and save it

from flask import Flask, request, render_template, send_file
import pandas as pd
from hdbcli import dbapi
import io
import os

app = Flask(__name__)

# HANA Cloud DB connection details
DB_HOST = 'hanahdb.hana.trial-us10.hanacloud.ondemand.com'
DB_PORT = 443
DB_USER = 'USERNAME'
DB_PASSWORD = 'PaSsWoRd@123'

def query_hana_db(table_name, filters):
    conn = dbapi.connect(
        address=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD
    )
    cursor = conn.cursor()
    
    # Build the query with filters if provided
    query = f'SELECT * FROM {table_name}'
    
    if filters:
        filter_conditions = [f"{col}='{val}'" for col, val in filters.items() if val]  # Assuming exact matches
        if filter_conditions:
            query += " WHERE " + " AND ".join(filter_conditions)
    
    cursor.execute(query)
    columns = [col[0] for col in cursor.description]
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    df = pd.DataFrame(data, columns=columns)
    df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
    return df

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        table_name = request.form['table_name']
        
        # Get the filter inputs from the form
        filter_column = request.form.get('filter_column')
        filter_value = request.form.get('filter_value')
        
        filters = {filter_column: filter_value} if filter_column and filter_value else {}
        
        try:
            data = query_hana_db(table_name, filters)
            table_html = data.to_html(classes='data', index=False, header="true")
            return render_template('index.html', table_html=table_html, table_name=table_name, filter_column=filter_column, filter_value=filter_value)
        except Exception as e:
            return f"Error: {str(e)}"
    return render_template('index.html')

@app.route('/download', methods=['POST'])
def download():
    table_name = request.form['table_name']
    
    # Get the filter inputs from the form for downloading filtered data
    filter_column = request.form.get('filter_column')
    filter_value = request.form.get('filter_value')
    
    filters = {filter_column: filter_value} if filter_column and filter_value else {}
    
    data = query_hana_db(table_name, filters)
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
        data.to_excel(writer, sheet_name=table_name, index=False)
    output.seek(0)
    return send_file(output, download_name=f'{table_name}.xlsx', as_attachment=True)

import logging
logging.basicConfig(level=logging.DEBUG)

if __name__ == '__main__':
    port = int(os.getenv('PORT', 5000))
    app.run(host='0.0.0.0', port=port, debug=True)

Please note the following

• DB_HOST = 'hanahdb.hana.trial-us10.hanacloud.ondemand.com': This is an the example server name. Replace it with the appropriate HANA server details
• DB_USER = 'USERNAME' & DB_PASSWORD = 'PaSsWoRd': Replace these with valid credentials

Step 6: Creation of Frontend

Create a folder named templates inside new-hana-app directory and inside that create a file index.html, add the below HTML code and save it

mkdir templates
cd templates

<!DOCTYPE html>
<html>
<head>
    <title>HANA Cloud Table Data</title>
    <!-- Import Google Icon Font and Materialize CSS -->
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

    <style>
        body {
            display: flex;
            min-height: 100vh;
            flex-direction: column;
        }

        main {
            flex: 1 0 auto;
        }

        .container {
            margin-top: 50px;
        }

        table {
            width: 100%;
            margin-top: 20px;
        }

        thead {
            background-color: #f5f5f5;
        }

        th, td {
            padding: 12px;
            text-align: left;
        }

        .form-container {
            padding: 20px;
            background-color: #f9f9f9;
            border-radius: 10px;
        }

        .table-container {
            margin-top: 40px;
        }
    </style>
</head>

<body>
    <nav>
        <div class="nav-wrapper blue">
            <a href="#" class="brand-logo center">HANA Cloud Data Viewer</a>
        </div>
    </nav>

    <main>
        <div class="container">
            <div class="row">
                <div class="col s12 m8 offset-m2 form-container z-depth-2">
                    <h5 class="center-align">Enter Table Name and Filter Data</h5>
                    <form method="POST">
                        <div class="input-field">
                            <input id="table_name" type="text" name="table_name" class="validate" required>
                            <label for="table_name">Table Name</label>
                        </div>
                        
                        <!-- Optional filters for columns -->
                        <div class="input-field">
                            <input id="filter_column" type="text" name="filter_column">
                            <label for="filter_column">Filter Column (Optional)</label>
                        </div>
                        <div class="input-field">
                            <input id="filter_value" type="text" name="filter_value">
                            <label for="filter_value">Filter Value (Optional)</label>
                        </div>

                        <div class="center-align">
                            <button class="btn waves-effect waves-light blue" type="submit" name="action">
                                Submit
                                <i class="material-icons right">send</i>
                            </button>
                        </div>
                    </form>
                </div>
            </div>

            {% if table_html %}
            <div class="table-container">
                <h5 class="center-align">Table Data</h5>
                <div class="card-panel">
                    <!-- Render HTML table -->
                    {{ table_html | safe }}
                </div>

                <form method="POST" action="/download">
                    <input type="hidden" name="table_name" value="{{ request.form['table_name'] }}">
                    <input type="hidden" name="filter_column" value="{{ request.form['filter_column'] }}">
                    <input type="hidden" name="filter_value" value="{{ request.form['filter_value'] }}">
                    <div class="center-align">
                        <button class="btn waves-effect waves-light green" type="submit">
                            Download Data
                            <i class="material-icons right">file_download</i>
                        </button>
                    </div>
                </form>
            </div>
            {% endif %}
        </div>
    </main>

    <!-- Footer -->
    <footer class="page-footer blue">
        <div class="container">
            <div class="center-align">
                2024 HANA Cloud Data Viewer
            </div>
        </div>
    </footer>

    <!-- Import jQuery and Materialize JS -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
</body>
</html>

Step 7: Dependencies file Creation

Create a 'requirements.txt' file inside the new-hana-app directory, add all required Python packages for deployment and save it

Flask==2.3.2
SQLAlchemy==1.3.24
hdbcli==2.22.27
pandas==2.2.3
xlsxwriter==3.2.0

Step 8: Creating Procfile to run script

In new-hana-app directory, create a file called Procfile file (without any file extension). Add the following content to the Procfile as this tells Cloud Foundry to run the app.py script when the app is started. Save the Procfile

web: python app.py

Step 9: Creating manifest.yml for Cloud Foundry Deployment

In new-hana-app directory, create a manifest.yml file. Add the following content to the manifest.yml as this defines how the app should be deployed. Save the file

---
applications:
  - name: new-hana-app
    random-route: true
    memory: 1G
    buildpacks:
      - python_buildpack
    env:
      FLASK_ENV: production
    health-check-type: port
    services:
      - HANA_SERVICE

Step 10: Login to Cloud Foundry

Copy the Cloud Foundry executable (cf8.exe) which is present in the installation directory and place it inside new-hana-app directory. Execute the below command. Enter the email and password. Once the authentication is successful, the targeted org, space along with API endpoint, API version, user, org and space will be displayed

cf8 login

Step 11: Create Service and Service Key

Create Service and Service Key to generates secure credentials (like DB hostname, username, and password) for the HANA Cloud instance. These credentials are needed to programmatically connect the application to the service without hardcoding sensitive information. Execute the below commands

cf8 create-service hana hdi-shared HANA_SERVICE
cf8 create-service-key HANA_SERVICE HANA_KEY

Please note that HANA_SERVICE and HANA_KEY are example HANA Cloud Service name and Key name. Replace it with the required HANA Service name and key name.

Step 12: Deploy App to Cloud Foundry

Execute the below command to push the application new-hana-app to Cloud Foundry environment

cf8 push

Step 13: Create Binding for HANA Cloud

Create Service Binding for HANA Cloud to enable the application to interact with the HANA Cloud instance. This links new-hana-app application to the HANA_CLOUD_DB database, allowing it to access the database securely and efficiently. Post this, restage the application. Execute the below commands

cf8 bind-service new-hana-app HANA_CLOUD_DB
cf8 restage new-hana-app

Please note that HANA_CLOUD_DB is an example HANA Cloud DB name. Replace it with the required HANA Cloud DB name.

Step 14: Cross verify in BTP Cockpit

Login to SAP BTP Cockpit and navigate to Cloud Foundry space 'dev'. Click on Instances under Services tab and cross verify the HANA Cloud DB, HANA Service, Service Key and Bindings

Step 15: Run the Python Application

• Login to SAP BTP Cockpit and navigate to Cloud Foundry space 'dev'.
• Select the Applications tab to view all the applications present in the space
• Click on the newly created application name 'new-hana-app'

• Click the URL present in the Application Routes

Step 16: Testing the Python Application

Create a sample table in the HANA Cloud DB. Once the table creation and data insertion are successful, provide the table name in the URL. Also, if any specific column needs to be filtered, provide the name of the column and it's value. Click on 'SUBMIT' to view filtered table data and click on 'DOWNLOAD DATA' to download the filtered table data.

[Satish_Jhariya]

thanks for sharing such capabilities of BTP platform.

[rakesh]

Excellent Blog. Can you guide how can we secure it using our SAP SSO authentication?