Flask-SQLAlchemy Many-to-Many Relationships: # Association Tables and Association Objects

SQLAlchemy gives two instruments for managing many-to-many relationships: Affiliation Tables and Affiliation Objects. This weblog will assessment tips on how to use affiliation tables and affiliation objects in a Flask software utilizing the Flask-SQLAlchemy extension.

Affiliation Tables

An affiliation desk, like a fundamental be part of desk in SQLlite, is a database desk that’s used to maintain observe of the relationships between two different tables. An affiliation tables doesn’t include any info in addition to what is important to handle these relationships.

Say now we have two fashions, Trainer and Scholar, which have a many-to-many relationship. Every trainer has many college students, and every pupil has many lecturers. Their relationship might be managed with an affiliation desk, teacher_students. The one columns that teacher_students accommodates are the columns that consult with the tables for Trainer and Scholar. teacher_students can’t be used to handle any extra info.

The connection between Trainer and Scholar is created by making columns in teacher_students that include overseas keys referring to the id columns of the Trainer and Scholar tables.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData
from sqlalchemy.ext.associationproxy import association_proxy

metadata = MetaData(naming_convention={
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
})

db = SQLAlchemy(metadata=metadata)

teacher_students = db.Desk(
    'teacher_students',
    metadata,
    db.Column('teacher_id', db.ForeignKey('lecturers.id'), 
        primary_key=True),
    db.Column('student_id', db.ForeignKey('college students.id'), 
        primary_key=True)
)

It is not essential to specify the information sort of the columns in teacher_students as a result of the kind of knowledge in every column is inferred from the kind of knowledge within the columns of the Trainer desk and Scholar desk.

Relating to the mannequin columns that ought to be utilized in affiliation desk:

It’s also really useful, although not in any means required by SQLAlchemy, that the columns which consult with the 2 entity tables are established inside both a singular constraint or extra generally as the first key constraint; this ensures that duplicate rows gained’t be endured inside the desk no matter points on the applying aspect.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#many-to-many

To create the many-to-many relationship between Trainer and Scholar, every mannequin is given a relationship() attribute that hyperlinks them to one another. Trainer is given a college students attribute and Scholar is given a lecturers attribute. The connection between the fashions and teacher_students is supplied by the secondary parameter in every mannequin’s relationship attribute, which factors to the affiliation desk.

class Trainer(db.Mannequin):
    __tablename__ = 'lecturers'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)

    college students = db.relationship('Scholar', secondary=teacher_students,
        back_populates="lecturers")

    def __repr__(self):
        return f'<Trainer {self.id}>'

class Scholar(db.Mannequin):
    __tablename__ = 'college students'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)

    lecturers = db.relationship('Trainer', secondary=teacher_students,     
        back_populates="college students")

    def __repr__(self):
        return f'<Scholar {self.id}>'

Further particulars and details about the secondary parameter might be discovered right here:
https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship.params.secondary

To see the scholars {that a} trainer has or the lecturers {that a} pupil has, the college students and lecturers attributes might be referred to as on cases of the Trainer and Scholar fashions:

>>> t1 = Trainer.question.first()
>>> t1
<Trainer 1>
>>> t1.college students
[<Student 1>, <Student 3>, <Student 6>]

>>> s1 = Scholar.question.first()
>>> s1
<Scholar 1>
>>> s1.lecturers
[<Teacher 1>, <Teacher 2>]

Affiliation Objects

What if, as a substitute of solely conserving observe of the relationships between Trainer and Scholar cases, we wished the be part of desk to additionally maintain observe of the exams that every trainer administers and every pupil takes. In that case, an affiliation object could be used as a substitute of an affiliation desk as a result of it’s vital for the be part of desk to carry further info. In different phrases, when the be part of desk must include extra columns than simply the overseas keys from different tables, an affiliation object ought to be used.

The affiliation object sample is a variant on many-to-many: it’s used when an affiliation desk accommodates extra columns past these that are overseas keys to the dad or mum and little one (or left and proper) tables, columns that are most ideally mapped to their very own ORM mapped class.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

Within the instance above, there are fashions for Trainer and Scholar. To create an affiliation object that retains observe of exams, together with the relationships between Trainer and Scholar cases, there must be a 3rd mannequin, Examination, which acts because the affiliation object. Just like the affiliation desk used above, the Examination mannequin’s desk remains to be given columns to maintain observe of the overseas keys of the many-to-many relationship fashions, however it may additionally include columns for no matter different info is required to be saved within the desk.

class Examination(db.Mannequin):
    __tablename__ = 'exams_table'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)

    teacher_id = db.Column(db.Integer, db.ForeignKey('lecturers.id'))
    student_id = db.Column(db.Integer, db.ForeignKey('college students.id'))

    trainer = db.relationship('Trainer', back_populates="exams")
    pupil = db.relationship('Scholar', back_populates="exams")

    def __repr__(self):
        return f'<Examination {self.id}>'

As an alternative of the Trainer and Scholar fashions having a direct, many-to-many relationship, they may now have one-to-many relationships with the Examination affiliation object.

Within the affiliation object sample, the relationship.secondary parameter shouldn’t be used; as a substitute, a category is mapped on to the affiliation desk. Two particular person relationship() constructs then hyperlink first the dad or mum aspect to the mapped affiliation class by way of one to many, after which the mapped affiliation class to the kid aspect by way of many-to-one, to type a uni-directional affiliation object relationship from dad or mum, to affiliation, to little one. For a bi-directional relationship, 4 relationship() constructs are used to hyperlink the mapped affiliation class to each dad or mum and little one in each instructions.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

As an alternative of Trainer having a college students attribute, it has an exams attribute, whereas the Scholar mannequin has its personal exams attribute.

class Trainer(db.Mannequin):
    __tablename__ = 'lecturers'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)

    exams = db.relationship('Examination', back_populates="trainer")

    college students = association_proxy('exams', 'pupil')

    def __repr__(self):
        return f'<Trainer {self.id}>'

class Scholar(db.Mannequin):
    __tablename__ = 'college students'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)

    exams = db.relationship('Examination', back_populates="pupil")

    lecturers = association_proxy('exams', 'trainer')

    def __repr__(self):
        return f'<Scholar {self.id}>'

Which means that, not like with an affiliation desk, the connection between Trainer and Scholar can’t be accessed straight from a relationship object in both mannequin. To make it simpler to view the connection between Trainer and Scholar now that they’re linked by an affiliation object, SQLAlchemy gives the Affiliation Proxy instrument.

To boost the affiliation object sample such that direct entry to the Affiliation object is non-obligatory, SQLAlchemy gives the Affiliation Proxy extension. This extension permits the configuration of attributes which is able to entry two “hops” with a single entry, one “hop” to the related object, and a second to a goal attribute.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

The affiliation proxies within the above instance (college students within the Trainer mannequin and lecturers within the Scholar mannequin) are supplied (1) the title of the fast goal that’s getting used to handle the many-to-many relationship and (2) an attribute that’s accessible in an occasion of that focus on. On this instance the fast goal is the connection attribute exams, which is the variable used to ascertain the one-to-many relationships between the Trainer and Scholar fashions and the Examination affiliation object. The attribute given to association_proxy() is the corresponding relationship variable within the Examination affiliation object.

Now, the Trainer and Scholar fashions every have technique to view the one-to-many relationship with the Examination affiliation object, in addition to a technique to straight view the many-to-many relationships via the affiliation proxies. Trainer‘s affiliation proxy gives the worth of the college students related to it whereas concealing, or skipping over, the affiliation object.

>>> t1 = Trainer.question.first()
>>> t1
<Trainer 1>
>>> t1.exams
[<Exam 1>, <Exam 2>, <Exam 3>]
>>> t1.college students
[<Student 1>, <Student 3>, <Student 6>]

By default, calling a mannequin’s affiliation proxy attribute will return cases of the goal object. So, calling Trainer.college students returns a listing of all of the cases of Scholar which have a relationship with a specific occasion of Trainer.

Further details about utilizing affiliation proxies might be discovered right here:

https://docs.sqlalchemy.org/en/20/orm/extensions/associationproxy.html