[Python] How to run a SQL merge statement within a Python loop?

I have some SQL code:

CREATE TABLE TABLE_1 AS
(
SELECT ACCOUNT, 0 AS REDEEMER
FROM ACCOUNTS
)
WITH DATA
IN MY_SCHEMA
COMPRESS YES
;

MERGE INTO TABLE_1 A
USING
(
SELECT ACCOUNT
FROM REDEEMERS
WHERE CATEGORY = 'x'
) B
ON A.ACCOUNT = B.ACCOUNT
WHEN MATCHED THEN UPDATE
SET A.REDEEMER = 1
;


Let's say table_1 looks like this when created, with redeemer set to 0 for all rows:

account | redeemer
------------------
a | 0
b | 0
c | 0


If accounts a and c have redeemed category x, the merge should make table_1 look like this:

account | redeemer
------------------
a | 1
b | 0
c | 1


This works as intended. I now want to do this for multiple categories in turn by embedding the SQL into a for loop in Python using sqlalchemy, like this:

connection.execute(
f'''
CREATE TABLE TABLE_1 AS
(
SELECT ACCOUNT, 0 AS REDEEMER
FROM ACCOUNTS
)
WITH DATA
IN MY_SCHEMA
COMPRESS YES
;''')

categories = [
"x",
"y",
"z"
]

for i in categories:
#Set redeemer column to 1 for redeemers of the current category
connection.execute(
f'''
MERGE INTO TABLE_1 A
USING
(
SELECT ACCOUNT
FROM REDEEMERS
WHERE CATEGORY = '{i}'
) B
ON A.ACCOUNT = B.ACCOUNT
WHEN MATCHED THEN UPDATE
SET A.REDEEMER = 1
;''')

#Code to do some stuff with the data in table_1 goes here

#Set redeemer column back to 0 in preparation for the next loop
connection.execute(
f'''
UPDATE TABLE_1
SET REDEEMER = 0
;''')


However, this doesn't achieve the desired result. There's no error message, but the redeemer column does not seem to be getting updated by the merge. I've added a sum statement right after the merge which confirms redeemer is still set to 0 for all rows.

Database is DB2 if that helps shed any light on things.

Why does this code not work when run through Python?

I've also tried explicitly committing the merge statement like this, but the result is the same:

#Set redeemer column to 1 for redeemers of the current category
with Session(connection) as session:
connection.execute(
f'''
MERGE INTO TABLE_1 A
USING
(
SELECT ACCOUNT
FROM REDEEMERS
WHERE CATEGORY = '{i}'
) B
ON A.ACCOUNT = B.ACCOUNT
WHEN MATCHED THEN UPDATE
SET A.REDEEMER = 1
;''')

session.commit()

Continue reading...