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?
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.
