Cx_oracle in Python giving "missing expression error" - date and timestamp literals?

Question

I have a chunk of code that is supporting a procedure for making survey records. 1 line is causing it to fail and I can't figure out why. Here is a snippet:

data = [4, 1, 5, '2015-01-01', '2016-07-26 14:32:19']
insert_query2 = 'INSERT INTO SURVEY_RESPONSE (ID, SURVEY_ID, RESPONDENT_ID, DATE_TAKEN, DATE_ENTERED) VALUES (:sr_id , :survey_id , :respondent_id , DATE :dt , TIMESTAMP :ts )'
cursor.prepare(insert_query)
cursor.execute(None, {"sr_id":data[0], "survey_id":data[1], "respondent_id":data[2], "dt":data[3], "ts":data[4]})

So my data is packed into a list and then I try to pass each list element as a parameter. I was trying to pass the row itself as I've seen in examples but was still getting the error:

File "myfile.py", line 95, in insert_new_survey_response
cursor.execute(insert_query2, {"sr_id":data[0], "survey_id":data[1], "respondent_id":data[2], "dt":data[3], "ts":data[4]})
cx_Oracle.DatabaseError: ORA-00936: missing expression

Now I know that the line works, if I were to copy it into my DBMS and hardcode the values I'm passing, it works. The columns are right and all my datatypes are right.

I think it is the way I am trying to use the DATE and TIMESTAMP literals. That's how it's said to be done in the Oracle documentation but I'm not sure if it's supported in cx_Oracle. Is there a way to get around needed them? The fields themselves are dates and timestamp datatypes so I thought this was correct.

Answer 1

I just found the answer - the literals are not good to use, to_date() and to_timestamp() are the correct methods for passing the correct formatting of the date and timestamp.

Discussion here: Oracle - literal does not match format string error

I'm going to leave it up for now in case someone else runs into this problem too :)