I'm sorry you're getting bit by this messaging - but also glad that I'm not the only one. This got me a while ago too.
SqlAlchemy just uses a bare field name when emitting the warning and accepting the `overlaps` arguments. In more complex models with 3+ tables that have standardize relationship names, it's hard to tell what caused the issue and fixing one relationship can unknowingly affect others. There is a related ticket/PR. I'm not sure if you can pull it against the current main branch, but you can do a manual patch of the warnings code locally to make the output better: https://github.com/sqlalchemy/sqlalchemy/issues/7309 - Make the overlaps arguments use fully-qualified names There's also a related ticket to improve the errors when not calling `configure_mappers` as above: https://github.com/sqlalchemy/sqlalchemy/issues/7305 On Thursday, March 10, 2022 at 12:27:33 PM UTC-5 Michael Merickel wrote: > Thank you Mike. Really appreciate you unpacking my rambling. This works > for me. I found a few spots in our codebase where we were relying on > append() working because it really was a simple link table but I rewrote > them to just create the link manually and add it to the session which also > causes them to appear in the lists. > > On Thu, Mar 10, 2022 at 9:17 AM Mike Bayer <mik...@zzzcomputing.com> > wrote: > >> 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 <mmer...@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 <mmer...@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 <mmer...@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+...@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+...@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 >> >> <https://groups.google.com/d/msgid/sqlalchemy/df6b41d7-4cf3-4d65-bbce-ef2b4a3b7fb8%40www.fastmail.com?utm_medium=email&utm_source=footer> >> . >> > > > -- > > - 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/004bd530-e533-4364-b748-4544844e52bcn%40googlegroups.com.