SQLAlchemy - select for update example

python sqlalchemy select-for-update
user317033 picture user317033 · Apr 10, 2012 · Viewed 36.1k times · Source

I'm looking for a complete example of using select for update in SQLAlchemy, but haven't found one googling. I need to lock a single row and update a column, the following code doesn't work (blocks forever):

s = table.select(table.c.user=="test",for_update=True)
# Do update or not depending on the row
u = table.update().where(table.c.user=="test")         
u.execute(email="foo")

Do I need a commit? How do I do that? As far as I know you need to: begin transaction select ... for update update commit

Answer

Matthew Moisen picture Matthew Moisen · Mar 4, 2017

If you are using the ORM, try the with_for_update function:

foo = session.query(Foo).filter(Foo.id==1234).with_for_update().one()
# this row is now locked

foo.name = 'bar'
session.add(foo)

session.commit()
# this row is now unlocked

Related questions

SQLAlchemy ORDER BY DESCENDING?

How can I use ORDER BY descending in a SQLAlchemy query like the following? This query works, but returns them in ascending order: query = (model.Session.query(model.Entry) .join(model.ClassificationItem) .join(model.EnumerationValue) .filter_by(id=c.row.…

Read More
How to execute raw SQL in Flask-SQLAlchemy app

How do you execute raw SQL in SQLAlchemy? I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. I need a way to run the raw SQL. The query involves multiple table joins …

Read More
Convert sqlalchemy row object to python dict

Is there a simple way to iterate over column name and value pairs? My version of sqlalchemy is 0.5.6 Here is the sample code where I tried using dict(row), but it throws exception , TypeError: 'User' object is not iterable import …

Read More