0

Inserting the rows into oracle table with cursor execute takes a lot of time in my python script. Turned auto-commit to false but still the same. Tried executemany with no luck, as it throws error. Below is my code:

insert_statement='INSERT INTO mytable
VALUES (:1,:2)
'
r = requests.get(url,cert=(auth_certificate,priv_key),verify=root_cert, timeout=3600, stream=True)

data=json.loads(r.text)

for item in data:
    try:
        id=(item.Get('data').get("test").get("id"))
    except Attribute Error:
        id=''
    try:
        name=(item.Get('data').get("name"))
    except Attribute Error:
        name=''
    rows=(id,name)
    cursor.Executemany(insert_statement,rows)
    connection_target.commit()

this throws error: TypeError: parameters should be a list of sequences/dictionaries or an integer specifying the number of times to execute the statement

could you please advise how to correctly use executemany with json data ?

Here the sample json data :

json_data=[{
  "publishTime" : "2021-05-29T12:52:15.129Z",
  "data" : {
    "identifier" : {
      "domain" : "AB",
      "id" : "1771374",
      "version" : "58593668"
    },
    "Accounts" : [
      {
        "effectiveEndDate" : "3000-01-01T00:00:00Z",
        "Name" : "w (S)",
        "effectiveStartDate" : "2016-09-16T04:21:33Z",
        "sAccount" : "SGLDPB_A"
      }
    ]
  }
}]
Tim Roberts
  • 48,973
  • 4
  • 21
  • 30
Brennan
  • 21
  • 6
  • Your insert statement has 20 columns, but your `rows=(id,name)` only has 2. How many are you trying to insert? Could you edit your question to add some details about your table and JSON structures? – kfinity Nov 29 '21 at 18:50
  • sorry , my bad i copied a wrong insert statement. Only 2 rows for now, but will have many later. i have added the same json data input. Just want to know the correct way of using executemany – Brennan Nov 29 '21 at 19:05
  • You are passing ONE row to `executemany`. That's not how it works. Your `for` loop should build up a list, where each element is a row you want to insert. Then, after the loop has ended, you call `executemany` and pass your list of rows. – Tim Roberts Nov 29 '21 at 19:10

1 Answers1

1

You need to gather up a list of rows, and then pass that list to executemany after the loop is complete. And watch your capitalization. Plus, the names in your code do not match the names in your sample JSON.

Note that I've modified this to do 1,000 rows at a time.

insert_statement='INSERT INTO mytable VALUES (:1,:2)'
r = requests.get(url,cert=(auth_certificate,priv_key),verify=root_cert, timeout=3600, stream=True)

data=json.loads(r.text)

myrows = []
for item in data:
    try:
        id=item['data']["identifier"]["id"]
    except AttributeError:
        id=''
    try:
        name=item['Accounts'][0]['Name']
    except AttributeError:
        name=''
    myrows.append( (id, name) )
    if len(myrows) >= 1000:
        cursor.executemany(insert_statement,myrows)
        myrows = []

cursor.executemany(insert_statement,myrows)
connection_target.commit()
Tim Roberts
  • 48,973
  • 4
  • 21
  • 30
  • Thank you Tim. Sorry new to python and learning. The above atleast does not give me any error. It runs but seems to be stuck on executemany step . There are no issues with table as i could insert data separately into it. Do you have any advise? – Brennan Nov 29 '21 at 23:36
  • How many rows are you adding? I believe there are better ways to do bulk insertion into an Oracle DB. – Tim Roberts Nov 29 '21 at 23:45
  • there are about 100k rows. what would be the efficient way to perform the bulk insertion? – Brennan Nov 29 '21 at 23:54
  • I was wrong, `executemany` is the preferred method. It makes one network round-trip. Unless your network is from 1995, I would expect that to take less than 10 seconds. – Tim Roberts Nov 30 '21 at 00:03
  • Thanks Tim. After 30 mins it errors out with "cx_Oracle.DatabaseError: ORA-01653: unable to extend table mytable by 8192 in tablespace DATA. Could the prolonged execution time be because of tablespace? – Brennan Nov 30 '21 at 18:56
  • That's too Oracle-specific for me. There seems to be something about it here, but I'm not smart enough to interpret it. https://stackoverflow.com/questions/27129376/ora-01653-unable-to-extend-table-by-in-tablespace-ora-06512 – Tim Roberts Nov 30 '21 at 19:03
  • You MIGHT try breaking this up into smaller chunks, like doing 1000 rows at a time. I'll show how. – Tim Roberts Nov 30 '21 at 19:04
  • thanks . I will take a look. I really appreciate your prompt help so far. cheers – Brennan Nov 30 '21 at 19:07
  • I troubleshooted the script run from database , i have 95k rows in the json file ,but the executemany keeps ingesting duplicate rows into table in loop. this may be the reason why tablespace was running out. The execute just worked fine, not sure if i missed anything in the code ? – Brennan Nov 30 '21 at 20:05
  • You shouldn't get duplicates unless there are duplicates in your JSON data. My (revised) loop ingests 1,000 rows, then clears the rowset, then ingests another 1,000 rows. – Tim Roberts Nov 30 '21 at 20:13