Community
Technology Blog Posts by Members
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Introduction

This blog aims to demonstrate how it is possible to deploy a python application on SAP BTP and connect to a PostgreSQL database located on-premise, using SAP Cloud Connector.

Acknowledgement

This blog heavily draws inspiration from the following blogs by @felixbartler :

In essence, this blog can be seen as an extension to the above blogs, and it tries to showcase one complete practical application, synthesizing the concepts from the above blogs.

The objective - what we are trying to achieve

The situation:

  • We have an existing Postgres database with a few tables in our local, on-premise network
  • We need to create an application on BTP to access the data from this database

The task:

  • Create a python application on SAP BTP
  • Utilize the standard connection mechanism of python to Postgres database, i.e. psycopg2 library, and route the connection via cloud connector, and connect to the on-premise Postgres database.
  • As proof-of-concept (POC), we will select the data from a single table inside a database in Postgres

Architecture Diagram

amitabha_samajpati_83_0-1786951524983.png

 

The process flow is as follows:

  1. A PostgreSQL database is installed on-premise.
  2. A cloud connector is installed on the same on-premise network and is connected to an SAP BTP subaccount.
  3. The Postgres is exposed via the cloud connector, over TCP, using a virtual host and virtual port.
  4. A python application is deployed on the same SAP BTP subaccount where the cloud connector is connected.
  5. The python app is based on a FastAPI based application, which uses the psycopg2 library for connection to the database, and calls a local proxy implementation via SocketSwap library, exposed at 127.0.0.1, port 2222
  6. The SocketSwap proxy, in turn, connects to a connection factory that first obtains authentication token from the BTP connectivity service, and then, using the token from the connectivity service, connects to the virtual host and port of the PostgreSQL database through cloud connector.
  7. The python application then uses SELECT query to select data from one of the tables in the database and returns as a JSON, and the JSON is displayed as an output.

This way, the FastAPI app uses the psycopg2 library to connect to the PostgreSQL host located inside the on-premise network.

Environment

This POC was carried out in windows environment. PostgreSQL version used was 16.14, and python version used was 3.13. The BTP platform was my own trial account.

PostgreSQL Installation

For this POC, Postgres is installed in the same system as the cloud connector. There are many tutorials on the internet to discuss the Postgres installation, so I will not detail it out here. The installers / binaries can be downloaded from the following link, and then the instructions in the installers can be followed to set up an Postgres installation:

https://www.postgresql.org/download/

I am using PgAdmin tool for visual monitoring of the database. The database server for this POC, named C3S1, is running on my PC, at port 9000, and it looks as follows over PgAdmin:

amitabha_samajpati_83_1-1786951524985.png

 

Inside the server, there is a database “rag_db_c3s7d1”, which houses a set of tables:

amitabha_samajpati_83_2-1786951524987.png

 

amitabha_samajpati_83_3-1786951524989.png

 

The table named “taxes” holds tax information per country:

amitabha_samajpati_83_4-1786951524993.png

 

Our application will try to select the data from this table and display as an output JSON. We will try to execute the following query from our BTP Python application:

SELECT tax_rate FROM taxes WHERE country = ‘USA’

and try to see if the correct tax rate percentage is displayed as the output. If for this query, a tax rate of 8.50 percentage is selected, then the POC will be considered a success.

Cloud Connector Configuration

SAP Cloud Connector is the main component which will provide the on-premise connectivity to the python application on BTP. You can install it by following one of the links below:

Once the cloud connector is installed and configured, the PostgreSQL database will need to be exposed through it. This is achieved by exposing the host and port of the Postgres DB server as a TCP connection in the cloud connector, Cloud-to-On-premise configuration.

Navigate to the “Cloud-to-On-Premise” section of the cloud connector:

amitabha_samajpati_83_5-1786951524994.png

 

Click “Add”:

amitabha_samajpati_83_6-1786951524995.png

 

Choose system type as “Non-SAP System” and click “Next”:

amitabha_samajpati_83_7-1786951524997.png

 

Choose protocol as “TCP” and click "Next”:

amitabha_samajpati_83_8-1786951524998.png

 

Choose the internal host and port, where the Postgres DB is running, and click “Next”.

For our POC, the cloud connector and the Postgres database are running on the same system, hence we choose localhost as the internal host, and the database port is 9000, as mentioned above. You have to adjust these details as per your setup:

amitabha_samajpati_83_9-1786951524999.png

 

In the following screen, choose the virtual host and port (the name by which the applications in BTP subaccount will know this database), and click “Next”:

amitabha_samajpati_83_10-1786951525000.png

 

Description is optional, we kept it blank. Choose “Next”:

amitabha_samajpati_83_11-1786951525001.png

 

Choose “Finish” on the last screen:

amitabha_samajpati_83_12-1786951525002.png

 

An entry appears in the cloud connector table. If the connectivity status shows “Unchecked”, then you can choose the “Check Availability” button to check for the connection from the cloud connector to the Postgres host:

amitabha_samajpati_83_13-1786951525005.png

 

If the connection works, then the “Check Result” column will turn green:

amitabha_samajpati_83_14-1786951525007.png

 

Connectivity Service Configuration

The connectivity service is important in the context of this POC because it will enable the cloud connector connection programmatically from the BTP python application.

To enable a connectivity service instance, we need to go to the subaccount (“trial” in the case of this POC), and navigate to “Instances and Subscriptions”:

amitabha_samajpati_83_15-1786951525009.png

 

Click on “Create”:

amitabha_samajpati_83_16-1786951525010.png

 

Choose the “Service Name” as “Connectivity Service”, provide a name for the service instance,  and choose “Next”:

amitabha_samajpati_83_17-1786951525012.png

 

Keep the parameters blank and choose “Next”:

amitabha_samajpati_83_18-1786951525015.png

 

Review and click “Create” in the next screen:

amitabha_samajpati_83_19-1786951525017.png

 

The service instance will be created:

amitabha_samajpati_83_20-1786951525019.png

 

Now we need to create a service key for the instance. The details from this service key will be utilized at runtime by the python application, to generate an authentication token and programmatically authorize the call to cloud connector tunnel socket.

Click on the three dots beside the connectivity service instance and choose “Create Service Key”:

amitabha_samajpati_83_21-1786951525022.png

 

Provide a name of the service key, keep the parameters blank, and click on “Create”:

amitabha_samajpati_83_22-1786951525025.png

 

The service key is created:

amitabha_samajpati_83_23-1786951525027.png

 

Note the following details from the JSON embedded inside the service key:

  • clientid
  • clientsecret
  • token_service_url
  • onpremise_proxy_host
  • onpremise_socks5_proxy_port

These details will be required for the python application development.

Cloud Foundry CLI Tool Installation and Initialization

The BTP Cloud Foundry command line interface (CLI) tool will help us to deploy the python application on BTP.

The cloud foundry CLI can be downloaded from the following link:

https://github.com/cloudfoundry/cli#downloads

Once you execute the installer, the installer asks for the path to the installation folder, as shown below:

amitabha_samajpati_83_24-1786951525029.png

 

And then the installation takes place.

After installation, we will have to set up the API endpoint as a one-time setup. For that, use the “cf api” command as shown below:

amitabha_samajpati_83_25-1786951525032.png

 

We will obtain the API URL from your subaccount details in BTP cockpit:

amitabha_samajpati_83_26-1786951525034.png

 

Once the API endpoint is set, we will need to use the “cf login” command to login to the cloud foundry account. You will have to provide your account email and password to login, and the cli tool will connect with those credentials:

amitabha_samajpati_83_27-1786951525039.png

 

Now we are ready for development in python and deploying it to BTP.

Python Development

The python file structure inside the root directory looks as follows:

python-onpremdbaccess/          # Root directory
├── connect_config.py           # Connection Configuration
├── db_conn_cloud_connector.py  # Connection factory for Cloud Connector
├── manifest.yaml               # Descriptor file for BTP App
├── requirements.txt            # Contains dependencies
├── runtime.txt                 # Contains Python Runtime version info
├── server.py                   # Main FastAPI entry point
└── README.md                   # This file

The various files perform the following activity:

  • server.py: Main API entry point for the on-premise app; builds the FastAPI service and connects through a proxy socket.
  • connect_config.py: Stores the PostgreSQL database credentials and SAP BTP connectivity settings used for token generation and proxy access.
  • db_conn_cloud_connector.py: Creates the SAP Cloud Connector socket that connects to the on-premise database via the configured proxy.
  • manifest.yaml: Holds the deployment details for the BTP application.
  • requirements.txt: Holds the details of the dependencies to be installed for the application to properly work.
  • runtime.txt: Holds the python runtime version information.

Codes for the files are given below.

connect_config.py

import requests
import logging

logging.basicConfig(level=logging.DEBUG)

# --- Database configuration -------------------------------------------------
DB_CONFIG = {
    "host": "<your_db_virtual_host_from_cloud_connector>",
    "port": "<your_db_virtual_port_from_cloud_connector>",
    "dbname": "<your_db_name>",
    "user": "<your_db_user>",
    "password": "<your_db_password>",
}

location_id = "<your_cloud_connector_location_id>"  # Adjust to match your Cloud Connector location
client_id = "<your_connectivity_service_client_id>"  # Adjust to match your Connectivity Service client ID
client_secret = "<your_connectivity_service_client_secret>"  # Adjust to match your Connectivity Service client secret
tenant_url = "<your_sap_btp_tenant_url>"  # Adjust to match your SAP BTP tenant URL
token_service_url = "<your_sap_btp_tenant_token_service_url>" # Adjust to match your SAP BTP tenant token service URL
onpremise_proxy_host = "<your_on_premise_proxy_host>"  # Adjust to match your on-premise proxy host
onpremise_proxy_port = "<your_on_premise_proxy_port>"  # Adjust to match your on-premise proxy port
onpremise_socks5_proxy_port = "<your_on_premise_SOCKS5_proxy_port>"  # Adjust to match your on-premise SOCKS5 proxy port

def get_connectivity_service_token():
    """
    Fetches an OAuth token from the SAP Connectivity Service.
    """
    logging.info("Connectivity Service Token Function Called")
    response = requests.post(
        url=token_service_url,
        params={"grant_type": "client_credentials"},
        auth=(client_id, client_secret)
    )

    if response.status_code != 200:
        print(f"Error: {response.status_code} - {response.text}")
        exit(-1)
    logging.info("Connectivity Service Request Status 200")
    logging.debug(f"Access Token: {response.json().get('access_token')}")   # Should be avoided, for test only
    return response.json().get("access_token")

As can be seen from the code above, the file houses the various connection parameters, and provides a function for obtaining the OAuth token from the connectivity service.

Important: the connectivity service token URL from the service key of the connectivity service ends with the suffix ondemand.com. However, the actual call to the token service resides at /oauth/token endpoint under it. So when you are setting the token URL in the file above, make sure to suffix the URL from the connectivity service with /oauth/token, so that it looks something similar to the below URL:

token_service_url = "https://xyztrial.authentication.us10.hana.ondemand.com/oauth/token"

 

db_conn_cloud_connector.py

from sapcloudconnectorpythonsocket import CloudConnectorSocket
from connect_config import DB_CONFIG, get_connectivity_service_token, location_id, onpremise_proxy_host, onpremise_socks5_proxy_port
import logging

logging.basicConfig(level=logging.DEBUG)

def conn_factory():
    logging.info("Inside Connection Factory Function")

    # Get an OAuth token from the Connectivity Service
    token = get_connectivity_service_token()

    logging.info("After Token, Inside Connection Factory Function")
    
    cc_socket = CloudConnectorSocket()
    logging.info("CC Socket Created, Inside Connection Factory Function")
    logging.info(f"Host: {DB_CONFIG['host']}, Port: {DB_CONFIG['port']}, Proxy Host: {onpremise_proxy_host}, Proxy Port: {onpremise_socks5_proxy_port}, Location ID: {location_id}")
    cc_socket.connect(
            dest_host=DB_CONFIG["host"], 
            dest_port=int(DB_CONFIG["port"]), 
            proxy_host=onpremise_proxy_host, 
            proxy_port=int(onpremise_socks5_proxy_port), 
            token=token,
            location_id=location_id
        )
    logging.info("CC Socket Connected, Inside Connection Factory Function")
    return cc_socket

As can be seen, the above code imports the connectivity details from the  connect_config.py and then inside the conn_factory method, gets an OAuth token from the connectivity service and then opens a cloud connector socket via the proxy host and SOCKS5 proxy port of the connectivity service, and connects to the virtual host and port of the database server located on-premise.

server.py

import os
from cfenv import AppEnv
import psycopg2
import uvicorn

from sap import xssec
from pydantic import BaseModel, Field
from fastapi import FastAPI, APIRouter, HTTPException
from typing import Optional, List

from sapcloudconnectorpythonsocket import CloudConnectorSocket
from SocketSwap import SocketSwapContext
from db_conn_cloud_connector import conn_factory
from connect_config import DB_CONFIG
import logging

logging.basicConfig(level=logging.DEBUG)

app = FastAPI(
    title="DB Query API",
    description="REST API for querying PostGreSql DB",
    version="1.0.0"
)
env = AppEnv()

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

# The single table/column this app is allowed to query.
# Hard-coding these (rather than accepting them from a request) avoids SQL
# injection, since table/column names can't be parameterized like values can.
TABLE_NAME = "taxes"
COLUMN_NAME = "tax_rate"

class QueryResponse(BaseModel):
    value: str 


def get_connection():
    """
    This function demos how to easily setup the local proxy using the SocketSwapContext-Manager.
    It exposes a local proxy on the localhost 127.0.0.1 on port 2222
    The connection factory is provided to handle the creation of a socket to the remote target
    """
    logging.info("Inside Get Connection Function")
    with SocketSwapContext(conn_factory, [], "127.0.0.1", 2222):
        conn = psycopg2.connect(
            host="127.0.0.1",
            database=DB_CONFIG["dbname"],
            user=DB_CONFIG["user"],
            password=DB_CONFIG["password"],
            port=2222
        )
    logging.info("SocketSwap Call Completed, Inside Get Connection Function")
    return conn


@app.get("/", response_model=QueryResponse)
def hello():


    query = f"SELECT {COLUMN_NAME} FROM {TABLE_NAME} WHERE country = 'USA' LIMIT 1;"
 
    try:
        
        conn = get_connection()
        try:
            logging.info("Executing query")
            with conn.cursor() as cur:
                cur.execute(query)
                rows = cur.fetchall()
            logging.info("Query Execution Completed")
        finally:
            conn.close()
    except psycopg2.Error as e:
        raise HTTPException(status_code=500, detail=f"Database error: {e}")
 
    logging.info("Looping through rows to get value")
    for row in rows:
        value = str(row[0])
        return QueryResponse(value=value)

if __name__ == '__main__':
    uvicorn.run(app, host='0.0.0.0', port=port)

As can be seen from above, the code does the following:

  • It exposes a FastAPI GET entry point
  • Inside the GET method, it formulates a SELECT query, referring to a table and column. The SELECT query is hard-coded for this POC to one country code and a single row; please feel free to adjust the code as per your use case.
  • Then the method calls a get_connection function.
  • The get_connection function uses SocketSwapContext method from SocketSwap package, to swap the connection to 127.0.0.1 host and port 2222 via the psycopg2 library for connection to Postgres, to a connection socket provided by the conn_factory method. Please recall that the conn_factory method was defined in the previously explained db_conn_cloud_connector.py file, and it opened a connectivity to the DB virtual host and port via cloud connector. So, this mechanism fuses the concept of TCP connectivity to the database host via cloud connector, and the connectivity to a specific database inside the host, using a username and password, via the psycopg2 library.
  • Once the connection is opened, the code executes the SELECT query and retrieves the data set.
  • Then it closes the connection, and loops over the retrieved rows and sends the first retrieved row as JSON response via a pydantic model.

requirements.txt

# Runtime dependencies for the on-premise DB access app
cfenv>=0.5.3,<1.0.0
fastapi>=0.115.0,<1.0.0
uvicorn[standard]>=0.30.0,<1.0.0
pydantic>=2.7.0,<3.0.0
psycopg2-binary>=2.9.0,<3.0.0
requests>=2.31.0,<3.0.0
sap-xssec>=4.0.0,<5.0.0
sapcloudconnectorpythonsocket>=0.1.0
SocketSwap>=0.1.0
PySocks>=1.7.0,<2.0.0

As described, this file details out the dependencies.

runtime.txt

python-3.13.x

As described, it defines the runtime for deployment of the application.

manifest.yaml

---
applications:
- name: zmyapp
  random-route: true
  path: .
  memory: 256M
  buildpacks:
    - python_buildpack
  command: python -m uvicorn server:app --host 0.0.0.0 --port $PORT

The important elements of the descriptor serve the following purpose:

  • The "name" attribute provides a name to the application.
  • The "random-route: true" attribute generates a random URL for the application.
  • The "command" attribute tells the runtime about which command to run to execute the application. As this is a FastAPI application, so the command uvicorn is used here.

Deployment and Testing

Using a command prompt, navigate to the root directory of your application (python-onpremdbaccess in our POC), and execute the following command (note: you have to be logged in via cf login):

cf push

amitabha_samajpati_83_28-1786955274563.png

You will observe a final message similar to the following:

amitabha_samajpati_83_29-1786955410521.png

Copy the URL shown in the "routes" section of the command prompt. Open a browser and hit the URL:

amitabha_samajpati_83_30-1786955739434.png

If your cloud connector is up and your DB is reachable through cloud connector, then you will see an output on the browser, similar to the following:

amitabha_samajpati_83_31-1786955846519.png

You can also check the logs by running the following command:

cf logs <your_app_name> --recent

If you have logging enabled at various points (similar to the code snippets given above), you will be able to observe the logs in the output of the above command:

amitabha_samajpati_83_32-1786956170508.png

This proves that the application was able to connect to the on-premise database via cloud connector, using psycopg2 library.

Future Enhancements

This proof-of-concept (POC) showcases an end-to-end scenario for connecting from a BTP based python application to an on-premise Postgres database. While this works as a POC, the following enhancements should be considered for an actual productive application:

  • Security – The current POC is not secured by authentication. It should be secured by validating authentication credentials.
  • Dynamic handling of SQL – In the current implementation, the SQL queries to Postgres are pointing to a single table and catering to a single query, located inside the python source file. For more dynamic operations, the query handling should be wrapped around. Also, SQL injection should be prevented, as the final query delivered to the DB is a raw SQL query.
  • User Interface (UI) Development – The python application just prints the output JSON on the browser. A real application will have a proper UI for handling user interaction, which was beyond the scope of the POC, but should be an integral part of a productive application.

Thank you for reading!

Comment
2 Comments
Labels in this area