join multiple tables using SQLalchemy and assess it using Flask and Jinja

Question

I'm looking to join 3 tables and selecting specific columns using Flask-SqlAlchemy in the front end to show all the details from the 3 tables.

here's the models :

class Venue(db.Model):
    __tablename__ = 'Venue'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    city = db.Column(db.String(120))
    state = db.Column(db.String(120))
    address = db.Column(db.String(120))
    phone = db.Column(db.String(120))
    image_link = db.Column(db.String(500))
    facebook_link = db.Column(db.String(120))
    website_link = db.Column(db.String(120))
    seeking_talent = db.Column(db.Boolean,default=False)
    genres = db.Column(db.ARRAY(db.String(120)))
    upcoming_shows = db.relationship("Upcoming_shows")
    past_shows = db.relationship("Past_shows")

class Artist(db.Model):
    __tablename__ = 'Artist'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    city = db.Column(db.String(120))
    state = db.Column(db.String(120))
    phone = db.Column(db.String(120))
    image_link = db.Column(db.String(500))
    facebook_link = db.Column(db.String(120))
    website_link = db.Column(db.String(120))
    seeking_talent = db.Column(db.Boolean,default=False)
    genres = db.Column(db.ARRAY(db.String(120)))
    upcoming_shows = db.relationship("Upcoming_shows")
    past_shows = db.relationship("Past_shows")

class Upcoming_shows(db.Model):
    __tablename__ = 'Upcoming_shows'

    id = db.Column(db.Integer, primary_key=True)
    venue_id = db.Column(db.Integer, db.ForeignKey('Venue.id'))
    artist_id = db.Column(db.Integer, db.ForeignKey('Artist.id'))
    start_time = db.Column(db.DateTime)

class Past_shows(db.Model):
    __tablename__ = 'Past_shows'

    id = db.Column(db.Integer, primary_key=True)
    venue_id = db.Column(db.Integer, db.ForeignKey('Venue.id'))
    artist_id = db.Column(db.Integer, db.ForeignKey('Artist.id'))
    start_time = db.Column(db.DateTime)

and here is the front end file :

{% extends 'layouts/main.html' %}
{% block content %}
<div class="row shows">
    {%for show in past_show %}
    <div class="col-sm-4">
        <div class="tile tile-show">
            <img src="{{ show.image_link }}" alt="Artist Image" />
            <h4>{{ show.start_time }}</h4>
            <h5><a href="/artists/{{ show.artist_id }}">{{ show.name }}</a></h5>
            <p>playing at</p>
            <h5><a href="/venues/{{ show.venue_id }}">{{ show.name }}</a></h5>
        </div>
    </div>
    {% endfor %}
    {%for show in upcoming_shows %}
    <div class="col-sm-4">
        <div class="tile tile-show">
            <img src="{{ artists_image_link }}" alt="Artist Image" />
            <h4>{{ show.start_time }}</h4>
            <h5><a href="/artists/{{ artists_id }}">{{ artists_name }}</a></h5>
            <p>playing at</p>
            <h5><a href="/venues/{{ venues_id }}">{{ venues_name }}</a></h5>
        </div>
    </div>
    {% endfor %}
</div>
{% endblock %}

I have done multiple queries but all I can show is the info in one of the table not all of them together

thanks

score 1 · Accepted Answer · answered May 11 '20 at 11:38

The simplest way to do this is by querying all three models and filtering on the ids:

q = (db.query(Upcoming_shows, Artist, Venue)
       .filter(Upcoming_shows.artist_id == Artist.id, 
               Upcoming_shows.venue_id == Venue.id))
for show, artist, venue in q:
    print(show.start_time, venue.name, artist.name)

and

q = (db.query(Past_shows, Artist, Venue)
       .filter(Past_shows.artist_id == Artist.id,
               Past_shows.venue_id == Venue.id))
for show, artist, venue in q:
    print(show.start_time, venue.name, artist.name)

This technique is called an implicit join. There are other, explicit, joining techniques, but they require more configuration to generate efficient SQL - this approach is good enough to begin with. You can start reading about joins in SQLAlchemy here.

One change you might consider: merge the Upcoming_shows and Past_shows models into a single Shows model. Past and future shows can be distinguished by filtering on the date:

import datetime
now = datetime.datetime.now()

# Get future shows
q = (db.query(Shows, Artist, Venue)
       .filter(Shows.artist_id == Artist.id,
               Shows.venue_id == Venue.id)
       .filter(Show.start_time > now))

join multiple tables using SQLalchemy and assess it using Flask and Jinja

1 Answers1