Sqlalchemy Update Postgresql Array Using Merge Not Work

I'm using SQLAlchemy to access PostgreSQL database, and I defined the object like this: class SessionLog(Base): __tablename__ = 'session_log' id = Column(Integer, primary_

Solution 1:

the SQLAlchemy ORM relies upon detection of events in order to tell when some data has changed, and thus when data needs to be flushed. in this case, you are altering the Python array value in-place, which does not by default produce any events. In order for this to work you need to use the mutable extension in conjunction with the ARRAY type (as well as a list subclass which sends these events) in order for changes to be sent as events related to the parent SessionLog object.

Solution 2:

Found this gist which helped me a lot. I added a custom __setitem__ to be able to change an item in the list, and __delitem__ to delete one:

from sqlalchemy.ext.mutable import Mutable
from sqlalchemy.dialects.postgresql import ARRAY

classMutableList(Mutable, list):

    def__setitem__(self, key, value):
        list.__setitem__(self, key, value)
        self.changed()

    def__delitem__(self, key):
        list.__delitem__(self, key)
        self.changed()

    defappend(self, value):
        list.append(self, value)
        self.changed()

    defpop(self, index=0):
        value = list.pop(self, index)
        self.changed()
        return value

    @classmethoddefcoerce(cls, key, value):
        ifnotisinstance(value, MutableList):
            ifisinstance(value, list):
                return MutableList(value)
            return Mutable.coerce(key, value)
        else:
            return value

Then in your model:

your_field = db.Column(
    MutableList.as_mutable(ARRAY(db.String())),
    server_default="{}"
)