多对多关联对象和定义的所有关系在删除时崩溃 [英] Many-to-Many with association object and all relationships defined crashes on delete

问题描述

在具有描述了所有关系的完全一对多的情况下，两个主要对象之一的删除会崩溃.

说明

汽车(.car_ownerships)<->(.car)汽车所有权(.person)<->(.car_ownerships)人

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

问题

删除汽车或人员时 SA首先删除关联对象 CarOwnership (由于与secondary参数具有'through'关系)，然后尝试将相同关联对象中的外键更新为NULL，从而崩溃.

我该如何解决?令我有些困惑的是，在文档中以及我在网上找不到的任何地方都没有解决此问题，因为我认为这种模式很常见:-/.我想念什么?

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

编辑:实际上，如果在会话中加载依赖对象，则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()具有一个创建者参数，该参数减轻了我最终在下面使用的庞杂构造函数的需要.

 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)
 

解决方案

最干净的解决方案如下，并且不涉及关联代理.这是成熟的多对多关系所缺少的秘诀.

在这里，我们编辑从依赖对象 Car 和 Person 到关联对象 CarOwnership 的直接关系，以防止这些关系在删除关联对象后发出UPDATE.为此，我们使用passive_deletes='all'标志.

结果是:

• 从依赖对象中查询和设置关联对象的能力

     # 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')
 

• 直接访问和编辑相关对象的能力:

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

• 在创建或删除从属对象时自动创建和删除关联对象

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

When having a fully fledged many-to-many with all relations described, the deletion of one of the two main objects crashes.

Description

Car (.car_ownerships) <-> (.car) CarOwnership (.person) <-> (.car_ownerships) Person

Car (.people) <-----------------> (.cars) Person

Problem

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!

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)

Filing objects

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

Deleting -> Crashing

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

Diagnostics

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

EDITS

Using association_proxy

We 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:

• ability to query and set the association object from the dependent objects

    # 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
  

Code

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)

这篇关于多对多关联对象和定义的所有关系在删除时崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！