question about cx_Oracle .thanks

Discussion in 'Python' started by coolmenu, Jun 25, 2004.

  1. coolmenu

    coolmenu Guest

    Hi
    i hava a db ORACLE 10G,a table valid_card
    (card_no,varchar2(10),now_balance number (12,2))

    its'some record in table ('7188','42055.66')

    i use cx_Oracle to select now_balance from table

    curobj.execute("select loan_amount from valid_card where
    card_no='7181930166881974'");
    [<NumberVar object at 0x013A6920>]
    >>> tuple=curobj.fetchone()
    >>> tuple

    (42505.660000000003)

    why 42505.66---->42505.6600000000003???
    thanks
    coolmenu, Jun 25, 2004
    #1
    1. Advertising

  2. coolmenu

    David Fraser Guest

    coolmenu wrote:
    > Hi
    > i hava a db ORACLE 10G,a table valid_card
    > (card_no,varchar2(10),now_balance number (12,2))
    >
    > its'some record in table ('7188','42055.66')
    >
    > i use cx_Oracle to select now_balance from table
    >
    > curobj.execute("select loan_amount from valid_card where
    > card_no='7181930166881974'");
    > [<NumberVar object at 0x013A6920>]
    >
    >>>>tuple=curobj.fetchone()
    >>>>tuple

    >
    > (42505.660000000003)
    >
    > why 42505.66---->42505.6600000000003???
    > thanks


    This is because of the conversion of the float to decimal. Float objects
    have limited accuracy. I don't think it's much to do with cx_Oracle

    David
    David Fraser, Jun 25, 2004
    #2
    1. Advertising

  3. coolmenu

    coolmenu Guest

    David Fraser <> wrote in message news:<cbghcs$5m3$>...
    > coolmenu wrote:
    > > Hi
    > > i hava a db ORACLE 10G,a table valid_card
    > > (card_no,varchar2(10),now_balance number (12,2))
    > >
    > > its'some record in table ('7188','42055.66')
    > >
    > > i use cx_Oracle to select now_balance from table
    > >
    > > curobj.execute("select loan_amount from valid_card where
    > > card_no='7181930166881974'");
    > > [<NumberVar object at 0x013A6920>]
    > >
    > >>>>tuple=curobj.fetchone()
    > >>>>tuple

    > >
    > > (42505.660000000003)
    > >
    > > why 42505.66---->42505.6600000000003???
    > > thanks

    >
    > This is because of the conversion of the float to decimal. Float objects
    > have limited accuracy. I don't think it's much to do with cx_Oracle
    >
    > David



    Can someone give me a advice? how can i do?
    donnt select number from oracle?
    coolmenu, Jun 25, 2004
    #3
  4. coolmenu

    Paul Watson Guest

    "coolmenu" <> wrote in message
    news:...
    > David Fraser <> wrote in message

    news:<cbghcs$5m3$>...
    > > coolmenu wrote:
    > > > Hi
    > > > i hava a db ORACLE 10G,a table valid_card
    > > > (card_no,varchar2(10),now_balance number (12,2))
    > > >
    > > > its'some record in table ('7188','42055.66')
    > > >
    > > > i use cx_Oracle to select now_balance from table
    > > >
    > > > curobj.execute("select loan_amount from valid_card where
    > > > card_no='7181930166881974'");
    > > > [<NumberVar object at 0x013A6920>]
    > > >
    > > >>>>tuple=curobj.fetchone()
    > > >>>>tuple
    > > >
    > > > (42505.660000000003)
    > > >
    > > > why 42505.66---->42505.6600000000003???
    > > > thanks

    > >
    > > This is because of the conversion of the float to decimal. Float objects
    > > have limited accuracy. I don't think it's much to do with cx_Oracle
    > >
    > > David

    >
    >
    > Can someone give me a advice? how can i do?
    > donnt select number from oracle?


    This has nothing to do with Oracle. This is the problem of representing
    floating point numbers on a binary system. You may need to format the
    number for presentation.

    >>> x = 42055.66
    >>> x

    42055.660000000003
    >>> print "the answer is %.2f" % (x)

    the answer is 42055.66
    Paul Watson, Jun 25, 2004
    #4
  5. coolmenu

    Steve Holden Guest

    Paul Watson wrote:
    > "coolmenu" <> wrote in message
    > news:...
    >
    >>David Fraser <> wrote in message

    >
    > news:<cbghcs$5m3$>...
    >
    >>>coolmenu wrote:
    >>>

    [...]
    >>>>>>>tuple=curobj.fetchone()
    >>>>>>>tuple
    >>>>
    >>>>(42505.660000000003)
    >>>>
    >>>>why 42505.66---->42505.6600000000003???

    [...]

    >>
    >>Can someone give me a advice? how can i do?
    >>donnt select number from oracle?

    >
    >
    > This has nothing to do with Oracle. This is the problem of representing
    > floating point numbers on a binary system. You may need to format the
    > number for presentation.
    >
    >
    >>>>x = 42055.66
    >>>>x

    >
    > 42055.660000000003
    >
    >>>>print "the answer is %.2f" % (x)

    >
    > the answer is 42055.66
    >

    And, in fact, more generally, see:

    http://www.python.org/doc/faq/general.html#why-are-floating-point-calculations-so-inaccurate

    which you will (I hope) appreciate does not just apply to Python. Python
    is confusing you by showing you the contents of your tuple as accurately
    as possible: your computer actually stores 4205.66 as 42505.660000000003
    - this is an accurate decimal representation of the binary value:

    >>> print repr(42055.66)

    42055.660000000003

    Even more confusingly, when you try it on a SPARC processor you may get
    slightly different answers. Wise programmers always round monetary
    calculations at every step along the way[1].

    regards
    Steve

    [1] Yes, I know this is a simplification, please let's not have yet
    another repeat of the eternal thread. Allow some room for pedagogy here!
    Steve Holden, Jun 25, 2004
    #5
  6. coolmenu

    Peter Hansen Guest

    Steve Holden wrote:

    > which you will (I hope) appreciate does not just apply to Python. Python
    > is confusing you by showing you the contents of your tuple as accurately
    > as possible: your computer actually stores 4205.66 as 42505.660000000003


    Or, more precisely, it stores it as the binary number represented
    here by these hexadecimal digits (shown here in big-endian format):

    >>> binascii.hexlify(struct.pack('>d', 42055.66))

    '40e488f51eb851ec'

    Since it has a limited number of binary digits available, it can't
    store precisely 42055.66, so 42055.6600000whatever is the next
    closest. To demonstrate, the nearest neighbours that can be
    represented accurately are these (binary value one higher or lower):

    >>> struct.unpack('>d', binascii.unhexlify('40e488f51eb851ed'))

    (42055.660000000011,)
    >>> struct.unpack('>d', binascii.unhexlify('40e488f51eb851eb'))

    (42055.659999999996,)

    So clearly the binary can capture only increments of about
    0.000000000007 or 0.000000000008 at a time, and all of these
    values must be treated as approximations... unless one was
    trying to store 42055.660000000003 in the first place!

    > - this is an accurate decimal representation of the binary value:
    >
    > >>> print repr(42055.66)

    > 42055.660000000003


    All very true. ;-)

    -Peter
    Peter Hansen, Jun 25, 2004
    #6
  7. coolmenu

    Steve Holden Guest

    Peter Hansen wrote:
    [...]

    > Since it has a limited number of binary digits available, it can't
    > store precisely 42055.66, so 42055.6600000whatever is the next
    > closest. To demonstrate, the nearest neighbours that can be
    > represented accurately are these (binary value one higher or lower):
    >
    > >>> struct.unpack('>d', binascii.unhexlify('40e488f51eb851ed'))

    > (42055.660000000011,)
    > >>> struct.unpack('>d', binascii.unhexlify('40e488f51eb851eb'))

    > (42055.659999999996,)
    >
    > So clearly the binary can capture only increments of about
    > 0.000000000007 or 0.000000000008 at a time, and all of these
    > values must be treated as approximations... unless one was
    > trying to store 42055.660000000003 in the first place!
    >

    Just to make things even more complicated, of course, we must also
    remember that the delta between two adjacent floating-point numbers
    isn't constant, but will vary according to the magnitude of the numbers.

    >> - this is an accurate decimal representation of the binary value:
    >>
    >> >>> print repr(42055.66)

    >> 42055.660000000003

    >
    >
    > All very true. ;-)
    >

    And it just goes to show that floating-point is quite confusing enough
    to make many a beginner run away in fright!

    regards
    Steve
    Steve Holden, Jun 26, 2004
    #7
  8. coolmenu

    coolmenu Guest

    Thanks all;-)

    but how can i select number from oracle?
    coolmenu, Jun 26, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Glenn Stauffer

    cx_Oracle & prepared statements

    Glenn Stauffer, Aug 16, 2003, in forum: Python
    Replies:
    0
    Views:
    839
    Glenn Stauffer
    Aug 16, 2003
  2. GrayGeek

    import cx_Oracle

    GrayGeek, Nov 1, 2003, in forum: Python
    Replies:
    12
    Views:
    1,097
    GrayGeek
    Nov 7, 2003
  3. Benson, John
    Replies:
    1
    Views:
    2,218
    Aurelio Martin
    Jan 28, 2004
  4. Pieter Claerhout
    Replies:
    0
    Views:
    601
    Pieter Claerhout
    Jan 28, 2004
  5. Richard Schulman

    cx_Oracle question

    Richard Schulman, Sep 8, 2006, in forum: Python
    Replies:
    4
    Views:
    610
    Richard Schulman
    Sep 8, 2006
Loading...

Share This Page