I want to insert a pandas DataFrame into a Snowflake table, however I get
Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
when I run
session.write_pandas(df=my_df, table_name='MY_TABLE_NAME')
However, when I insert and overwrite the content of the table, then it works:
session.write_pandas(df=my_df, table_name='MY_TABLE_NAME', overwrite=True)
Am I missing some option here to append only?
Edit
For context, I am running this in a Snowflake Python Worksheet. Before the line with session.write_pandas(...) I query multiple tables (via session.table(...).filter(...).to_pandas()).
Edit 2
Running session.sql("SHOW PARAMETERS LIKE 'AUTOCOMMIT';").show() right before write_pandas, I see that AUTOCOMMIT is set to false. I could not find a way to set it in the Snowpark Python API docs. Is there some other way to set it?
| "key" | "value" | "default" | "level" | "description" | "type" |
|---|---|---|---|---|---|
| AUTOCOMMIT | false | true | ACCOUNT | The autocommit property determines whether is s... wrapped within a transaction or not. If autocom... statement that requires a transaction is execut... implicitly. If autocommit is off then an explic... to close a transaction. The default autocommit ... |
BOOLEAN |
Set AUTOCOMMIT to TRUE on account level in Snowflake.
Reposting @sriga's comment, which answered the question:
If you are changing the account parameter inside the stored procedure, it won't allow. Instead you can change the account level parameter by running Alter session set autocommit=True; else you can run your python script outside the snowflake and change the session parameters as mentioned in the below code
Snowflake enforces the prohibition on setting AUTOCOMMIT inside a stored procedure. Note that changing the AUTOCOMMIT behavior outside a stored procedure will continue to work.