python - mssql - SQLAlchemy: eliminar en cascada

sqlalchemy python

Question

Debo faltar algo trivial con las opciones de cascada de SQLAlchemy porque no puedo hacer que una simple eliminación en cascada funcione correctamente: si se elimina un elemento primario, los secundarios persisten, con claves externas null .

He puesto un caso de prueba conciso aquí:

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key = True)

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key = True)
    parentid = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, cascade = "all,delete", backref = "children")

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()

parent = Parent()
parent.children.append(Child())
parent.children.append(Child())
parent.children.append(Child())

session.add(parent)
session.commit()

print "Before delete, children = {0}".format(session.query(Child).count())
print "Before delete, parent = {0}".format(session.query(Parent).count())

session.delete(parent)
session.commit()

print "After delete, children = {0}".format(session.query(Child).count())
print "After delete parent = {0}".format(session.query(Parent).count())

session.close()

Salida:

Before delete, children = 3
Before delete, parent = 1
After delete, children = 3
After delete parent = 0

Existe una relación simple, de uno a muchos, entre padres e hijos. El script crea un padre, agrega 3 hijos, y luego se compromete. A continuación, elimina el padre, pero los hijos persisten. ¿Por qué? ¿Cómo hago para que los niños eliminen en cascada?

Answer 1

El problema es que sqlalchemy considera a Child como el padre, porque ahí es donde definiste tu relación (no importa que lo llamaras "Child" por supuesto).

Si define la relación en la clase principal, funcionará:

children = relationship("Child", cascade="all,delete", backref="parent")

(tenga en cuenta "Child" como una cadena: esto se permite cuando se usa el estilo declarativo, de modo que puede hacer referencia a una clase que aún no está definida)

También es posible que desee agregar delete-orphan ( delete hace que se eliminen los hijos cuando se elimina el padre, delete-orphan también elimina a los hijos que fueron "eliminados" del padre, incluso si el padre no se elimina)

EDIT: acabo de descubrir: si realmente desea definir la relación en la clase secundaria , puede hacerlo, pero tendrá que definir la cascada en el backref (creando el backref explícitamente), así:

parent = relationship(Parent, backref=backref("children", cascade="all,delete"))

(implicando from sqlalchemy.orm import backref )

Answer 2

La respuesta de @ Steven es buena cuando se elimina a través de session.delete() que nunca sucede en mi caso. Noté que la mayoría de las veces borro a través de session.query().filter().delete() (que no pone elementos en la memoria y elimina directamente de db). El uso de este método sqlalchemy''s cascade=''all, delete'' no funciona. Sin embargo, hay una solución: ON DELETE CASCADE través de db (nota: no todas las bases de datos lo admiten).

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parents.id", ondelete=''CASCADE''))

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True)
    child = relationship(Child, backref="parent", passive_deletes=True)

Answer 3

La respuesta de Steven es sólida. Me gustaría señalar una implicación adicional.

Mediante el uso de relationship , está haciendo que la capa de aplicación (Flask) sea responsable de la integridad referencial. Eso significa que otros procesos que acceden a la base de datos no a través de Flask, como una utilidad de base de datos o una persona que se conecta directamente a la base de datos, no experimentarán esas restricciones y podrían cambiar sus datos de una manera que rompa el modelo de datos lógicos que tanto trabajó para diseñar. .

Siempre que sea posible, utilice el enfoque de ForeignKey descrito por d512 y Alex. El motor DB es muy bueno para hacer cumplir realmente las restricciones (de una manera inevitable), por lo que esta es, con mucho, la mejor estrategia para mantener la integridad de los datos. La única vez que necesita confiar en una aplicación para manejar la integridad de los datos es cuando la base de datos no puede manejarlos, por ejemplo, versiones de SQLite que no admiten claves externas.

Si necesita crear un vínculo adicional entre las entidades para habilitar los comportamientos de las aplicaciones, como navegar por las relaciones de objetos padre-hijo, use backref junto con ForeignKey .

Answer 4

Steven tiene razón en que necesita crear explícitamente la referencia, esto hace que la cascada se aplique al padre (en lugar de que se aplique al niño como en el escenario de prueba).

Sin embargo, la definición de la relación en el Niño NO hace que sqlalchemy considere al Hijo como el padre. No importa dónde se define la relación (hijo o padre), es la clave externa que vincula las dos tablas que determina cuál es el padre y cuál el hijo.

Sin embargo, tiene sentido atenerse a una convención y, en función de la respuesta de Steven, estoy definiendo todas las relaciones de mis hijos con los padres.

Answer 5

También luché con la documentación, pero descubrí que las cadenas de documentación tienden a ser más fáciles que el manual. Por ejemplo, si importa una relación desde sqlalchemy.orm y ayuda (relación), le dará todas las opciones que puede especificar para la cascada. La viñeta para "eliminar-huérfano" dice: "si se detecta un elemento del tipo del niño sin padre, márquelo para eliminarlo. Tenga en cuenta que esta opción evita que un elemento pendiente de la clase del niño sea persistente sin un padre presente".

Me doy cuenta de que su problema fue más con la documentación para definir las relaciones entre padres e hijos. Pero parecía que también podría tener un problema con las opciones en cascada, porque "todo" incluye "eliminar". "eliminar huérfano" es la única opción que no está incluida en "todos".

Answer 6

Una publicación bastante antigua, pero acabo de dedicar una o dos horas a esto, así que quería compartir mi descubrimiento, especialmente porque algunos de los otros comentarios enumerados no son del todo correctos.

TL; DR

Dale a la tabla secundaria un elemento externo o modifica el existente, agregando onedelete=''CASCADE'' :

parent_id = db.Column(db.Integer, db.ForeignKey(''parent.id'', ondelete=''CASCADE''))

Y una de las siguientes relaciones:

a) Esto en la tabla padre:

children = db.relationship(''Child'', backref=''parent'', passive_deletes=True)

b) O esto en la mesa infantil:

parent = db.relationship(''Parent'', backref=backref(''children'', passive_deletes=True))

Detalles

En primer lugar, a pesar de lo que dice la respuesta aceptada, la relación padre / hijo no se establece mediante el uso de relationship , se establece mediante ForeignKey . Puede poner la relationship en las tablas padre o hijo y funcionará bien. Aunque, aparentemente en las tablas secundarias, tiene que usar la función backref además del argumento de palabra clave.

Opción 1 (preferida)

En segundo lugar, SqlAlchemy admite dos tipos diferentes de cascada. El primero, y el que yo recomiendo, está integrado en su base de datos y usualmente toma la forma de una restricción en la declaración de clave externa. En PostgreSQL se ve así:

CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
REFERENCES parent_table(id) MATCH SIMPLE
ON DELETE CASCADE

Esto significa que cuando elimine un registro de parent_table , la base de datos eliminará todas las filas correspondientes en child_table . Es rápido y confiable y probablemente tu mejor apuesta. Lo configura en SqlAlchemy a través de ForeignKey esta manera (parte de la definición de la tabla secundaria):

parent_id = db.Column(db.Integer, db.ForeignKey(''parent.id'', ondelete=''CASCADE''))
parent = db.relationship(''Parent'', backref=backref(''children'', passive_deletes=True))

El ondelete=''CASCADE'' es la parte que crea el ON DELETE CASCADE en la tabla.

Gotcha!

Hay una advertencia importante aquí. Observe cómo tengo una relationship especificada con passive_deletes=True ? Si no tienes eso, la cosa entera no funcionará. Esto se debe a que, de forma predeterminada, al eliminar un registro principal, SqlAlchemy hace algo realmente extraño. Establece las claves externas de todas las filas secundarias en NULL . Entonces, si elimina una fila de parent_table donde id = 5, entonces básicamente se ejecutará

UPDATE child_table SET parent_id = NULL WHERE parent_id = 5

Por qué querrías esto, no tengo idea. Me sorprendería si muchos motores de base de datos le permitieran establecer una clave foránea válida en NULL , creando un huérfano. Parece una mala idea, pero tal vez hay un caso de uso. De todos modos, si permites que SqlAlchemy haga esto, evitarás que la base de datos pueda limpiar a los niños utilizando la ON DELETE CASCADE que configuraste. Esto se debe a que se basa en esas claves externas para saber qué filas secundarias se deben eliminar. Una vez que SqlAlchemy los ha establecido en NULL , la base de datos no puede eliminarlos. La configuración de passive_deletes=True evita que SqlAlchemy anule las claves externas.

Puedes leer más sobre las eliminaciones pasivas en los documentos de SqlAlchemy .

opcion 2

La otra forma de hacerlo es dejar que SqlAlchemy lo haga por usted. Esto se configura utilizando el argumento en cascade de la relationship . Si tiene la relación definida en la tabla principal, se ve así:

children = relationship(''Child'', cascade=''all,delete'', backref=''parent'')

Si la relación es con el niño, hazlo así:

parent = relationship(''Parent'', backref=backref(''children'', cascade=''all,delete''))

Nuevamente, este es el niño, así que tienes que llamar a un método llamado backref y poner los datos en cascada allí.

Con esto en su lugar, cuando eliminas una fila principal, SqlAlchemy ejecutará realmente las declaraciones de eliminación para que limpies las filas secundarias. Es probable que esto no sea tan eficiente como permitir que esta base de datos lo maneje, así que no lo recomiendo.

Aquí están los documentos de SqlAlchemy sobre las funciones en cascada que admite.