apply viewonly=True to those relationships that you aren't going to be
append()'ing towards.
the warnings are trying to indicate that if you have a certain Xs, and you
append a certain Ys to it, but at the same time, you also create an XY_assoc
object with those same Xs/Ys and also append that to Xs.xy_assoc, youll get a
primary key violation error. because you are inserting the same row twice in
different ways. the ORM doesn't track that.
On Sun, Jun 5, 2022, at 6:20 PM, Sanchay wrote:
> Suppose I have the standard M:N association table pattern, like so
>
> class Xs(ModelBase):
> id = Column(Integer, primary_key=True)
>
> ys = relationship('Ys', secondary='test.xy_assoc', back_populates='xs')
>
> class Ys(ModelBase):
> id = Column(Integer, primary_key=True)
>
> xs = relationship('Xs', secondary='test.xy_assoc', back_populates='ys')
>
> class XY_assoc(ModelBase):
> x_id = Column(Integer, ForeignKey(Xs.id), primary_key=True)
> y_id = Column(Integer, ForeignKey(Ys.id), primary_key=True)
>
> Everything is fine and works. But now I want to add a few extra relationships
> to the association table itself:
>
> class Xs(ModelBase):
> id = Column(Integer, primary_key=True)
>
> ys = relationship('Ys', secondary='test.xy_assoc', back_populates='xs')
> xy_assocs = relationship('XY_assoc', back_populates='x')
>
> class Ys(ModelBase):
> id = Column(Integer, primary_key=True)
>
> xs = relationship('Xs', secondary='test.xy_assoc', back_populates='ys')
> xy_assocs = relationship('XY_assoc', back_populates='y')
>
> class XY_assoc(ModelBase):
> x_id = Column(Integer, ForeignKey(Xs.id), primary_key=True)
> y_id = Column(Integer, ForeignKey(Ys.id), primary_key=True)
>
> x = relationship('Xs', back_populates='xy_assocs')
> y = relationship('Ys', back_populates='xy_assocs')
>
> With this, I get the following SQLA warnings. What is the right way to
> achieve this?
>
> /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2:
> SAWarning: relationship 'Xs.xy_assocs' will copy column xs.id to column
> xy_assoc.x_id, which conflicts with relationship(s): 'Xs.ys' (copies xs.id to
> xy_assoc.x_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="ys"' to the 'Xs.xy_assocs'
> relationship. xs = session.query(Xs).all()
> /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2:
> SAWarning: relationship 'Ys.xs' will copy column xs.id to column
> xy_assoc.x_id, which conflicts with relationship(s): 'Xs.xy_assocs' (copies
> xs.id to xy_assoc.x_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="xy_assocs"' to the 'Ys.xs' relationship. xs =
> session.query(Xs).all()
> /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2:
> SAWarning: relationship 'Ys.xy_assocs' will copy column ys.id to column
> xy_assoc.y_id, which conflicts with relationship(s): 'Xs.ys' (copies ys.id to
> xy_assoc.y_id), 'Ys.xs' (copies ys.id to xy_assoc.y_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="xs,ys"' to the 'Ys.xy_assocs' relationship. xs =
> session.query(Xs).all()
> /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2:
> SAWarning: relationship 'XY_assoc.x' will copy column xs.id to column
> xy_assoc.x_id, which conflicts with relationship(s): 'Xs.ys' (copies xs.id to
> xy_assoc.x_id), 'Ys.xs' (copies xs.id to xy_assoc.x_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="xs,ys"' to the 'XY_assoc.x' relationship. xs =
> session.query(Xs).all()
> /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2:
> SAWarning: relationship 'XY_assoc.y' will copy column ys.id to column
> xy_assoc.y_id, which conflicts with relationship(s): 'Xs.ys' (copies ys.id to
> xy_assoc.y_id), 'Ys.xs' (copies ys.id to xy_assoc.y_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="xs,ys"' to the 'XY_assoc.y' relationship. xs =
> session.query(Xs).all()
>
>
>
> --
> 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/0aed6514-494b-4cb3-a17a-a821c7c3a92fn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/0aed6514-494b-4cb3-a17a-a821c7c3a92fn%40googlegroups.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/2005a946-a155-4bbe-8e80-19aa12271093%40www.fastmail.com.
