How to query Many-To-Many SQLAlchemy

Question

Import Flask and SQLAlchemy modules first:

from flask import Flask from flask_sqlalchemy import SQLAlchemy 

Declare the app and db objects:

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

There are three tables: Artist, Album and Genre. The Artist object can be linked to multiple Albums. And the Album object can be linked to multiple Artists. The albums_to_artists_table is to keep the relationship between the Artists and Albums tight:

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(80), unique=True)     genre_id = db.Column(db.Integer, db.ForeignKey('genre.id'))      artists = db.relationship('Artist', backref='albums', 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)     _albums = db.relationship('Album', secondary=albums_to_artists_table, backref=db.backref('albums_to_artists_table_backref', lazy='dynamic'))  

So we have the Artist linked to the Album which is linked to Genre and it looks like this: Artist > Album > Genre.

Having this setup in place we go ahead and create the Genre object first:

db.drop_all() db.create_all()  genre = Genre(name='Heavy Metal') db.session.add(genre) db.session.commit() 

Then two albums:

album1 = Album(name='Ride the Lightning', genre_id = genre.id) album2 = Album(name='Master of Puppets ', genre_id = genre.id) db.session.add(album1) db.session.add(album2) db.session.commit() 

And the artist:

artist = Artist(name='Metallica',  _albums=[album1, album2])  db.session.add(artist) db.session.commit() 

After the database created we can query what Albums are linked to Genre:

print Album.query.filter_by(genre_id=1).all() 

and what Artists are linked to Album:

print Artist.query.filter(Artist._albums.any(id=album1.id)).all() 

Now I would like to query all the Artists that are linked to a Genre passing the genre.id. How to achieve it?

Answer 1

You can apply a filter in Artist.albums.any(), which will generate a subquery:

Artist.query.filter(Artist.albums.any(genre_id=genre.id)).all() 

Or you can use a join() on albums:

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