python – 所有列上的SQLAlchemy继承过滤器

发布时间：2020-12-20 13:53:04 所属栏目：Python 来源：网络整理

导读：所以我想对我的数据库模型的所有列使用表继承执行过滤器.我不确定这是否真的可行. 要开始使用,请使用SQLAlchemy Doc中稍微修改过的相同继承示例.我在这里省略了导入. class Employee(Base): __tablename__ = 'employee' id = Column(Integer,primary_key=Tru

所以我想对我的数据库模型的所有列使用表继承执行过滤器.我不确定这是否真的可行.

要开始使用,请使用SQLAlchemy Doc中稍微修改过的相同继承示例.我在这里省略了导入.

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer,primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

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

    @classmethod
    def get_all(cls,session,query):
        _filters = []
        for prop in class_mapper(cls).iterate_properties:
            if isinstance(prop,ColumnProperty):
                _col = prop.columns[0]
                _attr = getattr(cls,_cls.name)

                _filters.append(cast(_attr,String).match(query))

        result = session.query(cls)
        result = result.filter(or_(*_filters))
        return result.all()

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer,ForeignKey('employee.id'),primary_key=True)
    engineer_name = Column(String(30))
    foo = Column(String(10))

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

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer,primary_key=True)
    manager_name = Column(String(30))
    bar = Column(String(20))

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

现在让我们说我想查询所有Employee,其中一些字段与查询匹配.上面显示的get_all方法只会查询Employee类已知的列.

有没有办法在整个继承链的所有列中查询？

解决方法

它非常难看,但一种方法是找到从Employee继承的所有子类,然后将这些表连接起来并将它们的列添加到查询中.

如何获取子类：
https://stackoverflow.com/a/5883218/443900

没有测试过这个,但这样的事情应该有效.

@classmethod
def get_all(cls,query):
    _filters = []
    for prop in class_mapper(cls).iterate_properties:
        if isinstance(prop,ColumnProperty):
            _col = prop.columns[0]
            _attr = getattr(cls,_cls.name)

            _filters.append(cast(_attr,String).match(query))

    result = session.query(cls)
    result = result.filter(or_(*_filters))

    # get the subclasses
    subclasses = set()
    for child in cls.__subclasses__():
        if child not in subclasses:
            subclasses.add(child)
            # join the subclass
            result = result.outerjoin(child)
            # recurse to get the columns from the subclass
            result = subclass.get_all(session,result)

    # return a query,not a result to allow for the recursion. 
    # you might need to tweak this.
    return result

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!