SQLAlchemy: Uso de Cascade Deletes para eliminar objetos relacionados
Cascade Deletes es mucho más que añadir 'all, delete' a la relación.

Aunque la documentación de SQLAlchemy ORM es muy buena, a veces es confusa y carece de ejemplos claros, pero tal vez sea sólo cosa mía. Sobre los Cascade Delete, encuentro el siguiente texto de la documentación de SQLAlchemy alucinante:
¿Eh? ¿Qué? Y después de un tiempo WTF! ¡Ayuda! ¡Pero si sólo quiero recetas!
Pero no hay recetas. La única manera de asegurarse de que estamos haciendo lo correcto es leer la documentación, volver a leer la documentación y crear algunos ejemplos que confirmen que hemos entendido.
SQLAlchemy Cascade Deletes
Cascade Deletes en SQLAlchemy se puede especificar en una relación utilizando la opción 'cascade'. La opción 'cascade' tiene un valor por defecto. Esto implica que si no se utiliza la opción 'cascade' y se quiere añadir una opción 'cascade' a una relación, se está cambiando (!) el comportamiento por defecto. Para mantener el comportamiento por defecto, debe añadir 'save-update' y 'merge' a la opción 'cascade'.
A continuación, la documentación de SQLAlchemy ofrece un ejemplo de Cascade Delete: Cuando se elimina el padre, también deben eliminarse sus hijos. En el ejemplo la línea Cascade Delete es:
cascade='all, delete'
Esto es muy confuso porque 'delete' ya forma parte de 'all'. Esto significa que también podemos escribir:
cascade='all'
Pero espera, 'all' incluye también 'refresh-expire' y 'expunge'. Hay una advertencia en la documentación de que 'refresh-expire' cambia el comportamiento por defecto y también 'expunge' cambia el comportamiento por defecto. Esto significa que no queremos 'all' pero probablemente:
cascade='save-update, merge, delete'
Ejemplo: Customer, Order (One-To-Many)
Cuando eliminamos un cliente, también queremos eliminar todos los pedidos de este cliente.
Tenemos dos modelos, Customer y Order. Customer-Order es una relación Uno-a-Muchos. Un cliente puede tener cero o más pedidos y un pedido es parte de un solo cliente. Creamos una relación bidireccional. Un cliente puede referirse a los pedidos utilizando Customer.orders y un pedido puede referirse a un cliente utilizando Order.customer.
Nótese que en la ForeignKey Order.customer_id he añadido el atributo 'nullable=False'. La razón es que no queremos que haya pedidos sin cliente flotando.
Hacemos funcionar el Cascade Delete añadiendo la siguiente línea a la relación en el Customer:
cascade='save-update, merge, delete'
Nuestro modelo de clases:
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
# relationship: orders
orders = relationship(
'Order',
back_populates='customer',
# delete customer orders when customer deleted
cascade='save-update, merge, delete'
)
class Order(Base):
__tablename__ = 'order'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
# relationship: customer
customer_id = sa.Column(sa.Integer, sa.ForeignKey('customer.id'), nullable=False, index=True)
customer = relationship(
'Customer',
back_populates='orders',
)
Suponiendo un cliente con dos pedidos, los siguientes estados SQL son generados por SQLAlchemy:
DELETE FROM "order" WHERE "order".id = ?
((1,), (2,))
DELETE FROM customer WHERE customer.id = ?
(2,)
Se esperan tres SQL-statements, el primero seleccionando todos los pedidos del cliente. En mi script de prueba he creado los clientes y los pedidos antes de la sentencia DELETE, lo que significa que los objetos de los pedidos están en la sesión. SQLAlchemy sabe esto, y decidió que no hay necesidad de obtenerlos de la base de datos.
Ahora cambiemos la opción 'cascade': eliminemos 'delete' y añadamos 'delete-orphan', que también elimina cualquier hijo que haya sido eliminado del padre, incluso si el padre no es eliminado. Probablemente no se puede utilizar sin 'delete'. Veamos qué ocurre.
cascade='save-update, merge, delete-orphan',
Las declaraciones SQL y el resultado generado por SQLAlchemy:
UPDATE "order" SET customer_id=? WHERE "order".id = ?
((None, 1), (None, 2))
exception = IntegrityError, e.args = ('(sqlite3.IntegrityError) NOT NULL constraint failed: order.customer_id',)
SQLAlchemy hace un intento de eliminar los pedidos del cliente actualizando las ForeignKeys a NULL, pero eso no está permitido porque la ForeignKey incluye 'nullable=False'.
Por último, vamos a crear la opción 'cascade' con 'delete' y 'delete-orphan':
cascade='save-update, merge, delete, delete-orphan',
Los estados SQL generados por SQLAlchemy:
DELETE FROM "order" WHERE "order".id = ?
((1,), (2,))
DELETE FROM customer WHERE customer.id = ?
(2,)
Esto es exactamente igual que el caso de 'delete' solamente.
Eliminación de objetos de la base de datos mediante ForeignKey ON DELETE CASCADE
Muchos motores de bases de datos, pero no todos, admiten la acción 'ON DELETE CASCADE': Cada fila de la tabla hija asociada a la fila padre eliminada, también se elimina.
SQLAlchemy tiene soporte para esto. Además de la opción 'cascade' descrita anteriormente debemos añadir:
- passive_deletes=True, a la relación en el padre
- ondelete='CASCADE', a la ForeignKey en el hijo
Customer y Order después de realizar estos cambios:
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
# relationship: orders
orders = relationship(
'Order',
back_populates='customer',
# delete customer orders when customer deleted
cascade='save-update, merge, delete',
passive_deletes=True,
)
class Order(Base):
__tablename__ = 'order'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
# relationship: customer
customer_id = sa.Column(sa.Integer, sa.ForeignKey('customer.id', ondelete='CASCADE'), nullable=False, index=True)
customer = relationship(
'Customer',
back_populates='orders',
)
Si prueba esto, puede encontrar que el SQL generado es exactamente el mismo que el anterior. Es decir, dos SQL-statements, uno para eliminar los pedidos y otro para eliminar el cliente (y opcionalmente un tercer SQL-statement si los pedidos no están en la sesión). La razón es que los objetos a eliminar ya están en la sesión actual. De la documentación:
También podemos establecer passive_deletes='all', de la documentación SQLAlchemy :
Si intento esto me sale el siguiente mensaje de error, no entiendo por qué:
can't set passive_deletes='all' in conjunction with 'delete' or 'delete-orphan' cascade
De todos modos, para comprobar y ver lo que ocurre podemos o bien borrar los objetos primero, o bien, iniciar una nueva sesión, seleccionar un cliente y borrar este cliente. Yo he utilizado esta última opción. Ahora sólo se genera una única declaración SQL:
DELETE FROM customer WHERE customer.id = ?
(2,)
Por supuesto, esto no funcionó inmediatamente. Parece que hay que activar Foreign Keys cuando se utiliza SQLite. Cuando se olvida esto, los pedidos del cliente NO se borran. Falla silenciosamente... no hay errores ni mensajes... suspiro.
SQLAlchemy Cascade Deletes o base de datos Cascade Deletes?
De la documentación de SQLAlchemy :
Bien. Me gusta la eficiencia. Estoy utilizando PostgreSQL y voy a utilizar la base de datos Cascade Delete cuando sea posible. Pero al final todo depende de tu aplicación. Una aplicación web suele tener un número mínimo de borrados, así que ¿por qué molestarse?
El código
Por si quieres probar tú mismo, aquí tienes el código que he utilizado:
# customer-order ondelete
import sys
import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from sqlalchemy.engine import Engine
from sqlalchemy import event
from sqlite3 import Connection as SQLite3Connection
# enable foreign keys. if you do not do this, ON DELETE CASCADE fails silently!
@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
if isinstance(dbapi_connection, SQLite3Connection):
print('turning on foreign keys ...')
cursor = dbapi_connection.cursor()
cursor.execute('PRAGMA foreign_keys=ON;')
cursor.close()
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
# relationship: orders
orders = relationship(
'Order',
back_populates='customer',
cascade='save-update, merge, delete',
passive_deletes=True,
)
class Order(Base):
__tablename__ = 'order'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
# relationship: customer
customer_id = sa.Column(sa.Integer, sa.ForeignKey('customer.id', ondelete='CASCADE'), nullable=False, index=True)
#customer_id = sa.Column(sa.Integer, sa.ForeignKey('customer.id'), nullable=False, index=True)
customer = relationship(
'Customer',
back_populates='orders',
)
# get engine and create all
engine_echo = True
engine = sa.create_engine('sqlite:///:memory:', echo=engine_echo)
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
# class to dump objects
class DumpItem:
def __init__(self, model=None, many_attr_name=None):
self.model = model
self.many_attr_name = many_attr_name
# function to dump objects
def dump_all(title, do_not_print=False):
lines = []
sep_line = '-' * 60
dump_items = [
DumpItem(model=Customer, many_attr_name='orders'),
DumpItem(model=Order),
]
lines.append('\n{}\n{}'.format(sep_line, title))
for dump_item in dump_items:
stmt = sa.select(dump_item.model).order_by(dump_item.model.name)
objs = session.execute(stmt).scalars().all()
lines.append('{}\n{}, total {}\n{}'.format(sep_line, dump_item.model.__name__, len(objs), sep_line))
for i, obj in enumerate(objs):
lines.append('[{:d}] {}'.format(i, obj.name))
try:
many_attr = getattr(obj, dump_item.many_attr_name, None)
except:
continue
for j, many_obj in enumerate(many_attr):
lines.append(' [{}] {}'.format(j, many_obj.name))
dump = '{}\n'.format('\n'.join(lines))
if not do_not_print:
print(dump)
return dump
print('create session')
Session = sessionmaker(bind=engine)
session = Session()
print('create customers ...')
customers = [
Customer(name='customer0'), Customer(name='customer1'),
Customer(name='customer2'), Customer(name='customer3'),
]
session.add_all(customers)
print('create customer orders ...')
customers[1].orders = [Order(name='order0'), Order(name='order1')]
customers[2].orders = [Order(name='order2'), Order(name='order3')]
session.commit()
session.flush()
objs_before = dump_all('before delete', do_not_print=True)
# select delete with current session or delete with new session
delete_with_current_session = False
if delete_with_current_session:
print('before delete customer1, current session ...')
try:
session.delete(customers[1])
'''
emitted sql:
DELETE FROM "order" WHERE "order".id = ?
((1,), (2,))
DELETE FROM customer WHERE customer.id = ?
(2,)
'''
session.commit()
except Exception as e:
print('exception = {}, e.args = {}'.format(type(e).__name__, e.args))
session.rollback()
sys.exit()
print('after delete customer1, current session ...')
else:
print('before delete customer1, new session ...')
print('create a new session')
Session2 = sessionmaker(bind=engine)
session2 = Session2()
print('get customer1')
stmt = sa.select(Customer).where(Customer.name == 'customer1')
customer1 = session2.execute(stmt).scalars().first()
try:
session2.delete(customer1)
'''
emitted sql:
DELETE FROM customer WHERE customer.id = ?
(2,)
'''
session2.commit()
except Exception as e:
print('exception = {}, e.args = {}'.format(type(e).__name__, e.args))
session2.rollback()
sys.exit()
print('after delete customer1, current session ...')
objs_after = dump_all('after delete', do_not_print=True)
print(objs_before)
print(objs_after)
Y el resultado:
------------------------------------------------------------
before delete
------------------------------------------------------------
Customer, total 4
------------------------------------------------------------
[0] customer0
[1] customer1
[0] order0
[1] order1
[2] customer2
[0] order2
[1] order3
[3] customer3
------------------------------------------------------------
Order, total 4
------------------------------------------------------------
[0] order0
[1] order1
[2] order2
[3] order3
------------------------------------------------------------
after delete
------------------------------------------------------------
Customer, total 3
------------------------------------------------------------
[0] customer0
[1] customer2
[0] order2
[1] order3
[2] customer3
------------------------------------------------------------
Order, total 2
------------------------------------------------------------
[0] order2
[1] order3
Resumen
Como suele ocurrir con SQLAlchemy: Lo necesitabas, no te metiste en todos los detalles, funcionó, todos contentos. Pero cuando lo vuelves a investigar, empiezas a leer más, y haces algunos casos para volver a comprobar que funciona como se espera.
Cascade Deletes es un tema difícil. Debes tomar las decisiones correctas para tu modelo de datos y la única manera de hacerlo es entender completamente lo que está sucediendo.
Esta fue la primera vez que leí completamente la página 'SQLAlchemy - Cascadas', ver los enlaces más abajo. Asumí que el SQLAlchemy sería la mejor opción para mí (y funcionó, no hay nada malo en ello), pero fui ingenuo y no entendí completamente el problema.
De la documentación de SQLAlchemy :
Sí, claro, todo funciona bien, pero sustituye "en la práctica" por "después de muchas horas".
Enlaces / créditos
Cascade all vs all, delete #6403
https://github.com/sqlalchemy/sqlalchemy/issues/6403
SQLAlchemy - Basic Relationship Patterns
https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html
SQLAlchemy - Cascades
https://docs.sqlalchemy.org/en/14/orm/cascades.html
SQLAlchemy Cascade Delete: Clarity through Examples
https://esmithy.net/2020/06/20/sqlalchemy-cascade-delete
Leer más
SQLAlchemy
Deje un comentario
Comente de forma anónima o inicie sesión para comentar.
Comentarios (3)
Deje una respuesta.
Responda de forma anónima o inicie sesión para responder.

good one,might be better if you had used declarative mapping


Confusion - I have no better words for this. As a database/SQLAlchemy newbie I am struggling to understand what is going on so "trial and error/not what I want" method is my best friend:)
Very nice article, thanks for this!
Recientes
- Gráfico de series temporales con Flask, Bootstrap y Chart.js
- Utilización de IPv6 con Microk8s
- Uso de Ingress para acceder a RabbitMQ en un clúster Microk8s
- Galería de vídeo simple con Flask, Jinja, Bootstrap y JQuery
- Programación básica de trabajos con APScheduler
- Un conmutador de base de datos con HAProxy y el HAProxy Runtime API
Más vistos
- Usando PyInstaller y Cython para crear un ejecutable de Python
- Reducir los tiempos de respuesta de las páginas de un sitio Flask SQLAlchemy web
- Usando Python's pyOpenSSL para verificar los certificados SSL descargados de un host
- Conectarse a un servicio en un host Docker desde un contenedor Docker
- Usando UUIDs en lugar de Integer Autoincrement Primary Keys con SQLAlchemy y MariaDb
- SQLAlchemy: Uso de Cascade Deletes para eliminar objetos relacionados