prepared bulk-inserts into TIMESTAMP(3) silently truncated to seconds

[alberto-dellera]

Extract from the attached test case:

# insert two rows with fractional seconds
rows = [ [datetime.datetime(2018, 1, 26, 14, 49, 54, 826000)], 
         [datetime.datetime(2018, 1, 26, 14, 49, 54, 826000)] 
       ]
...
# bulk-insert using executemany() 
cursor.setinputsizes(cx_Oracle.DATETIME) # this does not change anything
cursor.prepare("""insert into test(x) values(:1)""")
cursor.executemany(None, rows)

Reading back the rows we see that the values were truncated:

(datetime.datetime(2018, 1, 26, 14, 49, 54),)
(datetime.datetime(2018, 1, 26, 14, 49, 54),)

even if the bind vars seem fine:

bindvars: [<cx_Oracle.DATETIME with value [datetime.datetime(2018, 1, 26, 14, 49, 54, 826000), datetime.datetime(2018, 1, 26, 14, 49, 54, 826000)]>]

inspection of the trace reveals that the bind var type is DATE(oacdty=12):

BINDS #...:
 Bind#0
  oacdty=12 ...

cxOracle_timestamp.zip

Answer the following questions:

1. What is your version of Python? 64-bit ( Python 3.6.3 :: Anaconda, Inc. )

2. What is your version of cx_Oracle? 6.2.1

3. What is your version of the Oracle client (e.g. Instant Client)? How was it
   installed? Where is it installed?
   12.2.0.1.0, Oracle Installer, comes with full install of server (Enterprise)

4. What is your version of the Oracle Database?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

5. What is your OS and version? Windows 7

6. What compiler version did you use? n/a

7. What environment variables did you set? How exactly did you set them?

NLS_LANG=American_America.UTF8
in cygwin's .bashrc

[anthony-tuininga]

Currently cx_Oracle uses the Oracle type DATE (cx_Oracle.DATETIME) to bind Python date values. It does not examine the value to determine if any fractional seconds are present. Instead, you need to use the Oracle type TIMESTAMP (cx_Oracle.TIMESTAMP) if you want to handle fractional seconds. If you use setinputsizes() or create a variable using cursor.var() and set its value and bind the variable instead, this problem should go away.

[alberto-dellera]

Additional infos: setting explicitly the bind variable type to cx_Oracle.TIMESTAMP solves the problem:

cursor.setinputsizes(cx_Oracle.TIMESTAMP)

[alberto-dellera]

Anthony, many thanks for your clear explanation.

I find quite surprising that the "default" (i.e. without setting setinputsizes()) bind type is DATE when the Python type is datetime, since the latter has microsecond precision and hence "equivalent" to Oracle TIMESTAMP(6). Might you tell me the rationale behind this choice?

[anthony-tuininga]

The type DATE in Oracle is older and more frequently used, especially in the past. If the default was changed to TIMESTAMP, then existing code that assumed the default of DATE would potentially result in poor performance (index selection is based on type) or stop working (PL/SQL procedure selection is based on type). So we're stuck with what we have. Thankfully you can tell cx_Oracle you know better and that TIMESTAMP should be used in this case. :-)

[anthony-tuininga]

Presuming this question has been answered. Please re-open if not!

[AbdealiLoKo]

In my application, we always use TIMESTAMP
Is there any way for me to always ask cx-oracle to use TIMESTAMP everywhere ?
I would like to avoid using setinputsizes for every query I make

PS: I am using sqlalchemy

[anthony-tuininga]

Yes, you can add an input type handler to the connection as seen in the documentation. Since you are using SQLAlchemy you will need to have some way of getting the connection object to set this attribute. I'm not sure how that is done with SQLAlchemy. One possibility is to allow this attribute to be set when building the connection. If that is something you would like, feel free to add an enhancement request to python-oracledb (the new name for cx_Oracle).

[AbdealiLoKo]

Got it - thanks
Will check the documentation and try it out !
Appreciate the swift response :)