使用Python 3.8在Snowflake中插入时间戳

Question

我在snowflake中有一个空表定义为；

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

它创建了正确的表，该表已使用sql中的desc命令进行了检查。然后使用雪花python连接器，我们尝试执行以下查询；

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)

就在这个查询定义变量之前，主要的挑战是将当前时间戳写入雪花。这里，CCD_ 2的值被定义为：；

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

2021-04-30 21:54:41.676406

但是，当我们尝试执行这个INSERT查询时，我们会得到以下errr消息；

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

我能在这里获得一些关于如何格式化日期时间值的帮助吗？感谢您的帮助。

Answer 1

除了@Lukasz提供的答案外，您还可以考虑将current_timestamp((定义为TIME_PREDICTED列的默认值：

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

然后只需插入ACCOUNT_ID和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)

它将自动将插入时间分配给time_PREDICTED

Answer 2

经过教育的猜测。使用执行插入时

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

这是一个字符串插值。ct是作为日期时间的字符串表示提供的，它与时间戳数据类型不匹配，因此出错。

我建议使用适当的变量绑定：

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) 
)
);

---

避免SQL注入攻击

避免使用Python的格式化函数绑定数据，因为存在SQL注入的风险例如：

# Binding data (UNSAFE EXAMPLE)
con.cursor().execute(
"INSERT INTO testtable(col1, col2) "
"VALUES({col1}, '{col2}')".format(
col1=789,
col2='test string3')
)

相反，将值存储在变量中，检查这些值(例如，通过在字符串中查找可疑的分号(，然后使用qmark或数字绑定样式绑定参数。

Answer 3

您忘记在｛ct｝之前和之后放置引号。代码应该是：

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)