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 <[email protected]> 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 [email protected].
> 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAKWoFMJu-v5vUTwLtOQBLzaiv%2BA0-BRQG954YEXz7oa8hN9J%3Dg%40mail.gmail.com.
