CODEWITHSUNDEEP
pythonpython-2.7sqlalchemy
Endophage
Endophage

Reputation: 21473

Efficient update, insert on fail, with sqlalchemy

I essentially need to do an INSERT ... ON DUPLICATE KEY UPDATE however the row will already exist 99.9...% of the time. Therefore, what I really want to do is an UPDATE, catch the odd exception and INSERT.

In sqlalchemy I would typically do the following:

new_obj = MyModel(the_pk=id)
new_obj = MyModel.__table__.c.some_val + val
session.merge(new_obj)

I noticed though that when I call session.merge sqlalchemy does a SELECT, which is inefficient in the case that I know I'm almost always UPDATE'ing. Is there some way to improve on this short of just writing raw SQL (which I'm fine with)? I was just wondering if there was some sqlalchemy trick I'm missing.

Also, after doing the merge, what happens if a row with a conflicting PK comes into existence before the session is flushed/committed? I assume the select is to determine if sqlalchemy needs to later do an INSERT or UPDATE

Upvotes: 0

Views: 1132

Answers (1)

Tony Gibbs
Tony Gibbs

Reputation: 2489

Couple options: 1) Bypass SA and do an actual merge statement or use a replace statement if your dbms supports it. Here is an SA ticket for adding merge statement, which also includes a receipt for replace statement: SA Ticket 960

2) Easy way, do an update, you'll get the number of rows updated back, if it's 0 do the insert.

The 2nd option could potentially have issues if you have multiple writers since two writers could update and both get 0 rows updated back and then only one of the two writers inserts would succeeded. Not sure if that is an issue in your case but something to be aware of.

Upvotes: 1

Related Questions