SQLAlchemy: Gebruik van Cascade Deletes om verwante objecten te verwijderen

Hoewel de documentatie van SQLAlchemy ORM erg goed is, is het soms verwarrend en ontbreken duidelijke voorbeelden, maar misschien ligt dat aan mij. Over Cascade Delete's vind ik de volgende tekst uit de SQLAlchemy documentatie mind-blowing:

Huh? Wat? En dan na enige tijd: WTF! Help! Maar ik wil alleen recepten!

Maar er zijn geen recepten. De enige manier om er zeker van te zijn dat we de juiste dingen doen is de documentatie lezen, de documentatie nog eens lezen, en wat voorbeelden maken die bevestigen dat we het begrepen hebben.

SQLAlchemy Cascade Deletes

Cascade Deletes in SQLAlchemy kunnen in een relatie worden gespecificeerd met behulp van de 'cascade'-optie. De 'cascade'-optie heeft een standaardwaarde. Dit houdt in dat als u de 'cascade'-optie niet gebruikte en een 'cascade'-optie aan een relatie wilt toevoegen, u het standaard gedrag wijzigt (!). Om het standaard gedrag te behouden, moet u 'save-update' en 'merge' toevoegen aan de 'cascade'-optie.

De SQLAlchemy documentatie geeft vervolgens een voorbeeld van Cascade Delete: Wanneer de ouder wordt verwijderd, moeten zijn kinderen ook worden verwijderd. In het voorbeeld is de regel Cascade Delete :

cascade='all, delete'

Dit is erg verwarrend omdat 'delete' al deel uitmaakt van 'all'. Dit betekent dat we ook kunnen schrijven:

cascade='all'

Maar wacht, 'all' omvat ook 'refresh-expire' en 'expunge'. Er staat een waarschuwing in de documentatie dat 'refresh-expire' het standaard gedrag wijzigt en ook 'expunge' wijzigt het standaard gedrag. Dit betekent dat we 'all' niet willen, maar waarschijnlijk wel:

cascade='save-update, merge, delete'

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

Wanneer wij een klant verwijderen, willen wij ook alle orders van deze klant verwijderen.

Wij hebben twee modellen, Customer en Order. Customer-Order is een One-To-Many relatie. Een klant kan nul of meer orders hebben en een order maakt slechts deel uit van één klant. Wij creëren een bidirectionele relatie. Een klant kan naar orders verwijzen met behulp van Customer.orders en een order kan naar een klant verwijzen met behulp van Order.customer.

Merk op dat ik in de ForeignKey Order.customer_id het attribuut 'nullable=False' heb toegevoegd. De reden hiervoor is dat we niet willen dat er orders zonder klant rondzweven.

We laten de Cascade Delete werken door de volgende regel toe te voegen aan de relatie in de Customer:

cascade='save-update, merge, delete'

Onze modelklassen:

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

Uitgaande van een klant met twee orders, worden de volgende SQL-statements gegenereerd door SQLAlchemy:

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

Je zou drie SQL-afschriften verwachten, waarbij de eerste alle bestellingen van de klant selecteert. In mijn testscript heb ik de klanten en orders aangemaakt vóór het DELETE-statement, wat betekent dat de orderobjecten zich in de sessie bevinden. SQLAlchemy weet dit, en heeft besloten dat het niet nodig is om ze uit de database te halen.

Laten we nu de 'cascade'-optie veranderen: verwijder 'delete' en voeg 'delete-orphan' toe, dat ook alle kinderen verwijdert die van de ouder zijn verwijderd, zelfs als de ouder niet is verwijderd. Kan waarschijnlijk niet gebruikt worden zonder 'delete'. Laten we eens kijken wat er gebeurt.

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

De SQL-statements en het resultaat gegenereerd door 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 doet een poging om de orders van de klant te verwijderen door de ForeignKeys bij te werken naar NULL, maar dat is niet toegestaan omdat de ForeignKey 'nullable=False' bevat.

Laten we tenslotte de 'cascade'-optie maken met zowel 'delete' als 'delete-orphan':

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

De SQL-opgaven gegenereerd door SQLAlchemy:

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

Dit is precies hetzelfde als het geval met 'delete'-only.

Verwijderen van databaseobjecten met behulp van ForeignKey ON DELETE CASCADE

Veel database-engines, maar niet alle, ondersteunen de actie 'ON DELETE CASCADE': Elke rij in de kind-tabel geassocieerd met de verwijderde ouderrij, wordt ook verwijderd.
SQLAlchemy heeft hier ondersteuning voor. Naast de hierboven beschreven 'cascade'-optie moeten wij toevoegen:

• passive_deletes=True, aan de relatie in de ouder
• ondelete='CASCADE', aan de ForeignKey in het kind

Customer en Order modelklassen na het aanbrengen van deze wijzigingen:

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

Als u dit probeert, kunt u merken dat de gegenereerde SQL precies hetzelfde is als hierboven. Dat wil zeggen twee SQL-statements, een om de orders te verwijderen en een om de klant te verwijderen (en optioneel een derde SQL-statement als de orders zich niet in de sessie bevinden). De reden hiervoor is dat de te verwijderen objecten zich al in de huidige sessie bevinden. Uit de documentatie:

We kunnen ook passive_deletes='all' instellen, Uit de SQLAlchemy documentatie:

Als ik dit probeer krijg ik de volgende foutmelding, begrijp niet waarom:

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

Hoe dan ook, om te controleren en te zien wat er gebeurt, kunnen we ofwel eerst de objecten wissen, of, een nieuwe sessie starten, een klant selecteren en deze klant verwijderen. Ik heb de laatste methode gebruikt. Nu wordt er slechts een enkel SQL-statement gegenereerd:

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

Dit werkte natuurlijk niet meteen. Het blijkt dat je Foreign Keys moet aanzetten als je SQLite gebruikt. Als je dit vergeet, worden de orders van de klant NIET verwijderd. Het mislukt geruisloos ... geen fouten of berichten ... zucht.

SQLAlchemy Cascade Deletes of database Cascade Deletes?

Uit de SQLAlchemy documentatie:

Prima. Ik hou van efficiënt. Ik gebruik PostgreSQL en ga waar mogelijk database Cascade Delete's gebruiken. Maar uiteindelijk hangt het allemaal af van uw toepassing. Een webapplicatie heeft meestal maar een minimaal aantal verwijderingen, dus waarom zou je je druk maken?

De code

Voor het geval je het zelf wilt proberen, hier is de code die ik heb gebruikt:

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

En het resultaat:

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

Samenvatting

Zoals zo vaak met SQLAlchemy: Je had het nodig, je hebt je niet in alle details verdiept, het werkte, iedereen blij. Maar als je er nog eens naar kijkt, begin je meer te lezen, en maak je een aantal zaken om te dubbelchecken of het werkt zoals verwacht.

Cascade Deletes is een moeilijk onderwerp. Je moet de juiste beslissingen nemen voor je datamodel en de enige manier om dit te doen is door volledig te begrijpen wat er gebeurt.

Dit was de eerste keer dat ik de pagina "SQLAlchemy - Cascades" volledig heb gelezen, zie onderstaande links. Ik ging ervan uit dat SQLAlchemy voor mij de beste keuze zou zijn (en het werkte, niets mis mee), maar ik was naïef en begreep het probleem niet volledig.

Uit de SQLAlchemy documentatie:

Ja zeker, het werkte allemaal goed, maar vervang "in de praktijk" door "na vele vele uren".

Links / credits

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