Teradata MERGE 通过 SQLAlchemy 执行时没有产生任何结果 [英] Teradata MERGE yielding no results when executed through SQLAlchemy

问题描述

我正在尝试使用 python 和 sqlalchemy 下载一些数据，在 Teradata 服务器上创建一个临时临时表，然后将该表合并到我创建的另一个表中以永久存储这些数据.我正在使用 sql = slqalchemy.text(merge) 和 td_engine.execute(sql) 其中 merge 是一个类似于下面的字符串:

I'm attempting to use python with sqlalchemy to download some data, create a temporary staging table on a Teradata Server, then MERGEing that table into another table which I've created to permanently store this data. I'm using sql = slqalchemy.text(merge) and td_engine.execute(sql) where merge is a string similar to the below:

MERGE INTO perm_table as p
USING temp_table as t
ON p.Id = t.Id
WHEN MATCHED THEN
UPDATE
SET col1 = t.col1,
col2 = t.col2,
...
col50 = t.col50
WHEN NOT MATCHED THEN
INSERT (col1,
col2,
...
col50)
VALUES (t.col1,
t.col2,
...
t.col50)

脚本一直运行到最后都没有错误，并且 SQL 通过 Teradata Studio 正确执行，但是由于某种原因，当我通过 SQLAlchemy 执行它时，该表不会更新.但是，我还运行了不同的 SQL 表达式，例如从同一个 Python 脚本填充 perm_table 的插入，并且它运行良好.也许有一些特定于 MERGE 和 SQLAlchemy 组合的东西?

The script runs all the way to the end without error and the SQL executes properly through Teradata Studio, but for some reason the table won't update when I execute it through SQLAlchemy. However, I've also run different SQL expressions, like the insert that populated perm_table from the same python script and it worked fine. Maybe there's something specific to the MERGE and SQLAlchemy combo?

推荐答案

由于您直接使用引擎，而不使用事务，因此您可能(除非您有看不见的配置)依赖于 SQLAlchemy 的 autocommit，它通过检测数据更改操作(例如 INSERT)来工作等.可能 MERGE 不是检测到的操作之一.试试

Since you're using the engine directly, without using a transaction, you're probably (barring unseen configuration on your part) relying on SQLAlchemy's version of autocommit, which works by detecting data changing operations such as INSERTs etc. Possibly MERGE is not one of the detected operations. Try

sql = sqlalchemy.text(merge).execution_options(autocommit=True)
td_engine.execute(sql)

这篇关于Teradata MERGE 通过 SQLAlchemy 执行时没有产生任何结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！