Path: csiph.com!news.swapon.de!fu-berlin.de!uni-berlin.de!not-for-mail
From: Chris Angelico <rosuav@gmail.com>
Newsgroups: comp.lang.python
Subject: Re: Problem with sqlite3 and Decimal
Date: Sat, 12 Dec 2015 05:45:46 +1100
Lines: 23
Message-ID: <mailman.154.1449859548.12405.python-list@python.org>
References: <n4ei3l$b98$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
X-Trace: news.uni-berlin.de PUvhvwdZzTh9JALyWeRrnQv0YZxzQpF5pCB2TUuBVUkw==
Return-Path: <rosuav@gmail.com>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.002
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'received:209.85.223': 0.03; 'subject:sqlite3': 0.07; 'cc:addr:python-list': 0.09; 'postgresql,': 0.09; 'sqlite': 0.09; 'python': 0.10; 'from:addr:rosuav': 0.16; 'from:name:chris angelico': 0.16; 'oddity': 0.16; 'received:io': 0.16; 'received:psf.io': 0.16; 'sqlite3': 0.16; 'subject:Problem': 0.16; 'url:sqlite': 0.16; 'why:': 0.16; 'wrote:': 0.16; 'string,': 0.18; '2015': 0.20; 'cc:2**0': 0.20; 'cc:addr:python.org': 0.20; 'select': 0.23; 'dec': 0.23; 'header:In-Reply-To:1': 0.24; "doesn't": 0.26; 'example': 0.26; 'fri,': 0.27; 'message-id:@mail.gmail.com': 0.27; 'issue,': 0.27; 'arithmetic': 0.29; 'noticed': 0.32; 'received:google.com': 0.35; 'ones': 0.35; 'asking': 0.35; "isn't": 0.35; 'but': 0.36; 'should': 0.36; 'url:org': 0.36; 'tool': 0.36; 'received:209.85': 0.36; 'pm,': 0.36; 'subject:: ': 0.37; 'available.': 0.37; 'skip:5 10': 0.37; 'received:209': 0.38; 'data': 0.39; 'subject:with': 0.40; 'your': 0.60; 'frank': 0.72; 'bal': 0.84; 'chrisa': 0.84; 'to:none': 0.91
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:cc :content-type; bh=48L2OO8ownXu4IoRx0hQYfJwnRQ3BCYc59A8sdInLRs=; b=pPy5Op6orzavUTBDVbfriw8k2Ounb2NBdURygFvvZlZ254v0/2djtMAHbeQgZIc1Gu EBLFoB8ln58gBfSkI3ifPsTqcGmHPcKbXGtl0bHfMuCJ9KmJ78Z4xTLwLFkqyoPT15xq 9btnfgvGO1w4OyCcqRiT25KkszX0ka3GcKOaspHPWqsGiqlwGF3HKLSVCZP+8aYq575y FHa4vogzik8oRp1TWBDPAquYY9f5OyoSfjC4X2968rH+PJX4eJ9NgZjtktnugk7EaLcn UkLqp4mhSxv5L2YUQ4vrdwuIOsW6dtiCJ2aF5dAps/mqmkq2+1eWSTYbdZwPzl7FCdsI 5GIQ==
X-Received: by 10.107.30.147 with SMTP id e141mr2267698ioe.31.1449859546463; Fri, 11 Dec 2015 10:45:46 -0800 (PST)
In-Reply-To: <n4ei3l$b98$1@ger.gmane.org>
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.20+
Precedence: list
List-Id: General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <http://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Xref: csiph.com comp.lang.python:100292

On Fri, Dec 11, 2015 at 8:21 PM, Frank Millman <frank@chagford.com> wrote:
> I noticed one oddity - I am asking sqlite3 to store the value as a string,
> but then I am asking it to perform arithmetic on it.

It's an SQLite3 issue, not a Python one. I used the sqlite3
stand-alone tool to do the same thing:

sqlite> update fmtemp set bal = bal + cast('123.45' as numeric);
sqlite> select bal from fmtemp;
...
5678.7
5802.15
5925.59999999999

And this might be why:

https://www.sqlite.org/datatype3.html

SQLite doesn't *have* all the SQL data types, and NUMERIC is one of
the ones that isn't available. If you recreate your example using
PostgreSQL, it should work fine.

ChrisA