Binding data in type (dict) is not supported-CodePudding

I am trying to write json object into a particular column of my sql table

variant_str = response.json()

print(con.cursor().execute('INSERT INTO TEST_TABLE (JSON_STR) VALUES (?)', (variant_str,)))

Here, the variant_str is of type dict.

I get an error that:

snowflake.connector.errors.ProgrammingError: 252004: Failed processing pyformat-parameters; 255001: Binding data in type (dict) is not supported.

The table's ddl where I am trying to load the data is this:

create or replace TABLE TEST_TABLE (
    JSON_STR VARIANT
);

CodePudding user response：

It is possible to use ? as the placeholder for parameter binding:

import snowflake.connector

snowflake.connector.paramstyle='qmark'

Insert using INSERT INTO ... SELECT PARSE_JSON(...)

con.cursor().execute('INSERT INTO TEST_TABLE (JSON_STR) SELECT PARSE_JSON(?)'
                     , (variant_str))

where variant_str is a valid JSON string

CodePudding user response：

Don't use response.json(), use response.text(). json() decodes the JSON into dictionaries and lists, but you need to store the raw JSON string into the table.