I am trying to create Class Table Inheritance with a composite key on the base table. I am trying to create sometime similar to this answer. However, the tables I am creating are EntityWithImages
, Experiment
, Sample
, and Images
; my code so far is,
class EntityWithImages(db.Model):
__tablename__ = "entity_images"
id = db.Column(db.Integer, primary_key=True)
entity_type = db.Column(db.String(32), nullable=False)
__mapper_args__ = {
"polymorphic_on": entity_type,
"polymorphic_identity": "entity_with_images",
}
class Experiment(TimeStampAuditMixin, EntityWithImages):
__tablename__ = "experiment"
# id = db.Column(db.Integer, primary_key=True)
id = db.Column(None, db.ForeignKey('entity_images.id'), primary_key=True)
experiment_name = db.Column(db.Text, unique=True)
samples = db.relationship("Sample", back_populates="experiment", foreign_keys="Sample.id")
__mapper_args__ = {
"polymorphic_identity": "experiment",
}
def __str__(self):
return self.experiment_name
class Sample(TimeStampAuditMixin, EntityWithImages):
__tablename__ = "sample"
# id = db.Column(db.Integer, primary_key=True)
id = db.Column(None, db.ForeignKey('entity_images.id'), primary_key=True)
sample_name = db.Column(db.Text, unique=True)
experiment_id = db.Column(db.ForeignKey("experiment.id", ondelete="CASCADE"))
experiment = db.relationship("Experiment", back_populates="samples", foreign_keys=experiment_id)
id: int
sample_name: str
s3_uri: str
experiment_id: int
experiment: "Experiment"
__mapper_args__ = {
"polymorphic_identity": "sample",
}
def __str__(self):
return self.sample_name
class ImageSource(db.Model):
__tablename__ = "image_source"
id = db.Column(db.Integer, primary_key=True)
entity_images_id = db.Column(db.ForeignKey("entity_images.id", ondelete="CASCADE"))
I am not clear on how to detail the value for the entity_type
in the corresponding Experiment
and Sample
tables, i.e. in the Experiment
table I want the default entity_type
to be experiment
.
I am currently using SQLAlchemy==1.4.46