How to use a SAP HANA database with your Python 3 Flask application via Flask-SQLAlchemy and PyHDB

First day of work and I had to deliver a crawler program that:

• populates data to a SAP HANA database instance.
• can be triggered through a HTTP endpoint

by next week.

In such a scenario, I naturally chose a lean programming framework which I am most comfortable with.

With Python 3 Flask, I can build web applications that are lean and portable. I can even deploy a Python 3 Flask application a Raspberry Pi 3 if I need a web interface to interact with the sensors connected to the Raspberry Pi 3.

As such, I chose Python 3 Flask as the programming framework to fulfil this task.

If you are looking to implement Python 3 Flask application backed by HANA server, read on to find out more.

Installing the Python dependencies

One of the first things for a Python 3 Flask application is to define the dependencies that are needed. Given that, I had the following requirements.txt file for pip installation:

Flask==1.0.2
Flask-SQLAlchemy==2.3.2
pyhdb==0.3.4
sqlalchemy-hana==0.3.0

In addition, Flask and Flask-SQLAlchemy to build a Python Flask application with SQLAlchemy as the ORM, we will also need the SQLAlchemy dialect (sqlalchemy-hana) and a database driver (pyhdb) to connect to a HANA database instance.

Why I chose PyHDB over SAP HANA Client Interface

Since PyHDB is written entirely in Python, I chose it over the SAP HANA Client Interface.

Doing so will enable my application to interact with the HANA database instance without installing the SAP HANA client. This will make my Python application very portable.

Defining the URI connection to the HANA database instance

Since I was using PyHDB as the underlying database driver, the URI that I supplied to Flask-SQLAlchemy via flask configuration looked like the following:

SQLALCHEMY_DATABASE_URI="hana+pyhdb://{username}:{password}@{host}:{port}"\
    .format(username='myhanauser', password='mypassword', host='12.34.46.78', port='30015')

Note that I do not include the database schema information in the URI since PyHDB does not support such a feature as of this time of writing. This is not too much of an issue as we can specify the database schema through the SQLAlchemy models.

In addition to the URI information, I also need to supply the following configuration to denote that I do not wish to track modifications:

SQLALCHEMY_TRACK_MODIFICATIONS = False

Defining the SQLAlchemy data model to map a database table in the database schema

The following is an example of how I can model a database table:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
DB_SCHEMA = 'MYDBSCHEMA'

class UserActivity(db.Model):
    __tablename__ = 'USERACTIVITY'
    __table_args__ = {
                      'schema': DB_SCHEMA,
                      'hana_table_type': 'ROW'}

    id_seq = db.Sequence('UA_ID_NUM', schema=DB_SCHEMA)
    id = db.Column(db.BigInteger, id_seq, primary_key=True, nullable=False, server_default=id_seq.next_value())
    name = db.Column(db.String)
    start_date_local = db.Column(db.TIMESTAMP(timezone=True))
    timezone = db.Column(db.String)
    moving_time = db.Column(db.Integer)
    elapsed_time = db.Column(db.Integer)
    distance = db.Column(db.DECIMAL)

Most part of the UserActivity class contains model database tables with Flask-SQLAlchemy.

How to tell Flask-SQLAlchemy which HANA database instance that our database table will reside in

In the UserActivity class, we indicate the name of the database schema and type of table that the model will map to via __table_args__.

How to enable the auto increment of an integer primary key in your HANA database table

In the UserActivity class, we first use the Sequence class to map to a database sequence: MYDBSCHEMA.UA_ID_NUM. After that, we pass in this sequence as a parameter to the Column class that we will use for describing the integer primary key in our HANA database table.

Use SQLAlchemy facilities as per normal

At this point in time, you should be able to use the data manipulation functions provided by SQLAlchemy to interact with your HANA database instance in your Python Flask program.