Loading data from file to oracle table using python

Question

import cx_Oracle
con=cx_Oracle.connect("SYSTEM/123456@localhost:1521/xe")
print("conected to oracle db")
q='CREATE TABLE EMP2(id VARCHAR2(4),FIRST_NAME VARCHAR2(40),LAST_NAME VARCHAR2(30), DEPARTMENT VARCHAR2(10), Phone_number VARCHAR2(10), Address VARCHAR2(100), salary VARCHAR2(100))'
s="insert into EMP2 (id,first_name,last_name,department,phone_number,address,salary) values (:0,:1,:2,:3,:4,:5,:6)"
con=con.cursor()
con.execute(q)
records=[]
file=open("C:\\Users\\Shrishubh\\Downloads\\employees (2).txt")
for i in file.readlines():
    records.append(i.split("/ "))
print(records)
for i in records:
    con.executemany(s,records)

The above code is loading data from txt file to oracle dB using list in python. Code is getting executed but no data is getting loaded in table EMP2.Need help for the same.

See the cx_Oracle manual Batch Statement Execution and Bulk Loading — Christopher Jones
– Christopher Jones, Commented Jan 27, 2021 at 8:29

Punker · Accepted Answer · 2021-01-27 06:54:17Z

2

You've forgot about commiting your changes. Try adding:

con.commit()

To the very end of your code. Like this:

import cx_Oracle
con=cx_Oracle.connect("SYSTEM/123456@localhost:1521/xe")
print("conected to oracle db")
q='CREATE TABLE EMP2(id VARCHAR2(4),FIRST_NAME VARCHAR2(40),LAST_NAME VARCHAR2(30), DEPARTMENT VARCHAR2(10), Phone_number VARCHAR2(10), Address VARCHAR2(100), salary VARCHAR2(100))'
s="insert into EMP2 (id,first_name,last_name,department,phone_number,address,salary) values (:0,:1,:2,:3,:4,:5,:6)"
cur=con.cursor()
cur.execute(q)
records=[]
file=open("C:\\Users\\Shrishubh\\Downloads\\employees (2).txt")
for i in file.readlines():
    records.append(i.split("/ "))
print(records)
for i in records:
    cur.executemany(s,records)
con.commit()

edited Jan 27, 2021 at 6:54

answered Jan 27, 2021 at 6:28

Punker

1,87811 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shubham Bhoyar Over a year ago

'cx_Oracle.Cursor' object has no attribute 'commit' getting this error.

Punker Over a year ago

@ShubhamBhoyar I've just updated an answer due to your issue. Please, try it again :)

Punker Over a year ago

@ShubhamBhoyar please mark this answer as solution if that helped

Shubham Bhoyar Over a year ago

File contains only 10 records but in table 100 records are getting loaded.

Punker Over a year ago

You're executing executemany for records times. In your code: for i in records: cur.executemany(s, records). Try removing the loop and execute plain cur.executemany(s, records)

Christopher Jones · Accepted Answer · 2021-01-27 08:30:14Z

0

From the cx_Oracle manual Batch Statement Execution and Bulk Loading:

import cx_Oracle
import csv

. . .

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

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

with open('testsp.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    sql = "insert into test (id,name) values (:1, :2)"
    data = []
    for line in csv_reader:
        data.append((line[0], line[1]))
        if len(data) % batch_size == 0:
            cursor.executemany(sql, data)
            data = []
    if data:
        cursor.executemany(sql, data)
    con.commit()

answered Jan 27, 2021 at 8:30

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Collectives™ on Stack Overflow

Loading data from file to oracle table using python

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related