Any plan to implement VARIANT insert with prepared statement?

[cedricwebisoft]

First sorry for my english.

I am trying to insert some json data into a VARIANT field. I tried with a python dict, and a python dict converted to json with json.dumps(). Both failed. Either with

expecting VARIANT but got VARCHAR(16) for column JSON_IMPORT_CONFIG

or with

Python data type [dict] cannot be automatically mapped to Snowflake data type. Specify the snowflake data type explicitly.

Code:

raw_sql = f"INSERT INTO table(col1, col2, json_col) VALUES(?, ?, ?);"
db.execute(raw_sql, (1,2, {'test': 'test'}))

I know we can use parse_json in the sql or something like that, but I feel like the code might be subject to SQL injection this way, and I simplified the code above, but in fact I wrote an ORM over python-snowflake-connector to have a django-like behavior. So I cant tell in advance how many columns I have and where is the json in that, so using parse_json feel very hackish for something like that.

[sfc-gh-stakeda]

Not in the short term. Planning this next year but not confirmed.

[cedricwebisoft]

Not in the short term. Planning this next year but not confirmed.

Thanks for the answer, then what would be the suggested syntax to insert json into a variant type, using prepared statement for the others fields?

I cant figure it out.

cs.execute("INSERT INTO table(col1, col2) VALUES(?,?)", (1, {"test": "test"}))

[sfc-gh-stakeda]

How about this?

con.cursor().execute("create or replace table t (c1 int, c2 string, c3 variant)")
con.cursor().execute("insert into t(c1,c2,c3) select column1, column2, parse_json(column3) from values(?, ?, ?)", (1, 'test', '{"test":"test2"}',));

[effiban]

How about this?

con.cursor().execute("create or replace table t (c1 int, c2 string, c3 variant)")
con.cursor().execute("insert into t(c1,c2,c3) select column1, column2, parse_json(column3) from values(?, ?, ?)", (1, 'test', '{"test":"test2"}',));

Thanks!! I wasted a ton of time on this... 👍

[ryanhaarmann]

What are the efforts for native support to write the Variant column type? The insert select is not an option for some of our data processes. If spark is supported, what is the hold up for the python support? Is it that complicated to build the binary format? Is there any way we could override the existing Variant column type and add a process_bind_param to convert a python object to the binary format?

[github-actions]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of March 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