SAP HANA and Python? Yes Sir!

It's been a while since I wrote my last Python blog...July 15, 2011 Tasting the mix of Python and SAP - Volume 3...almost a year...so I thought it was a good thing to get back into action...but...what else could I wrote about? I started to check my options and of course I quickly thought on SAP HANA. After all, I have my own server running on Amazon Web Services so it sound like a good plan.

At first I wanted to use SAP HANA as an ODBC connection like I did with #R in the early days...but then I heard that Python was actually embedded on the SAP HANA Client installation, so it only took a little checking, and some SAP internal forum to discover that, using Python and SAP HANA is easier that I could ever imagine...

First, you need to go where your SAP HANA Client is installed, and then copy the 3 files from the hdbcli folder...

Then, go to the Python folder and copy these files into the Lib folder...

Do the same with this 2 files in the hdbclient folder, copy them and paste them into the Python/Lib folder...

With that, we're ready to go...download any Python IDE and assign Python.exe (from the Python folder on hdbclient) as the Python executable.

import dbapi
 
conn = dbapi.connect('ecX-XX-XX-XXX-XXX.compute-1.amazonaws.com',
     30015, 'SYSTEM', 'manager')
 
print conn.isconnected()

If this little program prints "TRUE" on the screen, it means that the connection is done.

Go to http://pypi.python.org/pypi/setuptools#windows and copy the ez_setup.py file into your Python folder. Executed in the command line like Python ez_setup.py to install the tools and then add the directory "full path/Python/Scripts" to your path variable in the Windows System variables...

With that, you can directly call Easy_Install...that we're going to need in order to install Bottle, a Python Web Micro framework.

In the command prompt write:

easy_install -U bottle

With that, we're ready to start

from bottle import get, post, request, run, redirect, route
import dbapi
import time
 
 
@get('/login')
def login_form():
    return '''<DIV ALIGN='CENTER'><BR><BR><BR><BR>
                <H1>Python (Bottle) & SAP HANA</H1>
                <BR><TABLE BORDER='1' BORDERCOLOR='BLUE'
                     BGCOLOR='WHITE'>
                <FORM METHOD='POST'>
                <TR><TD>Server</TD><TD>
                <INPUT TYPE='TEXT' NAME='Server'></TD></TR>
                <TR><TD>Port</TD><TD>
                <INPUT TYPE='TEXT' NAME='Port'></TD></TR>
                <TR><TD>User</TD><TD>
                <INPUT TYPE='TEXT' NAME='User'></TD></TR>
                <TR><TD>Password</TD>
                <TD><INPUT TYPE='PASSWORD' NAME='Passwd'></TD></TR>
                <TR><TD COLSPAN='2' ALIGN='CENTER'>
                <INPUT TYPE='SUBMIT' value='Log In' NAME='LOG_IN'>
                <INPUT TYPE='RESET' value='Clear'></TD></TR>
                </FORM>
                <TABLE>
              </DIV>'''
 
 
@post('/login')
def login_submit():
    global cur
    Server = request.forms.get('Server')
    Port = request.forms.get('Port')
    User = request.forms.get('User')
    Passwd = request.forms.get('Passwd')
    Port = int(Port)
    conn = dbapi.connect(Server, Port, User, Passwd)
    cur = conn.cursor()
    redirect("/parameters")
 
 
@get('/parameters')
def choose_parameters():
    global cur
    query = "SELECT CARRID,CARRNAME FROM SFLIGHT.SCARR WHERE MANDT = 300"
    ret = cur.execute(query)
    ret = cur.fetchall()
    output = "'<CENTER><FORM METHOD='POST'>"
    output += "Carrier <SELECT NAME='Carrid'>"
    for row in ret:
        carrid = str(row[0])
        carrname = str(row[1])
        output += "<OPTION VALUE='%s'>%s</OPTION>" % (carrid, carrname)
    output += "</SELECT>"
    query = "SELECT DISTINCT CITYFROM FROM SFLIGHT.SPFLI WHERE MANDT = 300"
    ret = cur.execute(query)
    ret = cur.fetchall()
    output += "City From<SELECT NAME='Cityfrom'>"
    for row in ret:
        cityfrom = str(row[0])
        output += "<OPTION VALUE='%s'>%s</OPTION>" % (cityfrom, cityfrom)
    output += "</SELECT>"
    output += "<INPUT TYPE='SUBMIT' value='Show Query' NAME='show_query'>"
    output += " </FORM></CENTER>"
    return output
 
 
@post('/parameters')
def show_query():
    counter = 0
    start = time.clock()
    carrid = request.forms.get('Carrid')
    cityfrom = request.forms.get('Cityfrom')
    query = '''SELECT SBOOK.CARRID,SBOOK.CONNID,FLDATE,
                PASSNAME,CITYFROM,CITYTO
                FROM SFLIGHT.SBOOK INNER JOIN SFLIGHT.SPFLI
                ON SBOOK.CONNID = SPFLI.CONNID
                WHERE SBOOK.CARRID = '%s' AND CITYFROM = '%s'
                AND PASSNAME <> ''
                AND SBOOK.MANDT = 300
                AND year(FLDATE) = 2012
                ORDER BY FLDATE DESC''' % (carrid, cityfrom)
    ret = cur.execute(query)
    ret = cur.fetchall()
    output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
    output += "<TR BGCOLOR='#B9C9FE'>"
    output += "<TH>Carrier</TH><TH>Connection</TH>"
    output += "<TH>Flight Date</TH><TH>Passenger Name</TH>"
    output += "<TH>City From</TH><TH>City To</TH>"
    output += "</TR>"
    for row in ret:
        counter += 1
        carrid = str(row[0])
        connid = str(row[1])
        fldate = str(row[2])
        passname = row[3].encode('utf-8')
        cityfrom = row[4].encode('utf-8')
        cityto = row[5].encode('utf-8')
        output += "<TR BGCOLOR='#E8EDFF'>"
        output += '''<TD>%s</TD><TD>%s</TD>
                         <TD>%s</TD><TD>%s</TD>
                         <TD>%s</TD><TD>%s</TD>''' 
        % (carrid, connid, fldate, passname, cityfrom, cityto)
        output += "</TR>"
    output += "</TABLE>"
    end = time.clock()
    time_taken = end - start
    output += "<H1>%s records in %s seconds</H1></DIV>" 
              % (counter, time_taken)
    return output
 
run(host='localhost', port=8080)

We when run this code, the Bottle Web Server is going to start, so we need to go to http://localhost:8080/login to start the application.

We need of course to log in -;)

We can choose a Carrier and the City From for our query (Of course, both Dropdown list are filled with data coming from SAP HANA).

Let's show the query in a nice table...

It took a little bit more than 3 seconds to fetch data from SAP HANA and the to print it on Python. Almost 3K records in 3 seconds? That's fast...specially when we considered that Python is not exactly the fastest tool in the market -:)

I hope you like this blog, as I really enjoyed working on it. It's always a great experience to use Python, SAP HANA or any other programming language to develop something new -;)

Greetings,

Blag.