PySqlite - division of real numbers without decimal fractions

  • Thread starter Astley Le Jasper
  • Start date
A

Astley Le Jasper

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.
 
D

Dan Bishop

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.
 
A

Astley Le Jasper

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]
 
G

Gerhard Häring

Astley said:
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))")
>>> con.executemany("insert into foo(x) values (?)", [(3,), (3.0,)])
>>> print con.execute("select x from foo").fetchall() [(3,), (3,)] # <------------------------ !!!
>>> con.execute("create table bar(x real)")
>>> con.executemany("insert into bar(x) values (?)", [(3,), (3.0,)])
>>> print con.execute("select x from bar").fetchall()
[(3.0,), (3.0,)] # <------------------------ !!!

Do you see the difference?

-- Gerhard
 
A

Astley Le Jasper

        Did you mean SQLite? <G>




        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 address removed)             (e-mail address removed)
                HTTP://wlfraed.home.netcom.com/
        (Bestiaria Support Staff:               (e-mail address 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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top