SQLAlchemy ORM Update for HSTORE fields

Question

I have a problem when I try to update hstore field. I have the following translation hybrid and database model.

translation_hybrid = TranslationHybrid(
    current_locale='en',
    default_locale='de'
)

class Book:
    __tablename__ = "Book"
    id = Column(UUID(as_uuid=True), primary_key=True)
    title_translations = Column(MutableDict.as_mutable(HSTORE), nullable=False)

    title = translation_hybrid(title_translations)

I want to update title with the current locale using a single orm query. When I try the following query

query(Book).filter(Book.id == id).update({"title": "new_title"})

ORM converts this to the following sql:

UPDATE "Book" SET coalesce(title_translations -> 'en', title_translations -> 'de') = "new_title" WHERE "Book".id = id

And this sql gives the syntax error. What is the best way to update it without fetching the model first and assigning the value to the field?

Answer 1

We got this to run eventually, documenting here for the benefit of others that might run into this issue; Note that we're using the new select methodology and async.

As you already suggested, we solved this by assigning the updated values directly to the record object. We're basically implementing this solution from the SQLAlchemy docu:

    updated_record: models.Country = None  # type: ignore
    try:
        # fetch current data from database and lock for update
        record = await session.execute(
            select(models.Country)
            .filter_by(id=str(country_id))
            .with_for_update(nowait=True)
        )
        updated_record = record.scalar_one()
        logger.debug(
            "update() - fetched current data from database",
            record=record,
            updated_record=vars(updated_record),
        )

        # merge country_dict (which holds the data to be updated) with the data in the DB
        for key, value in country_dict.items():
            setattr(updated_record, key, value)
        logger.debug(
            "update() - merged new data into record",
            updated_record=vars(updated_record),
        )

        # flush data to database
        await session.flush()

        # refresh updated_record and commit
        await session.refresh(updated_record)
        await session.commit()

    except Exception as e:  # noqa: PIE786
        logger.error("update() - an error occurred", error=str(e))
        await session.rollback()
        raise ValueError("Record can not be updated.")

    return updated_record

Answer 2

I think I have solved a similar instance of the problem using the bulk update query variant.

In this case a PoC solution would look like this:

session.execute(update(Book), [{"id": id, "title": title}])
session.commit()

I am not sure why this does not trigger the coalesce() issue but it seems to be working. We should probably open an issue in SQLAlchemy as I don't have the time right now to debug it to its root cause.

UPDATE I think that the original issue actually originates in sqlalchemy-util as the coalesce seems to arise from the expr_factory of the hybrid property here.