SQLalchemy 基于 Mixin 列的加入继承查询 [英] SQLalchemy Joined Inheritance Query Based on Mixin Column

问题描述

我有一个如下所示的声明性类结构:

I have a declarative class structure that looks like this:

class BaseClass(Base):
      Column A
      Column B

class Mixin(object):
      Column C

class ItemA(BaseClass):
      Column D

class ItemB(Mixin, BaseClass):
      pass

class ItemC(Mixin, BaseClass):
      Column E

有没有一种方法可以使用 with_polymorphic，这样我就可以在所有 Items 中基于 Column C 进行查询，而无需必须明确知道我拥有什么 Item?本质上类似于

Is there a way I can use with_polymorphic such that I can do a query based on Column C across all of the Items, without having to explicitly know what Items I have? Something like essentially

mixin_poly = with_polymorphic(base = BaseClass, classes = MixinClass.__subclasses__())

假设在导入时 MixinClass 的所有派生类都将在 with_polymorphic 声明之前导入.

Lets assume that at import time all of the derivatives of MixinClass would have been imported before the with_polymorphic is declared.

编辑请注意，我已经省略了连接表继承的样板文件，但假设它已正确设置，以便执行

Edit Note that I've left out the boilerplate for joined-table inheritance, but assume it's properly set up such that doing

poly_base = with_polymorphic(base = BaseClass, classes = '*')
session.query(poly_base).filter(BaseClass.a == value, ...)

按预期从 BaseClass 查询列.重点是能够以相同的方式查询继承Mixin类的子类中的公共列.

performs as expected for querying columns from BaseClass. The point is to be able to query common columns in the subclasses that inherit the Mixin class in the same manner.

推荐答案

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import inspect

Base = declarative_base()

class BaseClass(Base):
    __tablename__ = 'base'

    id = Column(Integer, primary_key=True)
    type = Column(String)
    a = Column(Integer)
    b = Column(Integer)
    __mapper_args__ = {"polymorphic_on": type}

class Mixin(object):
    c = Column(Integer)

class ItemA(BaseClass):
    __tablename__ = 'a'
    id = Column(ForeignKey('base.id'), primary_key=True)
    d = Column(Integer)
    __mapper_args__ = {"polymorphic_identity": 'a'}

class ItemB(Mixin, BaseClass):
    __tablename__ = 'b'
    id = Column(ForeignKey('base.id'), primary_key=True)
    __mapper_args__ = {"polymorphic_identity": 'b'}

class ItemC(Mixin, BaseClass):
    __tablename__ = 'c'
    id = Column(ForeignKey('base.id'), primary_key=True)
    e = Column(Integer)
    __mapper_args__ = {"polymorphic_identity": 'c'}

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

def magic_w_poly(base):
    insp = inspect(base)

    w_poly = []
    crit = []

    for mapper in insp.self_and_descendants:
        if "c" in mapper.c:
            w_poly.append(mapper)
            crit.append(mapper.c.c)
    w_col_c = with_polymorphic(base, w_poly)

    def comparator(value):
        return or_(
                    crit_elem == value
                    for crit_elem in crit
                )

    return w_col_c, comparator

s = Session(e)

w_col, comp = magic_w_poly(BaseClass)

print s.query(w_col).filter(comp(35))

查询末尾:

SELECT base.id AS base_id, base.type AS base_type, base.a AS base_a, base.b AS base_b, b.id AS b_id, b.c AS b_c, c.id AS c_id, c.c AS c_c, c.e AS c_e 
FROM base LEFT OUTER JOIN b ON base.id = b.id LEFT OUTER JOIN c ON base.id = c.id 
WHERE b.c = :c_1 OR c.c = :c_2

这篇关于SQLalchemy 基于 Mixin 列的加入继承查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！