return NUMBER as int if possible, otherwise as decimal.Decimal

[leo-b]

Hi!

I'd like to add an outputtypehandler for DB_TYPE_NUMBER that returns an int if the corresponding data allows it or a decimal.Decimal otherwise.

So far I have two implementation variants but I'd appreciate your advise, maybe there is a better or more efficient way to do it...

V1: convert to decimal.Decimal and try to go back to int:

def _outconverter_int_or_dec(val_dec):
    val_int = int(val_dec)
    return val_int if val_int == val_dec else val_dec

def outputtypehandler_num1(cursor, name, default_type, size, precision, scale):
    if default_type is cx_Oracle.DB_TYPE_NUMBER:
        if scale == 0:
            return cursor.var(int, arraysize=cursor.arraysize)
        else:
            return cursor.var(decimal.Decimal, arraysize=cursor.arraysize, outconverter=_outconverter_int_or_dec)

V2: handle as str and then either convert to int or decimal.Decimal

def _outconverter_int_or_dec_from_str(val_str):
    return decimal.Decimal(val_str) if '.' in val_str else int(val_str)

def outputtypehandler_num2(cursor, name, default_type, size, precision, scale):
    if default_type is cx_Oracle.DB_TYPE_NUMBER:
        if scale == 0:
            return cursor.var(int, arraysize=cursor.arraysize)
        else:
            return cursor.var(str, arraysize=cursor.arraysize, outconverter=_outconverter_int_or_dec_from_str)

Are those methods reasonable ways to solve the problem or would you recommend a better solution?

[anthony-tuininga]

First, have you considered upgrading to python-oracledb which replaces cx_Oracle? That driver does effectively what you have in V2 automatically. I'd have to look at cx_Oracle again to see if that code is there, too -- but I would definitely recommend upgrading if you are able to do so!

[leo-b]

First, have you considered upgrading to python-oracledb which replaces cx_Oracle?

Thanks for the hint.
I know that python-oracledb has fetch_decimals and I am already using python-oracledb in new projects.
Unfortunately there are some places (old python 2.7 code) where switching is not possible instantly.

That driver does effectively what you have in V2 automatically.

In cx_Oracle I found some int or float logic or an unconditional use of decimal.Decimal:

python-cx_Oracle/src/cxoTransform.c

Line 884 in 6766bca

if (transformNum == CXO_TRANSFORM_INT &&

[anthony-tuininga]

Understood. In that case the second option is likely the best one -- but you can always run some tests to confirm!

Note that the new driver uses similar logic. If you are looking for returning a decimal.Decimal instance for those numbers that require a decimal point and integers for those numbers that are pure integers (even though found in a column that may contain numbers with decimal points) then you will still require an output type handler. Since internally the value is fetched as a string, the second option you have outlined is probably the path of least resistance. :-)

[leo-b]

Thanks!