python - 具有关联对象的多对多和定义的所有关系在删除时崩溃

Question

当具有描述了所有关系的完全成熟的多对多时，删除两个主要对象之一会崩溃。

描述

汽车 (.car_ownerships) <-> (.car) CarOwnership (.person ) <-> (.car_ownerships) 人

汽车 (.people) <----------------> (.cars >) 人

问题

删除汽车或人时SA 首先删除关联对象 CarOwnership(因为与 secondary 参数的“直通”关系)，然后尝试将相同关联对象中的外键更新为 NULL，因此崩溃。

我该如何解决？我有点困惑地看到文档中没有解决这个问题，也没有在我可以在网上找到的任何地方解决，因为我认为这种模式很常见:-/。我错过了什么？

我知道我可以为直通关系打开 passive_deletes 开关，但我想保留 delete 语句，只是为了防止更新发生或(使其发生在之前)。

编辑:实际上，如果在 session 中加载依赖对象，passive_deletes 并不能解决问题，因为 DELETE 语句仍然会发布。一个解决方案是使用 viewonly=True，但我不仅失去了删除，而且失去了关联对象的自动创建。我还发现 viewonly=True 非常危险，因为它可以让你 append() 而无需坚持!

REPEX

设置

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()
# ...

诊断

我在上面提出的解释得到了引擎使用 echo=True 给出的 SQL 语句的支持:

#### 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_proxy

我们可以使用关联代理来尝试实现“直通”关系。

然而，为了直接.append()一个依赖对象，我们需要为关联对象创建一个构造函数。这个构造函数必须被“破解”成双向的，所以我们可以使用两个赋值:

my_car.people.append(Person(name='my_son'))
my_husband.cars.append(Car(name='new_shiny_car'))

生成的(经过中间测试的)代码如下，但我对它不太满意(因为这个 hacky 构造函数还有什么会破坏？)。

编辑:关联代理的方式在 RazerM 的下面的回答中介绍。 association_proxy() 有一个 creator 参数，它减轻了我在下面最终使用的巨大构造函数的需要。

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)

Answer 1

您正在使用 Association Object ，因此您需要以不同的方式做事。

我已经改变了这里的关系，仔细看看它们，因为一开始你有点难以理解(至少对我来说是这样!)。

我使用了 back_populates 因为在这种情况下它比 backref 更清晰。多对多关系的双方都必须直接引用 CarOwnership，因为您将使用该对象。这也是您的示例显示的内容；您需要使用它，以便您可以设置 type。

class Person(Base):
    __tablename__ = 'persons'

    id = Column(Integer(), primary_key=True)
    name = Column(String(255))

    cars = relationship('CarOwnership', back_populates='person')

    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 = relationship('CarOwnership', back_populates='car')

    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))
    person_id = Column(Integer(), ForeignKey(Person.id))

    car = relationship('Car', back_populates='people')
    person = relationship('Person', back_populates='cars')

    def __repr__(self):
        return 'Ownership [{}]: {} <<-{}->> {}'.format(self.id, self.car, self.type, self.person)

请注意，删除任一侧后，car_ownerships 行不会被删除，它只会将外键设置为 NULL。如果您想设置自动删除，我可以在我的答案中添加更多内容。

编辑:要直接访问Car和Person对象的集合，需要使用association_proxy，然后类更改为:

from sqlalchemy.ext.associationproxy import association_proxy

class Person(Base):
    __tablename__ = 'persons'

    id = Column(Integer(), primary_key=True)
    name = Column(String(255))

    cars = association_proxy(
        'cars_association', 'car', creator=lambda c: CarOwnership(car=c))

    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(
        'people_association', 'person', creator=lambda p: CarOwnership(person=p))

    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), default='secondary')
    car_id = Column(Integer(), ForeignKey(Car.id))
    person_id = Column(Integer(), ForeignKey(Person.id))

    car = relationship('Car', backref='people_association')
    person = relationship('Person', backref='cars_association')

    def __repr__(self):
        return 'Ownership [{}]: {} <<-{}->> {}'.format(self.id, self.car, self.type, self.person)

编辑:在您的编辑中，您在将其转换为使用backref 时犯了一个错误。您的汽车和人的关联代理不能同时使用“car_ownerships”关系，这就是为什么我有一个名为“people_association”和一个名为“cars_association”的原因。

您拥有的“car_ownerships”关系与名为“car_ownerships”的关联表无关，因此我以不同的方式命名它们。

我修改了上面的代码块。要允许附加工作，您需要将创建者添加到关联代理。我已将 back_populates 更改为 backref，并将默认的 type 添加到 Column 对象而不是构造函数。