python SQLAlchemy 继承

SQLAlchemy Inheritance

提问by Noah

I'm a bit confused about inheritance under sqlalchemy, to the point where I'm not even sure what type of inheritance (single table, joined table, concrete) I should be using here. I've got a base class with some information that's shared amongst the subclasses, and some data that are completely separate. Sometimes, I'll want data from all the classes, and sometimes only from the subclasses. Here's an example:

我对 sqlalchemy 下的继承有点困惑，以至于我什至不确定我应该在这里使用什么类型的继承（单表、连接表、具体）。我有一个基类，其中包含在子类之间共享的一些信息，以及一些完全独立的数据。有时，我需要来自所有类的数据，有时只需要来自子类的数据。下面是一个例子：

class Building:
    def __init__(self, x, y):
        self.x = x
        self.y = y

class Commercial(Building):
    def __init__(self, x, y, business):
        Building.__init__(self, x, y)
        self.business = business

class Residential(Building):
    def __init__(self, x, y, numResidents):
        Building.__init__(self, x, y, layer)
        self.numResidents = numResidents

How would I convert this to SQLAlchemy using declarative? How, then, would I query which buildings are within x>5and y>3? Or which Residential buildings have only 1 resident?

我将如何使用声明式将其转换为 SQLAlchemy？那么，我将如何查询哪些建筑物在x>5和内y>3？或者哪些住宅楼只有1名居民？

回答by Ants Aasma

Choosing how to represent the inheritance is mostly a database design issue. For performance single table inheritance is usually best. From a good database design point of view, joined table inheritance is better. Joined table inheritance enables you to have foreign keys to subclasses enforced by the database, it's a lot simpler to have non-null constraints for subclass fields. Concrete table inheritance is kind of worst of both worlds.

选择如何表示继承主要是一个数据库设计问题。对于性能单表继承通常是最好的。从好的数据库设计的角度来看，联表继承更好。连接表继承使您可以拥有由数据库强制执行的子类的外键，为子类字段设置非空约束要简单得多。具体的表继承是两全其美的。

Single table inheritance setup with declarative looks like this:

声明式的单表继承设置如下所示：

class Building(Base):
    __tablename__ = 'building'
    id = Column(Integer, primary_key=True)
    building_type = Column(String(32), nullable=False)
    x = Column(Float, nullable=False)
    y = Column(Float, nullable=False)
    __mapper_args__ = {'polymorphic_on': building_type}

class Commercial(Building):
    __mapper_args__ = {'polymorphic_identity': 'commercial'}
    business = Column(String(50))

class Residential(Building):
    __mapper_args__ = {'polymorphic_identity': 'residential'}
    num_residents = Column(Integer)

To make it joined table inheritance, you'll need to add

要使其加入表继承，您需要添加

__tablename__ = 'commercial'
id = Column(None, ForeignKey('building.id'), primary_key=True)

to the subclasses.

到子类。

Querying is mostly the same with both approaches:

两种方法的查询大致相同：

# buildings that are within x>5 and y>3
session.query(Building).filter((Building.x > 5) & (Building.y > 3))
# Residential buildings that have only 1 resident
session.query(Residential).filter(Residential.num_residents == 1)

To control which fields are loaded you can use the query.with_polymorphic()method.

要控制加载哪些字段，您可以使用该query.with_polymorphic()方法。

The most important thing to think about using inheritance for the datamapping, is whether you actually need inheritance or can do with aggregation. Inheritance will be a pain if you will ever need to change the type of an building, or your buildings can have both commercial and residential aspects. In those cases it's usually better to have the commercial and residential aspects as related objects.

考虑对数据映射使用继承的最重要的事情是您是否真的需要继承或可以使用聚合。如果您需要更改建筑物的类型，或者您的建筑物可以同时具有商业和住宅方面，那么继承将是一件痛苦的事情。在这些情况下，通常最好将商业和住宅方面作为相关对象。

回答by adam

Ants Aasma's solution is much more elegant, but if you are keeping your Class definitions separate from your table definitions intentionally, you need to map your classes to your tables with the mapper function. After you have defined your classes, you need to define your tables:

Ants Aasma 的解决方案要优雅得多，但如果您有意将类定义与表定义分开，则需要使用 mapper 函数将类映射到表。定义类后，需要定义表：

building = Table('building', metadata,
    Column('id', Integer, primary_key=True),
    Column('x', Integer),
    Column('y', Integer),
)
commercial = Table('commercial', metadata,
    Column('building_id', Integer, ForeignKey('building.id'), primary_key=True),
    Column('business', String(50)),
)
residential = Table('residential', metadata,
    Column('building_id', Integer, ForeignKey('building.id'), primary_key=True),
    Column('numResidents', Integer),
)

Then you can map the tables to the classes:

然后您可以将表映射到类：

mapper(Building, building)
mapper(Commercial, commercial, inherits=Building, polymorphic_identity='commercial')
mapper(Residential, residential, inherits=Building, polymorphic_identity='residential')

Then interact with the classes the exact same way Ants Aasma described.

然后以与 Ants Aasma 描述的完全相同的方式与类交互。