alphanumeric alphanumeric - 19 days ago 6
Python Question

How to query many to many using Flask SQLAlchemy

With

Flask
and
SQLAlchemy
imported:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


I go ahead and declare
app
and
db
objects:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///inquestion.db'
db = SQLAlchemy(app)


I proceed creating three Tables:
Genre
,
Album
and
Artist
.
Since
Artist
can be linked to multiple
Albums
and
Albums
can include multiple
Artists
I need many to many attribute so there will be fourth table used to store
Artist
to
Album
ids:

albums_to_artists_table = db.Table('albums_to_artists_table',
db.Column('album_id', db.Integer, db.ForeignKey('album.id')),
db.Column('artist_id', db.Integer, db.ForeignKey('artist.id')))

class Genre(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), unique=True)

class Album(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
artists = db.relationship('Artist', backref='album', lazy='dynamic', secondary=albums_to_artists_table)

class Artist(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), unique=True)
genre_id = db.Column(db.Integer, db.ForeignKey('genre.id'))

_albums = db.relationship('Album', secondary=albums_to_artists_table, backref=db.backref('albums_to_artists_table_backref', lazy='dynamic'))


Finally I create the database:

db.drop_all()
db.create_all()

genre = Genre(name='Disco')
db.session.add(genre)
db.session.commit()

album1 = Album(name='Main Course')
album2 = Album(name='Spirits Having Flown')
db.session.add(album1)
db.session.add(album2)
db.session.commit()

artist = Artist(name='Bee Gees', genre_id = genre.id, _albums=[album1, album2])
db.session.add(artist)
db.session.commit()


Now I can query what
Artists
are connected to a given
Genre
:

Artist.query.filter_by(genre_id = genre.id).all()


I can also query what
Albums
the
Artist
is connected to:

Album.query.filter(Album.artists.any(name='Bee Gees')).all()


But how do I query what
Artists
are connected to a given
Genre
? By other words, I want to collect all the
Artists
linked to a
Genre
with a given
genre.id
?

enter image description here

Answer

But how do I query what Artists are connected to a given Genre? By other words, I want to collect all the Artists linked to a Genre with a given genre.id?

You gave an example in your question for doing exactly that, get Artist by Genre. I reckon that what you actually meant to ask is how to get Album by Genre, is that right? if so, try something like this:

Album.query.join(Artist).join(Genre).filter(Genre.id==YOUR_GENRE_ID).all()