使用事务插入数据时发生NorReferencedTableError

Question

我正在构建一个简单的民意测验web应用程序，我需要在数据库中插入民意测验，目前民意测验以JSON结构表示，如下所示：

[
    {
        "title":"Do you like dogs?",
        "type":"select",
        "options":[
            "Yes",
            "No"
        ]
    },
    {
        "title":"What type of dogs do you like?",
        "type":"choices",
        "options":[
            "All",
            "None",
            "Labrador"
        ]
    },
    {
        "title":"Why do you like dogs?",
        "type":"text"
    },
    {
        "title":"How much do you like dogs?",
        "type":"range"
    }
]

这是我目前使用的代码：

@polls.route('/create', methods=['POST'])
def pollcreate_post():
    data = request.form
    r, l, valid = [], None, {'select', 'text', 'range', 'checkbox', 'choices', 'range', 'date'}
    options = { 'select', 'checkbox', 'choices'}
    for a, b in data.items():
        if a in valid:
            l = a
            r.append({'title':b, 'type':a})
        elif a.startswith('option') and l in options:
            r[-1]['options'] = [*r[-1].get('options', []), b]
    try:
        pollname = 'Polls Test Name'
        newpoll = Poll(pollname=pollname, polldate=datetime.now())
        newpoll.questions = []
        db.session.add(newpoll)
        for question in r:
            questiontype = question['type']
            questiontitle = question['title']
            questiontypeid = QuestionType.query.filter_by(name=questiontype).first()
            categoryid = Category.query.filter_by(name='Animals').first()
            newquestion = Question(questiontypeid=questiontypeid.questiontypeid, categoryid=categoryid.categoryid, title = questiontitle, required=False)
            newquestion.answers = []
            if questiontype in {'select', 'checkbox', 'choices'}:
                for qoption in question['options']:
                    newanswer = Answer(questionid=newquestion.questionid,name=qoption)
                    newquestion.answers.append(newanswer)
            newpoll.questions.append(newquestion)
        db.session.commit()
    except:
        db.session.rollback()
        raise
    return render_template('poll-create.html')

我不知道为什么，但当收到数据并插入答案时，会引发此异常：

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'PollQuestions.questionid' could not find table 'questions' with which to generate a foreign key to target column 'questionid'

在这里您可以找到我的模型：

pollquestions = Table('PollQuestions', Base.metadata,
Column('pollid', Integer, ForeignKey('polls.pollid'), primary_key=True),
Column('questionid', Integer, ForeignKey('questions.questionid'), primary_key=True))

class Poll(db.Model):
    __tablename__ = 'polls'
    
    pollid = Column(Integer, primary_key=True)
    pollname = Column(String(255))
    polldate = Column(Date)
    questions = relationship("Question", secondary=pollquestions)

class Question(db.Model):
    __tablename__ = 'questions'

    questionid = Column(Integer, primary_key=True)
    questiontypeid = Column(Integer, ForeignKey('QuestionTypes.questiontypeid'))
    categoryid = Column(Integer, ForeignKey('Categories.categoryid'))
    title = Column(String(255))
    required = Column(Boolean)
    polls = relationship("Poll", secondary=pollquestions)
    answers = relationship("Answer")
    polls = relationship("Poll")

class Answer(db.Model):
    __tablename__ = 'answers'

    answerid = Column(Integer, primary_key=True)
    questionid = Column(Integer, ForeignKey('Questions.questionid'))
    name = Column(String(255))

Answer 1

显然，您的Question类具有由数据库自动生成的questionid属性。创建新的Question对象后，其questionid属性立即为None。将对象添加到会话中不会改变该值，因为到目前为止，一切都发生在SQLAlchemy端

如果需要questionid的实际值，则必须执行db.session.flush()将INSERT语句发送到数据库并更新ORM对象。然后newquestion.questionid将不再是None，您可以像以前一样创建Answer对象

或者，您可以在Answers类中设置一个relationship，这样您就可以根据其对象而不是其questionid值来定义相关的Question