SQL Alchemy - schema extraction with TypeDecorator

Question

I am using a TypeDecorator for Json extraction and another model uses it for one of its columns. I am storing python list objects using this TypeDecorator.

def process_bind_param(self, value, dialect):
    # etc...

def process_result_value(self, value, dialect):
    # THIS NEVER GETS CALLED!!
    if value is not None:
        return json.loads(value)
    return value

When I store data in the model that uses the decorator bind_param is called appropriately. Now I extract the schema from the model using the TypeDecorator via the following:

table = Table(table_name, meta, autoload=True, autoload_with=sengine)

Now for the query test (there many ways to loop and extract):

for record in source.query(table).all():
   print type(record.column_using_custom_type_list_object) == str
   # returns true ... this should be false ... should be of type list
   # json.loads() returns type list ???
   print record.column_using_custom_type_list_object[some_index] 
   # naturally this prints a character in the string, not a cell

The problem is process_result_value() is not being called when the table is queried and the object and then the column is fetched. I assumed SQLAlchemy reflection handles the dependencies? Am I missing some options in the constructor to transfer metadata requiring a custom type decorator?

Answer 1

I'm not sure if it's the same issue, but I think it's related enough for me to write the answer here.

After storing an object and trying to read the property, function process_result_value is not called because the object is cached. (Found that here http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/11406)

So the solution is simply to invalidate the object.

session.expire(obj)

https://docs.sqlalchemy.org/en/20/orm/session_basics.html#expiring-refreshing

Answer 2

A solution that I found through experimentation.

Apparently, in the Table() constructor for schema extraction type coercion does not happen for custom TypeDecorators. To solve this, you do the following:

table = Table(table_name, meta, Column(column_name, custom_type_Decorator), autoload=True, autoload_with=sengine)

This is unfortunate since I thought reflection would pick up this dependency in one of the columns.

Not sure why this was not answered after a few days. I guess new members are at a disadvantage when asking questions in accordance with the gamification rules system stackoverflow is using.