OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

SQL Alchemy is not properly updating columns

  • Thread starter Thread starter Rexxology
  • Start date Start date
R

Rexxology

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

Code:
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:

Code:
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?
<p>I have created a sql alchemy script however for some reason it is not behaving as intended:</p>
<pre><code>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))
</code></pre>
<p>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.</p>
<p>I have tried to add "session.merge()" to this bit:</p>
<pre><code>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
</code></pre>
<p>However I'm getting this error:</p>
<blockquote>
<p>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)")</p>
</blockquote>
<p>Which suggests that the function session.merge() is trying to create new rows even on these matching the condition.</p>
<p>Any suggestion?</p>
 
Top