SqlAlchemy 多对多数据建模

发布于 2024-12-17 04:10:23 字数 1210 浏览 0 评论 0原文

请帮助我在 SQLAlchemy 中对此进行建模。用户可以创建问题。一个问题可以有任意数量的选择(例如是、否、稍后、可能、不知道、明年、不适用)。我在问题和选择之间创建了一个映射器。如何在 SQLAlchemy 中对响应进行建模?

question_choices = Table('question_choices', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('question_id', Integer, ForeignKey('questions.id')),
    Column('choice_id', Integer, ForeignKey('choices.id'))
    )

class Choice(Base):
    __tablename__ = 'choices'
    id = Column(Integer, primary_key=True)
    value = Column(String(30), nullable=False)

class Question(Base):
    __tablename__ = 'questions'
    id = Column(Integer, primary_key=True)
    title   = Column(String(100))
    created = Column(DateTime)

    choices = relationship('Choice', secondary=question_choices)

class questionResponse(Base):
    """A friend's response to a question"""
    __tablename__ = 'question_responses'
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, ForeignKey('questions.id'))
    choice_id = Column(Integer, ForeignKey('choices.id'))
    user_id = Column(Integer, ForeignKey('users.id'))
    created = Column(DateTime)

问题响应模型未标准化。 Question_id 和listing_id 重复。我在映射器表中没有关系。我希望能够计算给定问题的回复数。

Please help me model this in SQLAlchemy. A user can create a question. A question can have any number of choices (E.g. YES, NO, LATER, MAY BE, DONT KNOW, NEXT YEAR, NOT APPLICABLE). I have created a mapper between questions and choices. How do I model the responses in SQLAlchemy?

question_choices = Table('question_choices', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('question_id', Integer, ForeignKey('questions.id')),
    Column('choice_id', Integer, ForeignKey('choices.id'))
    )

class Choice(Base):
    __tablename__ = 'choices'
    id = Column(Integer, primary_key=True)
    value = Column(String(30), nullable=False)

class Question(Base):
    __tablename__ = 'questions'
    id = Column(Integer, primary_key=True)
    title   = Column(String(100))
    created = Column(DateTime)

    choices = relationship('Choice', secondary=question_choices)

class questionResponse(Base):
    """A friend's response to a question"""
    __tablename__ = 'question_responses'
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, ForeignKey('questions.id'))
    choice_id = Column(Integer, ForeignKey('choices.id'))
    user_id = Column(Integer, ForeignKey('users.id'))
    created = Column(DateTime)

The questionResponse model is not normalized. Question_id and listing_id are repeated. I do not have a relationship in the mapper table. I want to be able to count the responses for a given question.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

听风念你 2024-12-24 04:10:23

您的 QuestionResponse 映射器已经相当不错了,但它不限制未配置的选项的答案:因此,如果LATER 不允许回答问题你愿意嫁给我吗?,数据库对此没有限制。

一个解决方案是向 QuestionResponse 添加一个两列外键约束:

class QuestionResponse(Base):
    """A friend's response to a question"""
    __tablename__ = 'question_responses'
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, ForeignKey('questions.id'))
    choice_id = Column(Integer, ForeignKey('choices.id'))
    # ...
    __table_args__ = (
            ForeignKeyConstraint(['question_id', 'choice_id'], ['question_choices.question_id', 'question_choices.choice_id']),
            )

替代方案(更规范化的数据库模型)是仅将 FK 定义为 question_choices.id

class QuestionResponse(Base):
    """A friend's response to a question"""
    __tablename__ = 'question_responses'
    id = Column(Integer, primary_key=True)
    question_choice_id = Column(Integer, ForeignKey('question_choices.id'))

edit-1:在这种情况下,您可以在 Question 和 QuestionResponse 之间定义如下关系,这将为您提供计数还有:

class Question(Base):
    # ....
    answers = relationship("QuestionResponse", 
        primaryjoin="Question.id==question_choices.c.question_id",
        secondary=question_choices,
        secondaryjoin="question_choices.c.id==QuestionResponse.question_choice_id",
        backref="question",
        )

在任何情况下你都可以想要将 UniqueConstraint 添加到 question_choices 表的 (question_id, choice_id) 列上。


现在,为了对响应进行计数,您可以添加 QuestionQuestionResponse 之间的关系并返回 len(answers),或者只创建一个查询基于问题的属性:

class Question(Base):
    # ...
    answer_count = column_property(
                select([func.count(QuestionResponse.__table__.c.id)]).
                where(question_choices.c.question_id==id).
                where(question_choices.c.id==QuestionResponse.__table__.c.question_choice_id)
            )

Your mapper for QuestionResponse is pretty good already, but it does not restrict an answer of the choice that is not configured: so if LATER is not allowed answer for a question Will you marry me?, the database does not restrict this.

One solution to this would be to also add a two-column foreign key constraint to the QuestionResponse:

class QuestionResponse(Base):
    """A friend's response to a question"""
    __tablename__ = 'question_responses'
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, ForeignKey('questions.id'))
    choice_id = Column(Integer, ForeignKey('choices.id'))
    # ...
    __table_args__ = (
            ForeignKeyConstraint(['question_id', 'choice_id'], ['question_choices.question_id', 'question_choices.choice_id']),
            )

Alternative (more normalized DB model) is to define the FK only to the question_choices.id:

class QuestionResponse(Base):
    """A friend's response to a question"""
    __tablename__ = 'question_responses'
    id = Column(Integer, primary_key=True)
    question_choice_id = Column(Integer, ForeignKey('question_choices.id'))

edit-1: In this case you can have a relationship between Question and QuestionResponse defined like below, which will provide you with count as well:

class Question(Base):
    # ....
    answers = relationship("QuestionResponse", 
        primaryjoin="Question.id==question_choices.c.question_id",
        secondary=question_choices,
        secondaryjoin="question_choices.c.id==QuestionResponse.question_choice_id",
        backref="question",
        )

In any case you might want to add a UniqueConstraint to the question_choices table on columns (question_id, choice_id).


Now, in order to count responses, you either add the relationship between Question and QuestionResponse and return len(answers) or you just create a query-based property on Question:

class Question(Base):
    # ...
    answer_count = column_property(
                select([func.count(QuestionResponse.__table__.c.id)]).
                where(question_choices.c.question_id==id).
                where(question_choices.c.id==QuestionResponse.__table__.c.question_choice_id)
            )
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文