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.
