Python SQLAlchemy upgrade from 1.X to 2.X...add_entity is no more working

Question

Here is my models.py

Base = declarative_base()


class Service(Base):
    __tablename__ = 'Service'

    id = Column('ID', primary_key=True)
    f_Service_type_id = Column('f_ServiceTypeID', Integer)

    sType = relationship('ServiceType',
                         primaryjoin="Service.f_Service_type_id == ServiceType.id",
                         foreign_keys="[Service.f_Service_type_id]")


class ServiceType(Base):
    __tablename__ = 'ServiceType'

    id = Column("ID", Integer, primary_key=True)
    name = Column("Name", String(100))


class Services2Produkt(Base):
    __tablename__ = 'services2produkt'

    service_id = Column("service_id", Integer, primary_key=True)

    service = relationship('Service',
                           primaryjoin="Services2Produkt.service_id == Service.id",
                           foreign_keys="[Services2Produkt.service_id]"
                           )

The following "add_entity" stuff is working with SQLAlchemy 1.x.x.

def get_services_of_product():

    query = session.query(Services2Produkt)

    query = query.add_entity(Service).join("service", "sType")
    return [service for (s2p, service) in query.all()]

When upgrading to SQLAlchemy 2.x I get the following error:

sqlalchemy.exc.ArgumentError: Join target, typically a FROM expression, or ORM relationship attribute expected, got 'service'.

What has changed here? I can't find it in the changelog... And how do I get it fixed?

Answer 1

I think the problem is in the .join("service", "sType") call rather than add_entity.

Joining on relationship names was deprecated in v1.4 and removed in v2.0. From the v1.4 docs for Query.join (see the "Legacy Features of Query.join()" section):

The Query.join() method currently supports several usage patterns and arguments that are considered to be legacy as of SQLAlchemy 1.3. A deprecation path will follow in the 1.4 series for the following features:

• Joining on relationship names rather than attributes:

session.query(User).join("addresses")

Why it’s legacy: the string name does not provide enough context for Query.join() to always know what is desired, notably in that there is no indication of what the left side of the join should be. This gives rise to flags like from_joinpoint as well as the ability to place several join clauses in a single Query.join() call which don’t solve the problem fully while also adding new calling styles that are unnecessary and expensive to accommodate internally.

You probably want to do something like this:

 query = query.add_entity(Service).join(Services2Produkt.service).join(Service.sType)