New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

integers are being inserted instead of floats using executemany #241

Closed
rtaft opened this Issue Nov 26, 2018 · 8 comments

Comments

Projects
None yet
2 participants
@rtaft
Copy link

rtaft commented Nov 26, 2018

I have an issue where using executemany in python 2.7 with an integer in the very first row of the very first executemany, causes all subsequent rows to be inserted as integers. Sample:

create table test_bug (ID NUMBER(10,0), testnum NUMBER(20,2));

import cx_Oracle

connection = cx_Oracle.connect(...)
cursor = connection.cursor()
query = "INSERT INTO test_bug (id, testnum) VALUES (:id, :testnum)"
cursor.prepare(query)

cursor.executemany(None, [dict(id=1, testnum=14.95)])
cursor.executemany(None, [dict(id=2, testnum=10), dict(id=3, testnum=14.95)])
connection.commit()
connection.close()

connection = cx_Oracle.connect(...)
cursor = connection.cursor()
query = "INSERT INTO test_bug (id, testnum) VALUES (:id, :testnum)"
cursor.prepare(query)
cursor.executemany(None, [dict(id=4, testnum=10), dict(id=5, testnum=14.95)])
connection.commit()
connection.close()

Results:

  ID TESTNUM
---- -------
   1   14.95
   2      10
   3   14.95
   4      10
   5      14

I expected ID=5 to be 14.95, but it is 14. This does not happen in python 3, nor cx_Oracle 5.3.0. This appears to be related to the fix for #82 and #75

Answer the following questions:

  1. What is your version of Python? Is it 32-bit or 64-bit?
    Python 2.7.12

  2. What is your cx_Oracle version?
    6.2.0, 6.3.0, 7.0.0

  3. What error(s) you are seeing?
    Data incorrect

  4. What OS (and version) is Python executing on?
    Ubuntu 16.4

  5. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?
    I tried both Instant Client 11.2 and 12.2

  6. What is your Oracle Database version?
    Oracle 12c

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 26, 2018

I am able to replicate the behaviour you are seeing and that is definitely incorrect. I'll look at correcting that. Thanks for the clear example and explanation!

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 26, 2018

I've taken a quick peek at this. The reason for the difference between Python 2 and Python 3 is that Python 3 has unified "integers" and "long integers" so cx_Oracle just uses a string representation for all integers rather than attempt to determine whether it can fit in a native integer (and incur the overhead of doing so). In Python 2 I have still been using native integers where applicable but there was no check for the type on the mistaken assumption that PyLong_AsLong() would raise an error if a floating point number was passed. It actually performs int() on the object in question which is what you are seeing. I'll add the code to raise the exception "expecting integer" so that the silent truncation no longer occurs. I'll also consider switching the Python 2 behaviour to match Python 3 behaviour.

@rtaft

This comment has been minimized.

Copy link
Author

rtaft commented Nov 26, 2018

Won't raising the error just bring back #82? I would prefer matching it to Python 3 as it would not involve a code change on my end. Though I have tracked down where the integers came from if necessary.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 26, 2018

Raising the error will indeed cause #82 to occur again -- but not sure if there is any solution to that now that this issue has been noted -- beyond switching the behaviour to the same as Python 3. I'll give it some thought. In the meantime, you have a workaround for the issue.

anthony-tuininga added a commit that referenced this issue Dec 8, 2018

Rework handling of numbers so that, unless using the NATIVE_INT,
NATIVE_FLOAT or NATIVE_DOUBLE types, all numbers are converted to strings and
passed through to ODPI-C in all Python versions; improved error message when
a value cannot be represented by an Oracle number value; improved test suite to
verify that calling executemany() with integers, floats and decimal values
intermixed with each other works as expected
(#241).
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Dec 8, 2018

I just pushed a set of changes that should resolve this issue as well as permit integers, floats and decimals to be intermixed in executemany(). Let me know what you think.

@rtaft

This comment has been minimized.

Copy link
Author

rtaft commented Dec 10, 2018

It seems to be working as desired with my example.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Dec 10, 2018

Great. Thanks for confirming. This will become part of version 7.1 when it is released.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Feb 5, 2019

Corrected in cx_Oracle 7.1 which was released yesterday.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment