I'm trying to query data using flask-sqlalchemy. I'm trying to join the table using sqlalchemy orm but it's only producing empty results if I want to show the data from both tables. However, if I only show the data from a single table from the join, it works just fine.
Model
class Persona(db.Model):
"""
Leadership Challenge Profile names and description
"""
__tablename__="persona"
id = Column(Integer(), primary_key=True)
tda_group_name = Column(Integer())
label = Column(String(50))
main_topic_sentence = Column(String(500))
supporting_sentence = Column(String(500))
leader_level_id = Column(Integer(), ForeignKey('leader_level.id'), nullable=False)
class LeaderLevel(db.Model):
"""List of Leader Level"""
__tablename__="leader_level"
id = Column(Integer(), primary_key=True)
short_name = Column(String(5))
full_name = Column(String(50))
program = Column(String(50))
persona = relationship('Persona',
backref=backref('leader_level', lazy="joined", uselist=False))
challenge_topic = relationship('ChallengeTopic',
backref=backref('leader_level', lazy="joined", uselist=False))
session.query(Persona).join(LeaderLevel).all()
Result
[
{
"supporting_sentence": "Long text 1",
"tda_group_name": 1,
"main_topic_sentence": "Short text 1",
"label": "Label 1",
"id": 1,
"leader_level_id": 2
},
{
"supporting_sentence": "Long text 2.",
"tda_group_name": 2,
"main_topic_sentence": "Short text 2",
"label": "Label 2",
"id": 2,
"leader_level_id": 2
}
]
This returns the result, but if I do the following:
session.query(Persona, LeaderLevel).join(LeaderLevel, Persona.leader_level_id = LeaderLevel.id).all()
Result
[
{},
{},
{},
{},
{},
{},
{},
{},
{},
{}
]
All I'm trying to do is to get the data in the following way:
[
{
"supporting_sentence": "Long text 1",
"tda_group_name": 1,
"main_topic_sentence": "Short text 1",
"label": "Label 1",
"id": 1,
"leader_level": "LM"
},
{
"supporting_sentence": "Long text 2.",
"tda_group_name": 2,
"main_topic_sentence": "Short text 2",
"label": "Label 2",
"id": 2,
"leader_level": "LM"
}
]
How can I fix this?