Is it possible to use passive_deletes with lazy='select' in sqlalchemy?

Question

I was trying to do something like this:

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)

class Child(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parents.id'))
    Parent = relationship('Parent', backref=backref('children', passive_deletes=True))

parent = db.session.query(Parent).filter(id=some_id).first()
print parent.children
db.session.delete(parent)
db.session.commit()

I don't want sqlalchemy to create a lot of queries for deleting children. I have foreign key constraint instead of it. But i am getting the error:

sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column "parent" violates not-null constraint

It is beacause i don't use lazy='dynamic' option in relationship parameters. But i can't use the option because joinedload option would not work with lazy='dynamic' option. How can i avoid these problems?

Answer 1

First of all please refine your question as it contains several mistakes. The working solution looks like this:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship('Parent', backref=backref('children', cascade='all, delete-orphan'))

parent = db.session.query(Parent).filter(id=some_id).first()
db_session.delete(parent)
db_session.commit()

If you want to delete a parent with its children you have to use option cascade='all, delete-orphan' to relationship.

Unfortunately, I could't reproduce the example with passive_deletes=True directive from doc:
Passive Deletes
See my question:
Not working example with passive deletes directive in sqlalchemy doc