How to delete a one-to-one relationship with SQLAlchemy

Question

I would like to create a nullable, self-referencing relationship which can be deleted using SQLAlchemy. An example model is as follows (note, using Flask-SQLAlchemy):

class Person(db.Model):

    __tablename__ = 'person'

    id          = db.Column(db.Integer, primary_key=True)
    partner_id  = db.Column(db.Integer, db.ForeignKey('person.id'), nullable=True)
    partner     = db.relationship('Person', uselist=False)

So think of this as a table of cops who have only a single partner, but that partner may turn out to have been in the mafia all along, so they lose their partner for a while. A cop without a partner is fine, at least in database terms - but I assume over the course of the show their partnerless status means a lot of property damage.

Needless to say, this question: sqlalchemy: one-to-one relationship with declarative discusses how to set up this relationship. The question is how do you remove the relationship? Normally with a different foreign key you'd do this as follows:

joe.partner.remove(larry)

Where joe and larry are both Person objects. However, via the uselist argument, joe.partner is now actually a Person with no remove method.

just to clarify, `larry` got caught in the cross fire when the family had a civil war, so he needs to be out of the database. Will that delete the larry record? — bbengfort, May 07 '15 at 21:36
think you need a `cascade="all, delete, delete-orphan"` on the relationship. can't test it at the moment though, me computer is fscked — reptilicus, May 07 '15 at 21:46

score 2 · Answer 1 · answered Feb 01 '22 at 18:16

How to delete one-to-one relationships is buried away in the SQLAlchemy documentation under the explanation of Cascades: https://docs.sqlalchemy.org/en/14/orm/cascades.html#notes-on-delete-deleting-objects-referenced-from-collections-and-scalar-relationships

The delete-orphan cascade can also be applied to a many-to-one or one-to-one relationship, so that when an object is de-associated from its parent, it is also automatically marked for deletion. Using delete-orphan cascade on a many-to-one or one-to-one requires an additional flag relationship.single_parent which invokes an assertion that this related object is not to shared with any other parent simultaneously

So you'll want to set up your one-to-one relationship like so:

partner = db.relationship(
    'Person',
    cascade='all, delete-orphan',
    uselist=False,
    single_parent=True,
)

Then, deleting a Person's partner is just a matter of setting it to None:

some_person.partner = None
session.flush()  # will delete the partner object

How to delete a one-to-one relationship with SQLAlchemy

1 Answers1