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