angle-uparrow-clockwisearrow-counterclockwisearrow-down-uparrow-leftatcalendarcard-listchatcheckenvelopefolderhouseinfo-circlepencilpeoplepersonperson-fillperson-plusphoneplusquestion-circlesearchtagtrashx

SQLAlchemy: Uso de Cascade Deletes para eliminar objetos relacionados

Cascade Deletes es mucho más que añadir 'all, delete' a la relación.

16 julio 2022
post main image

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:

El valor por defecto de la relación 'cascade'-opción es 'save-update, merge'. La configuración alternativa típica para este parámetro es todo o, más comúnmente, 'all, delete-orphan'. El símbolo 'all' es un sinónimo de 'save-update, merge', 'refresh-expire', 'expunge', 'delete', y su uso junto con 'delete-orphan' indica que el objeto hijo debe seguir a su padre en todos los casos, y ser eliminado una vez que ya no esté asociado a ese padre.

¿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:

SQLAlchemy sólo emite DELETE para aquellas filas que ya están presentes localmente en la Sesión; para cualquier colección que se descargue, la deja a la base de datos para que la maneje, en lugar de emitir un SELECT para ellos.

También podemos establecer passive_deletes='all', de la documentación SQLAlchemy :

Esto tiene el efecto de deshabilitar por completo el comportamiento de SQLAlchemy de establecer la columna de clave foránea a NULL, y se emitirá un DELETE para la fila padre sin afectar a la fila hija, incluso si la fila hija está presente en la memoria.

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 :

El nivel de base de datos ON DELETE CASCADE es generalmente mucho más eficiente que confiar en la función de eliminación en "cascada" 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 :

Si las cascadas son confusas, nos remitimos a su conclusión, diciendo "Las secciones que acabamos de cubrir pueden ser un poco confusas. Sin embargo, en la práctica, todo funciona bien".

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.

avatar

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

avatar

Muchas gracias por el aporte!!

avatar

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!