SQLAlchemy: Delete object directly from one-to-many relationship without using session.delete()

Question

I have the following SQLAlchemy setup:

Base = declarative_base()

class Post(Base):
    __tablename__ = 'post'
    id = Column(Integer, primary_key=True)
    title = Column(String(30))
    comments = relationship('Comment', cascade='all')

class Comment(Base):
    __tablename__ = 'comment'
    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey(Post.id, ondelete='CASCADE'), nullable=False)
    text = Column(Text)

With this, I can create post objects with a one-to-many relationship to comments. I want to handle creation and deletion of comments for posts without referencing the session. Adding a comment to a post works just fine:

post = Post(title='some post')
comment = Comment(text='some comment')
post.comments.append(comment)

My session handler only knows about posts, so it would do a session.add(post) and the comment is placed into the session automatically and is syncronized with the database on the next session.commit(). However, the same is not true for deletion of comments. I want to be able to delete a comment by just doing:

post.comments.remove(comment)

However, this produces the following error on the next session.commit():

sqlalchemy.exc.OperationalError: (OperationalError) (1048, "Column 'post_id' cannot be null") 'UPDATE comment SET post_id=%s WHERE comment.id = %s' (None, 1L)

How do I tell SQLAlchemy to not update the comment with a NULL value for post_id (which is not allowed due to the not null constraint on the column), but delete the comment instead? I know that I could do session.delete(comment), but since I did not need to add the comment to the session explicitly, I don't see a reason why I should have to delete it from the session explicitly.

I found several solutions for cascading deletes to related objects, but since I never issue any explicit deletions to the session (the post is still there), I don't think that is applicable.

Edit: I adjusted the example to include the cascading of deletions from posts. Now it works to do session.delete(post) and all comments are deleted. But I just want to automatically delete the comments I removed from the relationship and not delete the whole post with all comments.

TL;DR: How do I tell SQLAlchemy to issue a delete statement instead of an update statement when I remove an entry from a relationship list of a one-to-many relationship?

Answer 1

Read Configuring delete/delete-orphan Cascade section of documentation for more information, but basically you need delete-orphan as well in your cascade option of the relationship:

class Post(Base):
    # ...
    comments = relationship('Comment', cascade="all, delete-orphan")