Inserting Timestamp Into Snowflake Using Python 3.8

Question

I have an empty table defined in snowflake as;

CREATE OR REPLACE TABLE db1.schema1.table(
ACCOUNT_ID NUMBER NOT NULL PRIMARY KEY,
PREDICTED_PROBABILITY FLOAT,
TIME_PREDICTED TIMESTAMP
);

And it creates the correct table, which has been checked using desc command in sql. Then using a snowflake python connector we are trying to execute following query;

insert_query =  f'INSERT INTO DATA_LAKE.CUSTOMER.ACT_PREDICTED_PROBABILITIES(ACCOUNT_ID, PREDICTED_PROBABILITY, TIME_PREDICTED) VALUES ({accountId}, {risk_score},{ct});'
ctx.cursor().execute(insert_query)

Just before this query the variables are defined, The main challenge is getting the current time stamp written into snowflake. Here the value of ct is defined as;

import datetime
ct = datetime.datetime.now()
print(ct)

2021-04-30 21:54:41.676406

But when we try to execute this INSERT query we get the following errr message;

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 157 unexpected '21'.

Can I kindly get some help on ow to format the date time value here? Help is appreciated.

Answer 1

In addition to the answer @Lukasz provided you could also think about defining the current_timestamp() as default for the TIME_PREDICTED column:

CREATE OR REPLACE TABLE db1.schema1.table(
ACCOUNT_ID NUMBER NOT NULL PRIMARY KEY,
PREDICTED_PROBABILITY FLOAT,
TIME_PREDICTED TIMESTAMP DEFAULT current_timestamp
);

And then just insert ACCOUNT_ID and PREDICTED_PROBABILITY:

insert_query =  f'INSERT INTO DATA_LAKE.CUSTOMER.ACT_PREDICTED_PROBABILITIES(ACCOUNT_ID, PREDICTED_PROBABILITY) VALUES ({accountId}, {risk_score});'
ctx.cursor().execute(insert_query)

It will automatically assign the insert time to TIME_PREDICTED