SQLAlchemy delete doesn`t cascade SQLite

Question

I have user model and computers model.

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


engine = create_engine(
    "sqlite:///./database.db",
    echo=True,
)
Session = sessionmaker(bind=engine)
Base = declarative_base()

class UserOrm(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(nullable=True, default="")
    age: Mapped[int] = mapped_column(nullable=True, default=0)
    computers = relationship(
        "ComputerOrm",
        back_populates="host",
        cascade="all, delete",
        passive_deletes=True,
    )


class ComputerOrm(Base):
    __tablename__ = "computers"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str]
    host_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    host = relationship(UserOrm, back_populates="computers")

Base.metadata.create_all(engine)

If I execute the code:

with Session() as session:
    session.query(UserOrm).filter_by(id=some_id).delete()
    session.commit()

user deleted, but user`s computers does not delete.

SQLAlchemy echo:

2023-12-23 17:12:21,384 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-23 17:12:21,390 INFO sqlalchemy.engine.Engine DELETE FROM users WHERE users.id = ?
2023-12-23 17:12:21,392 INFO sqlalchemy.engine.Engine [generated in 0.00172s] (1,)

If I exec SQL script in DB Browser for SQLite all works. User and user`s computers deleted.

DELETE FROM users WHERE users.id = some_id;

SQL Create table scripts, generated by SQLAlchemy:

CREATE TABLE users (
    id INTEGER NOT NULL, 
    name VARCHAR, 
    age INTEGER, 
    PRIMARY KEY (id)
)

CREATE TABLE computers (
    id INTEGER NOT NULL, 
    name VARCHAR NOT NULL, 
    host_id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(host_id) REFERENCES users (id) ON DELETE CASCADE
)

How fix problem?

$ pip show sqlalchemy
Name: SQLAlchemy
Version: 2.0.23
...

python 3.12.1

SQLite 3.35.5

Windows 10 Pro 22H2 64-bit

What have I done

Check create table scripts.

Try execute SQL script from SQLAlchemy echo to DB Browser for SQLite.

Try more solution for my problem.

Try session.delete(user_orm).

Try more params combinations for relationship/ForeignKey.

https://docs.sqlalchemy.org/en/20/orm/cascades.html#using-foreign-key-on-delete-cascade-with-orm-relationships

Answer 1

Have a look on the SQLAlchemy documentation, how to switch on foreign_keys:

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()