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?

hmmm, your relationship doesn't seem right to me....with your current relationship one album can only have one genre, which means that for an Artist to be linked to x genre's he has to have x number of albums also....is that what you intended? — danidee
– danidee, Commented Nov 20, 2016 at 14:59
Yes, It is intentional that the Album and Genre have one to one relationship. — alphanumeric
– alphanumeric, Commented Nov 20, 2016 at 15:22
Given the example above how do I query artists which have more than 1 album ? — Alexander Todorov
– Alexander Todorov, Commented Apr 7, 2021 at 18:55

Dauros · Accepted Answer · 2016-11-20 16:57:32Z

36

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()

edited Nov 20, 2016 at 16:57

answered Nov 20, 2016 at 16:32

Dauros

10.7k2 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query Many-To-Many SQLAlchemy

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related