How to handle multiple join paths properly with automap?

user_1317366 0
Viewed 309

I try to automap my existing database to classes. Between two tables I have multiple join paths and I have troubles to manage those properly with SQLAlchemy.

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):

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 relationship? I have tried to set address = None in the class declaration but it didn't work. address relationship is still created.

sqlalchemy postgresql python
1 Answers

Answering my own question since I got this solved (Thanks Mike from sqlalchemy mailing list).

By default sqlalchemy 1.14 uses same name for both relationships so the latter overrides the previously created. Naming of those relationships can be overridden passing custom implementations of those naming methods to the automap.base.prepare. I implemented those to give unique name for each relationship. This way I don't need to even use the explicit declarative class style.

from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, sessionmaker, backref
from sqlalchemy.ext.automap import automap_base, generate_relationship

engine = create_engine(
    "postgresql://postgres:postgres@localhost:5432/postgres",
    future=True
)
Base = automap_base()


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
user_13173660
Related