Home > Back-end >  Inserting current date time in oracle apex (v20) Python
Inserting current date time in oracle apex (v20) Python

Time:09-16

I am using cx_Oracle in Python to update a timestamp column in an Apex table.

Here is what I have done:

  sql1 = ('SELECT * from ab.TEMPTABLE');
  sql2 = ('UPDATE AB.CTIMEUPDATED SET TSTAMP = SYSTIMESTAMP')
    try:
        connection=self.get_connection()
        with connection.cursor() as cursor:
             cursor.execute(sql2)
             cursor.execute(sql1)

This doesn't updates the value in CTIMEUPDATED table , however if

I run the same command in SQL command browser outside of Python, it works just fine.

I have also tried other options like this

d=datetime.datetime.now()
dtime=d.strftime("YYYY/MM/DD HH:mm:ss (%Y/%m/%d %H:%M:%S)")
cursor.prepare( "INSERT INTO AB.CTIMEUPDATED(Tstamp) VALUES(TO_DATE(:TS))" )
cursor.setinputsizes(TS=cx_Oracle.TIMESTAMP)
cursor.execute(None, {'TS':dtime})

or

cursor.execute("INSERT INTO AB.CTIMEUPDATED(Tstamp) VALUES(TO_DATE(:TS, 'yyyy/mm/dd hh24:mi:ss'))", {'TS': dtime})

Nothing works and there is no error as well.

CTIMEUPDATED table has a column TSTAMP which is not nullable and of data type TIMESTAMP(6)

Note: I do not need milliseconds only date & time should be fine

CodePudding user response:

You have two options here

Either you commit the transaction by using the corresponding method

  cursor.execute("INSERT INTO AB.CTIMEUPDATED(Tstamp) VALUES(TO_DATE(:TS, 'yyyy/mm/dd hh24:mi:ss'))", {'TS': dtime})
    connection.commit()

Or you use autocommit set to ON

 connection.autocommit = True 
     cursor.execute("INSERT INTO AB.CTIMEUPDATED(Tstamp) VALUES(TO_DATE(:TS, 'yyyy/mm/dd hh24:mi:ss'))", {'TS': dtime})
  • Related