SQLAlchemy : Utilisation de Cascade Deletes pour supprimer des objets connexes

Bien que la documentation de SQLAlchemy ORM soit très bonne, elle est parfois confuse et manque d'exemples clairs, mais c'est peut-être juste moi. À propos des Cascade Delete, je trouve le texte suivant de la documentation de SQLAlchemy époustouflant :

Huh ? Quoi ? Et après un certain temps : WTF! Au secours ! Mais je ne veux que des recettes !

Mais il n'y a pas de recettes. La seule façon de s'assurer que l'on fait bien les choses est de lire la documentation, de la relire, et de créer des exemples qui confirment que l'on a compris.

SQLAlchemy Cascade Deletes

Les Cascade Delete de SQLAlchemy peuvent être spécifiées dans une relation en utilisant l'option 'cascade'. L'option 'cascade' a une valeur par défaut. Cela signifie que si vous n'utilisiez pas l'option 'cascade' et que vous souhaitez ajouter une option 'cascade' à une relation, vous modifiez ( !) le comportement par défaut. Pour conserver le comportement par défaut, vous devez ajouter "save-update" et "merge" à l'option 'cascade'.

La documentation SQLAlchemy donne ensuite un exemple de Cascade Delete : lorsque le parent est supprimé, ses enfants doivent également être supprimés. Dans l'exemple, la ligne Cascade Delete est :

cascade='all, delete'

Ceci est très confus car 'delete' fait déjà partie de 'all'. Cela signifie que nous pouvons aussi écrire :

cascade='all'

Mais attendez, 'all' inclut aussi 'refresh-expire' et 'expunge'. La documentation contient un avertissement indiquant que 'refresh-expire' modifie le comportement par défaut et que 'expunge' modifie également le comportement par défaut. Cela signifie que nous ne voulons pas de 'all' mais probablement :

cascade='save-update, merge, delete'

Exemple : Customer, Order (One-To-Many)

Lorsque nous supprimons un client, nous voulons également supprimer toutes les commandes de ce client.

Nous avons deux modèles, Customer et Order. Customer-Order est une relation Un à plusieurs. Un client peut avoir zéro ou plusieurs commandes et une commande ne fait partie que d'un seul client. Nous créons une relation bidirectionnelle. Un client peut faire référence aux commandes en utilisant Customer.orders et une commande peut faire référence à un client en utilisant Order.customer.

Notez que dans la ForeignKey Order.customer_id j'ai ajouté l'attribut 'nullable=False'. La raison est que nous ne voulons pas que des commandes sans client flottent autour.

Nous faisons fonctionner le Cascade Delete en ajoutant la ligne suivante à la relation dans le Customer :

cascade='save-update, merge, delete'

Nos classes modèles :

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',
    )

En supposant qu'un client ait deux commandes, les états SQL suivants sont générés par SQLAlchemy :

DELETE FROM "order" WHERE "order".id = ?
((1,), (2,))
DELETE FROM customer WHERE customer.id = ?
(2,)

On pourrait s'attendre à trois relevés SQL, le premier sélectionnant toutes les commandes du client. Dans mon script de test, j'ai créé les clients et les commandes avant l'instruction DELETE, ce qui signifie que les objets de commande sont dans la session. SQLAlchemy le sait, et a décidé qu'il n'était pas nécessaire de les récupérer dans la base de données.

Maintenant, modifions l'option 'cascade' : supprimons 'delete' et ajoutons 'delete-orphan', qui supprime également tous les enfants qui ont été supprimés du parent, même si le parent n'est pas supprimé. Ne peut probablement pas être utilisé sans 'delete'. Voyons ce qui se passe.

cascade='save-update, merge, delete-orphan',

Les déclarations de SQL et le résultat généré par 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 tente de supprimer les commandes du client en mettant à jour les ForeignKeys vers NULL, mais cela n'est pas autorisé car la ForeignKey inclut 'nullable=False'.

Enfin, créons l'option 'cascade' avec les deux 'delete' et 'delete-orphan' :

cascade='save-update, merge, delete, delete-orphan',

Les états SQL générés par SQLAlchemy :

DELETE FROM "order" WHERE "order".id = ?
((1,), (2,))
DELETE FROM customer WHERE customer.id = ?
(2,)

C'est exactement le même cas que celui de 'delete' seulement.

Suppression d'objets de base de données à l'aide de ForeignKey ON DELETE CASCADE

De nombreux moteurs de base de données, mais pas tous, prennent en charge l'action "ON DELETE CASCADE" : Chaque ligne de la table enfant associée à la ligne parent supprimée est également supprimée.
SQLAlchemy prend en charge cette action. En plus de l'option 'cascade' décrite ci-dessus nous devons ajouter :

• passive_deletes=True, à la relation dans le parent
• ondelete='CASCADE', à la clé étrangère de l'enfant.

Customer et Order après avoir effectué ces modifications :

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 vous essayez ceci, vous pouvez constater que la SQL générée est exactement la même que ci-dessus. Cela signifie deux SQL-statements, un pour supprimer les commandes et un pour supprimer le client (et éventuellement un troisième SQL-statement si les commandes ne sont pas dans la session). La raison en est que les objets à supprimer se trouvent déjà dans la session actuelle. Extrait de la documentation :

Nous pouvons également définir passive_deletes='all', à partir de la documentation SQLAlchemy :

Si j'essaie cela, j'obtiens le message d'erreur suivant, je ne comprends pas pourquoi :

can't set passive_deletes='all' in conjunction with 'delete' or 'delete-orphan' cascade

Quoi qu'il en soit, pour vérifier et voir ce qui se passe, nous pouvons soit expurger les objets d'abord, soit démarrer une nouvelle session, sélectionner un client et supprimer ce client. J'ai utilisé cette dernière solution. Maintenant, seul un seul état SQL est généré :

DELETE FROM customer WHERE customer.id = ?
(2,)

Bien sûr, cela n'a pas fonctionné immédiatement. Il semble que vous devez activer Foreign Keys lorsque vous utilisez SQLite. Si vous oubliez cela, les commandes du client ne sont PAS supprimées. Il échoue silencieusement ... aucune erreur ou message ... soupir.

SQLAlchemy Cascade Delete ou base de données Cascade Delete ?

Extrait de la documentation SQLAlchemy :

Bien. J'aime l'efficacité. J'utilise PostgreSQL et je vais utiliser les bases de données Cascade Delete lorsque cela sera possible. Mais au final, tout dépend de votre application. Une application web n'a généralement qu'un nombre minimal de suppressions, alors pourquoi s'en préoccuper ?

Le code

Au cas où vous voudriez essayer vous-même, voici le code que j'ai utilisé :

# 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)

Et le résultat :

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

Résumé

Comme souvent avec SQLAlchemy : Vous en aviez besoin, vous n'êtes pas entré dans tous les détails, ça a marché, tout le monde est content. Mais quand vous vous penchez à nouveau sur la question, vous commencez à lire davantage et vous faites quelques vérifications pour vous assurer que tout fonctionne comme prévu.

Cascade Deletes est un sujet difficile. Vous devez prendre les bonnes décisions pour votre modèle de données et la seule façon de le faire est de bien comprendre ce qui se passe.

C'était la première fois que je lisais entièrement la page 'SQLAlchemy - Cascades', voir les liens ci-dessous. J'ai supposé que SQLAlchemy serait le meilleur choix pour moi (et ça a marché, rien de mal à ça), mais j'étais naïf et je n'ai pas bien compris le problème.

Extrait de la documentation de SQLAlchemy :

Oui, bien sûr, ça marche bien, mais remplacez s'il vous plaît "en pratique" par "après de nombreuses heures".

Liens / crédits

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