I have a model in SQLAlchemy which defines a many-to-many relationship using an association table (automap is being used here because I'm using an existing database):
from sqlalchemy import (Column, Table, MetaData, Integer, Text, LargeBinary,
ForeignKey, Float, Boolean, Index)
from sqlalchemy.ext.automap import automap_base, AutomapBase
from sqlalchemy.orm import Session, deferred, relationship
Base: AutomapBase = automap_base()
class VariantAssociation(Base):
__tablename__ = "sample_variant_association"
vid = Column(Integer, ForeignKey("variants.variant_id"),
primary_key=True)
sid = Column(Integer, ForeignKey("samples.sample_id"),
primary_key=True)
vdepth = Column(Integer)
valt_depth = Column(Integer)
gt = Column(Text)
gt_type = Column(Integer)
fraction = Column(Float)
variant = relationship("Variant", back_populates="samples")
sample = relationship("Sample", back_populates="variants")
__table_args__ = (Index('ix_sample_variant_association_valt_depth',
"valt_depth"),
Index('ix_sample_variant_association_vdepth',
"vdepth"),
Index('ix_sample_variant_association_vid', 'vid'),
Index('ix_sample_variant_association_sid', 'sid'),
Index('ix_sample_variant_association_fraction',
'fraction')
)
class Variant(Base):
__tablename__ = "variants"
variant_id = Column(Integer, primary_key=True)
info = deferred(Column(LargeBinary))
samples = relationship("VariantAssociation",
back_populates="variant")
class Sample(Base):
__tablename__ = "samples"
sample_id = Column(Integer, primary_key=True, index=True)
name = Column(Text, index=True)
variants = relationship("VariantAssociation",
back_populates="sample")
class SampleGenotypeCount(Base):
__tablename__ = 'sample_genotype_counts'
sample_id = Column(Integer, primary_key=True)
num_hom_ref = Column(Integer)
num_het = Column(Integer)
num_hom_alt = Column(Integer)
num_unknown = Column(Integer)
class DataMigration(Base):
__tablename__ = "datamigration"
done = Column(Boolean, primary_key=True)
On querying, this eventually generates this warning:
Query:
query = session.query(Variant).join(
Variant.samples).join(Sample)
Warning:
/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py:3441: SAWarning:
relationship 'Variant.variantassociation_collection' will copy column variants.variant_id to
column sample_variant_association.vid, which conflicts with relationship(s): 'Variant.samples'
(copies variants.variant_id to sample_variant_association.vid). If this is not the intention,
consider if these relationships should be linked with back_populates, or if viewonly=True
should be applied to one or more if they are read-only. For the less common case that foreign
key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate
the columns that should be written towards. The 'overlaps' parameter may be used to remove
this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)
I've been looking through SO and the SQLAlchemy documentation but I was unable to find what could cause this issue since (in my view) the back_populates parameters are in the right places.
Where would the error in the model be? SQLAlchemy 1.3.23 did not generate one, FTR.