PySqlite - division of real numbers without decimal fractions

[Gerhard Häring]

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