当具有描述的所有关系的成熟的多对多时，删除两个主要对象之一会崩溃。
描述
Car (.car_ownerships) (.car) 汽车保有量 (.person) (.car_ownerships) Person
Car (.people) (.cars) Person
Problem
当删除一个Car or a PersonSA首先删除关联对象汽车保有量（由于与secondary参数），然后尝试将同一关联对象中的外键更新为 NULL，因此崩溃。
我应该如何解决这个问题？我有点困惑地发现这个问题没有在文档中得到解决，也没有在我可以在网上找到的任何地方得到解决，因为我认为这种模式很常见：-/。我缺少什么？
我知道我可以拥有passive_deletes打开直通关系，但我想保留删除语句，只是为了防止更新发生或（使其在之前发生）。
Edit： 实际上，passive_deletes如果在会话中加载依赖对象，则不能解决问题，如下所示DELETE仍将发表声明。一个解决方案是使用viewonly=True，但随后我不仅失去了删除，还失去了关联对象的自动创建。我还发现viewonly=True相当危险，因为它让你append()无需坚持！
REPEX
Setup
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()
# ...
诊断
我上面提出的解释得到了引擎给出的 SQL 语句的支持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
EDITS
Using association_proxy
我们可以使用关联代理来尝试实现“通过”关系。
尽管如此，为了.append()直接依赖对象，我们需要为关联对象创建一个构造函数。这个构造函数必须被“破解”才能成为双向的，所以我们可以使用这两个赋值：
my_car.people.append(Person(name='my_son'))
my_husband.cars.append(Car(name='new_shiny_car'))
生成的（经过中间测试的）代码如下，但我对它感觉不太舒服（因为这个hacky构造函数还会有什么问题？）。
EDIT:下面 RazerM 的回答中介绍了使用关联代理的方法。association_proxy()有一个创建者参数，可以减轻对我最终在下面使用的巨大构造函数的需求。
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)
