CODEWITHSUNDEEP
pythonpython-3.xoracle-databasecx-oraclebind-variables
llanato
llanato

Reputation: 2491

ORA-01722: invalid number - Python with cx_Oracle

I have the below script inserting into an Oracle database. If I manually put all the passed in values into an INSERT statement and run the hardcoded statement in Python the INSERT works but for some reason when I use the below way it always fails with an ORA-01722: invalid number.

I thought it was original the datetime value so I tried TO_DATE(TO_CHAR(:9, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'), it didn't make any difference.

Spent ages trying different suggestions from SO and the internet but no luck as of yet.

data = ['NULL', 'NULL', 'NULL', 'NULL', '2', 'Test Text', 'NULL', 'NULL', '2017-06-02 16:00:00', '20180910122315440220.csv']

conn = cx_Oracle.connect(env.DB_USER, env.DB_PASSWORD,cx_Oracle.makedsn(env.DB_HOST, env.DB_PORT, env.DB_NAME));

# Disable auto commit.
conn.autocommit = False

cursor = conn.cursor()

sql = """INSERT INTO TESTDB (TESTDB_ID, TESTDB_ACYR_CODE, TESTDB_SEM_CODE, TESTDB_MODULEID, TESTDB_STU_PIDM, TESTDB_COMPONENT_ID, TESTDB_COMPONENT_NAME, TESTDB_GRADE_MARK, TESTDB_GRADER_PIDM, TESTDB_GRADE_MODIFIED_DATE, TESTDB_CREATION_DATE, TESTDB_FILENAME, TESTDB_ACTIVITY_DATE, TESTDB_USER_ID) VALUES(TESTDB_SEQ.NEXTVAL, :1, :2, :3, :4, :5, :6, :7, :8, TO_DATE(:9, 'YYYY-MM-DD HH24:MI:SS'), SYSDATE, :10, SYSDATE, USER)"""

cursor.prepare(sql)

cursor.execute(sql, data)

conn.commit()

cursor.close()

# Enable auto commit.
conn.autocommit = True
conn.close()

Upvotes: 2

Views: 4719

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31646

The error is not due to DATE. You used TO_DATE for converting it to date, which should work fine.

The problem is due to NUMBER columns, for eg: TESTDB_ID and you are trying to insert string 'NULL' into them. Note that python does not convert quoted string "NULL" to Oracle NULL by default. To do that you should use Python None type.

So, your data should be

data = [None, None, None, None, '2', 'Test Text', None, None, '2017-06-02 16:00:00', '20180910122315440220.csv']

and your insert statement contained in sql variable ( with to_date ) should work normally.

Upvotes: 3

APC
APC

Reputation: 146229

You need to compare the values in your data array with the projection of your table TESTDB. Make sure that the datatypes of the dictionary entries match the datatypes of their target columns.

However, I'm going to guess that one or more of the columns assigned 'NULL' is numeric, and what's happening is that your code is inserting a string of 'NULL' rather than a null value. Try replacing the 'NULL' assignments with empty strings or maybe a Pythonic None, and see whether that solves the problem.

Upvotes: 0

Related Questions