Flask SQL Alchemy Join Multiple Tables

user_6743728 0
Viewed 4002

I'm looking to join multiple tables and selecting specific columns using Flask-SqlAlchemy. I'm getting errors with my attempts as I believe my join statements are wrong

Any help would be appreciated

The objects "Question" and "Question" in the FROM clause have the same exposed names

models.py

class Survey(db.Model):
    __tablename__ = 'Survey'
    survey_id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String)

    def __repr__(self):
        return '<Survey %r>' % self.description


class Question(db.Model):
    __tablename__ = 'Question'
    question_id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String)
    survey_id = db.Column(db.Integer, db.ForeignKey('Survey.survey_id'), nullable=False)
    survey = db.relationship('Survey', backref=db.backref('questions', lazy=True))

    def __repr__(self):
        return '<Question %r>' % self.description


class Person(db.Model):
    __tablename__ = 'Person'
    person_id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String)
    survey_id = db.Column(db.Integer, db.ForeignKey('Survey.survey_id'), nullable=False)
    survey = db.relationship('Survey', backref=db.backref('persons', lazy=True))

    def __repr__(self):
        return '<Person %r>' % self.description


class Answer(db.Model):
    __tablename__ = 'Answer'
    answer_id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String)
    sentiment_azure = db.Column(db.Float)
    sentiment_google = db.Column(db.Float)
    question_id = db.Column(db.Integer, db.ForeignKey('Question.question_id'), nullable=False)
    person_id = db.Column(db.Integer, db.ForeignKey('Person.person_id'), nullable=False)
    question = db.relationship('Question', backref=db.backref('answers', lazy=True))
    person = db.relationship('Person', backref=db.backref('answers'), lazy=True)

    def __repr__(self):
        return '<Answer %r>' % self.description

routes.py

@app.route('/edit/<survey_id>')
def edit(survey_id):
    ans = db.session.query(Survey.description, Question.description, Answer.description).join(Survey).join(Question).join(Answer).join(Person).filter(Survey.survey_id == survey_id).all()
    print(ans)
    return 'OK'
flask-sqlalchemy python-3.x join
2 Answers

I managed to find a way to do it, however I think there's probably a better approach. Flask documentation suggests the following, but I couldn't figure to connect all the four tables:

from sqlalchemy.orm import joinedload
query = Category.query.options(joinedload('posts'))

This is my solution:

@app.route('/edit/<survey_id>')
def edit(survey_id):
    q = db.session.query(Survey, Person, Question, Answer)\
        .join(Person, Person.survey_id == Survey.survey_id)\
        .join(Question, Question.survey_id == Survey.survey_id)\
        .join(Answer, Answer.question_id == Question.question_id).all()
    print(q)
    return 'OK'
user_67437280 
@app.route('/edit/<survey_id>')
def edit(survey_id):
    q = db.session.query(Survey, Person, Question, Answer)
                  .filter(Person.survey_id == Survey.survey_id,
                          Question.survey_id == Survey.survey_id,
                          Answer.question_id == Question.question_id).all()
    print(q)
    return 'OK'

Hope this will help!

user_133593530
Related