Connecting to HANA with Python using ODBC or oData

[RonaldKonijnenb]

As a HANA developer it is almost mandatory for you to learn a scripting language. It will give you a ton of off new development opportunities. You probably have heard about using JavaScript as a scripting language for connecting an oData service to SAPUI5 or using R to make statistical calculations, but did you know that my TechEd Demo Jam entry from 2012 was entirely based on Python? And did you know that my HANA Inno Jam entry from 2012 was based on Ruby? I can imagine you didn’t. The point I’m trying to make is that learning a scripting language will broaden the things you can build with HANA tremendously. It is a corny statement, but the sky is truly the limit once you master the language(s).

Study young grasshopper!

Learning a scripting language can be tricky, but the web will give you lots of free training opportunities. Learn Python is an obvious one, Code Academy another one.  Every language has a similar training website. Just Google and enjoy 😉

A head start

To give you a head start after your training, I would like to share my preferred ways of connecting HANA via Python. Python is my favorite language as it was developed by a Dutch guy. Just kidding of course ;-), I love it as it is a language which is easy to learn and you can do great things with only a few lines of code (compared to other languages).  If you really want to know why I like it so much, a guy called Tim Peters said it best in his “Zen of Python”.

For the head start, I will give you two ways of connecting: via ODBC and ofourse via oData. For our examples we’ll use a script to create records in HANA. In the below examples I’ll use a simple table: two fields: a timestamp field and a numeric field.

ODBC

The easiest way of connecting to HANA via ODBC in Python is by using PyODBC.  Install it and create a so called DSN via an ODBC manager in Windows or Linux. After that, you will be creating records in HANA in no time via your Python script:

#!/usr/bin/python

# -*- coding: utf-8 -*-

import pyodbc

cnxn = pyodbc.connect('DSN=YOUR_ODBC_CONNECTION;UID=USERNAME;PWD=PASSWORD')

cursor = cnxn.cursor()

cursor.execute("insert into MY_TABLE values (CURRENT_TIMESTAMP,’100000’)”)

cnxn.commit()

We just posted a value of 100000 with the current timestamp of the system (using the function CURRENT_TIMESTAMP).

Ofcourse this is a hardcoded example. In a real life example you will be using variables and loops to create records. Be sure to follow one of the trainings as listed above and you will be able to handle those in no time aswell.

oData service

Connecting in Python via ODBC can be tricky as you will need an ODBC driver. Now HANA comes with one of course, but sometimes you are on some exotic Linux Distro and simply do not have an ODBC driver which will compile. In that case oData comes to the rescue.

HANA SPS6 comes with some great new features, using POST in your RESTfull service is one of them. The Python library of choice in this case is Requests. It is dead simple to use. Installing it is pretty much the hardest part.

So without further a due: this is how you create a script that posts records:

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import requests

import json

url = 'YOUR_ODATA_SERVICE_HERE'

payload = {'TIMESTAMP': '/Date(1474823911255)/', 'VALUE': '100000'}

headers = {"Content-type": 'application/json;charset=utf-8'}

auth = ‘USERNAME’, ' PASSWORD_HERE'

r = requests.post(url, data=json.dumps(payload), headers=headers, auth=auth)

print r.text

The timestamp might look a bit weird (13 digits). It is the only way the service accepts the value and represents the Unix timestamp with millisecond precision, i.e. the number of milliseconds since Unix epoch.

As you can see in the above example I also import “json”. This is required as the service can only handle the Json format.

Have fun developing, be creative and you will be creating that killer demo in no time!

Ronald.