Flask SQL Alchemy Join Multiple Tables

Question

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'

Laycoonz · Accepted Answer · 2020-02-28 02:47:05Z

3

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'

answered Feb 28, 2020 at 2:47

Laycoonz

1951 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Ke · Accepted Answer · 2020-07-25 09:31:17Z

1

@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!

edited Jul 25, 2020 at 9:31

Matt Ke

3,78912 gold badges35 silver badges53 bronze badges

answered Jul 25, 2020 at 9:08

Shrikant Chambhare

466 bronze badges

Collectives™ on Stack Overflow

Flask SQL Alchemy Join Multiple Tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related