How to use PL/SQL Code in Python correctly

I have an .ini file in which I have defined a PL/SQL Code(See below). I want to execute this PL/SQL code in Python to load data into my Oracle DB.

I have the following problem. The Python code is executed but the data is neither inserted or updated, the script just runs through without anything happening.

My data from the S3 bucket has the following columns in the specified order:. ID, STRING_COLUMN

Can anyone tell me what im doing wrong?

Kind regards

Jegor

Python Code

Python Code
....
# SQL Statement
sql_insert  = config.get('SQL_STATEMENT', 'sql_insert')

# Get File from S3 Bucket
obj = s3.Object(CDH_S3_Bucket, CDH_Path + '/' + s3_filename)
body = obj.get()['Body'].read().decode('utf-8').splitlines()

cursor.setinputsizes(None, 25)

batch_size = 10000

# ----------------------------------------------------------
print("Import Data...")
print("------------------------------")
csv_reader = csv.reader(body, delimiter=',')
# No Header
headings = next(csv_reader)
for line in csv_reader:
    data.append(line)
    if len(data) % batch_size == 0:
    cursor.executemany(sql_insert, data)
        data = []

if data:
    cursor.executemany(sql_insert, data)
    connection.commit()
#------------------------------------------------------------
# Close Cursor and Connection
cursor.close()
connection.close()
....

.Ini File PL/SQL Code

[SQL_STATEMENT]
oracle_table = TEST_TABLE
sql_insert = """Declare
       counter number;
    Begin
       Select
          Count(*) into counter
       From %(oracle_table)s
       Where ID  = :ID;

       If counter = 0 Then
          insert into %(oracle_table)s (ID,STRING_COLUMN) 
          values (:ID,:STRING_COLUMN);
          Else
             Update %(oracle_table)s
             Set STRING_COLUMN = :STRING_COLUMN;
          End If;
       End;"""

Solution

Your indentation is not correct so the script should not run:

for line in csv_reader:
    data.append(line)
    if len(data) % batch_size == 0:
    cursor.executemany(sql_insert, data)
        data = []

Should be:

for line in csv_reader:
    data.append(line)
    if len(data) % batch_size == 0:
        cursor.executemany(sql_insert, data)
        data = []

If you have a multiple of batch_size then at the end you will not COMMIT the data and the transaction will be rolled back.

if data:
    cursor.executemany(sql_insert, data)
    connection.commit()

Should be:

if data:
    cursor.executemany(sql_insert, data)

connection.commit()

You config file has SQL that contains %(oracle_table)s but you never appear to set the table name.

You can simplify the upsert by using a MERGE SQL statement.

sql_insert = """MERGE INTO %(oracle_table)s dst
USING (SELECT :id AS id, :string_column AS str FROM DUAL) src
ON (dst.id = src.id)
WHEN NOT MATCHED THEN
  INSERT (ID,STRING_COLUMN) 
  VALUES (src.id, src.str)
WHEN MATCHED THEN
  UPDATE
  Set STRING_COLUMN = src.str"""