python-3.xsnowflake-cloud-data-platformsnowflake-schema

Snowflake Python connector insert doesn't accept variables

Using snowflake connector I am trying to insert a record in a table.

In snowflake doc they have shown examples with hard coded strings, but when I try to use my variables instead, it doesn't work. Please suggest how to use variables in this case.

conn.cursor().execute(
            "INSERT INTO cm.crawling_metrics(FEED_DATE,COMP_NAME,REFRESH_TYPE,CRAWL_INPUT,CRAWL_SUCCESS) VALUES " +
            "(score_creation_date,compName,sRefreshType,mp_sku_count,comp_sku_count)"

I get the below error

snowflake.connector.errors.ProgrammingError: 000904 (42000): SQL compilation error: error line 1 at position 100 invalid identifier 'SCORE_CREATION_DATE'

NOTE: In the above code if i hard code with String instead of variables, it works.

Kindly suggest what is the right way ?

Solution

  • You need to use string interpolation / formatting for your code to use these as actual variables:

    conn.cursor().execute(
    "INSERT INTO cm.crawling_metrics (FEED_DATE, COMP_NAME, REFRESH_TYPE, CRAWL_INPUT, CRAWL_SUCCESS) VALUES " +
    f"('{score_creation_date}', '{compName}', '{sRefreshType}', '{mp_sku_count}', '{comp_sku_count}')"
)