How to do atomic increment / decrement using Elixir / SQLAlchemy

Question

I would like to increase (or decrease) the rating field in an Elixir object:

class Posting(Entity): score = Field(Integer, PassiveDefault(text('0'))) def upvote(self): self.score = self.score + 1 

However, this does not work reliably with simultaneous calls for promotion. The best I could come up with is an ugly mess (basically building SQL UPDATE with SQLAlchemy):

 def upvote(self): # sqlalchemy atomic increment; is there a cleaner way? update = self.table.update().where(self.table.c.id==self.id) update = update.values({Posting.score: Posting.score + 1}) update.execute() 

Do you see any problems with this solution? Are there any cleaner ways to achieve the same?

I would like to avoid using DB locks here. I use Elixir, SQLAlchemy, Postgres.

Update

Here is an option that is deduced from the vonPetrushev solution:

 def upvote(self): Posting.query.filter_by(id=self.id).update( {Posting.score: Posting.score + 1} ) 

This is slightly better than my first solution, but filtering for the current object is still required. Unfortunately, this does not work if Entity is spread across multiple tables.

Answer 1

I will try, but I'm not sure if this fits your needs:

 session.query(Posting).\ .filter(Posting.id==self.id)\ .update({'score':self.score+1}) 

You might want to do session.commit () right after that?

EDIT : [regarding updating the question]

If the posting is obtained from Entity, which is mapped to a class with several tables, the solution above still stands, but the value of the Posting.id attribute changes, that is, it no longer maps to any column of the table, but to a different composition. Here: http://docs.sqlalchemy.org/en/latest/orm/nonstandard_mappings.html#mapping-a-class-against-multiple-tables you can see how to determine it. I suggest it be like this:

  j = join(entity_table_1, entity_table_2) mapper(Entity, j, properties={ 'id': column_property(entity_table_1.c.id, entity_table_2.c.user_id) <... some other properties ...> })