This Monday, November 26 I participate in Montréal-Python 33: Qualified Quasar a really nice meetup organized by my friend from the Montreal Python Community. I didn't show up only as an attendee, but also as an speaker and I talked about "Python and SAP" (Will blog about it as soon as the videos got ready)...during my presentation, someone asked me about SQLAlchemy and SAP HANA.
Up to that day, I have never used SQLAlchemy and I really didn't knew if something like that existed for SAP HANA, but something...call it premonition...told me that someone was working on that...so I told the guy..."I don't know, but I think someone is working on that...will let you guys know as soon as I got some news".
On Tuesday, I started to check the SQLAlchemy page but didn't catch all the buzz around it...and suddenly, Srdjan Boskovic told me that Martin Stein had been working on...guess what? SQLAlchemy for SAP HANA...he asked me if I could play with it and maybe write a blog about...you could imagine how happy and excited I was...a new technology to learn...hours of head breaking trying to understand it...waking up early and going to bed late just to write a blog on SCN...I was in heaven for sure...but...I was busy already, so I promised both to take a look the next day...
Today, I woke up really early...earlier than usual and started to analyse the code and the examples...read a couple of tutorials and then started to work for the first time ever with SQLAlchemy...with some really nice help from Martin...I finally finished my application...I can say that it took me around 15 hours without brakes...but when you love your work...you just can't stop...
Anyway...this is still on "Beta"...no available yet, so for now...I will show you how to use it and how an application made with it looks like...of course...I return to my favourite Python micro framework...the almighty Bottle.
So, for this project we need Bottle, PyODBC and the SQLAlchemy_HANA libraries. I also used an ODBC Connection to my AWS SAP HANA Server.
Let's see the source code...
| Band_Model.py |
|---|
from sqlalchemy import Column, Integer, String
from meta import Base
class Singer(Base):
__tablename__ = 'SINGERS'
singer_id = Column(Integer, primary_key=True)
first_name = Column(String(20))
last_name = Column(String(20))
band = Column(String(20))
def __init__(self, singer_id, first_name, last_name, band):
self.singer_id = singer_id
self.first_name = first_name
self.last_name = last_name
self.band = band
def __repr__(self):
return "<Single('{first_name}', '{last_name}'\
,'{band}')>".format(**self.__dict__)
|
As I didn't have any idea on what to base my application, I decided to create something to keep track of some of my favorite Punk Rock singers...this first script Band_Model.py will be in charge of create the table SINGERS on SAP HANA.
| Band_App.py |
|---|
from bottle import get, post, request, run, redirect
from meta import Base, engine, Session
from sqlalchemy import *
from sqlalchemy.orm.exc import NoResultFound
from Band_Model import Singer
def connect():
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
@get('/show')
def show_form():
output = "<div align='center'>"
output += "<h1>SQLAlchemy on SAP HANA</h1>"
output += "<a href='/add_singer'>Add Singer</a>"
output += "<table border=1>"
singers = Session.query(Singer).all()
output += "<tr><th>Id</th><th>First Name</th>"\
"<th>Last Name</th><th>Band</th>"\
"<th>Update</th><th>Delete</th></tr>"
for singer in singers:
update = "/update_singer?singer_id=" + str(singer.singer_id)
delete = "/delete_singer?singer_id=" + str(singer.singer_id)
output += "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>"\
"<td><a href='%s'>Update</a></td><td>"\
"<a href='%s'>Delete</a></td></tr>"\
% (singer.singer_id, singer.first_name, singer.last_name,
singer.band, update, delete)
output += "</table></div>"
return output
@get('/add_singer')
def add_form():
query = ""
singer_id = 0
try:
query = Session.query(Singer.singer_id).\
order_by(desc(Singer.singer_id)).first()
singer_id = int(query[0]) + 1
except NoResultFound:
singer_id = 1
except TypeError:
singer_id = 1
output = "<DIV ALIGN='CENTER'><TABLE>"
output += "<FORM METHOD='POST'><TR><TD>"
output += "<INPUT TYPE='HIDDEN' NAME='Singer_Id'"\
"value='%s'></TD></TR>" % (singer_id)
output += "<TR><TD>First Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='First_Name'></TD></TR>"
output += "<TR><TD>Last Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='Last_Name'></TD></TR>"
output += "<TR><TD>Band</TD>"
output += "<TD><INPUT TYPE='TEXT' NAME='Band'></TD></TR>"
output += "<TR><TD COLSPAN='2' ALIGN='CENTER'>"
output += "<INPUT TYPE='SUBMIT' value='Add Singer' NAME='Add_Singer'>"
output += "<INPUT TYPE='RESET' value='Clear'></TD></TR>"
output += "</FORM><TABLE></DIV>"
return output
@post('/add_singer')
def create():
Singer_Id = request.forms.get('Singer_Id')
Singer_Id = int(Singer_Id)
First_Name = request.forms.get('First_Name')
Last_Name = request.forms.get('Last_Name')
Band = request.forms.get('Band')
singer = Singer(Singer_Id, First_Name, Last_Name, Band)
Session.add(singer)
Session.commit()
redirect("/show")
@get('/update_singer')
def update_form():
query = ""
singer_id = 0
singer_id = int(request.query.singer_id)
query = Session.query(Singer.first_name, Singer.last_name, Singer.band).\
filter_by(singer_id=singer_id).first()
first_name = query[0]
last_name = query[1]
band = query[2]
output = "<DIV ALIGN='CENTER'><TABLE>"
output += "<FORM METHOD='POST'><TR><TD>"
output += "<INPUT TYPE='HIDDEN' NAME='Singer_Id'"\
"value='%s'></TD></TR>" % singer_id
output += "<TR><TD>First Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='First_Name'"\
"value='%s'></TD></TR>" % first_name
output += "<TR><TD>Last Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='Last_Name'"\
"value='%s'></TD></TR>" % last_name
output += "<TR><TD>Band</TD>"
output += "<TD><INPUT TYPE='TEXT' NAME='Band'"\
"value='%s'></TD></TR>" % band
output += "<TR><TD COLSPAN='2' ALIGN='CENTER'>"
output += "<INPUT TYPE='SUBMIT' value='Update Singer'"\
"NAME='Update_Singer'>"
output += "<INPUT TYPE='RESET' value='Clear'></TD></TR>"
output += "</FORM><TABLE></DIV>"
return output
@post('/update_singer')
def update():
Singer_Id = request.forms.get('Singer_Id')
Singer_Id = int(Singer_Id)
First_Name = request.forms.get('First_Name')
Last_Name = request.forms.get('Last_Name')
Band = request.forms.get('Band')
Session.query(Singer).filter_by(singer_id=Singer_Id).\
update({'first_name': First_Name, 'last_name': Last_Name,
'band': Band})
Session.commit()
redirect("/show")
@get('/delete_singer')
def delete():
singer_id = 0
singer_id = int(request.query.singer_id)
Session.query(Singer).filter_by(singer_id=singer_id).delete()
Session.commit()
redirect("/show")
connect()
run(host='localhost', port=8080)
|
This script Band_App.py will be in charge of creating the Bottle Application, insert new singers, modify the existing ones and delete them. As you can see...there's no a single SELECT, as SQLAlchemy manages the tables like classes and provide handy methods to manage everything. Now I really get all the buzz around it...it's really impressive...and easy to use...after you spend 15 hours working with it...
Let's see the images...
Of course, the first time we run the application, the Table is going to be empty...we just create it...
We can insert our first record...or maybe more to make it look better...
Oh...it seems that Mr. Ness doesn't have a band...no problem...we can update his record...
We can check if everything is OK or not...
Yes, now everything looks good...however...we need to show that the Delete link works as well...so...sorry for the guys...
Well...that's it...I had a lot of fun working on this...and it's another proof that SAP HANA is really versatile and can be used everywhere. Till next time -:)
Greetings,
Blag.
