using list on postgresql JSON type with sqlalchemy

Question

I am using pyramid with sqlalchemy, pyramid_tm and postgresql to test this.

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()


class MyList(Base):
    id = Column(Integer, primary_key=True)
    lst = Column(JSON)

I am using postgresql 9.3+ and using JSON type. When i do this

mylst = MyList(lst=[])

i can see empty [] list being created on database as well, and

def view(request):
    mylst = DBSession.query(MyList).get(1)
    mylst.lst.append('45')
    print(DBSession.is_active, DBSession.is_modified(mylst))

i can see ['45'] in database, and print returns

True, True

continuing from above [edit] on next request (above is already committed)

def view(request):
    mylst = DBSession.query(MyList).get(1)
    mylst.lst.append('65')
    print(DBSession.is_active, DBSession.is_modified(mylst))

The db wont get updated, it is still ['45'] and print returns

True, False

Am i doing something wrong or is this a bug?

Answer 1

By default, SQLAlchemy only tracks changes of the value itself, which works "as expected" for simple values, such as ints and strings:

alice.name = "Alice"
alice.age = 8

It also works when you assign a new value to a column of a "complex type", such as dict or list:

alice.toys = ['doll', 'teddy bear']

However, SQLAlchemy does not notice a change if you modify one of the elements in the list, or append/remove a value:

alice.toys[0] = 'teapot'
alice.toys.append('lego bricks')

To make this work you can either make sure you assign a new list each time:

toys = alice.toys[:]  # makes a "clone" of the existing list
toys[0] = 'teapot'
toys.append('lego bricks')
alice.toys = toys

Or have a read of the Mutation Tracking chapter in SQLAlchemy docs to see how you can subclass a list or a dict so they track modifications of their elements.

Also, since you mentioned you're using Postgres - there's a dedicated ARRAY type in Postgres which you can use instead of JSON if all you need is to store lists. However, what is said above about the mutation tracking applies to columns of ARRAY type too.

Answer 2

You could flag an instance as modified manually

from sqlalchemy.orm.attributes import flag_modified

def view(session):
    mylst = Session.query(MyList).get(1)
    mylst.lst.append('45')
    flag_modified(mylst, 'lst') # flag its `lst' attribute is modified
    print(Session.is_active, Session.is_modified(mylst))
    # (True, True)