In messing around with getting the Trino SQLAlchemy Dialect to work with JSON columns/data, I came across at least a couple of necessary additions:
a visit_JSON method is needed in the TrinoTypeCompiler class since SQLAlchemy, by default, doesn't support a JSON type (as it is dialect-specific).
the TrinoDialect class needs both a _json_serializer and _json_deserializer member, which I believe can simply be json.dumps and json.loads, respectively.
Adding those two items allowed me to create tables with a JSON type and write to them all using SQLAlchemy. However, when attempting to read back from the table, I got the following error:
Traceback (most recent call last):
File "/Users/laserkaplan/PycharmProjects/proxima/proxima_warehouse/pipelines/process_shopify_orders/process.py", line 95, in <module>
print(session.execute(select(sis_table).limit(10)).all())
File "/Users/laserkaplan/.pyenv/versions/proxima-3.9.7/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 1068, in all
return self._allrows()
File "/Users/laserkaplan/.pyenv/versions/proxima-3.9.7/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 403, in _allrows
made_rows = [make_row(row) for row in rows]
File "/Users/laserkaplan/.pyenv/versions/proxima-3.9.7/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 403, in <listcomp>
made_rows = [make_row(row) for row in rows]
File "/Users/laserkaplan/.pyenv/versions/proxima-3.9.7/lib/python3.9/site-packages/sqlalchemy/sql/sqltypes.py", line 2693, in process
return json_deserializer(value)
TypeError: loads() takes 1 positional argument but 2 were given
So something is off with the deserializer, I just haven't had the chance to dig into it further yet.
Does anyone have any suggestions of how to proceed here? Or, if you have more SQLAlchemy JSON experience than I do (which isn't hard to do :) ), do you know if anything else is missing?
In messing around with getting the Trino SQLAlchemy Dialect to work with JSON columns/data, I came across at least a couple of necessary additions:
visit_JSON
method is needed in theTrinoTypeCompiler
class since SQLAlchemy, by default, doesn't support aJSON
type (as it is dialect-specific).TrinoDialect
class needs both a_json_serializer
and_json_deserializer
member, which I believe can simply bejson.dumps
andjson.loads
, respectively.Adding those two items allowed me to create tables with a JSON type and write to them all using SQLAlchemy. However, when attempting to read back from the table, I got the following error:
So something is off with the deserializer, I just haven't had the chance to dig into it further yet.
Does anyone have any suggestions of how to proceed here? Or, if you have more SQLAlchemy JSON experience than I do (which isn't hard to do :) ), do you know if anything else is missing?