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'
asked Feb 28, 2020 at 1:40
Laycoonz
1951 gold badge6 silver badges19 bronze badges
2 Answers 2
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
@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!
Matt Ke
3,78912 gold badges36 silver badges53 bronze badges
Comments
lang-py