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.

question from:https://stackoverflow.com/questions/65913862/loading-data-from-file-to-oracle-table-using-python

Answer 1

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