Why SQLAlchemy Join is returning empty data?

Viewed 139

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?

0 Answers
Related