Thank you Mike! I'm relieved I hadn't missed any straight forward obvious solutions without overriding those methods.
I ended up overriding the name_for_scalar_relationship and name_for_collection_relationship so that each relationship gets a unique name. Here's what I did and now it works like I wanted. def name_for_scalars(base, local_cls, referred_cls, constraint): if local_cls.__name__ == 'orders' and referred_cls.__name__ == 'address': if constraint.name == 'orders_billingaddr_id_fkey': return 'billingaddr' elif constraint.name == 'orders_shippingaddr_id_fkey': return 'shippingaddr' return referred_cls.__name__.lower() def name_for_collections(base, local_cls, referred_cls, constraint): if local_cls.__name__ == 'address' and referred_cls.__name__ == 'orders': if constraint.name == 'orders_billingaddr_id_fkey': return 'orders_billed' elif constraint.name == 'orders_shippingaddr_id_fkey': return 'orders_shipped' return referred_cls.__name__.lower() + "_collection" Base.prepare( autoload_with=engine, schema='shop', name_for_scalar_relationship=name_for_scalars, name_for_collection_relationship=name_for_collections ) Order = Base.classes.orders Address = Base.classes.address -Lauri On Thu, Apr 1, 2021 at 4:02 PM Mike Bayer <mike...@zzzcomputing.com> wrote: > I've just adjusted the logic for this warning for the upcoming 1.4.5 > release. A description of the warning is here: > https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y > > it looks like automap is generating relationships here that are > conflicting and setting up your own relationship on the class is not enough > for automap to know to skip these. you can implement > generate_relationship: > https://docs.sqlalchemy.org/en/14/orm/extensions/automap.html?highlight=automap#sqlalchemy.ext.automap.generate_relationship > to override what it's doing. Looking at the source it seems if you have > this return None, that will skip that relationship. that seems to not be > documented, but I would do that, provide that function and have it return > None. > > Another option is to use a simpler approach like just reflecting the > metadata directly and mapping to that. There is an older and simpler > extension called DeferredReflection: > https://docs.sqlalchemy.org/en/14/orm/extensions/declarative/index.html?highlight=deferredreflection#sqlalchemy.ext.declarative.DeferredReflection > which just does table reflection and that's it. > > On Thu, Apr 1, 2021, at 4:29 AM, Lauri Kajan wrote: > > Hi all! > > I try to automap my existing database to classes. Between two tables I > have multiple join paths and I have trouble managing those properly. > > Here's a sample schema from my database: > > CREATE SCHEMA shop; > CREATE TABLE shop.address ( > id SERIAL PRIMARY KEY, > name text, > address text > ) > CREATE TABLE shop.orders ( > id SERIAL PRIMARY KEY, > items text, > billingaddr_id integer REFERENCES address, > shippingaddr_id integer REFERENCES address > ); > > I have declared relationships for those foreign keys as follows: > > from sqlalchemy import create_enginefrom sqlalchemy.orm import relationship, > Sessionfrom sqlalchemy.ext.automap import automap_base > > engine = create_engine( > "postgresql://postgres:postgres@localhost:5432/postgres", > future=True > ) > Base = automap_base() > > class Order(Base): > __tablename__ = 'orders' > __table_args__ = {"schema": "shop"} > billingaddr = relationship('address', > foreign_keys="Order.billingaddr_id", backref="orders_billed") > shippingaddr = relationship('address', > foreign_keys="Order.shippingaddr_id", backref="orders_shipped") > > > Base.prepare(engine, schema='shop', reflect=True) > > Address = Base.classes.address > > Now when creating a new Address object: > > jack = Address(name='Jack', address='57815 Cheryl Unions') > > I get a warning: "SAWarning: relationship 'Order.address' will copy > column address.id to column orders.shippingaddr_id, which conflicts with > relationship(s): 'address.orders_shipped' (copies address.id to > orders.shippingaddr_id), 'Order.shippingaddr' (copies address.id to > orders.shippingaddr_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. The 'overlaps' parameter may be used to remove this > warning." > > How should this be solved? > > That address relationship is the one automap creates automatically and is now > messing with me. I don't actually need that anymore since I have created > relationships by myself. Can I somehow prevent automap from creating it by > default or can I delete created unnecessary relationships? I have tried to > set address = None in the class declaration but it didn't work. address > relationship is still created. > > > > Thanks, > > > Lauri > > > -- > 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/CAKWoFMKgPMvdrUd8DCNU99DMp3tLgACie5cpXsqMV_otVuAoag%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAKWoFMKgPMvdrUd8DCNU99DMp3tLgACie5cpXsqMV_otVuAoag%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/a3750285-e707-4d64-8a19-5834876bc094%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/a3750285-e707-4d64-8a19-5834876bc094%40www.fastmail.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/CAKWoFMJu-v5vUTwLtOQBLzaiv%2BA0-BRQG954YEXz7oa8hN9J%3Dg%40mail.gmail.com.