idwaker idwaker - 2 months ago 14
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)
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)
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?


By default, SQLAlchemy only tracks changes of the value itself, which works "as expected" for simple values, such as ints and strings: = "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: = ['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:[0] = 'teapot''lego bricks')

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

toys =[:]  # makes a "clone" of the existing list
toys[0] = 'teapot'
toys.append('lego bricks') = 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.