Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member182779
Active Contributor

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.

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 :smile:

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.

We need of course to log in :wink:

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 :smile:

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 

38 Comments