Merge raw sql statement is not working is sqlalchemy connection execute

user_11725310 0
Viewed 1521

When we are executing raw merge sql statement through python using sqlalchemy it is not adding the expected rows and execution showing as successful from python but when the query was executed in the db the table is populated with required data. For the same below code if i use any update or select statement from python it was successfully giving the expected output

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

Here we are not getting any error, the execution of the statement shows successful from python but data was not inserted in the table in the DB

sqlalchemy merge oracle sql python
3 Answers

Recently ran into this issue, apparently certain statements like merge need to be treated as committal. Please see this for more details.

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

This statement works perfectly for my using sqlalchemy.__version__ '1.3.10' and Oracle 12.2.0.1.0

You may validate the number of merged row using rs.rowcount

Be sure you use simple test data, that can be easy validated such as follows:

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
;

This snipped returns 1 row merged (here inserted as you do not update) as expected:

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
user_48081220

Faced similar issue try replacing

with engine.connect() as con:

to

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

user_181007180
Related