PySqlite - division of real numbers without decimal fractions

Discussion in 'Python' started by Astley Le Jasper, Nov 6, 2008.

  1. I've been getting errors recently when using pysqlite. I've declared
    the table columns as real numbers to 2 decimal places (I'm dealing
    with money), but when doing division on two numbers that happen to
    have no decimal fractions, the results through pysqlite are coming
    through as integers. The funny thing is that when looking at the
    database using SQLite Manager or SQLite Pro the results there are
    displayed correctly. As a temporary fix I've had to multiply one of
    the numbers with 1.0 which has fixed it but I wonder if there is
    something else I'm doing wrong.
     
    Astley Le Jasper, Nov 6, 2008
    #1
    1. Advertising

  2. Astley Le Jasper

    Dan Bishop Guest

    Re: PySqlite - division of real numbers without decimal fractions

    On Nov 6, 3:46 pm, Astley Le Jasper <> wrote:
    > I've been getting errors recently when using pysqlite. I've declared
    > the table columns as real numbers to 2 decimal places (I'm dealing
    > with money), but when doing division on two numbers that happen to
    > have no decimal fractions, the results through pysqlite are coming
    > through as integers. The funny thing is that when looking at the
    > database using SQLite Manager or SQLite Pro the results there are
    > displayed correctly. As a temporary fix I've had to multiply one of
    > the numbers with 1.0 which has fixed it but I wonder if there is
    > something else I'm doing wrong.


    You're using old-style division. Put the line "from __future__ import
    division" in your script.
     
    Dan Bishop, Nov 7, 2008
    #2
    1. Advertising

  3. Re: PySqlite - division of real numbers without decimal fractions

    On Nov 7, 6:36 am, Dan Bishop <> wrote:
    > On Nov 6, 3:46 pm, Astley Le Jasper <> wrote:
    >
    > > I've been getting errors recently when using pysqlite. I've declared
    > > the table columns as real numbers to 2 decimal places (I'm dealing
    > > with money), but when doing division on two numbers that happen to
    > > have no decimal fractions, the results through pysqlite are coming
    > > through as integers. The funny thing is that when looking at the
    > > database using SQLite Manager or SQLite Pro the results there are
    > > displayed correctly. As a temporary fix I've had to multiply one of
    > > the numbers with 1.0 which has fixed it but I wonder if there is
    > > something else I'm doing wrong.

    >
    > You're using old-style division.  Put the line "from __future__ import
    > division" in your script.


    Hi,

    But the calculations are being done within a sql statement within
    SQLite?

    ([actual_price]-[recommended_price])/[recommended_price]
     
    Astley Le Jasper, Nov 7, 2008
    #3
  4. Astley Le Jasper wrote:
    > I've been getting errors recently when using pysqlite. I've declared
    > the table columns as real numbers to 2 decimal places (I'm dealing
    > with money),


    MySQL doesn't have any MONEY type. All it has is INTEGER, REAL, TEXT,
    BLOB and NULL types.

    > but when doing division on two numbers that happen to
    > have no decimal fractions, the results through pysqlite are coming
    > through as integers. The funny thing is that when looking at the
    > database using SQLite Manager or SQLite Pro the results there are
    > displayed correctly. As a temporary fix I've had to multiply one of
    > the numbers with 1.0 which has fixed it but I wonder if there is
    > something else I'm doing wrong.


    Perhaps using SQLite's column affinity would help? Let the type be named
    "real" instead of anything fancy:

    >>> from pysqlite2 import dbapi2 as sqlite3
    >>> con = sqlite3.connect(":memory:")
    >>> con.execute("create table foo(x number(10,2))")

    <pysqlite2.dbapi2.Cursor object at 0xb7d6faa0>
    >>> con.executemany("insert into foo(x) values (?)", [(3,), (3.0,)])

    <pysqlite2.dbapi2.Cursor object at 0xb7d830e0>
    >>> print con.execute("select x from foo").fetchall()

    [(3,), (3,)] # <------------------------ !!!
    >>> con.execute("create table bar(x real)")

    <pysqlite2.dbapi2.Cursor object at 0xb7d83110>
    >>> con.executemany("insert into bar(x) values (?)", [(3,), (3.0,)])

    <pysqlite2.dbapi2.Cursor object at 0xb7d83170>
    >>> print con.execute("select x from bar").fetchall()

    [(3.0,), (3.0,)] # <------------------------ !!!

    Do you see the difference?

    -- Gerhard
     
    Gerhard Häring, Nov 7, 2008
    #4
  5. Re: PySqlite - division of real numbers without decimal fractions

    On 8 Nov, 05:39, Dennis Lee Bieber <> wrote:
    > On Fri, 07 Nov 2008 14:36:52 +0100, Gerhard Häring <>
    > declaimed the following in comp.lang.python:
    >
    > > Astley Le Jasper wrote:
    > > > I've been getting errors recently when using pysqlite. I've declared
    > > > the table columns as real numbers to 2 decimal places (I'm dealing
    > > > with money),

    >
    > > MySQL doesn't have any MONEY type. All it has is INTEGER, REAL, TEXT,
    > > BLOB and NULL types.

    >
    >         Did you mean SQLite? <G>
    >
    >
    >
    > > Perhaps using SQLite's column affinity would help? Let the type be named
    > > "real" instead of anything fancy:

    >
    >         If dealing with monetary computations, it might be better to define
    > converters/adapters for Python's decimal type... Though that may mean
    > that doing simple SQL arithmetic may not be possible -- might need to
    > supply a Python function to work the arithmetic with conversion of the
    > data...
    > --
    >         Wulfraed        Dennis Lee Bieber               KD6MOG
    >                    
    >                 HTTP://wlfraed.home.netcom.com/
    >         (Bestiaria Support Staff:               )
    >                 HTTP://www.bestiaria.com/


    Hi,

    Sorry. I don't get this.

    I am using numbers to 2dp (it doesn't really matter that it's money or
    not) and importing them into SQLite where all the views are held. One
    of the columns is doing the following calculations:


    ([actual_price]-[recommended_price]) AS [difference]
    ([actual_price]-[recommended_price])/[recommended_price] AS
    [difference_proportion]

    When using a SQLite gui like SQLiteManager I can see the imported data
    is stored correctly and the column has been calculated correctly. So
    I'll have something like:

    [actual_price],[recommended_price],[difference],
    [difference_proportion]
    199.99,299.99,-100.00,-0.343344445
    100.00,120.00,-100.00,-0.16666667

    However, when calling the view from pysqlite I get the following
    results
    199.99,299.99,-100.00,-0.34
    100.00,120.00,-100.00,0

    So the row where both numbers have no decimal fraction are changing to
    an integer. I looks like there is something going on in between sqlite
    and pysqlite.
     
    Astley Le Jasper, Nov 10, 2008
    #5
    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. Eoin Mcloughlin

    Decimal places and huge fractions.

    Eoin Mcloughlin, Feb 11, 2004, in forum: Perl
    Replies:
    5
    Views:
    1,640
    Eoin Mcloughlin
    Feb 13, 2004
  2. F. GEIGER
    Replies:
    2
    Views:
    562
    F. GEIGER
    May 21, 2005
  3. Curious Trigger
    Replies:
    2
    Views:
    1,855
    Curious Trigger
    Sep 9, 2006
  4. Replies:
    94
    Views:
    4,692
    ¬a\\/b
    Feb 9, 2007
  5. Gerhard Häring
    Replies:
    3
    Views:
    396
    Gerhard Häring
    Mar 13, 2008
Loading...

Share This Page