Skip to Content

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…

SQLAlchemy_HANA_01.pngOf course, the first time we run the application, the Table is going to be empty…we just create it…

SQLAlchemy_HANA_02.png

We can insert our first record…or maybe more to make it look better…

SQLAlchemy_HANA_03.pngOh…it seems that Mr. Ness doesn’t have a band…no problem…we can update his record…

SQLAlchemy_HANA_04.pngWe can check if everything is OK or not…

SQLAlchemy_HANA_05.pngYes, now everything looks good…however…we need to show that the Delete link works as well…so…sorry for the guys…

SQLAlchemy_HANA_06.pngWell…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 🙂


To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

    1. Alvaro Tejada Galindo Post author

      Vitaly:

      Good question 🙂 The connection to SAP HANA is done in the meta library…

      In the Band_Model.py you can see this lines…

      from sqlalchemy import Column, Integer, String

      from meta import Base

      I didn’t include it because it’s part of the source code…that I can’t share by now…

      But is as simple as…

      engine = create_engine(‘hana://SYSTEM:manager@HANA’, echo=False)

      Where “HANA” is the ODBC, and of course SYSTEM:manager are the Username and password 😉

      Greetings,

      Blag.

      (0) 
  1. Panagiotis Koutsaftikis

    Hello Alvaro,

    You mentioned that you used SQLAlchemy_HANA libraries. Where can I find them?

    My SQLAlchemy does not seem to connect to my HANA server, producing the below error:

    sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:hana

    Thanks,

    Panagiotis

    (0) 
    1. Alvaro Tejada Galindo Post author

      Panagiotis:

      As I said on the blog, I got in touch with Martin Stein and he gave me the library which was under development at that time…I haven’t talked to Martin since then…so I don’t know if he kept developing it or not…you can try to contact him and ask him 🙂

      Greetings,

      Blag.

      Development Culture.

      (0) 

Leave a Reply