Unable to load data from file to Oracle table using executeMany()

Question

I am struggling with below code to twik where i am going wrong and why my table is not getting loaded with data

My Oracle table structure :

Table name : test

create table test
(
    id number,
    name varchar2(50 char),
    surname varchar2(50 char),
    age number,
    country varchar2(50 char)
)

I am not getting any error in log file and nor table loaded with data

I have 10,000 records in file and records are pipe separated : '|'

My code:

import logging
import cx_Oracle
import csv

insertQuery="insert into test (id,name,surname,age,country) values (:1, :2 , :3 , :4 , :5)"
invalue='line[0], line[1] , line[2] , line[3] , line[4]'
oracleconnection = 'user/password@hostIP/database'
my_separator='|'
file_name = demo_2021.csv

def loadtable(oracleconnection,my_query,file_name,my_separator):
    try:
        con=cx_Oracle.connect(oracleconnection)
        cur=con.cursor()

        # Predefine the memory areas to match the table definition
        cur.setinputsizes(None, 25)

        # Adjust the batch size to meet your memory and performance requirements
        batch_size = 10000

        with open(file_name, 'r') as csv_file:
            csv_reader = csv.reader(csv_file, delimiter=my_separator,header=None)
            sql = insertQuery
            data = []
            for line in csv_reader:
                data.append((invalue))
                if len(data) % batch_size == 0:
                    cur.executemany(sql, data)
                    data = []
            if data:
                cur.executemany(sql, data)
            con.commit()
    except Exception as er:
        logging.error(f'{er}')

What happens if you replace data.append((invalue)) with data.append(line)? — Edo Akse
– Edo Akse, Commented Aug 1, 2021 at 11:18
Correction, try with data.append(tuple(line)). It needs a list of tuples... — Edo Akse
– Edo Akse, Commented Aug 1, 2021 at 12:53
data.append((invalue)) makes no sense, because for every line you append a tuple with the same content: exactly one string declared at the very beginning. But your query expects five bind variables for each line. If line is a tuple, then append it as is and then executemany. See cx_Oracle: Batch Statement Execution and Bulk Loading — astentx
– astentx, Commented Aug 1, 2021 at 13:50
@astentx can you tell how to modified above code so that it will load data to table — codeholic24
– codeholic24, Commented Aug 1, 2021 at 16:41
@astentx : This should be dynamic : data.append((invalue)) not hard coded like you shared in above link — codeholic24
– codeholic24, Commented Aug 1, 2021 at 17:34

Christopher Jones · Accepted Answer · 2021-08-02 22:53:11Z

With this test data file:

1|Fred|Nurke|21|UK
2|Henry|Crun|21|UK

this code works:

import logging
import cx_Oracle
import csv
import sys
import os

if sys.platform.startswith("darwin"):
    cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")

insertQuery="insert into test (id,name,surname,age,country) values (:1, :2, :3, :4, :5)"
oracleconnection = 'un/pw@localhost/orclpdb1'
my_separator='|'
file_name = 'demo_2021.csv'

try:
    con=cx_Oracle.connect(oracleconnection)
    cur=con.cursor()

    # Predefine the memory areas to match the table definition
    cur.setinputsizes(None, 50, 50, None, 50)

    # Adjust the batch size to meet your memory and performance requirements
    batch_size = 10000

    with open(file_name, 'r') as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=my_separator)
        data = []
        for line in csv_reader:
            data.append((line[0], line[1], line[2], line[3], line[4]))
            if len(data) % batch_size == 0:
                cur.executemany(sql, data)
                data = []
        if data:
            cur.executemany(insertQuery, data)
        con.commit()

        
except Exception as er:
    print(er)

In SQL*Plus afterwards:

  ID NAME  SURNAME AGE COUNTRY
---- ----- ------- --- -------
   1 Fred  Nurke    21 UK
   2 Henry Crun     21 UK

Collectives™ on Stack Overflow

Unable to load data from file to Oracle table using executeMany()

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related