CODEWITHSUNDEEP
pythonpandassqlalchemysnowflake-cloud-data-platform
NAB0815
NAB0815

Reputation: 471

Data load from pandas to snowflake rolling back

I am trying to push data into a snowflake table using pandas but something weird is happening. The data loads into the snowflake table(I can see it) and after a couple of seconds(like 2 seconds) then I can see no records. I have no idea what's happening here

This is my code block. I'd really appreciate it if I can get some help.

connection = create_engine(
    'snowflake://{user}:{password}@{account}/{database}/{schema}?warehouse={warehouse}'.format(
        user=''xxxxx,
        password='xxxxx',
        account='xxxx.xxxx-xxxxxxxx-1',
        warehouse='xxxx',
        database='xxxxx',
        schema='xxxxxx'
    )
)

Transformation

with connection.connect().execution_options(autocommit=True) as conn:
  check_sql = "select * from subscription_changes_new"
  check_df = pd.read_sql(check_sql, conn)
  if len(check_df) > 0:
    conn.execute(text("delete from subscription_changes_new'"))
    subscription_changes.to_sql('subscription_changes_new', conn, if_exists='append', index=False, index_label=None)
  elif len(check_df) == 0:
    subscription_changes.to_sql('subscription_changes_new', conn, if_exists='append', index=False, index_label=None)  
  else:
    None

I am using python 3.7

Upvotes: 1

Views: 242

Answers (1)

datamonk
datamonk

Reputation: 74

I am unable to reproduce the issue using python 3.7.9, and I don't see any issue with your code. Is there any other code in your program not shown that could be having an effect?

To troubleshoot I would recommend checking the History tab in Snowflake to see if any other processes are causing the deletion. I would also recommend changing your logic to use something like this:

with connection.connect().execution_options(autocommit=True) as conn:
    check_sql = "select count(*) as cnt from subscription_changes_new"
    check_df = pd.read_sql(check_sql, conn)

    if check_df.iloc[0, 0] > 0:
        conn.execute(text("delete from subscription_changes_new"))
        subscription_changes.to_sql('subscription_changes_new', conn, if_exists='append', index=False, index_label=None)
        print('I deleted and inserted')
    elif check_df.iloc[0, 0] == 0:
        subscription_changes.to_sql('subscription_changes_new', conn, if_exists='append', index=False, index_label=None)
        print('I just inserted')
    else:
        print('I did nothing')

This will have the same effect but won't require compute for the check query, and depending on the size of the table will improve performance by only hitting the metadata cache in Snowflake.

Upvotes: 1

Related Questions