How to update the current date, time in format dd-mm-yyyy hh:mm:ss in an oracle table through python

user_13349302 0
Viewed 1024

I am trying to update a date type column in an Oracle table but I am getting below error:

sql2 = f"""update a_table SET COMPLETION_DATE = '{datetime.datetime.now()}' WHERE ID=1"""
data2 = cursor.execute(sql2)

cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended

for information : I am using cx_Oracle module.

cx-oracle sql-update oracle sql python
2 Answers

If you want to set completion_date to the current date/time, you can just use sysdate in the query:

UPDATE a_table SET completion_date = SYSDATE WHERE ID=1

This assumes that completion_date is of date datatype. If that's a timestamp instead:

UPDATE a_table SET completion_date = SYSTIMESTAMP WHERE ID=1
user_106767160

Your problem is, that the SQL string produced in the variable sql2 looks like this

update a_table SET COMPLETION_DATE = '2020-12-14 14:09:11.553590' WHERE ID=1

The timestamp literal may cause problems in the conversion and you'll have to use expplicite conversion to make it safe (or use database value from SYSTIMESTAMPas proposed in other answer).

update a_table 
SET COMPLETION_DATE = to_timestamp('2020-12-14 14:09:11.553590','YYYY-MM-DD HH24:MI:SS.FF6')
WHERE ID=1

Anyway don't forget that the usage of bind variables is encouraged, so your solution should look something like this

sql2 =  "update a_table SET COMPLETION_DATE = :TST WHERE ID = :ID"
data2 = cursor.execute(sql2, TST = datetime.datetime.now(), ID = 1 )

The big advantage is that you get one SQL statement that may be reused for all IDs and timestamps.

If the column data type is TIMESTAMP and you want to insert also the milliseconds you have to add the setinputsizes before the execute statement - otherwise they will be truncated.

 cursor.setinputsizes(TST=cx_Oracle.TIMESTAMP)
user_48081220
Related