SQLAlchemy many-to-many relationship on declarative tables

Question

I have the following tables defined declaratively (very simplified version):

class Profile(Base):
        __tablename__ = 'profile'

        id = Column(Integer, primary_key = True)
        name = Column(String(65), nullable = False)

        def __init__(self, name):
            self.name = name


class Question(Base):
    __tablename__ = 'question'

    id = Column(Integer, primary_key = True)
    description = Column(String(255), nullable = False)
    number = Column(Integer, nullable = False, unique = True)


    def __init__(self, description, number):
        self.description = description
        self.number = number



class Answer(Base):
    __tablename__ = 'answer'

    profile_id = Column(Integer, ForeignKey('profile.id'), primary_key = True)
    question_id = Column(Integer, ForeignKey('question.id'), primary_key = True)
    value = Column(Integer, nullable = False)


    def __init__(self, profile_id, question_id, value):
        self.profile_id = profile_id
        self.question_id = question_id
        self.value = value

Profile is linked to Question via a many-to-many relationship. In the linking table (Answer) I need to store a value for the answer.

The documentation says I need to use an association object to do this but it's confusing me and I can't get it to work.

How do I define the many-to-many relationship for the Profile and Question tables using Answer as the intermediary table?

Answer 1

The documentation says I need to use an association object to do this but it's confusing me and I can't get it to work.

That's right. And the Answer class is your association object as it maps to the association table 'answer'.

How do I define the many-to-many relationship for the Profile and Question tables using Answer as the intermediary table?

The code you've presented in your question is correct. It only needs additional information about relationships on the ORM level:

from sqlalchemy.orm import relationship

...

class Profile(Base):
    __tablename__ = 'profile'

    ...

    answers = relationship("Answer", backref="profile")

    ...


class Question(Base):
    __tablename__ = 'question'

    ...

    answers = relationship("Answer", backref="question")

    ...

Also, you shouldn't setup values for profile_id and question_id in your Answer's init function, because it's the ORM that's responsible for setting them accordingly based on you assignments to relationship attributes of your objects.

You may be interested in reading documentation for declarative, especially the part about configuring relationships. Reading about working with related objects may be helpful as well.