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) carrname = str(row) 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) 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) connid = str(row) fldate = str(row) passname = row.encode('utf-8') cityfrom = row.encode('utf-8') cityto = row.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 -;)