When having a fully fledged many-to-many with all relations described, the deletion of one of the two main objects crashes.
Car (.car_ownerships) <-> (.car) CarOwnership (.person) <-> (.car_ownerships) Person
Car (.people) <-----------------> (.cars) Person
When deleting a Car or a Person
SA first deletes the association object CarOwnership (because of the 'through' relationship with the secondary argument) and then tries to update the foreign keys to NULL in the same association objects, hence crashing.
How should I solve this? I'm a little perplexed to see that this is not addressed in the docs nor anywhere I could find online, since I thought that this pattern was quite common :-/. What am I missing?
I know I could have the passive_deletes switch on for the through relationship, but I'd like to keep the delete statement, just to prevent the update from happening or (make it happen before).
Edit: Actually, passive_deletes doesn't solve the problem if the dependent objects are loaded in session, as DELETE statement will still be issued. A solution is to use viewonly=True, but then I lose not only deletion but automatic creation of association objects. Also I find viewonly=True to be quite dangerous, because it lets you append() without persisting!
from sqlalchemy import create_engine, Table, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker
engine = create_engine('sqlite:///:memory:', echo = False)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()
class Person(Base):
__tablename__ = 'persons'
id = Column(Integer(), primary_key=True)
name = Column(String(255))
cars = relationship('Car', secondary='car_ownerships', backref='people')
def __repr__(self):
return '<Person {} [{}]>'.format(self.name, self.id)
class Car(Base):
__tablename__ = 'cars'
id = Column(Integer(), primary_key=True)
name = Column(String(255))
def __repr__(self):
return '<Car {} [{}]>'.format(self.name, self.id)
class CarOwnership(Base):
__tablename__ = 'car_ownerships'
id = Column(Integer(), primary_key=True)
type = Column(String(255))
car_id = Column(Integer(), ForeignKey(Car.id))
car = relationship('Car', backref='car_ownerships')
person_id = Column(Integer(), ForeignKey(Person.id))
person = relationship('Person', backref='car_ownerships')
def __repr__(self):
return 'Ownership [{}]: {} <<-{}->> {}'.format(self.id, self.car, self.type, self.person)
Base.metadata.create_all(engine)
antoine = Person(name='Antoine')
rob = Person(name='Rob')
car1 = Car(name="Honda Civic")
car2 = Car(name='Renault Espace')
CarOwnership(person=antoine, car=car1, type = "secondary")
CarOwnership(person=antoine, car=car2, type = "primary")
CarOwnership(person=rob, car=car1, type = "primary")
session.add(antoine)
session.commit()
session.query(CarOwnership).all()
print('#### DELETING')
session.delete(car1)
print('#### COMMITING')
session.commit()
# StaleDataError Traceback (most recent call last)
# <ipython-input-6-80498b2f20a3> in <module>()
# 1 session.delete(car1)
# ----> 2 session.commit()
# ...
The explanation I propose above is backed by the SQL statements given by the engine with echo=True:
#### DELETING
#### COMMITING
2016-07-07 16:55:28,893 INFO sqlalchemy.engine.base.Engine SELECT persons.id AS persons_id, persons.name AS persons_name
FROM persons, car_ownerships
WHERE ? = car_ownerships.car_id AND persons.id = car_ownerships.person_id
2016-07-07 16:55:28,894 INFO sqlalchemy.engine.base.Engine (1,)
2016-07-07 16:55:28,895 INFO sqlalchemy.engine.base.Engine SELECT car_ownerships.id AS car_ownerships_id, car_ownerships.type AS car_ownerships_type, car_ownerships.car_id AS car_ownerships_car_id, car_ownerships.person_id AS car_ownerships_person_id
FROM car_ownerships
WHERE ? = car_ownerships.car_id
2016-07-07 16:55:28,896 INFO sqlalchemy.engine.base.Engine (1,)
2016-07-07 16:55:28,898 INFO sqlalchemy.engine.base.Engine DELETE FROM car_ownerships WHERE car_ownerships.car_id = ? AND car_ownerships.person_id = ?
2016-07-07 16:55:28,898 INFO sqlalchemy.engine.base.Engine ((1, 1), (1, 2))
2016-07-07 16:55:28,900 INFO sqlalchemy.engine.base.Engine UPDATE car_ownerships SET car_id=? WHERE car_ownerships.id = ?
2016-07-07 16:55:28,900 INFO sqlalchemy.engine.base.Engine ((None, 1), (None, 2))
2016-07-07 16:55:28,901 INFO sqlalchemy.engine.base.Engine ROLLBACK
association_proxyWe can use association proxies to try and materialize the 'through' relationship.
Nevertheless, in order to .append() a dependent object directly, we need to create a constructor for the association object. This constructor must be 'hacked' to be made bi-directional, so we can use both assignments:
my_car.people.append(Person(name='my_son'))
my_husband.cars.append(Car(name='new_shiny_car'))
The resulting (midly tested) code is below, but I don't feel quite comfortable with it (what else is going to break because of this hacky constructor?).
EDIT: The way to go with association proxies is presented in RazerM's answer below. association_proxy() has a creator argument that alleviates the need for the monstrous constructor I ended up using below.
class Person(Base):
__tablename__ = 'persons'
id = Column(Integer(), primary_key=True)
name = Column(String(255))
cars = association_proxy('car_ownerships', 'car')
def __repr__(self):
return '<Person {} [{}]>'.format(self.name, self.id)
class Car(Base):
__tablename__ = 'cars'
id = Column(Integer(), primary_key=True)
name = Column(String(255))
people = association_proxy('car_ownerships', 'person')
def __repr__(self):
return '<Car {} [{}]>'.format(self.name, self.id)
class CarOwnership(Base):
__tablename__ = 'car_ownerships'
id = Column(Integer(), primary_key=True)
type = Column(String(255))
car_id = Column(Integer(), ForeignKey(Car.id))
car = relationship('Car', backref='car_ownerships')
person_id = Column(Integer(), ForeignKey(Person.id))
person = relationship('Person', backref='car_ownerships')
def __init__(self, car=None, person=None, type='secondary'):
if isinstance(car, Person):
car, person = person, car
self.car = car
self.person = person
self.type = type
def __repr__(self):
return 'Ownership [{}]: {} <<-{}->> {}'.format(self.id, self.car, self.type, self.person)
The cleanest solution is below and doesn't involve association proxies. It is the missing recipe for fully-fledged many-through-many relations.
Here, we edit the direct relations that go from the dependent objects Car and Person to the association object CarOwnership, in order to prevent these relationships to issue an UPDATE after the association object has been deleted. To this end, we use the passive_deletes='all' flag.
The resulting interaction is:
# Changing Ownership type:
my_car.car_ownerships[0].type = 'primary'
# Creating an ownership between a car and a person directly:
CarOwnership(car=my_car, person=my_husband, type='primary')
ability to access and edit dependent objects directly:
# Get all cars from a person:
[print(c) for c in my_husband.cars]
# Update the name of one of my cars:
me.cars[0].name = me.cars[0].name + ' Cabriolet'
automatic creation and deletion of the association object when creation or deletion of a dependent objects
# Create a new owner and assign it to a car:
my_car.people.append(Person('my_husband'))
session.add(my_car)
session.commit() # Creates the necessary CarOwnership
# Delete a car:
session.delete(my_car)
session.commit() # Deletes all the related CarOwnership objects
class Person(Base):
__tablename__ = 'persons'
id = Column(Integer(), primary_key=True)
name = Column(String(255))
cars = relationship('Car', secondary='car_ownerships', backref='people')
def __repr__(self):
return '<Person {} [{}]>'.format(self.name, self.id)
class Car(Base):
__tablename__ = 'cars'
id = Column(Integer(), primary_key=True)
name = Column(String(255))
def __repr__(self):
return '<Car {} [{}]>'.format(self.name, self.id)
class CarOwnership(Base):
__tablename__ = 'car_ownerships'
id = Column(Integer(), primary_key=True)
type = Column(String(255))
car_id = Column(Integer(), ForeignKey(Car.id))
car = relationship('Car', backref=backref('car_ownerships', passive_deletes='all'))
person_id = Column(Integer(), ForeignKey(Person.id))
person = relationship('Person', backref=backref('car_ownerships', passive_deletes='all'))
def __repr__(self):
return 'Ownership [{}]: {} <<-{}->> {}'.format(self.id, self.car, self.type, self.person)