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

问题描述

我试图将python和sqlalchemy一起使用以下载一些数据，在Teradata Server上创建一个临时登台表，然后将该表合并到我创建的另一个表中，以永久存储此数据.我正在使用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的自动提交，该功能通过检测数据更改操作(例如INSERT)来工作等.可能不是检测到的操作之一.试试

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)

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