CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
alok sharma
alok sharma

Reputation: 35

cx_Oracle.DatabaseError ORA-01722: invalid number python

I need to insert multiple records from csv to oracle database.but getting below error .As Far as I understood it is throwing error because it is trying to convert date and time from string to date data type and throwing error . how can I rectify this error so that it get inserted . initially I am trying to insert only one row but it will have around half a million and also I am not allowed to change datatype at db level .There are similar questions but couldn't really understand or find solution for it that is why posting this .

Note:This code is working for different csv and different table having two columns and db datatype having varchar for both columns

Error:

    Traceback (most recent call last):
      File "D:\test\insertfutclient.py", line 26, in <module>
        cursor.executemany(sql, data)
    cx_Oracle.DatabaseError: ORA-01722: invalid number

MY CSV file:

CL_SEQ,CL_TMID,CL_CLIENT_ID,CL_PAN,CL_CLI_STATUS,CL_PAN_STATUS,CL_ACTION_TYPE,CL_CREATED_DATE,CL_CREATED_BY,CL_MODIFIED_DATE,CL_MODIFIED_BY
3793375,21,MEN0008,ARJPP6330Q,A,V ,0,9/6/2019 14:23,SYSTEM,9/9/2019 11:28,SYSTEM

My code:

    # 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 = 1

    with open('C:\\Users\\ncdex1124\Desktop\\New folder\\file9.csv', 'r') as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        sql = "INSERT INTO UCIDBA.fut_client_list VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)"
        data = []
        for line in csv_reader:
            data.append((line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7],line[8],line[9],line[10]))
            if len(data) % batch_size == 0:
                cursor.executemany(sql, data)
        if data:
            cursor.executemany(sql, data)
        connection.commit()
        print("successful")

Databse table info :

    desc fut_client_list
    Name                  Null     Type                                                                                                                                                                                                                

    CL_SEQ                NOT NULL NUMBER                                                                                                                                                                                                              
    CL_TMID                        VARCHAR2(5)                                                                                                                                                                                                         
    CL_CLIENT_ID                   VARCHAR2(10)                                                                                                                                                                                                        
    CL_PAN                         VARCHAR2(10)                                                                                                                                                                                                        
    CL_CLI_STATUS                  CHAR(1)                                                                                                                                                                                                             
    CL_PAN_STATUS                  CHAR(2)                                                                                                                                                                                                             
    CL_ACTION_TYPE                 VARCHAR2(1)                                                                                                                                                                                                         
    CL_CREATED_DATE                DATE                                                                                                                                                                                                                
    CL_CREATED_BY                  VARCHAR2(10)                                                                                                                                                                                                        
    CL_MODIFIED_DATE               DATE                                                                                                                                                                                                                
    CL_MODIFIED_BY                 VARCHAR2(10)

Upvotes: 0

Views: 1872

Answers (1)

kfinity
kfinity

Reputation: 9091

Your datatypes don't match up - on the Python side, all of your variables are strings (translated to VARCHAR2), but on the Oracle side, your table has a variety of datatypes. You can either fix this on the Python side or the Oracle side.

Python side: convert strings into appropriate datatypes. Cx_Oracle understands datetime.datetime objects, so convert your date strings to those.

data.append((int(line[0]),line[1],line[2],line[3],line[4],line[5],line[6],
    datetime.datetime.strptime(line[7], "%m/%d/%Y %H:%M"),line[8],
    datetime.datetime.strptime(line[9], "%m/%d/%Y %H:%M"),line[10]))

OR

Oracle side: convert varchar2 arguments into appropriate datatypes

"INSERT INTO UCIDBA.fut_client_list VALUES (to_number(:1),:2,:3,:4,:5,:6,
    to_date(:7,'mm/dd/yyyy hh24:mi'),:8,
    to_date(:9,'mm/dd/yyyy hh24:mi'),:10,:11)"

I added newlines to these code snippets for readability, but don't include those in your code.

Upvotes: 2

Related Questions