[Python] Snowflake: Sqlalchemy ORM update variant field

I have an issue with updating a python model that represents a table in snowflake using sqlalchemy. One of the columns is a variant field and when I attempt to update the model and save it back to the database snowflake complains that the value is not a variant. I've tried passing in the data as a dictionary as well as a json string, neither works.

The model property is defined as:

data = db.Column(snowsql.VARIANT)


snowsql being an alias for the snowflake-sqlalchemy package, which is installed but it appears to only provide type dialects and not much else.

Does anyone have any experience doing this?

Update:

The data that I am adding is a python list of dictionaries:

new_data['data'] = [{'component': None, 'display_name': 'blah', 'key': None, 'row': {'display_name': 'blah', 'type': 'blah'}, 'sub_type': None, 'type': 'blah'}]


Doing a json.dumps when setting the variant property:

new_blah = Blah(
data = json.dumps(new_data['data'])
)


Results in:

Expression type does not match column data type, expecting VARIANT but got VARCHAR(8905) for column DATA
​

The json produced by the dumps:

"[{\"component\":null,\"display_name\":\"blah\",\"key\":null,\"row\":{\"display_name\":\"blah\",\"type\":\"blah\"},\"sub_type\":null,\"type\":\"blah\"}]"


Alternatively, attempting to assign the list without doing a json.dumps:

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 252004: None: Failed processing pyformat-parameters; 'dict' object has no attribute 'replace'
​

Continue reading...