如何在sqlalchemy上映射一对多关系

Question

我正在学习烧瓶+sqlalchemy，我在ORM方面遇到了一些问题。我有一个带有class House和class Member的模型.py。

class House:
def __init__(self, members: typing.Set['Member'] = None) -> None:
if not patients:
self.member = set()
else:
self.member = set(member)
def add_member(self, person: 'Patient'):
self.patients.add(person)
def remove_member(self, patient: 'Member'):
self.member.remove(patient)
class Member:
def __init__(self, first_name: str, email: str, phone: str, age: int, gender: str, last_name=None) -> None:
self.first_name = first_name
self.last_name = last_name
self.email = email
self.phone = phone
self.age = age
self.gender = gender
def __hash__(self) -> int:
return hash(self.email)
def __eq__(self, other):
if isinstance(other, Patient):
return self.email == other.email
return NotImplemented

在orm.py上，我有：

house = Table(
'house', metadata,
Column('id', INTEGER, primary_key=True, autoincrement=True)
)
member = Table(
'patient', metadata,
Column('id', INTEGER, primary_key=True, autoincrement=True),
Column('first_name', String(255)),
Column('last_name', String(255)),
Column('email', String(255)),
Column('phone', String(255)),
Column('age', INTEGER),
Column('gender', String(255)),
member_allocation = Table(
'patient_allocation', metadata,
Column('id', INTEGER, primary_key=True, autoincrement=True),
Column('member_id', ForeignKey('member.id')),
Column('house_id', ForeignKey('house.id')),
)
def start_mappers():
member_mapper = mapper(model.Member,member)
house_mapper = mapper(
model.House,
house,
properties={
'members': relationship(
member_mapper,
secondary=member_allocation,
collection_class=set,
backref=backref('house')),
})

一个家庭可以有多个成员，一个成员只能有一个家庭。我觉得我不需要member_allocation表。我觉得在member表上有一个house_id作为外键('house.id'(就足够了，member_mapper必须是类似于的东西

member_mapper = mapper(
model.Member,
member,
properties={'house': relationship(house_mapper)
})

但如果我理解正确的话，properties字典上的键(上面代码中的house(必须是要映射的类的属性。在我的例子中，house不是member类的属性。

我做错了什么？

Answer 1

backref属性members和house都应该由SQLAlchemy创建和填充。

你是对的，如果每个成员只能有一个房子，那么你就不需要关联表(因为它是一个2many(。你需要那张桌子放很多人。您应该能够这样做(house_id列移动到成员表(：

def start_mappers():
member_mapper = mapper(model.Member,member)
house_mapper = mapper(model.House,
house,
properties={
'members': relationship(
member_mapper,
collection_class=set,
backref=backref('house')),
})