Skip to content

Any plan to implement VARIANT insert with prepared statement?Β #244

Closed as not planned

Description

@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.

Activity

  1. sfc-gh-stakeda commented on Dec 3, 2019

    @sfc-gh-stakeda
    Contributor

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

  2. cedricwebisoft commented on Dec 3, 2019

    @cedricwebisoft
    Author

    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"}))

  3. sfc-gh-stakeda commented on Dec 4, 2019

    @sfc-gh-stakeda
    Contributor

    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"}',));
    
    
  4. effiban commented on Aug 27, 2020

    @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... πŸ‘

  5. ryanhaarmann commented on Mar 1, 2021

    @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?

  6. github-actions commented on Mar 10, 2023

    @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

  7. timgraham commented on May 12, 2026

    @timgraham
    Contributor

    This is now tracked in #544.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    StaleenhancementThe issue is a request for improvement or a new feature

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions