Flask-SQLAlchemy update one-to-many record

Question

I have one-to-many relationship models, and here is the snippet of my models:

class ScheduleDayAndTime(db.Model):
    __tablename__ = 'schedule_day_and_time'
    id = db.Column(db.Integer, primary_key=True)
    day = db.Column(db.Enum(DayNameList, name='day'))
    start_at = db.Column(db.Time())
    end_at = db.Column(db.Time())
    requisition_schedule_id = db.Column(db.Integer, db.ForeignKey('requisition_schedule.id'), nullable=True)



class RequisitionSchedule(db.Model):
    __tablename__ = 'requisition_schedule'
    id = db.Column(db.Integer, primary_key=True)
    schedule_day_and_time = db.relationship('ScheduleDayAndTime', backref='requisition_schedule', lazy=True)
    # ...
    # ...

How to update the data on the many table..?

For now try it like this:

requisition_schedule = RequisitionSchedule.query.filter_by(id=requisition_schedule_id).first()

requisition_schedule.schedule_day_and_time.clear()
db.session.commit()

schedule_day_and_time_1 = ScheduleDayAndTime(
    day=form.schedule_day.data,
    start_at=form.start_at.data,
    end_at=form.end_at.data,
)

schedule_day_and_time_2 = ScheduleDayAndTime(
    day=form.schedule_day_2.data,
    start_at=form.start_at_2.data,
    end_at=form.end_at_2.data,
)

requisition_schedule.schedule_day_and_time.append(schedule_day_and_time_1)
requisition_schedule.schedule_day_and_time.append(schedule_day_and_time_2)
db.session.commit()

I clear the data first, and then append the new data. But I think that is not the best practice since I still have the old record on my table, it just delete the related ForeignKey id, but still have other records on related column.

So, how to do it in the correct way..?

score 1 · Accepted Answer · answered Sep 26 '19 at 04:11

I figure out this by doing this following:

First, I delete the current record:

ScheduleDayAndTime.query.filter(ScheduleDayAndTime.requisition_schedule_id==requisition_schedule_id).delete()
db.session.commit()

Then I append it like my above question:

schedule_day_and_time_1 = ScheduleDayAndTime(
    day=form.schedule_day.data,
    start_at=form.start_at.data,
    end_at=form.end_at.data,
)

schedule_day_and_time_2 = ScheduleDayAndTime(
    day=form.schedule_day_2.data,
    start_at=form.start_at_2.data,
    end_at=form.end_at_2.data,
)

requisition_schedule.schedule_day_and_time.append(schedule_day_and_time_1)
requisition_schedule.schedule_day_and_time.append(schedule_day_and_time_2)
db.session.commit()

Don't know if this the best practice or not, but I think this better than the solution on my question above.

Flask-SQLAlchemy update one-to-many record

1 Answers1