python - Update reference table when foreign key has been changed

Question

Using SQLAlchemy 1.3.22, Python 3.8, PostgreSQL 12

I have these models:

RecordAndTask = Table('record_and_task', Base.metadata, 
                      Column('record_id', String, ForeignKey('record.id'), primary_key=True),
                      Column('task_id', String, ForeignKey('task.id'), primary_key=True))

RecordAndWidget = Table('record_and_widget', Base.metadata,
                        Column('record_id', String, ForeignKey('record.id'), primary_key=True),
                        Column('widget_id', String, ForeignKey('widget.id'), primary_key=True))


class Record(Base):
    __tablename__ = 'record'

    id = Column(String, primary_key=True)

    tasks = relationship('Task', secondary=RecordAndTask, backref=backref('records', lazy='dynamic'), lazy='dynamic')
    widgets = relationship('Widget', secondary=RecordAndWidget, backref=backref('records', lazy='dynamic'), lazy='dynamic')


class Task(Base):
    __tablename__ = 'task'

    id = Column(String, primary_key=True)
    status = Column(String)

    widget_id = Column(String, ForeignKey('widget.id'))


class Widget(Base):
    __tablename__ = 'widget'

    id = Column(String, primary_key=True)

    tasks = relationship('Task', backref='widget', lazy='dynamic')

Data in PostgreSQL tables.

Record table (3 rows):

id='record_1'
id='record_2'
id='record_3'

Task table (3 rows):

id='task_1', status='done', widget_id='widget_1'  
id='task_2', status='failed', widget_id='widget_3'
id='task_3', status='done', widget_id='widget_2'  

Widget table (3 rows):

id='widget_1'
id='widget_2'
id='widget_3'

Table record_and_task (3 rows):

record_id='record_1', task_id='task_1'
record_id='record_1', task_id='task_2'
record_id='record_2', task_id='task_3'

Table record_and_widget (3 rows):

record_id='record_1', widget_id='widget_1'
record_id='record_1', widget_id='widget_3'
record_id='record_2', widget_id='widget_2'

Is it possible to change widget_id in record_and_widget when widget_id have been changed in Task? Simple example (corresponding to data in the tables above).

1. It is necessary to execute the Task again if their status was not done.
2. During this execution widget_id may change.
3. I need to update relations in record_and_widget table, because Task's widget_id has been changed, for example, from widget_3 to widget_2

After execution there should be:

Table Task:

id='task_1', status='done', widget_id='widget_1'  
id='task_2', status='done', widget_id='widget_2'        <-- status changed from 'failed' to 'done', widget_id changed from 'widget_3' to 'widget_2'
id='task_3', status='done', widget_id='widget_2' 

Table record_and_widget:

record_id='record_1', widget_id='widget_1'
record_id='record_1', widget_id='widget_2'              <-- widget_id SHOULD change there (from 'widget_3' to 'widget_2')
record_id='record_2', widget_id='widget_2'

What i'm missing? I have inserted onupdate/cascade='all' parameters in all corresponding models, but there is no effect. Maybe i do it wrong.

question from:https://stackoverflow.com/questions/65910199/update-reference-table-when-foreign-key-has-been-changed

Answer 1

Waitting for answers