This discussion is archived
4 Replies Latest reply: May 21, 2013 4:27 AM by 827481 RSS

cx_Oracle.NUMBER precision problem ..

827481 Newbie
Currently Being Moderated
Hi All,

i understand that floating point numbers are inherently inaccurate, eg:
0.1 + 0.2 != 0.3

the problem i'm seeing is that:

in sqlplus, a query is returning 0.088 from the Oracle db server,
but cx_Oracle is changing the value to 0.08800000000000001.

at the interpretor, v = 0.088, retains 0.088...
(most likely, the precision is maintained upon definition),
is there a way to set the precision for cx_Oracle.NUMBER ?

thanks very much in advance!

sam
  • 1. Re: cx_Oracle.NUMBER precision problem ..
    cj Employee ACE
    Currently Being Moderated
    Since I don't know what you are trying to do, or how you coded it, here's once reference: glance at the input/output handler section in http://www.oracle.com/technetwork/articles/tuininga-cx-oracle-084866.html
  • 2. Re: cx_Oracle.NUMBER precision problem ..
    827481 Newbie
    Currently Being Moderated
    Thanks CJ,

    i have seen the document you referenced.

    this is what i'm trying to do:

    <h6>
    SQL> select volume from goods;
    
    VOLUME
    ------
    0.088
    
    1 row selected
    
    (python/cx_Oracle)
    
    cur.execute("select volume from goods")
    rows = cur.fetchall()
    rows
    [(0.08800000000000001,)]
    
    v = 0.088
    v
    0.088
    </h6>

    the value stored in the DB has been set to precision 0.3f, and
    sqlplus returns it as-is.

    in python/cx_Oracle, the value is 0.08800000000000001.

    the example shows what is typed at the interpreter, and also
    in python scripts (outside of cx_Oracle).

    assignments keep the precision specified.

    is there a way to get the same behaviour ?

    thanks,
    sam
  • 3. Re: cx_Oracle.NUMBER precision problem ..
    cj Employee ACE
    Currently Being Moderated
    What about using one of these:
    # t.py
    
    import cx_Oracle
    import decimal
    
    db = cx_Oracle.connect("cj", "cj", "localhost/orcl")
    
    cur = db.cursor()
    cur.execute("select volume from goods")
    rows = cur.fetchall()
    print rows
    
    cur = db.cursor()
    cur.execute("select to_char(volume) from goods")
    rows = cur.fetchall()
    print rows
    print float(rows[0][0])
    
    def NumbersAsDecimal(cursor, name, defaultType, size, precision, scale): 
        if defaultType == cx_Oracle.NUMBER: 
            return cursor.var(str, 100, cursor.arraysize, outconverter = decimal.Decimal) 
    
    db.outputtypehandler = NumbersAsDecimal
    cur = db.cursor()
    cur.execute("select volume from goods")
    rows = cur.fetchall()
    print rows
    Output is:
    $ python t.py 
    [(0.08800000000000001,)]
    [('.088',)]
    0.088
    [(Decimal('0.088'),)]
  • 4. Re: cx_Oracle.NUMBER precision problem ..
    827481 Newbie
    Currently Being Moderated
    hi CJ,

    thank you very much for your suggestions!

    i cannot use the to_char/string solutions,
    because we need to be able to sort the data
    later.

    i used the decimal solution you proposed,
    and that resolved the problem.

    the next problem i ran into, was that
    json was not able to serialize decimal types.

    came across the following at:
    http://djangosnippets.org/snippets/2410/

    which help with the floats ...

    (sorry for the lapse and lateness in reply,
    was testing and adapting the solution.. and
    then worked on another issue)

    Thanks again! you've been a great help!
    sam

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points