SQLAlchemy Query Using Two Tables-CodePudding

I am developing a project on Flask and I am very new to SQLAlchemy. I have generated two tables, Users and Instructors, and there is a one-to-one relationship between them.

I am able to insert data to the tables, however, I can't query the Instructors table by filtering foreign key to the Users table from the Instructors table.

Here are my models:

class Users(db.Model, UserMixin):

    __tablename__ = 'Users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(64), unique=True)
    password = db.Column(db.LargeBinary)
    instructors = db.relationship('Instructors', backref='user', lazy='select', uselist=False)

    def __init__(self, **kwargs):
        for property, value in kwargs.items():
            # depending on whether value is an iterable or not, we must
            # unpack it's value (when **kwargs is request.form, some values
            # will be a 1-element list)
            if hasattr(value, '__iter__') and not isinstance(value, str):
                # the ,= unpack of a singleton fails PEP8 (travis flake8 test)
                value = value[0]

            if property == 'password':
                value = hash_pass(value)  # we need bytes here (not plain str)

            setattr(self, property, value)

    def __repr__(self):
        return str(self)

# create a table for instructors
class Instructors(db.Model):
    
    __tablename__ = 'Instructors'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'))
    courses = db.relationship('Courses', backref='instructor', lazy='select')

Here is what I fail to do:

# get the instructors
    instructors = db.session.query(Users,Instructors).filter(Users.id== Instructors.id).all()
    for instructor in instructors:
        print(instructor.username)

I get this error: AttributeError: Could not locate column in row for column 'username'

Surprisingly, I can query using the following command compromising on the data coming from the Users table:

 # get the instructors
    instructors = Instructors.query.all()

What can be the reason for the problem? Are my models wrong or anything else regarding the other parts? Thank you for your help.

CodePudding user response：

Your filter syntax doesn't make sense. If you want to query Instructors for a specific User, you can use the syntax below. Ps. It looks like a one-to-many relationship, not a one-to-one relationship.

# get the instructors
query = db.session.query(Instructors)
query = query.filter(Instructors.user_id == 2)
instructors = query.all()

for instructor in instructors:
   print(instructor.username)

CodePudding user response：

I found a solution finally on my code. The following syntax does the job:

# get the instructors
    instructors = db.session.query(Instructors).join(Users, Users.id == Instructors.user_id).all() # this line has been changed.
    for instructor in instructors:
        print(instructor.user.username) # this line has been changed.