How to append to a list in on conflict_do_update in SQLAlchemy?

user_9474934 0
Viewed 223

Model:

class Example(Base):
    __tablename__ = "example"

    create_time = Column(DateTime, server_default=func.now())
    time_stamps = Column(MutableList.as_mutable(ARRAY(DateTime)), server_default="{}")
    update_time = Column(DateTime, server_default=func.now())

Now when I insert new example, I need to append the create_time of new example into the time_stamps ARRAY, then I need to sort it to get the newest time and that time set as a new update_time.

I managed to do it separately

def update_record(db: Session, create_time: datetime, db_record: Example):
    db_record.time_stamps.append(create_time)
    sorted_times = sorted(db_record.time_stamps, reverse=True)
    db_record.update_time = sorted_times[0]
    db_record.time_stamps = sorted_times
    db.commit()

But I need to do it atomically using INSERT ON CONFLICT UPDATE clause.

So far I have:

db_dict = {"create_time": record.create_time,
          "time_stamps": [record.create_time],
          "update_time": record.create_time}

stm = insert(Example).values(db_dict)
do_update_stm = stm.on_conflict_do_update(constraint='my_unique_constraint',
                                         set_=dict(??)

My question is how to access and append to values of the original conflict row in set_ inside conflict_do_update in SQLAlchemy?

Thanks

upsert sqlalchemy postgresql python
1 Answers

In the end I bypassed SQLAlchemy by writing a textual query where I could use this syntax to append to ARRAY

.. DO UPDATE SET time_stamps = example.time_stamps || EXCLUDED.create_time,
user_94749340
Related