Semistructured Data support for UPDATE and INSERT

[olysyuk]

Hello
I have declared class using declarative_base

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, VARCHAR, ARRAY

Base = declarative_base()

class Video(Base):
    __tablename__ = 'videos'
    __table_args__ = {'schema': 'public'}
    id = Column(VARCHAR(8192))
    categories = Column(ARRAY(VARCHAR(8192)))

While trying to update existing video I change categories to array:

session = sessionmaker(create_engine(conf['snowflake_url']))()
existing_video = session.query(Video).filter_by(id='1').first()
existing_video.categories = ['category1', 'category2']
session.commit()

As a result I see following statement generated

UPDATE entities.videos SET categories='category1' WHERE entities.videos.id = '1'

I believe that something like this should be instead

UPDATE entities.videos SET categories=ARRAY_CONSTRUCT('category1') WHERE entities.videos.id = '1'

Any support of ARRAY, OBJECT, VARIANT on insert is highly appreciated. For example postgresql has it's own JSON type.

[gordonhart]

+1 from me — it's unfortunate to see that there hasn't been any motion on this since 2019. From what I can gather, this issue precludes the usage of VARIANT/ARRAY/OBJECT types using the SQLAlchemy connector and leads to opaque error messages like:

Insert value list does not match column list expecting 10 but got 13

[eyalyoli]

What I did as a workaround is to set the value of the array to func.array_construct(*my_list), but this works only for update statement, on insertion you'll need to insert the entity without the array and then update it later.

Can someone work on this please? This is very basic (totally a bug in the dialect)

[luciengaitskell]

Anyone looking for an INSERT ... SELECT workaround, the author of SQLAlchemy helped me out here: sqlalchemy/sqlalchemy#7894

[a114383]

@luciengaitskell Can you share a snippet? I'm not getting json encoded data like the author there suggests:

from sqlalchemy.types import VARCHAR
from snowflake.sqlalchemy import ARRAY, TIMESTAMP, VARIANT

class Example:
    tags_varchar = Column(VARCHAR(100), nullable=True)
    tags_variant = Column(VARIANT, nullable=True)
    tags_array = Column(ARRAY, nullable=True)

tags = ["one", "two"]
db.add(**Example({"tags_varchar": tags, "tags_variant": tags, "tags_array": tags})
# shows a query history of
# INSERT INTO "EXAMPLE" (tags_varchar,tags_variant,tags_array) 
# VALUES ('one','two','one','two','one','two')
# Insert value list does not match column list expecting 3 but got 6

[github-actions]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response