How to increase the speed of writing to the database when parsing an XML file?

Question

Oracle

I made a program that parses XML files and writes data to the Oracle database.
The problem is that writing to the database is very slow. As I understand it because of the commit that comes after each insert, tell me how best to commit and write to the database, and is there a more convenient parsing method than the one I use?

main.py

import xml.etree.ElementTree as ET
import condb

tree = ET.parse(filexml)
element_xml_root = tree.getroot()
for elem in element_xml_root.findall('ZAP'):
    idpac = elem.find('ID_PAC').text
    fam = elem.find('FAM').text
    im = elem.find('IM') .text
    ot = elem.find('OT') .text
    Insert_pac(idpac, fam, im, ot)
    for data in elem.findall('DATA'):
        code_usl = data.find('CODE') .text
        date_usl = data.find('DATE') .text
        price_usl = data.find('PRICE') .text
        insert_usl(idpac, code_usl, date_usl, price_usl)

condb.py

import cx_Oracle

def con()
    db = cx_Oracle.connect("SYSTEM", "1234567890", "localhost/ORCL")
    dbcur = db.cursor()


def insert_pac(idpac, fam, im, ot):
        db = con()
        query = dbcur.prepare('INSERT INTO pac_table (idpac, fam, im, ot) VALUES (:idpac, : fam, : im, : ot)')
        dbcur.execute(query, (idpac, fam, im, ot))
        db.commit()
        dbcur.close()

def insert_usl(idpac, code_usl, date_usl, price_usl):
        db = con()
        query = dbcur.prepare('INSERT INTO usl_table (idpac, code_usl, date_usl, price_usl) VALUES (:idpac, : code_usl, : date_usl, : price_usl)')
        dbcur.execute(query, (idpac, code_usl, date_usl, price_usl))
        db.commit()
        dbcur.close()

Answer 1

The brakes are primarily due to the constant creation-deletion of the connection, secondly - due to the creation-deletion of the cursor, thirdly - due to the constant creation of the query, fourthly - due to commits.
All these operations need to be done once.
update Vladimir , in fact, I did not suggest creating 100500 tivial functions, you can do without them:

import cx_Oracle
import xml.etree.ElementTree as ET

tree = ET.parse(filexml)
element_xml_root = tree.getroot()
db = cx_Oracle.connect("SYSTEM", "1234567890", "localhost/ORCL")
dbcur = db.cursor()
query_pac = dbcur.prepare('INSERT INTO pac_table (idpac, fam, im, ot)'
                          ' VALUES (:idpac, : fam, : im, : ot)')
query_usl = dbcur.prepare('INSERT INTO usl_table (idpac, code_usl, date_usl, price_usl)'
                          ' VALUES (:idpac, : code_usl, : date_usl, : price_usl)')

for elem in element_xml_root.findall('ZAP'):
    idpac = elem.find('ID_PAC').text
    fam = elem.find('FAM').text
    im = elem.find('IM') .text
    ot = elem.find('OT') .text
    dbcur.execute(query_pac, (idpac, fam, im, ot))
    for data in elem.findall('DATA'):
        code_usl = data.find('CODE') .text
        date_usl = data.find('DATE') .text
        price_usl = data.find('PRICE') .text
        dbcur.execute(query_usl, (idpac, code_usl, date_usl, price_usl))
db.commit()
dbcur.close()

PS neatly - the code is written on the knee) I did not work with Oracle and, for example, I'm not sure if a commit is needed here - maybe autocommit works by default when inserting a single line.

Answer 2

respected longclaps wanted to say that you need to make a separate function for opening a connection and creating a cursor, a second function for inserts or use bulkinsert, a third for commit, a fourth for closing the cursor. or look whether there are triggers for inserting on a table or running stored procedures. this can also slow down the process.
in your situation, what you wrote is how to come to the store for each coin, climb into your wallet, open it, take out a coin, close your wallet, put it in your pocket and then again on a new one until you reach the required amount