SQLAlchemy 声明表上的多对多关系 [英] SQLAlchemy many-to-many relationship on declarative tables

问题描述

我以声明方式定义了以下表格(非常简化的版本):

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.

如何使用 Answer 作为中间表为 Profile 和 Question 表定义多对多关系?

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

推荐答案

文档说我需要使用一个关联对象来做到这一点，但这让我很困惑，我无法理解工作.

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.

没错.Answer 类是您的关联对象，因为它映射到关联表answer".

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

如何定义多对多Profile 的关系和使用答案作为问题表中间表?

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

您在问题中提供的代码是正确的.它只需要有关 ORM 级别关系的附加信息:

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")

    ...

此外，您不应在答案的 init 函数中设置 profile_id 和 question_id 的值，因为 ORM 负责根据您对对象的关系属性的分配来相应地设置它们.

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.

这篇关于SQLAlchemy 声明表上的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！