idwaker idwaker - 1 month ago 7
Python Question

using list on postgresql JSON type with sqlalchemy

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

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.