2

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
LOTR
  • 113
  • 1
  • 1
  • 10
  • The second argument to `cur.execute()` should be a list of all the values, not a string. – Barmar Sep 29 '21 at 04:08
  • And if you're using named parameters, it should be a dictionary. – Barmar Sep 29 '21 at 04:09
  • And datatypes should be corresponding python datatypes. There cannot be `to_date` or anything similar, because bind variables substitute the data, and the data is passed as is, not as SQL expression (because it is not a part of SQL statement and is not evaluated). – astentx Sep 29 '21 at 05:51

2 Answers2

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()
Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

The second argument to cur.execute() should be the list of values, not a string made by joining them. So use

cur.execute(sql, lst)
Barmar
  • 741,623
  • 53
  • 500
  • 612