sqlalchemy - how to copy deep copy a entry and all its foreign relation

Question

Now I have a database schema:

For a video it contains many segment and for a segment its contains many jobs and in each job it contains many paths and so on...

Now I want to copy segment and combine together to make a new video. I know I could write a script to loop over from segment to jobs to... to bboxs and copy all the entry one by one. But is there a better solution that I could do the deep copy and all its foreign relation entry in a smarter way?

Answer 1

I tried to find a solution for this but ended up adding manual copy methods as suggested by some of the users in the comment links above but I opted for a more manual approach which, in my case, simplified things. With your example, my solution would have looked like this:

class Video(Model):
    __tablename__ = 'videos'

    id = Column(Integer, primary_key=True)
    vidcol1 = Column(...)
    vidcol2 = Column(...)

    segments = relationship('Segment', uselist=True)

    def copy(self):
        new = Video()
        new.vidcol1 = self.vidcol1
        new.vidcol2 = self.vidcol2
        for segment in self.segments:
            new.segments.append(segment.copy())
        return new


class Segment(Model):
    __tablename__ = 'segments'

    id = Column(Integer, primary_key=True)
    video_id = Column(Integer, ForeignKey('videos.id'))
    segcol1 = Column(...)
    segcol2 = Column(...)

    jobs = relationship('Job', uselist=True)

    def copy(self):
        new = Segment()
        new.segcol1 = self.segcol1
        new.segcol2 = self.segcol2
        for job in self.jobs:
            new.jobs.append(job.copy())
        return new


class Job(Model):
    __tablename__ = 'jobs'

    id = Column(Integer, primary_key=True)
    segment_id = Column(Integer, ForeignKey('segments.id'))
    jobcol1 = Column(...)
    jobcol2 = Column(...)

    paths = relationship('Path', uselist=True)

    def copy(self):
        new = Job()
        new.jobcol1 = self.jobcol1
        new.jobcol2 = self.jobcol2
        for path in self.paths:
            new.paths.append(path.copy())
        return new


class Path(Model):
    __tablename__ = 'paths'

    id = Column(Integer, primary_key=True)
    job_id = Column(Integer, ForeignKey('jobs.id'))
    pthcol1 = Column(...)
    pthcol2 = Column(...)

    bboxs = relationship('BBox', uselist=True)

    def copy(self):
        new = Path()
        new.pthcol1 = self.pthcol1
        new.pthcol2 = self.pthcol2
        for bbox in self.bboxs:
            new.bboxs.append(bbox.copy())
        return new


class BBox(Model):
    __tablename__ = 'bboxs'

    id = Column(Integer, primary_key=True)
    path_id = Column(Integer, ForeignKey('paths.id'))
    boxcol1 = Column(...)
    boxcol2 = Column(...)

    def copy(self):
        new = BBox()
        new.boxcol1 = self.boxcol1
        new.boxcol2 = self.boxcol2
        return new

Each model is responsible for copying its own columns and calling the copy method of its direct relationships. This way, videos don't need to be aware of all the deeper relationships and you can do something like this:

video_copy = existing_video.copy()
session.add(video_copy)
session.commit()

In my situation I also had many-to-many relations (as secondary tables and AssociationObjects). If you wanted to add other types of relations it wouldn't be too complicated.