Forum Stats

  • 3,770,467 Users
  • 2,253,119 Discussions
  • 7,875,467 Comments

Discussions

insert with timestamp variable fails

_AZ_
_AZ_ Member Posts: 494
edited Aug 2, 2017 6:15PM in Python

this is going a bit hairy.

1. I'm running against the TimesTen database, which is somewhat different from regular Oracle db.

b. the "insert" statements with regular subs variables ( not TS) work fine.

c. the "insert" statements with TS as literal work fine too.

d. the "insert" with TS as a subs variable - do NOT:

create table test(ts timestamp, one unmber);

in cx_oracle:

cursor.execute("INSERT INTO test VALUES (TIMESTAMP ':ts',:one,2)", one=1, ts='2007-01-20 12:45:23' )  cursor.execute("INSERT INTO test VALUES (TIMESTAMP ':ts',:one,2)", one=1, ts='2007-01-20 12:45:23' )cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

cursor.execute("INSERT INTO test VALUES (TIMESTAMP '2007-01-20 12:45:23',:one,2)", one=1)<no error>

the ultimate goal is to get either datetime.datetime.now() type or string passed to the "insert"

thank you

Message was edited by: _AZ_ syntax highlight

_AZ_

Best Answer

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Aug 2, 2017 10:16AM Accepted Answer

    Is there a reason you aren't binding the datetime.datetime() Python object directly? cx_Oracle will transform that into a date automatically. If you need to bind it as a timestamp instead you can use cursor.setinputsizes(). No need for NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT at all!

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Aug 2, 2017 2:45AM

    According to the timesten documentation, NLS_TIMESTAMP_FORMAT is fixed as 'yyyy-mm-dd hh24:mi:ss.ff6'

    Date and timestamp formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMATTimesTen does not support user-specified NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT settings.NLS_DATE_FORMAT is always 'yyyy-mm-dd'.NLS_TIMESTAMP_FORMAT is always 'yyyy-mm-dd hh:mi:ss.ff6' (fractional seconds to six decimal places).

    So change what ever string format you are using to the above timestamp format and allow the implicit conversion from string to timestamp.

  • _AZ_
    _AZ_ Member Posts: 494
    edited Aug 2, 2017 10:00AM

    ... or , alternatively insert varchar and create a view as timestamp....

    I can't believe there is no proper way to work with TS.... and this is version 11... jeez...

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Aug 2, 2017 10:16AM Accepted Answer

    Is there a reason you aren't binding the datetime.datetime() Python object directly? cx_Oracle will transform that into a date automatically. If you need to bind it as a timestamp instead you can use cursor.setinputsizes(). No need for NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT at all!

  • _AZ_
    _AZ_ Member Posts: 494
    edited Aug 2, 2017 10:59AM

    well, i didn't know about that function

    this article addresses the MS portion of the problem

    As for Anthony's question - the use case is :

    a. insert the record with predetermined TS format

    b. if there is no TS - use current time

    I really wanted to keep any python level muddling with the data to a minimum, to have logic and performance as simple and high as possible. but from what you are explaining, looks like i'll have to validate/parse the incoming TS in the python before passing down to DB.

    thank you for the input, let me work on it.

  • _AZ_
    _AZ_ Member Posts: 494
    edited Aug 2, 2017 3:08PM

    here is what i came up with (simplified):

    def writeTicks( timestmp = None):
      if timestmp is None:
      timestmp = datetime.now()  else:
      timestmp = datetime.strptime(timestmp, '%Y/%m/%d %H:%M:%S.%f')  try:
      cursor = self.con.cursor()  cursor.setinputsizes(ts=cx_Oracle.TIMESTAMP)  cursor.execute("insert into ticks( ts) values( :ts ) ",ts=timestmp )....

    the above works fine if:

    a. i pass None ( as a TS)

    b. I pass string literal with microseconds aka "2017 06 10 20:00:00.12345"

    A few followup questions:

    a. what is the right approach dealing with literal _without_ microseconds ? e.g "2017 06 10 20:00:00"

    b. what is the right approach to handle variable with a different type ( passed as TS)?  e.g. writeTicks(datetime.datetime.now() )

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Aug 2, 2017 3:21PM

    a. It depends on the frequency of this occurrence which of these will perform most effectively. You can check the length of the string and adjust the format accordingly like this:

    fmt = "%Y/%m/%d %H:%M:%S.%f" if len(timestamp) > 19 else "%Y %m %d %H:%M:%S"

    You could also use a try/except block:

    try:

       ts = datetime.strptime("%Y/%m/%d %H:%M:%S.%f", timestamp)

    except ValueError:

       ts = datetime.strptime("%Y %m %d %H:%M:%S", timestamp)

    b. You can adjust the if/else block as follows:

    if timestamp is None:

       timestamp = datetime.now()

    elif isinstance(timestamp, str):

      timestmp = datetime.strptime(timestmp, '%Y/%m/%d %H:%M:%S.%f')

    In the event that the timestamp is not None or a string, it will be left unchanged. If it isn't a datetime.datetime() instance you'll get a different error, which should be sufficient, I think.

    _AZ_
This discussion has been closed.