如何使用 SQLAlchemy 将多个类映射到单个表

Question

我有一个定义两个对象的域：Property和Address。他们有一对一的关系，每个Property都有一个Address。

我希望有一个包含Property和Address数据的表，以避免在查询Properties时进行不必要的连接：

create table properties
(
id integer primary key,
name varchar(50) not null,
address_city varchar(50) not null
)

并具有波纹管类定义：

class Property(object):
def __init__(self, id, name, address):
self.id = id
self.name = name
self.address = address
class Address(object):
def __init__(self, city):
self.city = city

如何配置 SQLAlchemy 映射器以支持这种结构？(我看到了继承配置，但它不适合我的情况)

Answer 1

虽然总的来说，我会投票支持创建单独的属性和地址关系，但使用合适的外键约束，您可以使用复合列类型将类映射到单个表：

In [2]: class Address:
...:     def __init__(self, city):
...:         self.city = city
...:     def __composite_values__(self):
...:         # NOTE: returns a 1-tuple
...:         return self.city,
...:     def __repr__(self):
...:         return "Address(city={!r})".format(self.city)
...:     def __eq__(self, other):
...:         return isinstance(other, Address) and self.city == other.city
...:     def __ne__(self, other):
...:         return not self.__eq__(other)
In [3]: class Property(Base):
...:     __tablename__ = "properties"
...:     id = Column(Integer, primary_key=True)
...:     name = Column(Unicode(50), nullable=False)
...:     address_city = Column(Unicode(50), nullable=False)
...:     address = composite(Address, address_city)
...: 
...:     def __init__(self, name, address):
...:         self.name = name
...:         self.address = address

使用上述类型和模型，您可以创建新属性：

In [5]: session.add(Property('Ye Olde House', Address('London')))
In [6]: session.commit()

和查询：

In [7]: session.query(Property).
...:     filter_by(address=Address('London')).
...:     first()
Out[7]: <__main__.Property at 0x7f02dd8892e8>
In [8]: _.address.city
Out[8]: 'London'

---

在即将发布的 Python 3.7 中，实现了 PEP 557 – 数据类，Address类可以定义为：

from dataclasses import dataclass, astuple
@dataclass
class Address:
city: str
def __composite_values__(self):
return astuple(self)