我在SQL Alchemy中的关系有什么问题？ [英] What is wrong with my relationships in SQL Alchemy?

问题描述

我正在结合使用SQLAlchemy和Flask为我的应用程序创建关系。我最近重新编写了关系，无论更改什么，我都会不断收到错误：

I am using SQLAlchemy with Flask to create relationships for my application. I recently rewrote the relationships, and, no matter what I change, I keep getting the error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between 
parent/child tables on relationship CurriculumVersion.enrollments - there are 
no foreign keys linking these tables.  Ensure that referencing columns are 
associated with a ForeignKey or ForeignKeyConstraint, or specify
a 'primaryjoin' expression.

在我的模型上：

class User(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    tf_login = db.Column(db.String(255), unique=True, nullable=False) # can assume is an email
    password = db.Column(db.String(120), nullable=False)
    salt = db.Column(db.String(80))
    role = db.Column(db.String(80)) # for later when have different permission types
    zoho_contactid = db.Column(db.String(20), unique=True, nullable=False)
    created_asof = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')
    firstname = db.Column(db.String(80))
    lastname = db.Column(db.String(80))


    def __repr__(self):
        return '#%d tf_login: %s, First Name: %s Last Name: %s created_asof %s' % (self.id, self.tf_login, self.firstname, self.lastname, self.created_asof)

    def __getstate__(self):
        return {
            'id': self.id,
            'tf_login': self.tf_login,
            'firstname': self.firstname,
            'lastname': self.lastname,
            'role': self.role,
            'created_asof': self.created_asof,
        }

    @classmethod
    def load_current_user(cls, apply_timeout=True):
        data = get_current_user_data(apply_timeout)
        if not data:
            return None
        return cls.query.filter(cls.email==data['email']).one()
        return '#%d Course Name: %s, Course Version: %s, Closing Date: %s' %(self.id, self.course_name, self.course_version, self.closing_date)

class Enrollment(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    version_id = db.Column(db.Integer, db.ForeignKey('curriculumversion.id'))
    cohort_id = db.Column(db.Integer, db.ForeignKey('cohort.id'))

    def __repr__(self):
        return '#%d User ID: %d Version ID: %d, Cohort ID: %d' %(self.id, self.user_id, self.version_id, self.cohort_id)

class Cohort(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    days_to_completion = db.Column(db.String(20))
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
    enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')

    def __repr__(self):
        return '#%d Days To Completion: %s' %(self.id, self.days_to_completion)


class CurriculumVersion(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    version_number = db.Column(db.String(6))
    date_implemented = db.Column(db.DateTime)
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
    enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')

    def __repr__(self):
        return '#%d Version Number: %s, Date Implemented: %s' %(self.id, self.version_number, self.date_implemented)

class Course(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    course_code = db.Column(db.String(20))
    course_name = db.Column(db.String(50))
    versions = db.relationship('CurriculumVersion', backref='version', lazy='dynamic')
    cohorts = db.relationship('Cohort', backref='cohort', lazy='dynamic')


    def __repr__(self):
        return '#%d Course Code: %s, Course Name: %s' %(self.id, self.course_code, self.course_name)

任何帮助将不胜感激！

推荐答案

此错误：

Could not determine join condition between parent/child tables on relationship CurriculumVersion.enrollments 

表示SQLAlchemy在注册中找不到合适的列用作关系中的外键。

means that SQLAlchemy could not find a proper column in Enrollments to use as the foreign key in the relationship.

您定义了外键，但是使用了错误的表名。创建表时，Flask-SQLAlchemy将 CamelCase 类转换为 camel_case ，因此您需要更改以下内容：

You defined the foreign key, but you used an incorrect table name. Flask-SQLAlchemy converts CamelCase classes to camel_case when creating the table, so you need to change this:

class Enrollment(db.Model, AuthUser):
    # ...
    version_id = db.Column(db.Integer, db.ForeignKey('curriculumversion.id'))
    #...

class Enrollment(db.Model, AuthUser):
    # ...
    version_id = db.Column(db.Integer, db.ForeignKey('curriculum_version.id'))
    #...

或者，您可以使用 __ tablename __ 属性覆盖Flask-SQLAlchemy使用的默认命名约定。

Alternatively you can use the __tablename__ attribute to override the default naming convention used by Flask-SQLAlchemy.

这篇关于我在SQL Alchemy中的关系有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！