1

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 ?

Vinod Jayachandran
  • 3,726
  • 8
  • 51
  • 88

1 Answers1

5

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}')"
)
Yuval Itzchakov
  • 146,575
  • 32
  • 257
  • 321
  • Can you explain why the character f in the above ? Is it typo ? – Vinod Jayachandran Apr 02 '20 at 08:15
  • 1
    I tried the following but didn't work 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})" ) Got below error syntax error line 1 at position 101 unexpected 'score_creation_date'. syntax error line 1 at position 123 unexpected 'compName'. syntax error line 1 at position 134 unexpected 'sRefreshType'. – Vinod Jayachandran Apr 02 '20 at 08:23
  • @VinodJayachandran `f` is for Python's [string interpolation](https://stackoverflow.com/questions/4450592/is-there-a-python-equivalent-to-rubys-string-interpolation). Try assigning the string to a variable before passing it to `execute` to see the full generated query. Which Python version are you running? – Yuval Itzchakov Apr 02 '20 at 08:53
  • It was failing because of data type mismatch during db insert. Added single quotes surrounding the {variable} for appropriate datatype conversion. Updated the answer too. – Vinod Jayachandran Apr 02 '20 at 10:13
  • Thanks a bunch. You made my day. – Vinod Jayachandran Apr 02 '20 at 10:14