sqlalchemy many to many association table not inserting rows

Question

Writing my first web app using flask / SQLAlchemy. I have a many to many relationship between 'persons' and 'facilities.' When I successfully add a person using the registration form, the association table does not get a row added. Do I have to insert that row manually?

Here is the pertinent part of the model:

# app/models.py

from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash

from app import db, login_manager

# [START model]

# Build secondary table for many to many between facilities and persons
workers = db.Table('workers',
    db.Column('facility_id', db.Integer, db.ForeignKey('facilities.id')),
    db.Column('person_id', db.Integer, db.ForeignKey('persons.id'))
)


class Facility(db.Model):
    __tablename__='facilities'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60))
    description = db.Column(db.String(128))
    persons = db.relationship('Person', secondary='workers', backref='facilities', lazy = 'dynamic')

    def __repr__(self):
        return "<Facility name='%s')" % (self.name)

class Person(UserMixin, db.Model):
    __tablename__ = 'persons'

    id = db.Column(db.Integer, primary_key=True)
    last_name = db.Column(db.String(60), index=True)
    username = db.Column(db.String(60), index=True, unique=True)
    email = db.Column(db.String(80), index=True)
    password_hash = db.Column(db.String(128))
    first_name = db.Column(db.String(60), index=True)
    role = db.Column(db.Integer, db.ForeignKey('roles.id'))
    is_person_active = db.Column(db.Boolean, index=True)
    is_admin = db.Column(db.Boolean, default=False)
    comments = db.Column(db.String(255))
    animals = db.relationship('Animal', secondary='permissions', backref='persons', lazy = 'dynamic'))

    @property
    def password(self):
        """
        Prevent password from being accessed
        """
        raise AttributeError('password is not a readable attribute.')

    @password.setter
    def password(self, password):
        """
        Set password to a hashed password
        """
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        """
        Check if hashed password matches actual password
        """
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return "<Person name='%s', '%s', '%s')" % (self.first_name, self.last_name, self.username)


# Set up user_loader
@login_manager.user_loader
def load_user(user_id):
    return Person.query.get(int(user_id))

And here is the view:

# app/auth/views.py

from flask import flash, redirect, render_template, url_for
from flask_login import login_required, login_user, logout_user

from . import auth
from .forms import LoginForm, RegistrationForm
from .. import db
from ..models import Person, Facility

@auth.route('/register', methods=['GET', 'POST'])
def register():
    """
    Handle requests to the /register route
    Add a person to the database through the registration form
    """
    form = RegistrationForm()
    form.facility_id.choices = [(f.id, f.name) for f in Facility.query.order_by('name')]
    if form.validate_on_submit():
        person = Person(facility=form.facility_id.data,
                            email=form.email.data,
                            username=form.username.data,
                            first_name=form.first_name.data,
                            last_name=form.last_name.data,
                            password=form.password.data)

        # add person to the database
        db.session.add(person)
        db.session.commit()
        flash('You have successfully registered! You may now login.')

        # redirect to the login page
        return redirect(url_for('auth.login'))

    # load registration template
    return render_template('auth/register.html', form=form, title='Register')

Answer 1

Thanks for the support @Michael. You were close enough that I found the problem; it was that I was not adding the person to the persons collection for the facility, so no row was inserted into the workers table. I added

        facility = Facility.query.filter_by(id=form.facility_id.data).first()
        facility.persons.append(person)
        db.session.commit()

after the existing code

        db.session.add(person)
        db.session.commit()

in the registration view and it is correctly inserting rows in the workers table now.

Answer 2

Perhaps this question is a duplicate of https://stackoverflow.com/a/25669256/1449799 ? It seems that the issue you're having is that in your register() function, there's no mention of facilities.

Without changing your model classes (e.g. to have the Person model know about its connected facilities in addition to the reverse of what you do have now in Facility for Person), I think you may be able to do something in register() like:

#this should maybe come after db.session.add(person), but before db.session.commit()
selected_facility = Facility.query.get(form.facility_id.data)
selected_facility.persons.append(person)

or alternatively

#this should maybe come after db.session.add(person), but before db.session.commit()
selected_facility = Facility.query.get(form.facility_id.data)
person.facilities.append(selected_facility)

Answer 3

If the above https://stackoverflow.com/a/60100671/1449799 doesn't work, I wonder if it's as simple as a spelling issue? You've said that the back ref from Facility to Person should be called facilities. perhaps in your call to the Person constructor in your register function you should change:

person = Person(facility=form.facility_id.data,

to

person = Person(facilities=[form.facility_id.data],