SNOW-801402: Writing dictionary as variant using sqlalchemy orm

[alex-linx]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   3.9

2. What operating system and processor architecture are you using?

   macOS-13.3.1-arm64-arm-64bit

3. What are the component versions in the environment (pip freeze)?

   Replace with the output of python -m pip freeze

4. What did you do?

from datetime import datetime
from uuid import uuid4
import json

from sqlalchemy.orm import declarative_base, Session
from sqlalchemy import create_engine, Column, VARCHAR

from snowflake.sqlalchemy import URL, VARIANT

Base = declarative_base()

class MyTable(Base):
    __tablename__ = "my_table"

    id_: str = Column(
        "id_", VARCHAR(200), primary_key=True, default=lambda: str(uuid4())
    )
    json_data = Column("json_data", VARIANT, nullable=False)

Base.metadata.create_all(engine)

my_dict = {"hello": "world"}
my_item = MyTable(json_data=my_dict)

with Session(engine) as sess:
    sess.add(my_item)
    sess.commit()

When I run sess.commit() I get an error

ProgrammingError: (snowflake.connector.errors.ProgrammingError) 252004: Failed processing pyformat-parameters: 255001: Binding data in type (dict) is not supported.
[SQL: INSERT INTO my_table (id_, json_data) VALUES (%(id_)s, %(json_data)s)]
[parameters: {'id_': '26c72a93-d68d-4ff3-b40d-dbaa348309d6', 'json_data': {'hello': 'world'}}]
(Background on this error at: https://sqlalche.me/e/14/f405)

I also tried with my_item = MyTable(json_data=json.dumps(my_dict)), then I get a different error

ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002023 (22000): SQL compilation error:
Expression type does not match column data type, expecting VARIANT but got VARCHAR(18) for column JSON_DATA
[SQL: INSERT INTO my_table (id_, json_data) VALUES (%(id_)s, %(json_data)s)]
[parameters: {'id_': '35008208-e84d-4e1d-b751-df8a8997666b', 'json_data': '{"hello": "world"}'}]
(Background on this error at: https://sqlalche.me/e/14/f405)

I also tried converting the dictionary to a string and wrapping it with "PARSE_JSON()". None of it worked.

5. What did you expect to see?

   I expect to be able to run

sess.add(my_item)
sess.commit()

And the dictionary to be written to snowflake as a json object with VARIANT type.

Is there any way to do that?

[ilyatovbin-pp]

same issue with array, object types as well. looks like an old one and no one at Snowflake cares enough to fix this. see #299 there are some odd solutions there which might be helpful.

[mikwieczorek]

Bump. I was about to create almost identical issue. There is some workaround from the issue #299 mentioned already and Snowflake Knowledge Base. However I would expect same as @alex-linx that using Python dict or json.dumps(my_dict) would work correctly with OBJECT, VARIANT datatypes.

Maybe we can expect proper support when snowflake-sqlalchemy switches to SQLAlchemy 2.0? #452

[sfc-gh-dszmolka]

hi and thank you for submitting this issue and for all your patience here. Tried to make this work but it does not seem to be a way to pass the dict as a VARIANT upon initial table creation. We'll consider this request as a future enhancement but as always, if there's a possibility for submitting a PR, that would be greatly appreciated and speed things up.

For now, the workarounds already mentioned can be used (e.g. create the table then populate it with the VARIANT data afterwards as described in above KB article)

[steve-dyno]

Bumping this. It's been year since https://github.com/snowflakedb/snowflake-sqlalchemy/issues/299 was logged and we still can't do basic things with arrays. Sad to see the level of neglect on this repo.