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 <[email protected]>
> 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 <[email protected]>
>> 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 <[email protected]>
>> 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 <[email protected]>
>> 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 [email protected].
>> 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 [email protected].
>>
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/004bd530-e533-4364-b748-4544844e52bcn%40googlegroups.com.
