How to filter and display flask sqlalchemy one to many relationship data with jinja

I have two mysql tables (“song_info” and “djartist1”) which they have one to many relationship. I want to display the all songs from a single artist (all posts from a specific user). But I’m getting a list, instead of what I want to be displayed on the html file. Can you give me a solution please ?.

here are the codes related to this issue

#Parent
class Djartist1(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    artistid = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(30), nullable=False)
    slug = db.Column(db.String(30), nullable=False)
    dj_img_location = db.Column(db.String(250), nullable=True)
    facebook = db.Column(db.String(200), nullable=True)
    email = db.Column(db.String(50), nullable=True)
    song_info = db.relationship('Song_info', backref='djartist1', lazy=True)


#Child
class Song_info(db.Model):
    song_id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(60), nullable=False)
    slug = db.Column(db.String(50), nullable=False)
    artist = db.Column(db.String(60), nullable=True)
    djartist = db.Column(db.String(20), nullable=True)
    owner_id = db.Column(db.Integer, db.ForeignKey('djartist1.id'), nullable=False)
    file_location_name = db.Column(db.String(250), nullable=False)
    img_location_name = db.Column(db.String(250), nullable=False)
    lyrics = db.Column(db.String(400), nullable=True)
    dateadded = db.Column(db.String, nullable=False)


@app.route("/dj-artists/<string:dj_slug>.html", methods=['GET'])
def dj_artist(dj_slug):
    artists2 = Djartist1.query.filter_by(slug=dj_slug).first()
    songs = Djartist1.query.filter_by(slug=dj_slug).all()
    return render_template('dj-artist.html', parameter=parameter, info=artists2, songs=songs)

JavaScript
​x
 
#Parent
class Djartist1(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    artistid = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(30), nullable=False)
    slug = db.Column(db.String(30), nullable=False)
    dj_img_location = db.Column(db.String(250), nullable=True)
    facebook = db.Column(db.String(200), nullable=True)
    email = db.Column(db.String(50), nullable=True)
    song_info = db.relationship('Song_info', backref='djartist1', lazy=True)
​
​
#Child
class Song_info(db.Model):
    song_id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(60), nullable=False)
    slug = db.Column(db.String(50), nullable=False)
    artist = db.Column(db.String(60), nullable=True)
    djartist = db.Column(db.String(20), nullable=True)
    owner_id = db.Column(db.Integer, db.ForeignKey('djartist1.id'), nullable=False)
    file_location_name = db.Column(db.String(250), nullable=False)
    img_location_name = db.Column(db.String(250), nullable=False)
    lyrics = db.Column(db.String(400), nullable=True)
    dateadded = db.Column(db.String, nullable=False)
​
​
@app.route("/dj-artists/<string:dj_slug>.html", methods=['GET'])
def dj_artist(dj_slug):
    artists2 = Djartist1.query.filter_by(slug=dj_slug).first()
    songs = Djartist1.query.filter_by(slug=dj_slug).all()
    return render_template('dj-artist.html', parameter=parameter, info=artists2, songs=songs)
​

This is the html code

<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
<h1>Hello</h1>
songs {{songs}}
<br>
<p>forloop for songs</p>
    {% for song in songs %}
      <li>{{ song.song_info }}</li>  
    {% endfor %}

</body>
</html>

JavaScript
 
<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
<h1>Hello</h1>
songs {{songs}}
<br>
<p>forloop for songs</p>
    {% for song in songs %}
      <li>{{ song.song_info }}</li>  
    {% endfor %}
​
</body>
</html>
​

This is the html output I’m getting

Hello
songs [<Djartist1 3>]

forloop for songs
[<Song_info 4>, <Song_info 5>, <Song_info 6>, <Song_info 7>, <Song_info 8>, <Song_info 9>, <Song_info 10>, <Song_info 11>, <Song_info 12>]

JavaScript
 
Hello
songs [<Djartist1 3>]
​
forloop for songs
[<Song_info 4>, <Song_info 5>, <Song_info 6>, <Song_info 7>, <Song_info 8>, <Song_info 9>, <Song_info 10>, <Song_info 11>, <Song_info 12>]
​

Above numbers are correct. But what I need is the name of the song of those numbers (ID’s).

Jinja2 HTML code and the output

Answer

You need to specify which attributes you want to see, currently you’re referencing an entire object, not a string or integer, and that is also what’s being represented.

<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
<h1>Hello</h1>
songs {{songs}}
<br>
<p>forloop for songs</p>
    {% for song in songs %}
      <li>{{ song.song_info.title }}</li>  
    {% endfor %}

</body>
</html>

JavaScript
 
<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
<h1>Hello</h1>
songs {{songs}}
<br>
<p>forloop for songs</p>
    {% for song in songs %}
      <li>{{ song.song_info.title }}</li>  
    {% endfor %}
​
</body>
</html>
​

There are a few more notes to give on this:

Are the artist slugs unique? Because it looks like you have multiple artists with the same slug and each artist has one song. It would make more sense to have one song with an artist_id and an artist relationship, and then give the artist a songs attribute. Here is an example:

class Customer:
    id = Column(BigInteger, primary_key=True)
    orders = relationship("Order", back_populates="customer")

class Order:
    customer_id = Column(Integer, ForeignKey('customer.id'), nullable=False)
    customer = relationship("Customer", innerjoin=True, back_populates="orders")

JavaScript
 
class Customer:
    id = Column(BigInteger, primary_key=True)
    orders = relationship("Order", back_populates="customer")
​
class Order:
    customer_id = Column(Integer, ForeignKey('customer.id'), nullable=False)
    customer = relationship("Customer", innerjoin=True, back_populates="orders")
​

Besides, if you have only one Djartist per slug, then you don’t need to do multiple queries. You can change your view function to

@app.route("/dj-artists/<string:dj_slug>.html", methods=['GET'])
def dj_artist(dj_slug):
    artist = Djartist1.query.filter_by(slug=dj_slug).one()
    return render_template('dj-artist.html', info=artist)

JavaScript
 
@app.route("/dj-artists/<string:dj_slug>.html", methods=['GET'])
def dj_artist(dj_slug):
    artist = Djartist1.query.filter_by(slug=dj_slug).one()
    return render_template('dj-artist.html', info=artist)
​

And reference songs in the template through {% for song in artist.songs} %}

Advertisement

Answer