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.

I believe timestamp should be passed with quotes

Sriga
– Sriga

2021-05-25 03:19:16 +00:00
Commented May 25, 2021 at 3:19 — Sriga
– Sriga, Commented May 25, 2021 at 3:19

codie-fz · Accepted Answer · 2021-05-24 21:47:39Z

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

answered May 24, 2021 at 21:47

codie-fz

1266 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2021-05-01 07:33:25Z

Educated guess. When performing insert with:

insert_query =  f'INSERT INTO ...(ACCOUNT_ID, PREDICTED_PROBABILITY, TIME_PREDICTED) 
VALUES ({accountId}, {risk_score},{ct});'

It is a string interpolation. The ct is provided as string representation of datetime, which does not match a timestamp data type, thus error.

I would suggest using proper variable binding instead:

ctx.cursor().execute("INSERT INTO DATA_LAKE.CUSTOMER.ACT_PREDICTED_PROBABILITIES "
                     "(ACCOUNT_ID, PREDICTED_PROBABILITY, TIME_PREDICTED) "
                     "VALUES(:1, :2, :3)",
                      (accountId,
                       risk_score, 
                       ("TIMESTAMP_LTZ", ct) 
                      )
                   );

Avoid SQL Injection Attacks

Avoid binding data using Python’s formatting function because you risk SQL injection. For example:
# Binding data (UNSAFE EXAMPLE)
con.cursor().execute(
    "INSERT INTO testtable(col1, col2) "
    "VALUES({col1}, '{col2}')".format(
        col1=789,
        col2='test string3')
    )
Instead, store the values in variables, check those values (for example, by looking for suspicious semicolons inside strings), and then bind the parameters using qmark or numeric binding style.

Ravikiran Rao · Accepted Answer · 2021-07-02 10:06:01Z

0

You forgot to place the quotes before and after the {ct}. The code should be :

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

answered Jul 2, 2021 at 10:06

Ravikiran Rao

343 bronze badges

Collectives™ on Stack Overflow

Inserting Timestamp Into Snowflake Using Python 3.8

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related