SNOW-1309051: Arrays not translating into proper sql #1914
Comments
github-actions
bot
changed the title
|
Hello @DreamwareDevelopment , Thanks for raising the issue, could you please share the code snippet/application/script so we can reproduce the issue. Regards, |
sfc-gh-sghosh
added
status-triage
|
Sure, from snowflake.sqlalchemy import ARRAY
from sqlmodel import SQLModel
...
def SomeModel(SQLModel, Table=True)
tags: list[str] = Field(default=[], sa_column=Column(ARRAY)) |
|
Hello @DreamwareDevelopment , With the specific version of the libraries you mentioned, The above code snippet throws an error for Could you please check the versions of the libraries? Regards, |
|
Hello @DreamwareDevelopment , Did you get a chance to go through the above questions, or is the issue resolved? Regards, |
sfc-gh-sghosh
added
the
status-information_needed
|
Sorry for the late reply, I've been moving forward with other work and missed your response. I'm using sqlalchemy<2.0.0,>=1.4.36 in my requirements.txt |
|
Thank you, @DreamwareDevelopment, for the update. Closing the issue as its resolved. Regards, |
sfc-gh-sghosh
added
status-triage_done
Python version
Python 3.11.4 (main, Jul 13 2023, 22:36:37) [Clang 14.0.3 (clang-1403.0.22.14.1)]
Operating system and processor architecture
macOS-13.6.2-arm64-arm-64bit
Installed packages
What did you do?
However, the connector is putting in empty strings instead of an empty array.
snowflake-connector-python/src/snowflake/connector/converter.py
Line 709 in 8706d83
I get the following error from sqlalchemy:
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error: syntax error line 1 at position 1,244 unexpected ','.When I change the line to return an empty array if there are no values, I get the following:
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002014 (22000): SQL compilation error: Invalid expression [ARRAY_CONSTRUCT()] in VALUES clauseHowever, this is strange because the sql sent to snowflake doesn't include the ARRAY_CONSTRUCT() function. So I assume that Snowflake is adding it on the backend.
I've tried patching it but I keep coming up with either the unexpected comma error, the ARRAY_CONSTRUCT error, or another one when I'm ensuring the arrays aren't empty:
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002023 (22000): 01b379c4-0001-eb7a-0000-0002a8dae7c5: SQL compilation error: Expression type does not match column data type, expecting ARRAY but got VARCHAR(4) for column TAGSThe text was updated successfully, but these errors were encountered: