Re: [pysqlite] retrieving 64-bit numbers from SQLite

I created the table with the num column as an INTEGER.  If I change it to REAL, I work around the problem, but I will lose precision.

              CREATE TABLE
              stats(
                name           TEXT,
                num            INTEGER
               )

When I do this, the row['num'] retrieves a 32-bit signed number.  What should the num column definition be?

Thanks!!!!

Bruce



----- Original Message ----
From: Dennis Lee Bieber <[email protected]>
To: list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
Sent: Wednesday, September 10, 2008 4:22:48 PM
Subject: Re: [pysqlite] retrieving 64-bit numbers from SQLite

On Wed, 10 Sep 2008 15:37:52 -0700 (PDT), Bruce Greenblatt
<bgreenblatt-/[email protected]> declaimed the
following in gmane.comp.python.db.pysqlite.user:

> When I retrieve large numbers from a SQLite table, they seem to be getting truncated to 32-bits, and treated as signed integers.
>
    And how is the table defined? Did you define the field as "double",
or left it undefined or general "numeric"?

    With a table definition of:

>>> cur.execute("""create table stats (id integer primary key,
...         name char(50),
...         num double)"""
...         )

(skipping insert statements)

    I get:

[(u'integer range', 123.0), (u'single range', 3.1415926500000002),
(u'single range int', 31415926500.0), (u'double range',
5.1234567689012342), (u'double range int?', 5.1234567689012347e+020)]

    Inputs were "123" (integer), "3.14159265" (float), "3.14159265E10"
(float rep of an integral value), etc.

    NOW, recreating the table definition as:

>>> cur.execute("""create table stats (id integer primary key,
...         name char(50),
...         num numeric)"""
...         )

and re-entering the same type of data gives:

[(u'integer range', 123), (u'single range', 3.1415926500000002),
(u'single range int', 31415926500L), (u'double range',
5.1234567689012342), (u'double range int?', 5.1234567689012347e+020)]

    If the field definition is not explicitly typed, SQLite uses the
smallest storage format it can safely coerce the input data toward.
Which means, for numeric values that can be represented as an
integer/long integer, that is the default format used.
-- 
    Wulfraed    Dennis Lee Bieber        KD6MOG
    [email protected]        [email protected]
        HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff:        [email protected])
        HTTP://www.bestiaria.com/

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite