Merge raw sql statement is not working is sqlalchemy connection execute

Question

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

Answer 1

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