SQLAlchemy 1.4 warnings on overlapping relationships with a many-to-many relationship with association table

Viewed 1140

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.

1 Answers

In order to set your own relationship names, you need to prevent Automap from generating relationships by iteself. You can achieve this by setting 'generate_relationship' to a function that returns None.

def generate_relationships(base, direction, return_fn, attrname, local_cls, referred_cls, **kw):
    return None

Base.prepare(generate_relationship=generate_relationships)
Related