Currently Being Moderated

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...

 

Hdbcli_Folder.PNG

 

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

 

Python_Lib_Folder.PNG

 

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

 

Hdbclient_Folder.PNG

 

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.

 

Test_Python_HANA.py

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

 

Python_HANA.py

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.

 

Python_Bottle_HANA_01.PNG

We need of course to log in

 

Python_Bottle_HANA_02.png

Python_Bottle_HANA_03.png

 

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).

 

Python_Bottle_HANA_04.png

 

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

 

Python_Bottle_HANA_05.png

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

Comments

Actions

Filter Blog

By author:
By date:
By tag: