hey there. The warnings go away entirely by making Parent.children viewonly=True, which for this type of mapping is recommended:
class Parent(Base): __tablename__ = "left" id = Column(Integer, primary_key=True) children = relationship( "Child", secondary=Association.__table__, backref="parents", viewonly=True ) you wouldn't want to append new records to Parent.children because that would create invalid Association rows (missing extra_data). The warning box at the end of https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object discusses this situation and the desirability of making the relationship which includes "secondary" as viewonly=True. hope this helps On Wed, Mar 9, 2022, at 8:09 PM, Michael Merickel wrote: > Sorry for the rambling, it's been difficult for me to figure out what > question to ask because I'm so confused. Below is the minimum viable example > that produces no warnings with respect to the overlaps flags and I cannot > explain hardly any of them. For example, why does Child.parents require > "child_links,parent,child"? 3 values that seem to be somewhat unrelated and > are at the very least definitely on different models? > > class Association(Base): > __tablename__ = 'association' > left_id = Column(ForeignKey('left.id'), primary_key=True) > right_id = Column(ForeignKey('right.id'), primary_key=True) > extra_data = Column(String(50)) > > parent = relationship('Parent', back_populates='child_links') > child = relationship('Child', back_populates='parent_links') > > class Parent(Base): > __tablename__ = 'left' > id = Column(Integer, primary_key=True) > > children = relationship( > 'Child', > secondary=Association.__table__, > back_populates='parents', > overlaps='child,parent', > ) > child_links = relationship( > 'Association', > back_populates='parent', > overlaps='children', > ) > > class Child(Base): > __tablename__ = 'right' > id = Column(Integer, primary_key=True) > > parents = relationship( > 'Parent', > secondary=Association.__table__, > back_populates='children', > overlaps='child_links,parent,child', > ) > parent_links = relationship( > 'Association', > back_populates='child', > overlaps='children,parents', > ) > > > On Wed, Mar 9, 2022 at 4:50 PM Michael Merickel <mmeri...@gmail.com> wrote: >> I think ultimately I want the overlaps config but reading through >> https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y >> it doesn't make any sense to me what the values in the overlaps= argument >> are referring to. For example in last snippet that was simpler, what is >> overlaps='parent' referring to? Neither the Parent object, nor the Child >> object has something named "parent" so other than blinding trusting the >> warning I'm unclear how to see what the mapper is building that conflicts >> here. >> >> On Wed, Mar 9, 2022 at 4:33 PM Michael Merickel <mmeri...@gmail.com> wrote: >>> It's probably worth noting I can narrow it down to a single warning with >>> the following snippet and it's still unclear to me how to resolve this: >>> >>> class Association(Base): >>> __tablename__ = 'association' >>> left_id = Column(ForeignKey('left.id'), primary_key=True) >>> right_id = Column(ForeignKey('right.id'), primary_key=True) >>> extra_data = Column(String(50)) >>> >>> parent = relationship('Parent') >>> >>> class Parent(Base): >>> __tablename__ = 'left' >>> id = Column(Integer, primary_key=True) >>> children = relationship('Child', secondary=Association.__table__) >>> >>> class Child(Base): >>> __tablename__ = 'right' >>> id = Column(Integer, primary_key=True) >>> >>>> foo.py:24: SAWarning: relationship 'Parent.children' will copy column >>>> left.id to column association.left_id, which conflicts with >>>> relationship(s): 'Association.parent' (copies left.id to >>>> association.left_id). 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. To silence this warning, add the parameter >>>> 'overlaps="parent"' to the 'Parent.children' relationship. (Background on >>>> this error at: https://sqlalche.me/e/14/qzyx) >>> >>> On Wed, Mar 9, 2022 at 4:31 PM Michael Merickel <mmeri...@gmail.com> wrote: >>>> I have looked at the couple examples in the docs (many-to-many, and >>>> association table) and have noticed that my codebase has a slightly >>>> different pattern which is causing warnings when upgrading to 1.4. I'm >>>> trying to figure out the best pattern to accomplish what I've been doing >>>> which doesn't match the docs exactly. >>>> >>>> In the below example you can see that there are backrefs on all of the >>>> links, and that there are backrefs from the link table to the related >>>> objects, as well as a secondary link from Parent to Child via >>>> Parent.children and Child.parents. >>>> >>>> There seem to be several options and I'm struggling to figure out what the >>>> solution should be to maintain the behavior with all of the following >>>> relationships working: >>>> >>>> - Parent.children >>>> - Parent.child_links >>>> - Child.parents >>>> - Child.parent_links >>>> - Association.parent >>>> - Association.child >>>> >>>> Code and warnings are below: >>>> >>>> from sqlalchemy import Column, ForeignKey, String, Integer >>>> from sqlalchemy.orm import configure_mappers, relationship >>>> from sqlalchemy.ext.declarative import declarative_base >>>> >>>> Base = declarative_base() >>>> >>>> class Association(Base): >>>> __tablename__ = 'association' >>>> left_id = Column(ForeignKey('left.id'), primary_key=True) >>>> right_id = Column(ForeignKey('right.id'), primary_key=True) >>>> extra_data = Column(String(50)) >>>> >>>> parent = relationship('Parent', backref='child_links') >>>> child = relationship('Child', backref='parent_links') >>>> >>>> class Parent(Base): >>>> __tablename__ = 'left' >>>> id = Column(Integer, primary_key=True) >>>> children = relationship('Child', secondary=Association.__table__, >>>> backref='parents') >>>> >>>> class Child(Base): >>>> __tablename__ = 'right' >>>> id = Column(Integer, primary_key=True) >>>> >>>> configure_mappers() >>>> >>>>> foo.py:25: SAWarning: relationship 'Child.parents' will copy column >>>>> right.id to column association.right_id, which conflicts with >>>>> relationship(s): 'Association.child' (copies right.id to >>>>> association.right_id), 'Child.parent_links' (copies right.id to >>>>> association.right_id). 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. To silence this warning, add the parameter >>>>> 'overlaps="child,parent_links"' to the 'Child.parents' relationship. >>>>> (Background on this error at: https://sqlalche.me/e/14/qzyx) >>>>> configure_mappers() >>>>> foo.py:25: SAWarning: relationship 'Child.parents' will copy column >>>>> left.id to column association.left_id, which conflicts with >>>>> relationship(s): 'Association.parent' (copies left.id to >>>>> association.left_id), 'Parent.child_links' (copies left.id to >>>>> association.left_id). 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. To silence this warning, add the parameter >>>>> 'overlaps="child_links,parent"' to the 'Child.parents' relationship. >>>>> (Background on this error at: https://sqlalche.me/e/14/qzyx) >>>>> configure_mappers() >>>>> foo.py:25: SAWarning: relationship 'Parent.children' will copy column >>>>> left.id to column association.left_id, which conflicts with >>>>> relationship(s): 'Association.parent' (copies left.id to >>>>> association.left_id), 'Parent.child_links' (copies left.id to >>>>> association.left_id). 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. To silence this warning, add the parameter >>>>> 'overlaps="child_links,parent"' to the 'Parent.children' relationship. >>>>> (Background on this error at: https://sqlalche.me/e/14/qzyx) >>>>> configure_mappers() >>>>> foo.py:25: SAWarning: relationship 'Parent.children' will copy column >>>>> right.id to column association.right_id, which conflicts with >>>>> relationship(s): 'Association.child' (copies right.id to >>>>> association.right_id), 'Child.parent_links' (copies right.id to >>>>> association.right_id). 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. To silence this warning, add the parameter >>>>> 'overlaps="child,parent_links"' to the 'Parent.children' relationship. >>>>> (Background on this error at: https://sqlalche.me/e/14/qzyx) >>>>> configure_mappers() >>>> >>>> Thanks! >>>> >>>> -- >>>> >>>> Michael >>> >>> >>> -- >>> >>> Michael >> >> >> -- >> >> Michael > > > -- > > Michael > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAKdhhwEG%2BE8kY2mMW1R%3D-Mi%3DnevYyR%2BAfftrqEzFyVsz%2BG23Nw%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAKdhhwEG%2BE8kY2mMW1R%3D-Mi%3DnevYyR%2BAfftrqEzFyVsz%2BG23Nw%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/df6b41d7-4cf3-4d65-bbce-ef2b4a3b7fb8%40www.fastmail.com.