How to map one-to-many relationship on sqlalchemy-CodePudding

I am learning flask sqlalchemy and I am having some trouble with the ORM. I have a model.py that have a class House and a class Member.

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

On orm.py I have:

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')),
        })

One house can have many members and one member can have only one house. I feel like I don't need the member_allocation table. I fell like simple having a house_id as a foreignkey('house.id') on the member table would be enough and that the member_mapper would have to be something like:

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

But if I understood right, the key (house on the code above) on the properties dictionary has to be an attribute of the class being mapped. And in my case house is not an attribute of the member class.

What am I getting wrong here?

CodePudding user response：

The backref properties, members and house should both be created and populated by SQLAlchemy.

You're right, if each member can only have one house then you don't need the association table (since its one2many). You need that table for many2many. You should be able to do something like this (with house_id column moved to members table):

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')),
        })