python - SQLAlchemy update PostgreSQL array using merge not work

Question

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?

Answer 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.