SQLAlchemy: Verwendung von Cascade Deletes zum Löschen verwandter Objekte

Obwohl die Dokumentation von SQLAlchemy ORM sehr gut ist, ist sie manchmal verwirrend und es fehlt an klaren Beispielen, aber vielleicht liegt das nur an mir. Was Cascade Deletes angeht, so finde ich den folgenden Text aus der SQLAlchemy -Dokumentation umwerfend:

Häh? Was ist das? Und dann nach einiger Zeit: WTF! Hilfe! Ich will aber nur Rezepte!

Aber es gibt keine Rezepte. Der einzige Weg, um sicher zu gehen, dass wir das Richtige tun, ist, die Dokumentation zu lesen, die Dokumentation noch einmal zu lesen und einige Beispiele zu erstellen, die bestätigen, dass wir es verstanden haben.

SQLAlchemy Cascade Deletes

Cascade Deletes in SQLAlchemy können in einer Beziehung mit der Option 'cascade' angegeben werden. Die Option 'cascade' hat einen Standardwert. Das bedeutet, dass Sie, wenn Sie die Option 'cascade' nicht verwenden und eine 'cascade'-Option zu einer Beziehung hinzufügen möchten, das Standardverhalten ändern (!). Um das Standardverhalten beizubehalten, müssen Sie der 'cascade'-Option 'save-update' und 'merge' hinzufügen.

Die SQLAlchemy -Dokumentation gibt dann ein Beispiel für Cascade Delete: Wenn der Elternteil gelöscht wird, müssen auch seine Kinder gelöscht werden. In dem Beispiel ist die Zeile Cascade Delete :

cascade='all, delete'

Das ist sehr verwirrend, denn 'delete' ist bereits Teil von 'all'. Das heißt, wir können auch schreiben:

cascade='all'

Aber halt, 'all' umfasst auch 'refresh-expire' und 'expunge'. Es gibt eine Warnung in der Dokumentation, dass 'refresh-expire' das Standardverhalten ändert und auch 'expunge' ändert das Standardverhalten. Dies bedeutet, dass wir 'all' nicht wollen, aber wahrscheinlich:

cascade='save-update, merge, delete'

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

Wenn wir einen Kunden löschen, wollen wir auch alle Bestellungen dieses Kunden löschen.

Wir haben zwei Modelle, Customer und Order. Customer-Order ist eine One-To-Many-Beziehung. Ein Kunde kann null oder mehr Aufträge haben und ein Auftrag gehört nur zu einem Kunden. Wir erstellen eine bidirektionale Beziehung. Ein Kunde kann sich über Customer.orders auf Aufträge beziehen und ein Auftrag kann sich über Order.customer auf einen Kunden beziehen.

Beachten Sie, dass ich im ForeignKey Order.customer_id das Attribut 'nullable=False' hinzugefügt habe. Der Grund dafür ist, dass wir nicht wollen, dass Aufträge ohne einen Kunden im Umlauf sind.

Damit die Cascade Delete funktioniert, fügen wir die folgende Zeile zur Beziehung in der Customer hinzu:

cascade='save-update, merge, delete'

Unsere Modellklassen:

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

Angenommen, ein Kunde hat zwei Aufträge, dann werden die folgenden SQL-Anweisungen von SQLAlchemy erzeugt:

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

Man würde drei SQL-Befehle erwarten, wobei der erste alle Aufträge des Kunden auswählt. In meinem Testskript habe ich die Kunden und Aufträge vor der DELETE-Anweisung erstellt, d. h. die Auftragsobjekte befinden sich in der Sitzung. SQLAlchemy weiß das und hat beschlossen, dass es nicht nötig ist, sie aus der Datenbank zu holen.

Ändern wir nun die 'cascade'-Option: Entfernen Sie 'delete' und fügen Sie 'delete-orphan' hinzu, das auch alle Kinder löscht, die vom Elternteil entfernt wurden, selbst wenn der Elternteil nicht gelöscht wird. Kann wahrscheinlich nicht ohne 'delete' verwendet werden. Schauen wir mal, was passiert.

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

Die SQL-Anweisungen und das von SQLAlchemy erzeugte Ergebnis:

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 versucht, die Aufträge aus dem Kunden zu entfernen, indem die ForeignKeys auf NULL aktualisiert werden, aber das ist nicht erlaubt, weil der ForeignKey 'nullable=False' enthält.

Schließlich erstellen wir die Option 'cascade' mit sowohl 'delete' als auch 'delete-orphan':

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

Die von SQLAlchemy erzeugten SQL-Anweisungen:

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

Dies ist genau dasselbe wie der Fall 'delete'-only.

Löschen von Datenbankobjekten mit ForeignKey ON DELETE CASCADE

Viele Datenbank-Engines, aber nicht alle, unterstützen die Aktion 'ON DELETE CASCADE': Jede Zeile in der untergeordneten Tabelle, die mit der gelöschten übergeordneten Zeile verbunden ist, wird ebenfalls gelöscht.
SQLAlchemy unterstützt dies. Zusätzlich zu der oben beschriebenen 'cascade'-Option müssen wir hinzufügen:

• passive_deletes=True, zu der Beziehung in der übergeordneten
• ondelete='CASCADE', zum ForeignKey im Child

Customer und Order Modellklassen, nachdem Sie diese Änderungen vorgenommen haben:

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

Wenn Sie dies versuchen, werden Sie feststellen, dass die generierte SQL genau die gleiche ist wie oben. Das bedeutet zwei SQL-Anweisungen, eine zum Löschen der Aufträge und eine zum Löschen des Kunden (und optional eine dritte SQL-Anweisung, wenn die Aufträge nicht in der Sitzung sind). Der Grund dafür ist, dass sich die zu löschenden Objekte bereits in der aktuellen Sitzung befinden. Aus der Dokumentation:

Wir können auch passive_deletes='all' setzen, Aus der SQLAlchemy Dokumentation:

Wenn ich dies versuche, erhalte ich die folgende Fehlermeldung, ich verstehe nicht, warum:

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

Um zu überprüfen, was passiert, können wir entweder die Objekte zuerst löschen oder eine neue Sitzung starten, einen Kunden auswählen und diesen Kunden löschen. Ich habe Letzteres getan. Jetzt wird nur noch eine einzige SQL-Anweisung erzeugt:

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

Das hat natürlich nicht sofort funktioniert. Es scheint, dass Sie Foreign Keys aktivieren müssen, wenn Sie SQLite verwenden. Wenn Sie dies vergessen, werden die Bestellungen des Kunden NICHT gelöscht. Es schlägt lautlos fehl ... keine Fehler oder Meldungen ... seufz.

SQLAlchemy Cascade Deletes oder Datenbank Cascade Deletes?

Aus der SQLAlchemy Dokumentation:

Sehr gut. Ich mag Effizienz. Ich verwende PostgreSQL und werde nach Möglichkeit die Datenbank Cascade Deletes verwenden. Letztendlich hängt aber alles von Ihrer Anwendung ab. Eine Webanwendung hat in der Regel nur eine minimale Anzahl von Löschvorgängen, warum sich also die Mühe machen?

Der Code

Falls Sie es selbst versuchen wollen, hier ist der von mir verwendete Code:

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

Und das Ergebnis:

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

Zusammenfassung

Wie so oft bei SQLAlchemy: Man hat es gebraucht, man hat sich nicht mit allen Details beschäftigt, es hat funktioniert, alle sind zufrieden. Aber wenn man es sich noch einmal ansieht, fängt man an, mehr zu lesen und einige Fälle zu machen, um zu überprüfen, ob es wie erwartet funktioniert.

Cascade Deletes ist ein schwieriges Thema. Sie müssen die richtigen Entscheidungen für Ihr Datenmodell treffen, und die einzige Möglichkeit, dies zu tun, besteht darin, die Vorgänge vollständig zu verstehen.

Dies war das erste Mal, dass ich die Seite 'SQLAlchemy - Kaskaden' vollständig gelesen habe, siehe Links unten. Ich nahm an, dass SQLAlchemy die beste Wahl für mich sein würde (und es funktionierte, daran ist nichts auszusetzen), aber ich war naiv und verstand das Problem nicht ganz.

Aus der SQLAlchemy -Dokumentation:

Ja, sicher, es hat alles gut funktioniert, aber bitte ersetzen Sie "in der Praxis" durch "nach vielen, vielen Stunden".

Links / Impressum

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