0

I am trying to load data to oracle table. here is my previous question where i have posted whole code how I am doing that .

this is the code:

def Create_list():
reader = csv.reader(open("Query_result_combined.csv","r"))
lines=[]
print("Creating a list")
for line in reader:
    lines.append(line)
return lines


def Insert_data():
#connection logic goes here 
print("Connecting Now!!")
#conn = cx_Oracle.connect(connstr)
con = cx_Oracle.connect(db_user,db_password,db_connection_name)
print("Connected to Oracle!!")
lines=Create_list()
cur=con.cursor()
print("Inserting data")
for line in lines:
    cur.execute("INSERT INTO A608232_QUERY_RESULT (InteractionId,QueryId,Score,StartOffsetInMs,EndOffsetInMs,SpeakerRole,QueryIdentity,SpeakerId) VALUES(:1,:2,:3,:4,:5,:6,:7,:8)",line)
con.commit ()
cur.close()
print("completed")

So i rectified all aproches suggested in answer to my question so that error has been solved now i am getting this new error ORA-01722: invalid number.

when i loaded the data directly in to oracle using import option that data is getting loaded. When i am trying to read the same file in this code and trying to push the data i am getting this error.

I print lines[:1] and lines[:2] this is the output I get:

[['InteractionId', 'QueryId', 'Score', 'StartOffsetInMs', 'EndOffsetInMs', 
'SpeakerRole', 'QueryIdentity', 'SpeakerId']]
[['InteractionId', 'QueryId', 'Score', 'StartOffsetInMs', 'EndOffsetInMs', 
'SpeakerRole', 'QueryIdentity', 'SpeakerId'], ['34118470', '27', '45.63345', 
'89900', '90980', 'U', 'e54fd492-8877-4534-997b-9dbe9a8fbd74', '']]
 Inserting data

can someone please point out the mistake that i am doing in the code

PriyalChaudhari
  • 363
  • 1
  • 7
  • 23

1 Answers1

1

You have a title line in your csv file, which is not part of the numerical data. You have to skip it using next(reader) just after mapping the csv.reader on the file handle.

Aside: use a context manager to ensure the file will be closed, and, no need for a loop, just convert the row iterator to a list (when the first line has been skipped) to read all the lines (will be faster)

def Create_list():
    with open("Query_result_combined.csv","r") as f:
       reader = csv.reader(f)
       next(reader)  # skips title line
       return list(lines)  # directly iterate on the rest of the lines
Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219