CODEWITHSUNDEEP
pythonjsonoracle-database
Brennan
Brennan

Reputation: 21

how to use cursor.executemany with json data for oracle inserts

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"
      }
    ]
  }
}]

Upvotes: 0

Views: 702

Answers (1)

Tim Roberts
Tim Roberts

Reputation: 54635

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()

Upvotes: 1

Related Questions