How to handle multiple relationships between tables properly with automap?

[Lauri Kajan]

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_engine
from sqlalchemy.orm import relationship, Session
from 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

[Mike Bayer]

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.

--

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/CAKWoFMKgPMvdrUd8DCNU99DMp3tLgACie5cpXsqMV_otVuAoag%40mail.gmail.com.

[Lauri Kajan]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a3750285-e707-4d64-8a19-5834876bc094%40www.fastmail.com.