python批量删除字段,python – SQLAlchemy已加入继承快速批量删除Child对象

在尝试了一两个小时之后,我找到了一个没有太多代码的解决方案.然后我会重现它.

我检查了delete()的文件.有两句话：

This method does not work for joined inheritance mappings, since the multiple table deletes are not supported by SQL as well as that the join condition of an inheritance mapper is not automatically rendered

和

However the above SQL will not delete from the Engineer table, unless an ON DELETE CASCADE rule is established in the database to handle it.

Short story, do not use this method for joined inheritance mappings unless you have taken the additional steps to make this feasible.

因此,定义外键约束是必要的.像这样：

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()

但是,海报想要删除字段而不是位置.

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

这只删除了Field中没有行的行.因为Field是外表,所以它不能级联主表.

那么,现在我们应该做的是根据Field.size

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位置FROM位置JOIN字段ON location.id = field.id WHERE field.size