SQLAlchemy 使用合并更新 PostgreSQL 数组不起作用 [英] SQLAlchemy update PostgreSQL array using merge not work

问题描述

我正在使用 SQLAlchemy 访问 PostgreSQL 数据库，并且我像这样定义了对象:

class SessionLog(Base):__tablename__ = 'session_log'id = 列(整数，primary_key=True)recordFile = Column('record_file', String(128))appSrcPorts = Column('app_src_ports', ARRAY(Integer))info5 = Column('info5', String(100))

然后我像这样选择并更新 session_log 表:

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

但奇怪的是，在我调用了 merge() 和 commit() 之后，app_src_ports"列没有更新.我找到了一个丑陋的方法来让它工作，在 append() 行之前，添加:

sessionLog.appSrcPorts = list(sessionLog.appSrcPorts)

谁能告诉我为什么?

解决方案

SQLAlchemy ORM 依赖于对事件的检测来判断某些数据何时发生更改，以及何时需要刷新数据.在这种情况下，您正在就地更改 Python 数组值，默认情况下不会产生任何事件.为了使其工作，您需要使用可变扩展结合 ARRAY 类型(以及发送这些事件的 list 子类)，以便将更改作为与父 SessionLog 对象相关的事件发送.>

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?

解决方案

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.

这篇关于SQLAlchemy 使用合并更新 PostgreSQL 数组不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！