Flask Postgresql array not permanently updating

Question

I'm working on a project using Flask and a PostgreSQL database, with SQLAlchemy.

I have Group objects which have a list of User IDs who are members of the group. For some reason, when I try to add an ID to a group, it will not save properly.

If I try members.append(user_id), it doesn't seem to work at all. However, if I try members += [user_id], the id will show up in the view listing all the groups, but if I restart the server, the added value(s) is (are) not there. The initial values, however, are.

Related code:

Adding group to the database initially:

db = SQLAlchemy(app)
# ...
g = Group(request.form['name'], user_id)
db.session.add(g)
db.session.commit()

The Group class:

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.dialects.postgresql import ARRAY

class Group(db.Model):
    __tablename__ = "groups"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    leader = db.Column(db.Integer)

    # list of the members in the group based on user id
    members = db.Column(ARRAY(db.Integer))

    def __init__(self, name, leader):
        self.name = name
        self.leader = leader
        self.members = [leader]

    def __repr__(self):
        return "Name: {}, Leader: {}, Members: {}".format(self.name, self.leader, self.members)

    def add_user(self, user_id):
        self.members += [user_id]

My test function for updating the Group:

def add_2_to_group():
    g = Group.query.all()[0]
    g.add_user(2)
    db.session.commit()
    return redirect(url_for('show_groups'))

Thanks for any help!

Answer 1

As you have mentioned, the ARRAY datatype in sqlalchemy is immutable. This means it isn’t possible to add new data into array once it has been initialised.

To solve this, create class MutableList.

from sqlalchemy.ext.mutable import Mutable

class MutableList(Mutable, list):
    def append(self, value):
        list.append(self, value)
        self.changed()

    @classmethod
    def coerce(cls, key, value):
        if not isinstance(value, MutableList):
            if isinstance(value, list):
                return MutableList(value)
            return Mutable.coerce(key, value)
        else:
            return value

This snippet allows you to extend a list to add mutability to it. So, now you can use the class above to create a mutable array type like:

class Group(db.Model):
    ...
    members = db.Column(MutableList.as_mutable(ARRAY(db.Integer)))
    ...

Answer 2

You can use the flag_modified function to mark the property as having changed. In this example, you could change your add_user method to:

from sqlalchemy.orm.attributes import flag_modified

# ~~~

    def add_user(self, user_id):
        self.members += [user_id]
        flag_modified(self, 'members')