This is set up using the cascade argument of the relationship. The other way you can do it is to let SqlAlchemy do it for you. You can read more about passive deletes in the SqlAlchemy docs. Setting the passive_deletes=True prevents SqlAlchemy from NULLing out the foreign keys. Once SqlAlchemy has set them all to NULL, the database can't delete them. This is because it relies on those foreign keys to know which child rows to delete. Anyway, if you let SqlAlchemy do this, you will prevent the database from being able to clean up the children using the ON DELETE CASCADE that you set up. Seems like a bad idea, but maybe there's a use case. I'd be surprised if many database engines even allowed you to set a valid foreign key to NULL, creating an orphan. So if you delete a row from parent_table where id = 5, then it will basically execute UPDATE child_table SET parent_id = NULL WHERE parent_id = 5 It sets the foreign keys of all child rows to NULL. This is because by default when you delete a parent record SqlAlchemy does something really weird. Notice how I have a relationship specified with passive_deletes=True? If you don't have that, the entire thing will not work. The ondelete='CASCADE' is the part that creates the ON DELETE CASCADE on the table. Parent = db.relationship('Parent', backref=backref('children', passive_deletes=True)) You set this up in SqlAlchemy through ForeignKey like this (part of the child table definition): parent_id = db.Column(db.Integer, db.ForeignKey('parent.id', ondelete='CASCADE')) It's fast and reliable and probably your best bet. This means that when you delete a record from parent_table, then all the corresponding rows in child_table will be deleted for you by the database. In PostgreSQL it looks like this: CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id) The first, and the one I recommend, is built into your database and usually takes the form of a constraint on the foreign key declaration. Second, SqlAlchemy supports two different kinds of cascading. Although, apparently on the child tables, you have to use the backref function in addition to the keyword argument. You can put the relationship on either the parent or child tables and it will work fine. Give the child table a foreign or modify the existing one, adding ondelete='CASCADE': parent_id = db.Column(db.Integer, db.ForeignKey('parent.id', ondelete='CASCADE'))Ī) This on the parent table: children = db.relationship('Child', backref='parent', passive_deletes=True)ī) Or this on the child table: parent = db.relationship('Parent', backref=backref('children', passive_deletes=True))įirst off, despite what the accepted answer says, the parent/child relationship is not established by using relationship, it's established by using ForeignKey. Pretty old post, but I just spent an hour or two on this, so I wanted to share my finding, especially since some of the other comments listed aren't quite right. (implying from sqlalchemy.orm import backref) You might want to add delete-orphan as well ( delete causes children to be deleted when the parent gets deleted, delete-orphan also deletes any children that were "removed" from the parent, even if the parent is not deleted)ĮDIT: just found out: if you really want to define the relationship on the Child class, you can do so, but you will have to define the cascade on the backref (by creating the backref explicitly), like this: parent = relationship(Parent, backref=backref("children", cascade="all,delete")) (note "Child" as a string: this is allowed when using the declarative style, so that you are able to refer to a class that is not yet defined) If you define the relationship on the Parent class instead, it will work: children = relationship("Child", cascade="all,delete", backref="parent") The problem is that sqlalchemy considers Child as the parent, because that is where you defined your relationship (it doesn't care that you called it "Child" of course). Why? How do I make the children cascade delete? Next, it deletes the parent, but the children persist. The script creates a parent, adds 3 children, then commits. There is a simple, one-to-many relationship between Parent and Child. Print "Before delete, children = ".format(session.query(Parent).count()) Parent = relationship(Parent, cascade = "all,delete", backref = "children")Įngine = create_engine("sqlite:///:memory:") Parentid = Column(Integer, ForeignKey(Parent.id)) I've put a concise test case here: from sqlalchemy import Column, Integer, ForeignKeyįrom import declarative_base I must be missing something trivial with SQLAlchemy's cascade options because I cannot get a simple cascade delete to operate correctly - if a parent element is a deleted, the children persist, with null foreign keys.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |