0

I am trying to load a csv file into an Oracle table and I am getting this error:

DatabaseError: ORA-00928: missing SELECT keyword

Below is the code I am using:

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

def Insert_data():
db_user = "XXXXXXX"  # replace this with correct user name
db_password = "********"  # Set the environment variable DB_PASSWORD
db_connection_name = "***********"# repalce this with correct database name
#db_driver = "oracle.jdbc.OracleDriver","C:/Oracle/32bitclient/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar"
print("Connecting Now!!")
con = cx_Oracle.connect(db_user,db_password,db_connection_name)
print("Connected to Oracle!!")
lines=Create_list()
#print lines
cur=con.cursor()
print("Inserting data")
cur.executemany("INSERT INTO BRIODB.A608232_QUERY_RESULT ('InteractionId','QueryId','Score','StartOffsetInMs','EndOffsetInMs','SpeakerRole','QueryIdentity','SpeakerId') VALUES(:1,:2,:3,:4,:5,:6,:7,:8)",lines)
con.commit ()
cur.close()
print("completed")

If 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

I couldn't resolve the problem.

Jeff Holt
  • 2,940
  • 3
  • 22
  • 29
PriyalChaudhari
  • 363
  • 1
  • 7
  • 23

1 Answers1

1

If the table has uppercase column names, then remove the ' surrounding the column names in the insert statement's column list. If the case of the column names in the insert statement matches those found in the table, then replace ' with ". The latter is how you reference a column with special characters or when a column name is mixed case.

Jeff Holt
  • 2,940
  • 3
  • 22
  • 29
  • Hi my table names are in upper case so i modified my insert query to `cur.executemany("INSERT INTO BRIODB.A608232_QUERY_RESULT (InteractionId,QueryId,Score,StartOffsetInMs,EndOffsetInMs,SpeakerRole,QueryIdentity,SpeakerId) VALUES(:1,:2,:3,:4,:5,:6,:7,:8)",lines)` but i am getting following eroor `DatabaseError: ORA-01031: insufficient privileges` – PriyalChaudhari Aug 02 '17 at 13:43
  • You're connecting to a user that has insufficient privileges on at least one of BRIODB's tables. You need to make friends with your DBA. If you are the DBA, then searching [ora-1031](https://stackoverflow.com/questions/34732548/ora-01031-insufficient-privileges-on-insert) will be fruitful. – Jeff Holt Aug 02 '17 at 13:54
  • thanks . I solved the privileges proble. Now i can insert data but the error i am getting is `DatabaseError: ORA-01722: invalid number` when i am directly inserting in to database like this `INSERT INTO A608232_QUERY_RESULT values('34118470', '27', '45.63345', '89900', '90980', 'U', 'e54fd492-8877-4534-997b-9dbe9a8fbd74', '');` It is getting inserted. But when i run my code i am getting above error – PriyalChaudhari Aug 02 '17 at 14:24
  • is that error `DatabaseError: ORA-01722: invalid number` because of wrong arguments I am passing. But the same arguments working in the query editor If i give values directly – PriyalChaudhari Aug 02 '17 at 15:00
  • [This](https://stackoverflow.com/questions/12549029/sql-error-ora-01722-invalid-number) will probably help. All you have to do is type `ora-01722 on insert` in most browsers and it will find the information for you. The often quoted joke is LMGTFY. – Jeff Holt Aug 02 '17 at 15:28
  • Well thank you I checked the provided link . It is mentioned that the error might be trying to convert string to a number . I couldnt figure out which column giving error. Here is my table defination: `CREATE TABLE A608232_QUERY_RESULT( InteractionId NUMBER, QueryId NUMBER, Score NUMBER, StartOffsetInMs NUMBER, EndOffsetInMs NUMBER, SpeakerRole VARCHAR(5), QueryIdentity NVARCHAR2(500), SpeakerId CHAR(10) );` The values going in to these are mentioned in question. The print statements – PriyalChaudhari Aug 02 '17 at 15:47
  • That is exactly what it is doing. You have either a mismatch between the elements of `list` and the order of the column names in the column list of the insert statement or you have unexpected values in the input file. – Jeff Holt Aug 02 '17 at 15:55
  • Make that `lines`, not `list`. – Jeff Holt Aug 02 '17 at 16:12
  • Yeah but when I am importing same file in oracle the data gets inserted. So what is the problem with code. – PriyalChaudhari Aug 02 '17 at 20:22
  • I highly recommend you post a new question regarding (a) your latest error, (b) what you have tried that worked, and (c) what you have tried that does not work. Doing this in the comments is the worst possible approach to solving a problem and especially one that does not match the title of your post. – Jeff Holt Aug 02 '17 at 20:27