Merge raw sql语句不工作是sqlalchemy连接execute

Question

当我们使用sqlalchemy通过python执行原始merge sql语句时，它没有添加预期的行，并且在python中显示执行成功，但是当在db中执行查询时，表中填充了所需的数据。对于下面的代码，如果我使用python中的任何update或select语句，它都会成功地给出预期的输出

from sqlalchemy.sql import text
from sqlalchemy import create_engine, types
oracle_connection_string = ('oracle+cx_oracle://{username}:{password}@'+
                       cx_Oracle.makedsn('{hostname}', '{port}', service_name='{service_name}'))

engine = create_engine(oracle_connection_string.format(
                    username='test',
                    password ='test',
                    hostname='test.com',
                    port='1521',
                    service_name='test.net'
                    ))
pop_stmt = """merge into test1 a
using (select id from test2) b
on (a.id= b.id)
when not matched then
insert (a.id) values (b.id)"""

with engine.connect() as con:
     con.execute(text(pop_stmt).execution_options(autocommit=True))

在这里我们没有得到任何错误，语句的执行显示从python成功，但数据没有插入到DB的表中

Answer 1

最近遇到了这个问题，显然像merge这样的某些语句需要被视为提交。更多详情请查看this。

with engine.begin() as conn:
    conn.execute(statement1)
    conn.execute(statement2)
    # ... and so one

Answer 2

此语句非常适用于我使用的sqlalchemy.__version__ '1.3.10'和Oracle 12.2.0.1.0

您可以使用rs.rowcount验证合并的行数

确保使用简单的测试数据，这些数据可以很容易地进行验证，如下所示：

create table test1 as
select 1 id  from dual;

create table test2 as
select 1 id  from dual union all
select 2 id  from dual
;

这段代码按照预期返回1行合并(在这里插入是因为您没有更新)：

pop_stmt = """merge into test1 a
using (select id from test2) b
on (a.id= b.id)
when not matched then
insert (a.id) values (b.id)"""

with engine.connect() as con:
    rs =  con.execute(text(pop_stmt).execution_options(autocommit=True))
    print(rs.rowcount)

1

Answer 3

遇到类似问题，请尝试更换

with engine.connect() as con:

至

with engine.begin(text(<query>)) as con: