How I do to update data on many-to-many with WTForms and SQLAlchemy?

Question

I've a small problem on my App build with Flask Framework.

I'm trying to create a simple User + Permissions module. To archive it, I've a many-to-many relation between Users and Permissions table.

Here is my model, form and route

Model

user_perm = db.Table('user_perm',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('perm_id', db.Integer, db.ForeignKey('permissions.id'))
)

class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    pwdhash = db.Column(db.String(16), nullable=False)
    email = db.Column(db.String(128))
    permissions = db.relationship('Permission', secondary=user_perm, backref=db.backref('users', lazy='dynamic'))

    def __init__(self, username, pwdhash, email, perms):
        self.username = unicode(username)
        self.pwdhash = pwdhash
        self.email = email
        self.permissions = perms

    def __repr__(self):
        return '<User {0}>'.format(self.username)

class Permission(db.Model):
    __tablename__ = 'permissions'
    id = db.Column(db.Integer)
    perm = db.Column(db.String(50), primary_key=True, unique=True, nullable=False)

    def __init__(self, perm):
        self.perm = unicode(perm)

    def __repr__(self):
        return '<Permission {0}>'.format(self.perm)

Form

class AddUser(Form):
    username = TextField(u'Username', required)
    pwdhash = TextField(u'Password', required)
    email = TextField(u'E-email', email_validators)
    permissions = SelectMultipleField(u'Permissions', required, coerce=int)

class EditUser(AddUser):
    pass

Routes

@app.route('/admin/user/add', methods=['GET', 'POST'])
@login_required
def admin_user_add():
    form = AddUser(request.form)
    form.permissions.choices = [(p.id, p.perm) for p in Permission.query.order_by('perm')]
    if request.method == 'POST' and form.validate():
        user = User(
            form.username.data,
            form.pwdhash.data,
            form.email.data,
            Permission.query.filter(Permission.id.in_(form.permissions.data)).all()
        )
        db.session.add(user)
        db.session.commit()
        flash('Successfully added user', category='success')
        return redirect(url_for('users'))
    return render_template('admin_user_add.html', form=form)

@app.route('/admin/user/edit/<int:user_id>', methods=['GET', 'POST'])
@login_required
def admin_user_edit(user_id):
    user = User.query.filter_by(id=user_id).first_or_404()
    form = EditUser(request.form, obj=user)
    form.permissions.choices = [(p.id, p.perm) for p in Permission.query.order_by('perm')]
    form.permissions.data = [p.id for p in user.permissions]
    if request.method == 'POST' and form.validate():
        form.populate_obj(user)
        user.username = form.username.data
        user.pwdhash = form.pwdhash.data
        user.email = form.email.data
        user.permissions = Permission.query.filter(Permission.id.in_(form.permissions.data)).all()
        db.session.merge(user)
        db.session.commit()
        flash('Successfully updated user', category='success')
        return redirect(url_for('users'))
    return render_template('admin_user_add.html', form=form, edit=True)

The adding actions works perfectly, the relation are been add, but when I'm trying to edit an user, I get this error.

Traceback

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1518, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1506, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1504, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1264, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1262, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1248, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/var/www/deploy.staging.inovae.ch/webapp/webapp/utilities.py", line 29, in inner
    return f(*args, **kwargs)
  File "/var/www/deploy.staging.inovae.ch/webapp/webapp/utilities.py", line 44, in wrapper
    return f(*args, **kwargs)
  File "/var/www/deploy.staging.inovae.ch/webapp/webapp/views.py", line 161, in admin_user_edit
    form.populate_obj(user)
  File "/usr/local/lib/python2.7/dist-packages/wtforms/form.py", line 73, in populate_obj
    field.populate_obj(obj, name)
  File "/usr/local/lib/python2.7/dist-packages/wtforms/fields/core.py", line 283, in populate_obj
    setattr(obj, name, self.data)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/attributes.py", line 155, in __set__
    instance_dict(instance), value, None)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/attributes.py", line 892, in set
    lambda adapter, i: adapter.adapt_like_to_iterable(i))
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/attributes.py", line 927, in _set_iterable
    collections.bulk_replace(new_values, old_collection, new_collection)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/collections.py", line 681, in bulk_replace
    new_adapter.append_with_event(member)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/collections.py", line 555, in append_with_event
    getattr(self._data(), '_sa_appender')(item, _sa_initiator=initiator)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/collections.py", line 945, in append
    item = __set(self, item, _sa_initiator)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/collections.py", line 920, in __set
    item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/collections.py", line 614, in fire_append_event
    item, initiator)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/attributes.py", line 800, in fire_append_event
    value = fn(state, value, initiator or self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 35, in append
    item_state = attributes.instance_state(item)
AttributeError: 'long' object has no attribute '_sa_instance_state'

Answer 1

The cause of the problem

The crux of your problem is that SQL Alchemy is expecting the permissions relationship to be populated with sqlalchemy permission objects, not the ints you are passing it.
In your view:

form = EditUser(request.form, obj=user)
form.permissions.choices = [(p.id, p.perm) for p in Permission.query.order_by('perm')]
form.permissions.data = [p.id for p in user.permissions]

So in your form, form.permissions is actually just holding the id (int) of the permission object's, not the objects themselves. When you call form.populate_obj(user), when it gets to the permissions segment, its going to call this method:

setattr(obj, name, self.data)

Which translates to:

setattr(user, permissions, sefl.data)

Which translates to:

user.permissions = [1, 3, 4 ...]

But SQL Alchemy expects:

user.permissions = [sqlalchemyobject1, sqlalchemyobject2, ...]

Solution

There are at least a few ways to solve this problem. The method I use is sqlalchemy's Association Proxy. This will let you do things like pass a list of integers to your user model, and have the Model convert them to Permissions objects:

class User(db.Model):
    ...
    permissions_relationship = ('Permission', cascade="all,delete,delete-orphan")
    permissions = association_proxy('permissions_relationship', 'perm',
                            creator=lambda perm: Permission(perm=perm)

class Permission(db.Model):  
    ...
    perm = ...

    def __init__(self,perm):  
        self.perm = perm

In a Nutshell, the association_proxy call simply says: "When you get passed a list of items (Ints, Strings, whatever), convert them to Permission objects by doing this: Permission(perm=perm). Using this solution in your above code, you would remove the line form.permissions.data = [p.id for p in user.permissions].

Finally, wtforms also provides a QuerySelectMultiple field. I haven't used it, but imagine it would also serve as a solution to this problem.