Using a list by JSON type postgresql with sqlalchemy

Question

I am using a pyramid with sqlalchemy, pyramid_tm and postgresql to verify 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 am using the JSON type. When i do it

 mylst = MyList(lst=[]) 

I can see that an empty [] list is also being created in the database, 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 the database, and print returns

 True, True 

continued from above [edit] at the next request (already done above)

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

db will not be updated, it is still ['45'] and print returns

 True, False 

Am I doing something wrong or is it a mistake?

Answer 1

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

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

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

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

However, SQLAlchemy does not notice the changes if you change one of the items in the list or add / remove a value:

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

For this to work, you can make sure that you assign a new list every time:

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

Or, read the Mutation Tracking chapter in the SQLAlchemy documentation to see how you can subclass a list or an indication so that they can track changes to their elements.

In addition, since you mentioned that you are using Postgres, Postgres has a special type of ARRAY that you can use instead of JSON if all you need is to store lists. However, what was said above about mutation tracking also applies to ARRAY columns.

Answer 2

You can specify an instance with manually modified

 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) 

Answer 3

Try DBSession.flush() after mylst.lst.append('45') . This allows you to update the database before pyramid_tm completes the commit.

Further information can be found here: http://docs.sqlalchemy.org/en/latest/orm/session.html#flushing