Connect SAP Hana Cloud Database Through Python

Connect SAP Hana Cloud Database Through Python

In this blog, we will open SAP HANA Databse in SAP HANA Database Explorer. We will create schema, table and insert data in table. After that we will use Python module to connect SAP HANA and fetch data from SAP HANA Cloud Database.

In first part, we have created SAP BTP Cockpit trial account. If you don't have an account in, you can follow below like:

How to Setup SAP BTP Trial Account and Create SAP HANA Cloud Database

Go to SAP BTP Cockpit Trail Account

After login to Go to this url: Then click "Go To Your Trial Account".

Next screen will show like below. Then click on "trial".

After clicking on "trial", next screen will show like below. We need to click on "dev".

In next screen, we need to click on "SAP HANA Cloud".

In next screen -> Click on "Actions" -> "Open in SAP HANA Database Explorer".

It will ask the "Choose your identity provider", click on "Sign in with default identity provider".

Next screen will show like below. Now SAP HANA Database instance opened in SAP HANA Database Explorer.

Create schema and table in database

Create Schema

Right click on "Schemas" -> "Open SQL Console".

Create schema in SQL Console. In this example, i am creating schema called hotel. Then click on Run.


Schema created successfully.

Create table

Create table called customer. Then click on Run.

CREATE COLUMN TABLE hotel.customer( cno NUMERIC(4) PRIMARY KEY, title CHAR(7), firstname CHAR(20), name CHAR(40) NOT NULL, zip CHAR(5), address CHAR(40) NOT NULL );

We can see, table customer created successfully.

Insert records in customer table.

INSERT INTO hotel.customer VALUES(3000, 'Mrs', 'Jenny', 'Porter', '10580', '1340 N. Ash Street, #3'); INSERT INTO hotel.customer VALUES(3100, 'Mr', 'Peter', 'Brown', '48226', '1001 34th St., APT.3'); INSERT INTO hotel.customer VALUES(3200, 'Company', NULL, 'Datasoft', '90018', '486 Maple St.'); INSERT INTO hotel.customer VALUES(3300, 'Mrs', 'Rose', 'Brian', '75243', '500 Yellowstone Drive, #2'); INSERT INTO hotel.customer VALUES(3400, 'Mrs', 'Mary', 'Griffith', '20005', '3401 Elder Lane'); INSERT INTO hotel.customer VALUES(3500, 'Mr', 'Martin', 'Randolph', '60615', '340 MAIN STREET, #7'); INSERT INTO hotel.customer VALUES(3600, 'Mrs', 'Sally', 'Smith', '75243', '250 Curtis Street'); INSERT INTO hotel.customer VALUES(3700, 'Mr', 'Mike', 'Jackson', '45211', '133 BROADWAY APT. 1'); INSERT INTO hotel.customer VALUES(3800, 'Mrs', 'Rita', 'Doe', '97213', '2000 Humboldt St., #6'); INSERT INTO hotel.customer VALUES(3900, 'Mr', 'George', 'Howe', '75243', '111 B Parkway, #23'); INSERT INTO hotel.customer VALUES(4000, 'Mr', 'Frank', 'Miller', '95054', '27 5th St., 76'); INSERT INTO hotel.customer VALUES(4100, 'Mrs', 'Susan', 'Baker', '90018', '200 MAIN STREET, #94'); INSERT INTO hotel.customer VALUES(4200, 'Mr', 'Joseph', 'Peters', '92714', '700 S. Ash St., APT.12'); INSERT INTO hotel.customer VALUES(4300, 'Company', NULL, 'TOOLware', '20019', '410 Mariposa St., #10');

Record inserted in customer table.

If you want to see table and data, you select "Table", which is showing on the left side -> Then select Table which you want to see. Here i am clicking on Customer -> then click on "Open Data".

You can see raw data of customer table like below:

Now let us pull this customer data through python. For that we need to install SAP HANA(hana-ml) module.

Python Machine Learning Client for SAP HANA (hana-ml)

This package enables Python data scientists to access SAP HANA data and build various machine learning models using the data directly in SAP HANA. This page provides an overview of hana-ml.

hana-ml uses SAP HANA Python driver (hdbcli) to connect to and access SAP HANA.

Python machine learning client for SAP HANA consists of two main parts:

    SAP HANA DataFrame, which provides a set of methods for accessing and querying data in SAP HANA without bringing the data to the client.

    A set of machine learning APIs for developing machine learning models.

Specifically, machine learning APIs are composed of two packages:

    PAL package

    PAL package consists of a set of Python algorithms and functions which provide access to machine learning capabilities in SAP HANA Predictive Analysis Library(PAL). SAP HANA PAL functions cover a variety of machine learning algorithms for training a model and then the trained model is used for scoring.

    APL package

    Automated Predictive Library (APL) package exposes the data mining capabilities of the Automated Analytics engine in SAP HANA through a set of functions. These functions develop a predictive modeling process that analysts can use to answer simple questions on their customer datasets stored in SAP HANA.

Install hana-ml library

sudo -E pip install hana-ml

Connect SAP HANA Cloud Database with Python

Create configuration of SAP HANA Cloud Database

Create a json file called hana_cloud_config.json in current directory and write database details in that. Then save and close the file.

{ "user": "DBADMIN", "pwd": "xxxxxxxx", "url": "", "port": 443 }

Load the json file.

In [1]:
import json
In [2]:
sap_hana_config_file = "hana_cloud_config.json"
In [3]:
with open(sap_hana_config_file) as f:
    sap_hana_config = json.load(f)
    db_url  = sap_hana_config['url']
    db_port = sap_hana_config['port']
    db_user = sap_hana_config['user']
    db_pwd  = sap_hana_config['pwd']

We can print and see the database details.

In [4]:
db_port, db_user, db_url, db_pwd

Connect SAP HANA Cloud with ConnectionContext

In [5]:
from hana_ml.dataframe import ConnectionContext

class hana_ml.dataframe.ConnectionContext(address='', port=0, user='', password='', autocommit=True, packetsize=None, userkey=None, **properties)

Bases: object

Represents a connection to an SAP HANA system.

ConnectionContext includes methods for creating DataFrames from data on SAP HANA. DataFrames are tied to a ConnectionContext, and are unusable once their ConnectionContext is closed.
In [12]:
cc = ConnectionContext(db_url, db_port, db_user, db_pwd)
<hana_ml.dataframe.ConnectionContext at 0x7f27355fc910>

If you are not able to connect, you can check your database instance is running or not. If it is stopped, then you can start your database instance.

In [7]:
print(cc.hana_version()) (fa/CE2022.4)

Fetch data from SAP HANA

We have to give schema and table name. Here my schema is hotel and table is customer.

In [8]:
hana_ml_df = cc.table('CUSTOMER', schema='HOTEL')
<hana_ml.dataframe.DataFrame at 0x7f27356304d0>

SAP HANA DataFrame object is created. We need to transform a SAP HANA DataFrame to a Pandas DataFrame. We can use collect() for that.

Convert into Pandas dataframe

In [9]:
df = hana_ml_df.collect()
0 3000 Mrs Jenny Porter 10580 1340 N. Ash Street, #3
1 3100 Mr Peter Brown 48226 1001 34th St., APT.3
2 3200 Company None Datasoft 90018 486 Maple St.
3 3300 Mrs Rose Brian 75243 500 Yellowstone Drive, #2
4 3400 Mrs Mary Griffith 20005 3401 Elder Lane

View the dataframe details

In [10]:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 14 entries, 0 to 13
Data columns (total 6 columns):
 #   Column     Non-Null Count  Dtype 
---  ------     --------------  ----- 
 0   CNO        14 non-null     object
 1   TITLE      14 non-null     object
 2   FIRSTNAME  12 non-null     object
 3   NAME       14 non-null     object
 4   ZIP        14 non-null     object
 5   ADDRESS    14 non-null     object
dtypes: object(6)
memory usage: 800.0+ bytes
In [11]:
(14, 6)
In [ ]:

Machine Learning

  1. Deal Banking Marketing Campaign Dataset With Machine Learning


  1. Difference Between Scalar, Vector, Matrix and Tensor
  2. TensorFlow Deep Learning Model With IRIS Dataset
  3. Sequence to Sequence Learning With Neural Networks To Perform Number Addition
  4. Image Classification Model MobileNet V2 from TensorFlow Hub
  5. Step by Step Intent Recognition With BERT
  6. Sentiment Analysis for Hotel Reviews With NLTK and Keras
  7. Simple Sequence Prediction With LSTM
  8. Image Classification With ResNet50 Model
  9. Predict Amazon Inc Stock Price with Machine Learning
  10. Predict Diabetes With Machine Learning Algorithms
  11. TensorFlow Build Custom Convolutional Neural Network With MNIST Dataset
  12. Deal Banking Marketing Campaign Dataset With Machine Learning


  1. How to Parallelize and Distribute Collection in PySpark
  2. Role of StringIndexer and Pipelines in PySpark ML Feature - Part 1
  3. Role of OneHotEncoder and Pipelines in PySpark ML Feature - Part 2
  4. Feature Transformer VectorAssembler in PySpark ML Feature - Part 3
  5. Logistic Regression in PySpark (ML Feature) with Breast Cancer Data Set


  1. Build the Neural Network with PyTorch
  2. Image Classification with PyTorch
  3. Twitter Sentiment Classification In PyTorch
  4. Training an Image Classifier in Pytorch

Natural Language Processing

  1. Spelling Correction Of The Text Data In Natural Language Processing
  2. Handling Text For Machine Learning
  3. Extracting Text From PDF File in Python Using PyPDF2
  4. How to Collect Data Using Twitter API V2 For Natural Language Processing
  5. Converting Text to Features in Natural Language Processing
  6. Extract A Noun Phrase For A Sentence In Natural Language Processing