Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > PySqlite - division of real numbers without decimal fractions

Reply
Thread Tools

PySqlite - division of real numbers without decimal fractions

 
 
Astley Le Jasper
Guest
Posts: n/a
 
      11-06-2008
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.
 
Reply With Quote
 
 
 
 
Dan Bishop
Guest
Posts: n/a
 
      11-07-2008
On Nov 6, 3:46*pm, Astley Le Jasper <(E-Mail Removed)> 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.
 
Reply With Quote
 
 
 
 
Astley Le Jasper
Guest
Posts: n/a
 
      11-07-2008
On Nov 7, 6:36*am, Dan Bishop <(E-Mail Removed)> wrote:
> On Nov 6, 3:46*pm, Astley Le Jasper <(E-Mail Removed)> 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]
 
Reply With Quote
 
Gerhard Häring
Guest
Posts: n/a
 
      11-07-2008
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

 
Reply With Quote
 
Astley Le Jasper
Guest
Posts: n/a
 
      11-10-2008
On 8 Nov, 05:39, Dennis Lee Bieber <(E-Mail Removed)> wrote:
> On Fri, 07 Nov 2008 14:36:52 +0100, Gerhard Häring <(E-Mail Removed)>
> 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
> * * * * (E-Mail Removed) * * * * * * (E-Mail Removed)
> * * * * * * * * HTTP://wlfraed.home.netcom.com/
> * * * * (Bestiaria Support Staff: * * * * * * * (E-Mail Removed))
> * * * * * * * * 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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: [pysqlite] [ANN] pysqlite and APSW projects moved Gerhard Häring Python 3 03-13-2008 10:03 PM
division by 7 without using division operator krypto.wizard@gmail.com C Programming 94 02-09-2007 06:57 AM
[pysqlite] How do I use pysqlite in a multi-threading env.? F. GEIGER Python 2 05-21-2005 07:42 AM
fractions - entering your own numbers in Word? John Computer Information 3 04-29-2004 11:50 PM
Decimal places and huge fractions. Eoin Mcloughlin Perl 5 02-13-2004 08:33 PM



Advertisments