Multiple join paths in SQLAlchemy

Question

I try to test the code example on SQLAlchemy documentation about handling multiple join paths. However after I create a customer object, both relationship attributes are None. I wonder how to properly handle multiple join paths? Do I need to create a relationship in Address class too? When do I need to use back_populates?

Handling Multiple Join Paths

from sqlalchemy import create_engine
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

engine = create_engine('sqlite:///Testing.db')

Base.metadata.create_all(engine)

a1 = Address(street="a street", city="a city", state="A", zip="12345")
a2 = Address(street="b street", city="b city", state="B", zip="1233")
c1 = Customer(name="Jack")
print(c1.billing_address)

Ilja Everilä · Accepted Answer · 2018-03-08 07:34:51Z

1

The relationships in Address are not required, if you don't need them. back_populates= is for explicitly linking 2 relationships together, or as the docs put it, "establish “bidirectional” behavior between each other".

The reason why your customer and addresses are not linked is that you never link them. Pass the addresses to the customer during construction or set them afterwards:

c1 = Customer(name="Jack", billing_address=a1)
c1.shipping_address = a2

Now when you add c1 to a session and commit, SQLAlchemy will handle inserting a1, a2, and c1 in the correct order so that it can fill in the foreign key attributes of c1. This happens because by default a relationship() has the save-update cascade enabled, which places associated objects to the session as well.

edited Mar 8, 2018 at 7:34

answered Mar 8, 2018 at 6:26

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user5574376 Over a year ago

Can we assign relationship like that? when I do that, I got an error: "sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type. [SQL: 'INSERT INTO customer (name, billing_address_id, shipping_address_id) VALUES (?, ?, ?)']".

Ilja Everilä Over a year ago

You're assigning the to-be-related object to the foreign key attribute directly, aren't you? Use the relationship attribute.

user5574376 Over a year ago

Oh, sorry, my bad, did not notice that. Thanks!

Gabriel Over a year ago

I have an issue pretty similiar to this, i've been able to handle the first half of the issue using the exact same method for multiple joins paths. However how do i cater for a relationship from address back to customer. As of now i can only transverse in one direction (customer to address), but not the other way round (address to customer).

Collectives™ on Stack Overflow

Multiple join paths in SQLAlchemy

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related