SQLAlchemy Inheritance Inheritance of rapid mass deletion of child objects

Question

Consider the following SQLAlchemy mappings using federated inheritance:

from sqlalchemy import sa

class Location(Base):
    id = Column(Integer, primary_key=True)
    name = sa.Column(sa.String)
    type_ = sa.column(sa.String)

    __tablename__ = 'location'
    __mapper_args__ = {
        'polymorphic_identity': 'location',
        'polymorphic_on': type_,
    }

class Field(Location):
    id = Column(Integer, primary_key=True)
    size = sa.Column(sa.Float)

    __tablename__ = 'field'
    __mapper_args__ = {
        'polymorphic_identity': 'field',
    }
    __table_args__ = (
        sa.ForeignKeyConstraint(['id'], ['location.id']),
    )


session.query(Field).filter(Field.size < 5).delete()

, - . Field ( , query.delete() ). , session.delete(obj), ORM . n SQL ( n - ). , 100 000 , (, , ORM - ).

- SQLAlchemy , , Field Location, n SQL?

: PostgreSQL, db-agnostic.

: .

Answer 1

, . .

1. delete(). :

, SQL, mapper

SQL Engineer, ON DELETE CASCADE .

, , , .

, . :

class Location(Base):
    __tablename__ = 'location'
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR(30))
    type = Column(VARCHAR(30))

    __mapper_args__ = {
        'polymorphic_identity': 'location',
        'polymorphic_on'      : type,
    }

class Field(Location):
    __tablename__ = 'field'
    id = Column(INTEGER, ForeignKey('location.id', ondelete='cascade'), primary_key=True)
    size = Column(DECIMAL(20, 2))

    __mapper_args__ = {
        'polymorphic_identity': 'field',
    }

2. , Location, Field .

session.query(Location).filter(Location.id == 1).delete()

3. Field Location.

session.query(Field).filter(Field.size < 5).delete()

Field Location. Field , .

, Location Field.size < 5.

session.query(Location).filter(Field.size < 5).delete()

session.query(Location).outerjoin(Field, Location.id == Field.id).filter(Field.size < 5).delete()

.

:

statment = delete(Field, prefixes=[Location.__tablename__]).where(Field.size == 1)
session.execute(statment)

sql DELETE location FROM location JOIN field ON location.id = field.id WHERE field.size < 5