SQLAlchemy: cascade delete
@Steven's asnwer is good when you are deleting through session.delete()
which never happens in my case. I noticed that most of the time I delete through session.query().filter().delete()
(which doesn't put elements in the memory and deletes directly from db).
Using this method sqlalchemy's cascade='all, delete'
doesn't work. There is a solution though: ON DELETE CASCADE
through db (note: not all databases support it).
class Child(Base):
__tablename__ = "children"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parents.id", ondelete='CASCADE'))
class Parent(Base):
__tablename__ = "parents"
id = Column(Integer, primary_key=True)
child = relationship(Child, backref="parent", passive_deletes=True)
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).
If you define the relationship on the Parent
class instead, it will work:
children = relationship("Child", cascade="all,delete", backref="parent")
(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)
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)
EDIT: 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"))
(implying from sqlalchemy.orm import backref
)
Alex Okrushko answer almost worked best for me. Used ondelete='CASCADE' and passive_deletes=True combined. But I had to do something extra to make it work for sqlite.
Base = declarative_base()
ROOM_TABLE = "roomdata"
FURNITURE_TABLE = "furnituredata"
class DBFurniture(Base):
__tablename__ = FURNITURE_TABLE
id = Column(Integer, primary_key=True)
room_id = Column(Integer, ForeignKey('roomdata.id', ondelete='CASCADE'))
class DBRoom(Base):
__tablename__ = ROOM_TABLE
id = Column(Integer, primary_key=True)
furniture = relationship("DBFurniture", backref="room", passive_deletes=True)
Make sure to add this code to ensure it works for sqlite.
from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection
@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
if isinstance(dbapi_connection, SQLite3Connection):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON;")
cursor.close()
Stolen from here: SQLAlchemy expression language and SQLite's on delete cascade
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.
TL;DR
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'))
And one of the following relationships:
a) This on the parent table:
children = db.relationship('Child', backref='parent', passive_deletes=True)
b) Or this on the child table:
parent = db.relationship('Parent', backref=backref('children', passive_deletes=True))
Details
First off, despite what the accepted answer says, the parent/child relationship is not established by using relationship
, it's established by using ForeignKey
. You can put the relationship
on either the parent or child tables and it will work fine. Although, apparently on the child tables, you have to use the backref
function in addition to the keyword argument.
Option 1 (preferred)
Second, SqlAlchemy supports two different kinds of cascading. 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. In PostgreSQL it looks like this:
CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
REFERENCES parent_table(id) MATCH SIMPLE
ON DELETE CASCADE
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. It's fast and reliable and probably your best bet. 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'))
parent = db.relationship('Parent', backref=backref('children', passive_deletes=True))
The ondelete='CASCADE'
is the part that creates the ON DELETE CASCADE
on the table.
Gotcha!
There's an important caveat here. Notice how I have a relationship
specified with passive_deletes=True
? If you don't have that, the entire thing will not work. This is because by default when you delete a parent record SqlAlchemy does something really weird. It sets the foreign keys of all child rows to NULL
. 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
Why you would want this I have no idea. I'd be surprised if many database engines even allowed you to set a valid foreign key to NULL
, creating an orphan. Seems like a bad idea, but maybe there's a use case. 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. This is because it relies on those foreign keys to know which child rows to delete. Once SqlAlchemy has set them all to NULL
, the database can't delete them. Setting the passive_deletes=True
prevents SqlAlchemy from NULL
ing out the foreign keys.
You can read more about passive deletes in the SqlAlchemy docs.
Option 2
The other way you can do it is to let SqlAlchemy do it for you. This is set up using the cascade
argument of the relationship
. If you have the relationship defined on the parent table, it looks like this:
children = relationship('Child', cascade='all,delete', backref='parent')
If the relationship is on the child, you do it like this:
parent = relationship('Parent', backref=backref('children', cascade='all,delete'))
Again, this is the child so you have to call a method called backref
and putting the cascade data in there.
With this in place, when you delete a parent row, SqlAlchemy will actually run delete statements for you to clean up the child rows. This will likely not be as efficient as letting this database handle if for you so I don't recommend it.
Here are the SqlAlchemy docs on the cascading features it supports.