alphanumeric alphanumeric - 18 days ago 5
Python Question

How to query Many-To-Many SQLAlchemy

enter image description here

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?

enter image description here

Answer

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