Python and Snowflake Unable to insert a data frame transformed into JSON into a variant field on snowflake

Question

I am trying to insert a JSON variable in python into snowflake. At first, I transformed the data frame into JSON using:

data = data.dropna(axis=0, how='all')
json_data = data.to_json(orient="records")

Then I performed the INSERT INTO query into snowflake:

conn = snowCtx.connect(
    user=user,
    password=password,
    account=account,
    database='MY_DB',
    schema='PUBLIC',
    warehouse='COMPUTE_WH',
    role='SYSADMIN'
)

cursor = con.cursor()

query = """INSERT INTO ALL_SURVEYS_VARIANT(ALL_SURVEYS_VARIANT_ID, SURVEY_ID, SURVEY_DATA, DATE_ADDED,
         DATE_UPDATED) VALUES (AUTO_INCREMENT_VARIANT_TABLE.nextval, (SELECT PARSE_JSON(%s)), %s, %s, %s)"""
if(cursor.execute(query, (json_data, nextval, now, now))):
    print(filename+' JSON File INSERT query added successfully')
else:
    print(filename+' JSON File INSERT query was not added')

I used cursor.execute(query, (json.dumps(json_data), nextval, now, now)).

But I keep getting the following error:

The following error occured: 002014 (22000): SQL compilation error:

Invalid expression [PARSE_JSON('[{"start":"2020-06-24T12:48:57.504+03:00","end":"2020-07-02T10:05:...,"_xform_id":535961.0}]')] in VALUES clause

Is the error you provided in full? This "2020-07-02T10:05:... seems missing a ", this is why I am asking. What if you run separately SELECT PARSE_JSON, does that work? — Sergiu

Marcel Marcel · Accepted Answer · 2020-11-25T09:47:29

PARSE_JSON() is not allowed in the VALUES-clause of your query // You cannot combine SELECT and VALUES in one single INSERT-statement.

Reason: When you insert data you can either use the result of a SELECT-statement or explicitly specified values, not a combination of both.

See here: https://docs.snowflake.com/en/sql-reference/sql/insert.html

To solve this, you have to rewrite your query so that you are using the following syntax:

insert into my_table select ...;

You can also think about creating a CTE in front.

Python and Snowflake Unable to insert a data frame transformed into JSON into a variant field on snowflake

1 Answers