SQLAlchemy: Использование Cascade Deletes для удаления связанных объектов

Хотя документация SQLAlchemy ORM очень хороша, иногда она запутанна и не содержит четких примеров, но, возможно, это только у меня. Что касается Cascade Delete, я нахожу следующий текст из документации SQLAlchemy умопомрачительным:

А? Что? А потом через некоторое время: WTF! Помогите! Но мне нужны только рецепты!

Но рецептов нет. Единственный способ убедиться, что мы делаем все правильно, - это читать документацию, читать документацию снова и создавать примеры, подтверждающие, что мы все поняли.

SQLAlchemy Cascade Deletes

Cascade Deletes в SQLAlchemy могут быть указаны в отношениях с помощью 'cascade'-опции. Опция 'cascade' имеет значение по умолчанию. Это означает, что если вы не использовали 'cascade'-option и хотите добавить 'cascade'-option в отношения, вы изменяете (!) поведение по умолчанию. Чтобы сохранить поведение по умолчанию, вы должны добавить 'save-update' и 'merge' к 'cascade'-option.

Далее в документации SQLAlchemy приводится пример Cascade Delete: Когда родитель удаляется, его дочерние элементы также должны быть удалены. В примере строка Cascade Delete :

cascade='all, delete'

Это очень запутанно, потому что 'delete' уже является частью 'all'. Это означает, что мы также можем написать:

cascade='all'

Но подождите, 'all' включает также 'refresh-expire' и 'expunge'. В документации есть предупреждение, что 'refresh-expire' изменяет поведение по умолчанию, а также 'expunge' изменяет поведение по умолчанию. Это означает, что нам не нужен 'all' , но возможно:

cascade='save-update, merge, delete'

Пример: Customer, Order (один-ко-многим).

Когда мы удаляем клиента, мы также хотим удалить все заказы этого клиента.

У нас есть две модели, Customer и Order. Customer-Order - это отношение "один-ко-многим". Заказчик может иметь ноль или более заказов, а заказ является частью только одного заказчика. Мы создаем двунаправленную связь. Клиент может ссылаться на заказы, используя Customer.orders , а заказ может ссылаться на клиента, используя Order.customer.

Обратите внимание, что в ForeignKey Order.customer_id я добавил атрибут 'nullable=False'. Причина в том, что нам не нужны заказы без клиента.

Мы заставим Cascade Delete работать, добавив следующую строку к отношению в Customer:

cascade='save-update, merge, delete'

Классы нашей модели:

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

Если предположить, что у клиента два заказа, то следующие SQL-выписки генерируются SQLAlchemy:

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

Вы ожидаете три SQL-выписки, первая из которых выбирает все заказы клиента. В моем тестовом сценарии я создал клиентов и заказы до DELETE-выражения, что означает, что объекты заказов находятся в сессии. SQLAlchemy знает об этом и решил, что нет необходимости получать их из базы данных.

Теперь изменим 'cascade'-опцию: удалим 'delete' и добавим 'delete-orphan', которая также удаляет все дочерние объекты, которые были удалены из родителя, даже если родитель не удален. Возможно, это нельзя использовать без 'delete'. Давайте посмотрим, что произойдет.

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

SQL-статьи и результат, сгенерированный 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 делает попытку удалить заказы от клиента, обновив ForeignKeys на NULL, но это не разрешено, поскольку ForeignKey включает 'nullable=False'.

Наконец, давайте создадим 'cascade'-опцию с 'delete' и 'delete-orphan':

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

SQL-выписки, созданные SQLAlchemy:

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

Это точно такой же случай, как и случай 'delete'-only.

Удаление объектов базы данных с использованием внешних ключей ON DELETE CASCADE

Многие движки баз данных, но не все, поддерживают действие 'ON DELETE CASCADE': Каждая строка в дочерней таблице, связанная с удаленной родительской строкой, также удаляется.
SQLAlchemy поддерживает это действие. В дополнение к описанной выше опции 'cascade' мы должны добавить:

• passive_deletes=True, к отношению в родителе
• ondelete='CASCADE', к внешнему ключу в дочерней структуре

Customer и Order классах модели после внесения этих изменений:

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

Если вы попробуете это сделать, вы можете обнаружить, что сгенерированный SQL точно такой же, как и выше. То есть два SQL-выражения, одно для удаления заказов и одно для удаления клиента (и, как вариант, третье SQL-выражение, если заказы не находятся в сессии). Причина в том, что удаляемые объекты уже находятся в текущей сессии. Из документации:

Мы также можем установить passive_deletes='all', Из документации SQLAlchemy :

Если я попробую это сделать, то получу следующее сообщение об ошибке, не понимаю почему:

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

В любом случае, чтобы проверить и увидеть, что происходит, мы можем либо сначала удалить объекты, либо начать новую сессию, выбрать клиента и удалить этого клиента. Я использовал последний вариант. Теперь генерируется только один SQL-статейт:

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

Конечно, это сработало не сразу. Оказывается, при использовании SQLite необходимо включить Foreign Keys . Когда вы забываете об этом, заказы клиента НЕ удаляются. Это происходит молча ... никаких ошибок или сообщений ... вздох.

SQLAlchemy Cascade Deletes или база данных Cascade Deletes?

Из документации по SQLAlchemy :

Отлично. Мне нравится эффективность. Я использую PostgreSQL и собираюсь использовать базу данных Cascade Delete там, где это возможно. Но в конечном итоге все зависит от вашего приложения. В веб-приложении обычно требуется минимальное количество удалений, так зачем беспокоиться?

Код

Если вы хотите попробовать сами, вот код, который я использовал:

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

И результат:

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

Резюме

Как часто бывает с SQLAlchemy: вам это было нужно, вы не вникали во все детали, это сработало, все довольны. Но когда вы изучаете его снова, вы начинаете читать больше, и делаете несколько примеров, чтобы дважды проверить, что все работает так, как ожидалось.

Cascade Deletes - сложный предмет. Вы должны принимать правильные решения для вашей модели данных, и единственный способ сделать это - полностью понять, что происходит.

Впервые я полностью прочитал страницу 'SQLAlchemy - Cascades', см. ссылки ниже. Я предполагал, что SQLAlchemy будет лучшим выбором для меня (и он работал, ничего плохого в этом нет), но я был наивен и не до конца понимал проблему.

Из документации SQLAlchemy :

Да, конечно, все получилось, но, пожалуйста, замените "на практике" на "после многих-многих часов".

Ссылки / кредиты

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