How to update db from json patch

Question

I get a complex json every few hours. Part of it is a Game object with a list of Player objects that each Player has a list of Training object (each object has also other fields - ints, strings, list of strings etc.). If the Game object doesn't exist in my Postgres db (I check by the Game's id field), I insert the whole structure to the db, each object as its own table (a table for Game, a table for Player and a table for Training). Next time I get the json for this Game, it already exists in the db, so I want to update it. I get the old json, the updated json, and the json_patch. I wanted to query the db, convert it to json, and apply the patch on that json. The problem is that the lists (of the players for example) are not sorted in the same way as the lists in the updated_object json. But I need to somehow work on the db because I need to have the primary keys of the objects so the ORM knows which objects to update. What's the best way to approach it?

models:

class Game(Base):
    __tablename__ = "game"
    game_id: int = Column(INTEGER, primary_key=True,
                              server_default=Identity(always=True, start=1, increment=1, minvalue=1,
                                                      maxvalue=2147483647, cycle=False, cache=1),
                              autoincrement=True)
    unique_id: str = Column(TEXT, nullable=False)
    name: str = Column(TEXT, nullable=False)
    players = relationship('Player', back_populates='game')

class Player(Base):
    __tablename__ = "player"
    player_id: int = Column(INTEGER, primary_key=True,
                          server_default=Identity(always=True, start=1, increment=1, minvalue=1,
                                                  maxvalue=2147483647, cycle=False, cache=1),
                          autoincrement=True)
    unique_id: str = Column(TEXT, nullable=False)
    game_id: int = Column(INTEGER, ForeignKey('game.game_id'), nullable=False)
    name: str = Column(TEXT, nullable=False)
    birth_date = Column(DateTime, nullable=False)
    game = relationship('Game', back_populates='players')
    trainings = relationship('Training', back_populates='player')

class Training(Base):
    __tablename__ = "training"
    training_id: int = Column(INTEGER, primary_key=True,
                               server_default=Identity(always=True, start=1, increment=1, minvalue=1,
                                                       maxvalue=2147483647, cycle=False, cache=1),
                               autoincrement=True)
    unique_id: str = Column(TEXT, nullable=False)
    name: str = Column(TEXT, nullable=False)
    number_of_players: int = Column(INTEGER, nullable=False)
    player_id: int = Column(INTEGER, ForeignKey('player.player_id'), nullable=False)
    player = relationship('Player', back_populates='players')

json with updated data:

{"original_object":{"name":"Table Tennis","unique_id":"432","players":[{"unique_id":"793","name":"John","birth_date":"2023-10-28T00:10:56Z","trainings":[{"unique_id":"43","name":"Morning Session","number_of_players":3}, {"unique_id":"44","name":"Evening Session","number_of_players":2}]}]},"updated_object":{"name":"Table Tennis","unique_id":"432","players":[{"unique_id":"793","name":"John","birth_date":"2023-10-28T00:10:56Z","trainings":[{"unique_id":"43","name":"Morning Session","number_of_players":3}, {"unique_id":"44","name":"Evening Session","number_of_players":4}]}]},"json_patch":[{"op":"replace","path":"/players/0/trainings/1/numbre_of_players","value":4}],"timestamp":"2023-10-28T02:00:36Z"}

The json_patch updates the 'numbre_of_players' field of the second training to the value 4.

Code to add a new Game:

    Session = sessionmaker(bind=engine_sync)
        session = Session()
        session.begin()
        game = Game.from_dict(json['updated_object'])
        existing_game = session.query(Game).filter_by(unique_id=game.id).first()
    if not existing_game:
        session.add(game)
        session.commit()

But if the Game does already exists in the db, I'm not sure what I should do.

Answer 1

This looks like a task for sqlalchemy.orm.Session.merge(). You're looking at 3 possible states of the same Game:

1. one in the database,
2. one in your session,
3. another one, wherever you're getting this updated state from

You want to update/overwrite whatever the first two states are, assuming the third one is true.

Session = sessionmaker(bind=engine_sync)
session = Session()
session.begin()
game = Game.from_dict(json['updated_object'])
game = session.merge(game)
session.commit()

No need to check if it's already in you session and/or in the db, because merge() checks this automatically, and if it doesn't find a matching Game, it adds it as a new one. From the doc:

Session.merge() examines the primary key attributes of the source instance, and attempts to reconcile it with an instance of the same primary key in the session. If not found locally, it attempts to load the object from the database based on primary key, and if none can be located, creates a new instance. The state of each attribute on the source instance is then copied to the target instance. The resulting target instance is then returned by the method; the original source instance is left unmodified, and un-associated with the Session if not already.

Remember to set up your cascades accordingly, to deal with all objects that might be related to it.