如何查询多对多 SQLAlchemy

问题描述

首先导入Flask 和SQLAlchemy 模块:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

声明 app 和 db 对象:

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

共有三个表:Artist、Album 和Genre.Artist 对象可以链接到多个 Albums.而Album 对象可以链接到多个Artists.albums_to_artists_table 是为了保持 Artists 和 Albums 之间的关系紧密:

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

所以我们将 Artist 链接到 Album 链接到 Genre，它看起来像这样:Artistcode> > 专辑 > 流派.

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

完成此设置后，我们继续创建 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()

然后是两张专辑:

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

和艺术家:

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

以及哪些 Artists 链接到 Album:

and what Artists are linked to Album:

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

现在我想查询所有与 Genre 相关联的 Artists 传递了 Genre.id.如何实现?

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

推荐答案

您可以在 Artist.albums.any() 中应用过滤器，这将生成子查询:

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

或者你可以在相册上使用join():

Or you can use a join() on albums:

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

这篇关于如何查询多对多 SQLAlchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！