SAWarning: DELETE statement on table 'child' expected to delete 2 row(s); 1 were matched.  Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.

from sqlalchemy import Column, Integer, ForeignKey, create_engine, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship

Base = declarative_base()


class Parent(Base):
    __tablename__ = "parent"

    pk = Column(Integer, primary_key=True)

    children = relationship(
        "Child", back_populates="parent", cascade="all, delete-orphan"
    )


class Child(Base):
    __tablename__ = "child"

    pk = Column(Integer, primary_key=True)
    parent_pk = Column(Integer, ForeignKey("parent.pk"), nullable=False)

    parent = relationship("Parent", back_populates="children")


# Setup the engine + session + tables
engine = create_engine("postgresql:///transiter_test_db_2", use_batch_mode=True)
session_factory = sessionmaker(bind=engine, autoflush=False)
Session = scoped_session(session_factory)
session = Session()
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)


def insert_parent_and_two_children():
    # Put one parent with two children
    parent = Parent()
    child_1 = Child()
    child_2 = Child()
    parent.children = [child_1, child_2]
    session.merge(parent)
    session.commit()
    print("Number of children: ", len(session.query(Child).all()))


def delete_parent_using_orm():
    for parent in session.query(Parent):
        session.delete(parent)
    session.commit()
    print("Number of children: ", len(session.query(Child).all()))


def delete_children_using_native_query():
    sql = "DELETE FROM child WHERE pk =:pk"
    # Note, following the script then 3 and 4 are the PKs created
    result = session.execute(sql, [{"pk": 3}, {"pk": 4}])
    session.commit()
    print("Number of children deleted: ", result.rowcount)
    print("Number of children: ", len(session.query(Child).all()))


insert_parent_and_two_children()
delete_parent_using_orm() # note the warning

print('----')

insert_parent_and_two_children()
delete_children_using_native_query() # note the mismatching numbers