Why does SQLAlchemy not update relations after object deletion?

Question

Problem

Within the same session, after deletion of an object, the relation attributes of other objects in the session that included or pointed to this deleted object are not updated. In short:

sesion.add(a, b)
a.parent = b
print(a.parent)  # b
session.delete(b)
print(a.parent)  # b

The complete, reproducible example is below.

Material

I have read at length SQLA doc, including:

• the misleading part on 'deleting from collections'. Misleading becasue it makes you think this lack of update after deletion is restricted to collections. The example below shows it's not.
• the part on the cascades, and especially the 'delete-orphan'. This is not what I am looking for since I do not want all removal of relation to delete the related object.

Potential solution

Individual objects and even single object attributes can be manually set to be reloaded using session.expire(). I thought the ORM was responsible for doing this job :-)

Questions

• Have I missed a simple solution?
• Have I missed a place in the doc where it's explicitly written and explained? (I spent quite some time narrowing down the problem)
• Is there a reason why SQLAlchemy doesn't / cannot mark for update the relations tied to an object that is removed?

Code Example

SQLA 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=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

Model definition

class Country(Base):
    __tablename__ = 'countries'

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

    def __repr__(self):
        return '<Country {} [{}]>'.format(self.name, self.id)


class Capital(Base):
    __tablename__ = 'capitals'

    id = Column(Integer(), primary_key=True)
    name = Column(String(255))
    country_id = Column(Integer(), ForeignKey(Country.id), unique=True)
    country = relationship('Country', backref=backref('capital', uselist=False))

    def __repr__(self):
        return '<Capital {} [{}]>'.format(self.name, self.id)


Base.metadata.create_all(engine)

Tests

# Creating both objects
us = Country(name="USA")
ny = Capital(name="NYC")
session.add(us)
session.add(ny)
session.commit()

print("\n### 1. Creating the relation:")
# Loading relations:
print("## us.capital: ", us.capital)
print("## ny.country: ", ny.country)
# Creating the relation:
us.capital = ny
# Checking that it's upated on the other side:
print("## ny.country (after rel creation): ", ny.country)
# Saving
session.commit()


print("\n### 2. Deleting relation:")
# Loading relations:
print("## us.capital: ", us.capital)
print("## ny.country: ", ny.country)
# Deleting one object of the relation:
us.capital = None
# The relation from the other side are updated accordingly
print("## ny.country (after rel deletion): ", ny.country)
# Rolling back
session.rollback()


print("\n### 3. Deleting one object:")
# Loading relations:
print("## us.capital: ", us.capital)
print("## ny.country: ", ny.country)
# Deleting one object of the relation:
session.delete(us)
# The relations are not updated!
print("## ny.country (after deletion of us): ", ny.country)
# Flushing doesn't change anything (undersantably so)
session.flush()
print("## ny.country (+ flush): ", ny.country)
# Expiring manually
session.expire(ny, ['country'])
# Looks okay
print("## ny.country (+ expire): ", ny.country)
# Rolling back
session.rollback()


print("\n### 4. Deleting the other object:")
# Loading relations:
print("## us.capital: ", us.capital)
print("## ny.country: ", ny.country)
# Deleting one object of the relation:
session.delete(ny)
# The relations are not updated!
print("## us.capital (after deletion of ny): ", us.capital)
# Flushing doesn't change anything (undersantably so)
session.flush()
print("## us.capital (+ flush): ", us.capital)
# Expiring manually
session.expire(us, ['capital'])
# Looks okay
print("## us.capital (+ expire): ", us.capital)
# Rolling back
session.rollback()

Results

### 1. Creating the relation:
## us.capital:  None
## ny.country:  None
## ny.country (after rel creation):  <Country USA [1]>

### 2. Deleting relation:
## us.capital:  <Capital NYC [1]>
## ny.country:  <Country USA [1]>
## ny.country (after rel deletion):  None

### 3. Deleting one object:
## us.capital:  <Capital NYC [1]>
## ny.country:  <Country USA [1]>
## ny.country (after deletion of us):  <Country USA [1]>
## ny.country (+ flush):  <Country USA [1]>
## ny.country (+ expire):  None

### 4. Deleting the other object:
## us.capital:  <Capital NYC [1]>
## ny.country:  <Country USA [1]>
## us.capital (after deletion of ny):  <Capital NYC [1]>
## us.capital (+ flush):  <Capital NYC [1]>
## us.capital (+ expire):  None

Answer 1

To be clear, SQLAlchemy does update relations after object deletion and commit.

Here is a summary of the state and relation changes at each step:

# Delete
session.delete(us)
assert instance_state(ny.country) in session._deleted

# Flush
assert not instance_state(ny.country).deleted
session.flush()
assert instance_state(ny.country).deleted

# Commit
assert ny.country is not None
session.commit()
assert ny.country is None

Why not update relations before commit

Is there a reason why SQLAlchemy doesn't / cannot mark for update the relations tied to an object that is removed?

It may just be that the additional handling hasn't been requested, discussed and implemented.

Some points:

• Unassigning a relation behaves differently since that is via RelationshipProperty itself.
• Modifying the relation directly has side effects that session.expunge() cannot recover.

But RelationshipProperty could decide not to return an instance based on its state.

Implementing a mixin to hide deleted relations

We can implement mixins that intercept __getattribute__ to hide deleted relations.

class HideDeletedRelationshipWithoutFlushMixin:

    def __getattribute__(self, item):
        value = super().__getattribute__(item)
        if value is not None and hasattr(value, DEFAULT_STATE_ATTR):
            state = instance_state(value)
            if (
                    state.deleted or                                               # After flush
                    state.session is not None and state in state.session._deleted  # Before flush
            ):
                return None
        return value


class HideDeletedRelationshipAfterFlushMixin:

    def __getattribute__(self, item):
        value = super().__getattribute__(item)
        if value is not None and hasattr(value, DEFAULT_STATE_ATTR) and instance_state(value).deleted:
            return None
        return value

Usage, depending on when you expect deleted relations to be None:

# Either
Base = declarative_base(cls=HideDeletedRelationshipWithoutFlushMixin)
# Or
Base = declarative_base(cls=HideDeletedRelationshipAfterFlushMixin)

---

Alternative: Expiring relations on flush

We can implement a function to expire relations in the 'persistent_to_deleted' event.

def expire_back_populated_relations(session, instance):
    for relation in instance_state(instance).manager.mapper.relationships:
        related_instance = getattr(instance, relation.key)
        session.expire(related_instance, (relation.back_populates,))

Usage:

@event.listens_for(Session, 'persistent_to_deleted')
def receive_persistent_to_deleted(session, instance):
    expire_back_populated_relations(session, instance)