UPDATE .. LIMIT 1 with SqlAlchemy and PostgreSQL

Question

With SqlAlchemy, is it possible to build a query which will update only the first matching row?

In my case, I need to update the most recent log entry:

class Log(Base):
    __tablename__ = 'logs'
    id = Column(Integer, primary_key=True)
    #...
    analyzed = Column(Boolean)

session.query(Log)  \
    .order_by(Log.id.desc())  \
    .limit(1)  \
    .update({ 'analyzed': True })

Which results into:

InvalidRequestError: Can't call Query.update() when limit() has been called

It makes sense, since UPDATE ... LIMIT 1 is a MySQL-only feature (with the solution given here)

But how would I do the same with PostgreSQL? Possibly, using the subquery approach?

Answer 1

The subquery recipe is the right way to do it, now we only need to build this query with SqlAlchemy.

Let's start with the subquery:

sq = ssn.query(Log.id)  \
    .order_by(Log.id.desc())  \
    .limit(1)  \
    .with_for_update()

And now use it with as_scalar() with the example from the update() docs:

from sqlalchemy import update

q = update(Log)  \
    .values({'analyzed': True})  \
    .where(Log.id == sq.as_scalar())

Print the query to have a look at the result:

UPDATE logs 
SET analyzed=:analyzed 
WHERE logs.id = (
    SELECT logs.id 
    FROM logs ORDER BY logs.id DESC 
    LIMIT :param_1 
    FOR UPDATE
)

Enjoy!