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!

ritesh · Accepted Answer · 2018-03-06 19:02:52Z

7

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)))
    ...

answered Mar 6, 2018 at 19:02

ritesh

2,2652 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

snakecharmerb Over a year ago

There has been a built-in MutableList since v1.1, so defining your own class should no longer be necessary.

joerick · Accepted Answer · 2018-09-08 14:58:21Z

5

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')

answered Sep 8, 2018 at 14:58

joerick

16.5k4 gold badges56 silver badges59 bronze badges

Comments

kelleyb · Accepted Answer · 2015-03-11 01:35:29Z

1

To anyone in the future: so it turns out that arrays through SQLAlchemy are immutable. So, once they're initialized in the database, they can't change size. There's probably a way to do this, but there are better ways to do what we're trying to do.

answered Mar 11, 2015 at 1:35

kelleyb

792 silver badges6 bronze badges

1 Comment

Ilja Everilä Over a year ago

ARRAY is not immutable. It just does not track changes to it by default, so SQLA does not know that data in the session has changed and needs to be flushed / committed.

Microshark · Accepted Answer · 2018-11-30 09:29:40Z

1

This is a hacky solution, but what you can do is:

Store the existing array temporarily
Set the column value to None
Set the column value to the existing temporary array

For example:

g = Group.query.all()[0]
temp_array = g.members
g.members = None
db.session.commit()
db.session.refresh(g)
g.members = temp_array
db.session.commit()

answered Nov 30, 2018 at 9:29

Microshark

336 bronze badges

Comments

Yubraj · Accepted Answer · 2023-02-05 06:32:07Z

0

In my case it was solved by using the new reference for storing a object variable and assiging that new created variable in object variable.so, Instead of updating the existing objects variable it will create a new reference address which reflect the changes.

Here in Model,

 Table: question


optional_id = sa.Column(sa.ARRAY(sa.Integer), nullable=True)

In views,

        option_list=list(question.optional_id if question.optional_id else [])

        if option_list:
            question.optional_id.clear()
            option_list.append(obj.id)
            question.optional_id=option_list
        else:
            question.optional_id=[obj.id]

answered Feb 5, 2023 at 6:32

Yubraj

12 bronze badges

Collectives™ on Stack Overflow

Flask Postgresql array not permanently updating

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related