如何在SQLite中用SQLAlchemy实现级联删除？配置未生效问题求解

我查阅了SQLAlchemy文档中多个级联删除示例，但尝试的方法均未生效。以下是基于文档改编的示例代码，我使用back_populates替代了已被弃用的backref。

在代码的main部分，我预期删除包含条目（Item）的订单（Order）时，关联的条目也会被删除，但实际并未发生。显然我对表的配置存在误解，请问问题出在哪里？

# third party imports
from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy_utils import create_database, database_exists

Base = declarative_base()


class Order(Base):
    __tablename__ = "business_order"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    items = relationship(
        "Item", back_populates="order", cascade="all, delete, delete-orphan"
    )


class Item(Base):
    __tablename__ = "business_item"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    order_id = Column(Integer, ForeignKey("business_order.id"))
    order = relationship("Order", back_populates="items")


def get_session(url="sqlite:///:memory:", create_db=True):
    """Get a SQLAlchemy Session instance for input database URL.
    :param url:
      SQLAlchemy URL for database, described here:
        http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls.
    :param create_db:
      Boolean indicating whether to create database from scratch.
    :returns:
      Sqlalchemy Session instance.
    """
    # Create a sqlite in-memory database engine
    if not database_exists(url):
        if create_db:
            create_database(url)
        else:
            msg = (
                "Database does not exist, will not create without "
                "create_db turned on."
            )
            print(msg)
            return None

    connect_args = {}

    engine = create_engine(url, echo=False, connect_args=connect_args)
    Base.metadata.create_all(engine)

    # create a session object that we can use to insert and
    # extract information from the database
    Session = sessionmaker(bind=engine, autoflush=False)
    session = Session()

    return session


if __name__ == "__main__":
    sqlite_url = "sqlite:///test_sqlite.db"
    session = get_session(sqlite_url)
    order = Order(name="order1")
    session.add(order)
    item = Item(order_id=order.id, name="item1")
    session.add(item)
    session.commit()
    session.delete(order)  # should delete items too, right?
    session.commit()
    orders = session.query(Order).all()
    print(len(orders))  # this returns 0 as expected
    items = session.query(Item).all()
    print(len(items))  # this returns 1, why?

你的级联删除配置本身没有问题，核心问题出在没有通过ORM关系关联Order和Item对象，而是手动设置了order_id。SQLAlchemy的ORM级联操作依赖于内存中对象的关联关系，而非仅靠数据库外键。

具体问题点： #

创建Item时，你直接设置item = Item(order_id=order.id, name="item1")，此时Order对象虽已添加到session，但未提交前order.id是临时值，且ORM并未在内存中建立Order和Item的关联关系。由于ORM不知道两者的关联，删除Order时不会触发级联删除逻辑。

修复方式： #

通过ORM关系关联对象，而非手动设置外键ID，两种方法任选其一：

if __name__ == "__main__":
    sqlite_url = "sqlite:///test_sqlite.db"
    session = get_session(sqlite_url)
    order = Order(name="order1")
    
    # 方法1：将Item添加到Order的items集合
    item = Item(name="item1")
    order.items.append(item)
    session.add(order)
    session.commit()
    
    # 方法2：直接设置Item的order属性
    # item = Item(name="item1", order=order)
    # session.add(order)
    # session.add(item)
    # session.commit()

    session.delete(order)
    session.commit()
    orders = session.query(Order).all()
    print(len(orders))  # 输出0
    items = session.query(Item).all()
    print(len(items))  # 现在输出0，符合预期

额外方案：数据库级联删除 #

如果希望不依赖ORM内存关联，仅靠数据库层面实现级联删除，可以在定义外键时添加ondelete="CASCADE"：

order_id = Column(Integer, ForeignKey("business_order.id", ondelete="CASCADE"))

这种方式是数据库原生级联，即使ORM未关联对象，删除Order时数据库会自动删除关联的Item。建议同时使用ORM级联和数据库级联，兼顾内存操作和数据一致性。

内容的提问来源于stack exchange，提问作者M. Hearne