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?
Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.
You will link it with the existing posts table using an association table, which is a table that connects your two tables in a many-to-many relationship. A many-to-many relationship links two tables where each item in a table has many related items in the other table.
The sqlalchemy backref is one of the type keywords and it passed as the separate argument parameters which has to be used in the ORM mapping objects. It mainly includes the event listener on the configuration attributes with both directions of the user datas through explicitly handling the database relationships.
method sqlalchemy.orm.Query. all() Return the results represented by this Query as a list. This results in an execution of the underlying SQL statement. The Query object, when asked to return either a sequence or iterator that consists of full ORM-mapped entities, will deduplicate entries based on primary key.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With