SQLAlchemy: usuwanie kaskadowe

Musi brakować czegoś trywialnego w opcjach kaskadowych SQLAlchemy, ponieważ nie mogę uzyskać prostego usuwania kaskadowego, aby działał poprawnie - jeśli element nadrzędny jest usunięty, elementy podrzędne pozostają z nullkluczami obcymi.

Umieściłem tutaj zwięzły przypadek testowy:

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

Wynik:

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

Między Rodzicem a Dzieckiem istnieje prosta relacja jeden do wielu. Skrypt tworzy rodzica, dodaje troje dzieci, a następnie zatwierdza. Następnie usuwa rodzica, ale dzieci pozostają. Czemu? Jak sprawić, by dzieci były usuwane kaskadowo?

Problem polega na tym, że sqlalchemy uważa Childza rodzica, ponieważ to właśnie tam zdefiniowałeś swój związek (oczywiście nie obchodzi go, że nazwałeś go „dzieckiem”).

Jeśli Parentzamiast tego zdefiniujesz relację w klasie, zadziała:

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

(uwaga "Child"jako ciąg znaków: jest to dozwolone w przypadku używania stylu deklaratywnego, dzięki czemu można odwołać się do klasy, która nie została jeszcze zdefiniowana)

Możesz również chcieć dodać delete-orphan( deletepowoduje usunięcie dzieci, gdy rodzic zostanie usunięty, delete-orphanusuwa również wszystkie dzieci, które zostały „usunięte” z rodzica, nawet jeśli rodzic nie został usunięty)

EDYCJA: właśnie się dowiedziałem: jeśli naprawdę chcesz zdefiniować relację w Childklasie, możesz to zrobić, ale będziesz musiał zdefiniować kaskadę w odwołaniu wstecznym (tworząc jawnie odwołanie wsteczne), na przykład:

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

(sugerując from sqlalchemy.orm import backref)

Odpowiedź @ Stevena jest dobra, gdy usuwasz, session.delete()co nigdy się nie zdarza w moim przypadku. Zauważyłem, że przez większość czasu kasuję przez session.query().filter().delete()(co nie umieszcza elementów w pamięci i usuwa bezpośrednio z db). Używanie tej metody sqlalchemy cascade='all, delete'nie działa. Jest jednak rozwiązanie: ON DELETE CASCADEprzez db (uwaga: nie wszystkie bazy danych to obsługują).

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)

Dość stary post, ale spędziłem nad tym godzinę lub dwie, więc chciałem podzielić się moim odkryciem, zwłaszcza, że ​​niektóre inne wymienione komentarze nie są całkiem poprawne.

TL; DR

Nadaj tabeli potomnej obcą tabelę lub zmodyfikuj istniejącą, dodając ondelete='CASCADE':

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

I jedną z następujących relacji:

a) To na stole nadrzędnym:

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

b) Lub to na stole podrzędnym:

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

Detale

Po pierwsze, pomimo tego, co mówi zaakceptowana odpowiedź, relacja rodzic / dziecko nie jest ustanawiana przez używanie relationship, lecz przez używanie ForeignKey. Możesz umieścić relationshiptabelę nadrzędną lub podrzędną i będzie działać dobrze. Chociaż najwyraźniej w tabelach podrzędnych musisz użyć backreffunkcji oprócz argumentu słowa kluczowego.

Opcja 1 (preferowana)

Po drugie, SqlAlchemy obsługuje dwa różne rodzaje kaskadowania. Pierwsza i ta, którą polecam, jest wbudowana w twoją bazę danych i zwykle ma formę ograniczenia deklaracji klucza obcego. W PostgreSQL wygląda to tak:

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

Oznacza to, że jeśli usuniesz rekord z parent_table, wszystkie odpowiadające mu wiersze child_tablezostaną usunięte przez bazę danych. Jest szybki i niezawodny i prawdopodobnie najlepszym rozwiązaniem. Konfigurujesz to w SqlAlchemy w ForeignKeynastępujący sposób (część definicji tabeli podrzędnej):

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

Jest ondelete='CASCADE'to część, która tworzy ON DELETE CASCADEna stole.

Mam cię!

Tutaj jest ważne zastrzeżenie. Zauważ, jak mam relationshipokreślone z passive_deletes=True? Jeśli tego nie masz, całość nie będzie działać. Dzieje się tak, ponieważ domyślnie podczas usuwania rekordu nadrzędnego SqlAlchemy robi coś naprawdę dziwnego. Ustawia klucze obce wszystkich wierszy podrzędnych na NULL. Więc jeśli usuniesz wiersz, z parent_tablektórego id= 5, to w zasadzie zostanie wykonany

UPDATE child_table SET parent_id = NULL WHERE parent_id = 5

Dlaczego tego chcesz, nie mam pojęcia. Zdziwiłbym się, gdyby wiele silników baz danych pozwoliło nawet ustawić prawidłowy klucz obcy na NULL, tworząc sierotę. Wydaje się, że to zły pomysł, ale może jest przypadek użycia. W każdym razie, jeśli pozwolisz SqlAlchemy to zrobić, uniemożliwisz bazie danych wyczyszczenie elementów podrzędnych za pomocą skonfigurowanego przez Ciebie pliku ON DELETE CASCADE. Dzieje się tak, ponieważ opiera się na tych kluczach obcych, aby wiedzieć, które wiersze potomne należy usunąć. Gdy SqlAlchemy ustawi je wszystkie na NULL, baza danych nie może ich usunąć. Ustawienie passive_deletes=Trueuniemożliwia SqlAlchemy NULLwyodrębnianie kluczy obcych.

Możesz przeczytać więcej o pasywnym usuwaniu w dokumentacji SqlAlchemy .

Opcja 2

Innym sposobem, w jaki możesz to zrobić, jest pozwolenie SqlAlchemy zrobić to za Ciebie. Jest to konfigurowane za pomocą cascadeargumentu relationship. Jeśli masz relację zdefiniowaną w tabeli nadrzędnej, wygląda to tak:

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

Jeśli związek dotyczy dziecka, robisz to w ten sposób:

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

Ponownie, jest to dziecko, więc musisz wywołać wywołaną metodę backrefi umieścić tam dane kaskady.

Dzięki temu po usunięciu wiersza nadrzędnego SqlAlchemy faktycznie uruchomi instrukcje usuwania, aby wyczyścić wiersze podrzędne. Prawdopodobnie nie będzie to tak wydajne, jak pozwolenie tej bazie danych na obsługę, jeśli więc nie polecam tego.

Oto dokumenty SqlAlchemy dotyczące obsługiwanych funkcji kaskadowych.

Steven ma rację, ponieważ musisz jawnie utworzyć odniesienie wsteczne, co powoduje, że kaskada jest nakładana na rodzica (w przeciwieństwie do stosowania do dziecka, jak w scenariuszu testowym).

Jednak zdefiniowanie relacji na dziecku NIE powoduje, że sqlalchemy uważa dziecko za rodzica. Nie ma znaczenia, gdzie zdefiniowano relację (dziecko czy rodzic), jest to klucz obcy, który łączy dwie tabele, które określają, która jest rodzicem, a która dzieckiem.

Jednak trzymanie się jednej konwencji ma sens i na podstawie odpowiedzi Stevena definiuję wszystkie moje relacje dziecka z rodzicem.

Miałem również problemy z dokumentacją, ale odkryłem, że same dokumenty są łatwiejsze niż podręcznik. Na przykład, jeśli zaimportujesz relację z sqlalchemy.orm i zrobisz help (relacja), da ci to wszystkie opcje, które możesz określić dla kaskady. Punkt oznaczający delete-orphanmówi:

jeśli zostanie wykryty element typu dziecka bez rodzica, zaznacz go do usunięcia.
Zauważ, że ta opcja zapobiega utrwalaniu oczekującego elementu klasy dziecka bez rodzica.

Zdaję sobie sprawę, że twoim problemem był bardziej sposób, w jaki dokumentacja definiowała relacje rodzic-dziecko. Wydawało się jednak, że możesz mieć również problem z opcjami kaskadowymi, ponieważ "all"obejmuje "delete". "delete-orphan"to jedyna opcja, której nie ma w programie "all".

Odpowiedź Stevena jest solidna. Chciałbym zwrócić uwagę na dodatkową implikację.

Używając relationship, robisz warstwę aplikacji (Flask) odpowiedzialną za integralność referencyjną. Oznacza to, że inne procesy, które uzyskują dostęp do bazy danych nie przez Flask, takie jak narzędzie bazy danych lub osoba łącząca się bezpośrednio z bazą danych, nie napotkają tych ograniczeń i mogą zmienić Twoje dane w sposób, który złamie logiczny model danych, nad którym tak ciężko pracowałeś. .

O ile to możliwe, stosuj ForeignKeypodejście opisane przez d512 i Alexa. Silnik bazy danych jest bardzo dobry w rzeczywistym egzekwowaniu ograniczeń (w sposób nieunikniony), więc jest to zdecydowanie najlepsza strategia utrzymania integralności danych. Jedynym przypadkiem, w którym musisz polegać na aplikacji do obsługi integralności danych, jest sytuacja, gdy baza danych nie może ich obsłużyć, np. Wersje SQLite, które nie obsługują kluczy obcych.

Jeśli musisz utworzyć dalsze powiązania między jednostkami, aby włączyć zachowania aplikacji, takie jak nawigacja w relacjach obiektu nadrzędnego-podrzędnego, użyj backrefw połączeniu z ForeignKey.

Odpowiedź Stevana jest doskonała. Ale jeśli nadal otrzymujesz błąd. Inną możliwą próbą byłoby -

http://vincentaudebert.github.io/python/sql/2015/10/09/cascade-delete-sqlalchemy/

Skopiowano z linku-

Szybka wskazówka, jeśli masz problemy z zależnością klucza obcego, nawet jeśli w modelach określono usuwanie kaskadowe.

Używając SQLAlchemy, aby określić usuwanie kaskadowe, które powinieneś mieć cascade='all, delete'w tabeli nadrzędnej. Ok, ale wtedy, gdy wykonasz coś takiego:

session.query(models.yourmodule.YourParentTable).filter(conditions).delete()

W rzeczywistości wywołuje błąd dotyczący klucza obcego używanego w tabelach podrzędnych.

Rozwiązanie wykorzystałem do odpytania obiektu, a następnie go usunąłem:

session = models.DBSession()
your_db_object = session.query(models.yourmodule.YourParentTable).filter(conditions).first()
if your_db_object is not None:
    session.delete(your_db_object)

Powinno to usunąć Twój rekord nadrzędny ORAZ wszystkie powiązane z nim elementy podrzędne.

Odpowiedź Alexa Okrushko prawie działała dla mnie najlepiej. Użyto ondelete = 'CASCADE' i passive_deletes = True łącznie. Ale musiałem zrobić coś więcej, aby działał w sqlite.

Base = declarative_base()
ROOM_TABLE = "roomdata"
FURNITURE_TABLE = "furnituredata"

class DBFurniture(Base):
    __tablename__ = FURNITURE_TABLE
    id = Column(Integer, primary_key=True)
    room_id = Column(Integer, ForeignKey('roomdata.id', ondelete='CASCADE'))


class DBRoom(Base):
    __tablename__ = ROOM_TABLE
    id = Column(Integer, primary_key=True)
    furniture = relationship("DBFurniture", backref="room", passive_deletes=True)

Pamiętaj, aby dodać ten kod, aby upewnić się, że działa on dla sqlite.

from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection

@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    if isinstance(dbapi_connection, SQLite3Connection):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON;")
        cursor.close()

Skradzione stąd: język wyrażeń SQLAlchemy i SQLite podczas kaskady usuwania

TLDR: Jeśli powyższe rozwiązania nie działają, spróbuj dodać wartość nullable = False do kolumny.

Chciałbym tutaj dodać małą uwagę dla niektórych osób, które mogą nie mieć funkcji kaskadowej do pracy z istniejącymi rozwiązaniami (które są świetne). Główna różnica między moją pracą a przykładem polegała na tym, że użyłem automapy. Nie wiem dokładnie, jak mogłoby to wpływać na konfigurację kaskad, ale chcę zauważyć, że go użyłem. Pracuję również z bazą danych SQLite.

Wypróbowałem każde opisane tutaj rozwiązanie, ale wiersze w mojej tabeli podrzędnej nadal miały klucz obcy ustawiony na null, gdy wiersz nadrzędny został usunięty. Próbowałem wszystkich rozwiązań tutaj bez skutku. Jednak kaskada zadziałała, gdy ustawiłem kolumnę podrzędną z kluczem obcym na wartość null = False.

Na stole podrzędnym dodałem:

Column('parent_id', Integer(), ForeignKey('parent.id', ondelete="CASCADE"), nullable=False)
Child.parent = relationship("parent", backref=backref("children", passive_deletes=True)

W tej konfiguracji kaskada działała zgodnie z oczekiwaniami.