I have created a sql alchemy script however for some reason it is not behaving as intended:

for index, row in df_journey.iterrows():
    journey = JourneySummary(**row)
    print(vars(journey))
    existing_journey = session.query(JourneySummary).filter(
        JourneySummary.RouteIDs == journey.RouteIDs,
        JourneySummary.Planned_Start_Time == journey.Planned_Start_Time
    ).first()
    if existing_journey is None:
        session.add(journey)
        added_rows += 1
    else:
        # Update the existing journey with the new data
        for attr, value in vars(journey).items():
            setattr(existing_journey, attr, value)
        merged_rows += 1
    try:
        session.commit()
        print("Data successfully updated")
    except SQLAlchemyError as e:
        session.rollback()
        print(f"Data update failed: {e}")
        print(str(e))

The issue I have is that when RouteIDs and Planned_Start_Time are matching the condition, the script is NOT updating the columns with new data for these rows that already exist.

I have tried to add “session.merge()” to this bit:

else:
        # Update the existing journey with the new data
        for attr, value in vars(journey).items():
            setattr(existing_journey, attr, value)
        session.merge()
        merged_rows += 1

however I’m getting this error:
IntegrityError: (‘23000’, “[23000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint ‘PK__Journeys__3214EC07F297BA5F’. Cannot insert duplicate key in object ‘dbo.Journeys’. The duplicate key value is (2860). (2627) (SQLParamData); [23000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement has been terminated. (3621)”)

which suggests that the function session.merge() is trying to create new rows even on these matching the condition.

any suggestion?
Many thanks