对于 SQLite 使用 SQLAlchemy WriteOnlyMapped 时，“删除”级联行为失效的解决方案

背景

WriteOnlyMapped 使用 lazy="write_only" 加载策略，该策略不在内存中加载集合的内容，只在写入时进行操作。这对于包含大量数据的集合特别有用，因为不会因加载所有数据而导致内存占用过高【1】。

问题描述

from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.orm import (
    DeclarativeBase,
    WriteOnlyMapped,
    Mapped,
    sessionmaker,
    relationship,
    mapped_column
)


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    addresses: WriteOnlyMapped['Address'] = relationship(
        cascade="all, delete-orphan", passive_deletes=True)

    def __repr__(self):
        return f'<User {self.id}>'


class Address(Base):
    __tablename__ = 'address'
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey('user.id', ondelete='cascade'))

    def __repr__(self):
        return f'<Address {self.id}>'


engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

user = User()
session.add(user)
user = session.get(User, 1)
address1 = Address(user_id=user.id)
address2 = Address(user_id=user.id)
session.add(address1)
session.add(address2)
session.commit()

print(session.query(User).all())
print(session.query(Address).all())

session.delete(user)
session.commit()

print('\ndelete user')
print(session.query(User).all())
print(session.query(Address).all())

上述代码时官方文档示例【1】的简化，期望的效果是删除一个 user 后，该 user 的 address 会被自动删除：

[<User 1>]
[<Address 1>, <Address 2>]

delete user
[]
[]

但是由于使用了 SQLite，实际是该 user 的 address 并没有被自动删除：

[<User 1>]
[<Address 1>, <Address 2>]

delete user
[]
[<Address 1>, <Address 2>]

解决方案

使用 SQLite 时，必须显式启用外键支持【2】【3】:

import re
import sqlite3
from sqlalchemy import Engine, event

def is_sqlite(db_uri):
    """检查数据库 URI 是否为 SQLite"""
    return bool(re.match(r'sqlite:///', db_uri))

# 每次建立新连接时执行 set_sqlite_pragma（仅在使用 SQLite 时）
if is_sqlite(db_uri):
    @event.listens_for(Engine, 'connect')
    def set_sqlite_pragma(dbapi_connection: sqlite3.Connection, _):
        """启用 SQLite 的外键约束"""
        cursor = dbapi_connection.cursor()
        cursor.execute('PRAGMA foreign_keys=ON')
        cursor.close()

完整示例代码

import re
import sqlite3
from sqlalchemy import (
    ForeignKey,
    Engine,
    event,
    create_engine
)
from sqlalchemy.orm import (
    DeclarativeBase,
    WriteOnlyMapped,
    Mapped,
    sessionmaker,
    relationship,
    mapped_column
)


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    addresses: WriteOnlyMapped['Address'] = relationship(
        cascade="all, delete-orphan", passive_deletes=True)

    def __repr__(self):
        return f'<User {self.id}>'


class Address(Base):
    __tablename__ = 'address'
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey('user.id', ondelete='cascade'))

    def __repr__(self):
        return f'<Address {self.id}>'


def is_sqlite(db_uri):
    """检查数据库 URI 是否为 SQLite"""
    return bool(re.match(r'sqlite:///', db_uri))

db_uri = 'sqlite:///:memory:'
engine = create_engine(db_uri)

# 每次建立新连接时执行 set_sqlite_pragma（仅在使用 SQLite 时）
if is_sqlite(db_uri):
    @event.listens_for(Engine, 'connect')
    def set_sqlite_pragma(dbapi_connection: sqlite3.Connection, _):
        """启用 SQLite 的外键约束"""
        cursor = dbapi_connection.cursor()
        cursor.execute('PRAGMA foreign_keys=ON')
        cursor.close()

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

user = User()
session.add(user)
user = session.get(User, 1)
address1 = Address(user_id=user.id)
address2 = Address(user_id=user.id)
session.add(address1)
session.add(address2)
session.commit()

print(session.query(User).all())
print(session.query(Address).all())

session.delete(user)
session.commit()

print('\ndelete user')
print(session.query(User).all())
print(session.query(Address).all())

完整示例代码（Flask-SQLAlchemy）

import re
import sqlite3
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import (
    ForeignKey,
    Engine,
    event
)
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    WriteOnlyMapped,
    relationship,
    mapped_column
)


class Base(DeclarativeBase):
    pass


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(model_class=Base)
db.init_app(app)


class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    addresses: WriteOnlyMapped['Address'] = relationship(
        cascade="all, delete-orphan", passive_deletes=True)

    def __repr__(self):
        return f'<User {self.id}>'


class Address(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey('user.id', ondelete='cascade'))

    def __repr__(self):
        return f'<Address {self.id}>'


def is_sqlite(db_uri):
    """检查数据库 URI 是否为 SQLite"""
    return bool(re.match(r'sqlite:///', db_uri))


# 每次建立新连接时执行 set_sqlite_pragma（仅在使用 SQLite 时）
if is_sqlite(app.config['SQLALCHEMY_DATABASE_URI']):
    @event.listens_for(Engine, 'connect')
    def set_sqlite_pragma(dbapi_connection: sqlite3.Connection, _):
        """启用 SQLite 的外键约束"""
        cursor = dbapi_connection.cursor()
        cursor.execute('PRAGMA foreign_keys=ON')
        cursor.close()


with app.app_context():
    db.create_all()

    user = User()
    db.session.add(user)
    user = db.session.get(User, 1)
    address1 = Address(user_id=user.id)
    address2 = Address(user_id=user.id)
    db.session.add(address1)
    db.session.add(address2)
    db.session.commit()

    print(db.session.query(User).all())
    print(db.session.query(Address).all())

    db.session.delete(user)
    db.session.commit()

    print('\ndelete user')
    print(db.session.query(User).all())
    print(db.session.query(Address).all())