Re: Python, SQLAlchemy and SAP HANA

[Alvaro Tejada]

As promised...here's the link on the SAP Community Network...

http://scn.sap.com/community/developer-center/hana/blog/2012/11/29/sqlalchemy-and-sap-hana

Greetings,

Alvaro Tejada Galindo

a.tejada...@sap.com

Development Expert

Developer Experience

SAP Labs Montreal

[julien tayon]

Hello, 

Thx for your feedback but : 

Le 29 novembre 2012 15:50, Alvaro Tejada <ate...@gmail.com> a écrit :

As promised...here's the link on the SAP Community Network...

http://scn.sap.com/community/developer-center/hana/blog/2012/11/29/sqlalchemy-and-sap-hana

        query = Session.query(Singer.singer_id).\

                    order_by(desc(Singer.singer_id)).first()

        singer_id = int(query[0]) + 1

My eyes are hurting: if two persons ask for the same form (at almost the same time), but the first fill it in slower than the second one, then, the second's data will be lost. 

You'd rather not specify the ID and normally by asking Singer.singer_id after saving it you got your last inserted id. If singer_id is not an autoincremented field, you have a problem. 

 

Cheers

-- 

Julien Tayon

[Alvaro Tejada]

Julien:

I know :) But this is just a simple example, so I didn't want to go into much details...also...I have only used SQLAlchemy for 15 hours and I don't work with Python in a regular basis (only when I need to write a blog about it)...so I'm sure there might be more "you shouldn't do it like this" on my code...

How would you do it? AFAIK in SQLite that can be done automatically...in SAP HANA I don't think so, so it must be handled on the SQLAlchermy or Python side...

Greetings,

Alvaro Tejada Galindo

a.tejada...@sap.com

Development Expert

Developer Experience

SAP Labs Montreal

[julien tayon]

2012/11/29 Alvaro Tejada <ate...@gmail.com>

I know :) But this is just a simple example, so I didn't want to go into much details...also...I have only used SQLAlchemy for 15 hours and I don't work with Python in a regular basis (only when I need to write a blog about it)...so I'm sure there might be more "you shouldn't do it like this" on my code...

How would you do it? AFAIK in SQLite that can be done automatically...in SAP HANA I don't think so, so it must be handled on the SQLAlchermy or Python side...

You have to have a semaphore on the ID provider whatever you do. ID is best set at insert time by the DB and normally when you call save() on an sqlachemy object id are updated (you got the inserted id).

(It is a concurrency problem)

A database not providing the autoincrement on insert in a safe way is considered to be not transactionnal http://en.wikipedia.org/wiki/Transaction_processing

In this case it would lack the isolation criteria.

If the DB is not transactional in its core it is quite a problem, you would have to implement all the ACID stuff at session() level http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html or serialize the access (denying access on the DB is someone is already in the process of accessing it). In other word you DB may not be accessible concurrently (at least in writing).  

 

BTW, if you have a commit, you *should* have a rollback 

If you handle the session by yourself every DB interaction should look like

http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html#using-transactions

session.begin()

try:

    # all goes welll

    session.commit()

except: 

    session.rollback() 

But with context management this code does the same: 

# runs a transaction

with engine.begin() as connection:

    r1 = connection.execute(table1.select())

    connection.execute(table1.insert(), col1=7, col2='this is some data')

(It does implicit commit rollback, but I think explicit is better than implicit)

But knowing that this is so a pattern, another way to do it is with a decorator to any db operation that commit on success, rollback (or abort) on exception.

they do it in django, turbogears, pyramid, zope with decorators with stuff like  @transaction.commit_on_success_rollback_on_failure. By reusing the idea of a decorator you may

come to a QAMDH solution (Quick And Maybe Dirty Hack) . 

To give a usable answer for your problem, I would need more than 15 minutes studying :P 

My .02 €cents :) 

-- 

Julien Tayon

[Alvaro Tejada]

Thanks Julien! -:D Very nice answer -:) SAP HANA is an In-Memory Database, so everything resides on memory...the inserts are not done directly by defined by a delta where everything is sent back to the disk, that way multiple users saving at the same time is not a problem as the database handles it...anyway...I'm not an SAP HANA "expert"...

You're right about the rollback...I guess I was to tired to think about it...used to use it all the time when I was working as an ABAP Consultant...it's been a year, so I'm forgetting a couple of things -:)

Will make sure to pay more attention to the details when I wrote my next "Python" based blog...thanks again for your comments! -:D

Greetings,

Alvaro.