Insert data into Oracle table Using Python script. Expected str instance, bytes found

Question

I want to insert data into Oracle Table, where one row type-RAW(In python BYTES).

sql = f"INSERT /*+ APPEND */ INTO {table}  ({columns}) VALUES  ({values})"
ins_r = ', '.join(lst)
cur.execute(sql, ins_r)

This is my printed SQL:

INFO - INSERT /*+ APPEND */ INTO TR.ZZ_TEST  (REGION_ID, REGION_NAME, CHANGE_DATE, MD5) VALUES  (:REGION_ID, :REGION_NAME, :CHANGE_DATE, :MD5)

And this my data which I want to Insert:

['1', "'TEST'", "to_date('2021-09-28 18:48:23','YYYY-MM-DD HH24:MI:SS')", b'aWQ\x9b\xa6(\x17zj\xab\x97\x8e\x12uE4']

And I have the error:

ins_r = ', '.join(lst)
TypeError: sequence item 3: expected str instance, bytes found

And I wanted to convert bytes to str, but is not correct.

Answer 1

You can convert your parameter to list of tuples in order to be able insert multiple values along with using executemany method rather than execute as being more performant for bulk loads.

Convert the current to_date conversion of the DB to datetime.datetime(a type of datetime module), and count the number commas within the columns string in order to generate a bind variables list(values -> in this case :0, :1, :2, :3)

import cx_Oracle
import datetime
...
...
table = 'tr.zz_test'
columns = 'region_id,region_name,change_date,md5'
prm=[    
    (11,'TEST1',datetime.datetime(2021, 9, 29, 17, 28, 11),b'aWQ\x9b\xa6(\x17zj\xab\x97\x8e\x12uE4'),
    (12,'TEST2',datetime.datetime(2021, 9, 28, 18, 48, 23),b'aWQ\x9b\xa5(\x17zj\xab\x97\x8e\x12uE2')
]

val=""
for i in range(0,columns.count(",")+1):
    val+=':'.join(' '+str(i)) + ','
values=val.rstrip(",")
my_query = f"INSERT /*+ APPEND */ INTO {table}({columns}) VALUES({values})"

cur.executemany(my_query,prm)
con.commit()