ruipacheco ruipacheco - 5 months ago 9x
Python Question

How to add value to join table with SQLAlchemy?

I have 3 tables:

. Besides joining users and departments on id, which I have working well, I need to set one of the users as the head of the department by setting a boolean on Users_Departments.

I've configured relationships on both users and departments:

departments = db.relationship('Department', secondary=users_departments, backref=db.backref('users', lazy='dynamic'))

users = db.relationship('User', secondary=users_departments, backref=db.backref('departments', lazy='dynamic'))

But I don't know how to set the boolean value that is defined in Users_Departments when I add a user to a department and vice-versa.


You can use an assocation object:

"The association object pattern is a variant on many-to-many: it’s used when your association table contains additional columns beyond those which are foreign keys to the left and right tables."

In this case you might define the users_departments table as an ORM object:

class UserDepartment(db.Model): # Since we're using Flask-SQLAlchemy
    __tablename__ = 'users_departments'

    user_id        = db.Column(db.Integer, ForeignKey(''), primary_key=True)
    department_id  = db.Column(db.Integer, ForeignKey(''), primary_key=True)

    is_manager     = db.Column(db.Boolean)

    user       = relationship('User', back_populates='department_assoc')
    department = relationship('Department', back_populates='user_assoc')

I have had success using a table like this as secondary, with the viewonly flag set True. So you might define the relationship in the Users class:

departments = db.relationship('Department',
    backref=db.backref('users', viewonly=True, lazy='dynamic'),

This defines a read-only relationship, where actual changes are made directly on the users_departments table:

>>> x = UserDepartment(user_id=n, department_id=m, is_manager=False)
>>> db.session.add(x)
>>> db.session.commit()

Look at the documentation linked at the beginning of this post to understand why viewonly is a good idea. The documentation for viewonly itself is here.

You could also define a column users.department_assoc in the User class:

department_assoc = db.relationship('UserDepartment', back_populates='user')

and make changes:

>>>> some_user = User.query.get(n)
>>>> some_user.department_assoc.append(UserDepartment(department_id=m,  is_manager=False))
>>>> db.session.commit()