SQLAlchemy update PostgreSQL array using merge not work

user_1321045 0
Viewed 6297

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_key=True)
    recordFile = Column('record_file', String(128))
    appSrcPorts = Column('app_src_ports', ARRAY(Integer))
    info5 = Column('info5', String(100))

and I select and update the session_log table like this:

session = Session()
sessionLog = session.query(SessionLog).filter_by(id=sessionLogId).first()
sessionLog.appSrcPorts.append(1)
session.merge(sessionLog)
session.commit()

But it is weird the column 'app_src_ports' not update after I called merge() and commit(). And I find a ugly way to make it work, before the append() line, add this:

sessionLog.appSrcPorts = list(sessionLog.appSrcPorts)

Anyone can tell me why?

sqlalchemy postgresql sql python
2 Answers

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

class MutableList(Mutable, list):

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

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

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

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

    @classmethod
    def coerce(cls, key, value):
        if not isinstance(value, MutableList):
            if isinstance(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="{}"
)
user_38674060
Related