classification
Title: sqlite numeric type conversion problems
Type: enhancement Stage:
Components: Library (Lib) Versions: Python 2.5
process
Status: closed Resolution: accepted
Dependencies: Superseder:
Assigned To: ghaering Nosy List: ghaering, wrobell
Priority: normal Keywords: patch

Created on 2008-02-21 18:57 by wrobell, last changed 2008-05-04 13:43 by ghaering. This issue is now closed.

Files
File name Uploaded Description Edit
python-sqlite-numeric.patch wrobell, 2008-02-21 18:57 fix numeric type conversion
Messages (6)
msg62640 - (view) Author: wrobell (wrobell) Date: 2008-02-21 18:57
Numeric type conversion does not always work when using SQLite module.

Let's assume schema:

    create table test_num(no numeric);

Fetching a `test_num.no` table gives float by default.

Now, let's register some converter

    sqlite3.register_converter('numeric', lambda d: Decimal(d))

Fetching `test_num.no` gives Decimal.

But change `test_num.no` type to `numeric(10, 2)`. Float is returned.
This can be fixed by

    sqlite3.register_converter('numeric(10,', lambda d: Decimal(d))

But above will fail for `test_num.no: numeric(10,2)', which works in
case of

    sqlite3.register_converter('numeric(10,2)', lambda d: Decimal(d))

SQLite module's cursor object type cast mechanism breaks declared
type after first space {i.e. "numeric(10, 2)" -> "numeric(10,"} and
then looks up registered type converter using the first "word".

The simple fix for above could be to perform the break after space
or "(" character. Patch attached.
msg63076 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2008-02-27 19:52
The same question came up in the external pysqlite project once. My
answer is the same here: this won't be fixed because of backwards
compatibility reasons. The current behaviour is well documented.

I acknowledge that it would be nice to be able to use declaratos like
number(x,y) used with other databases and reuse them with SQLite and
pysqlite. But we can't implement your feature request with 100 %
backwards compatibility.
msg63079 - (view) Author: wrobell (wrobell) Date: 2008-02-27 20:44
fair enough, but...

1. i really do not see that behaviour documented, i.e.

       http://docs.python.org/lib/node347.html

   says nothing about types being broken on space character.

2. i am bit missing the point about backward compability.
   when looking on internet for a reason why types like
   "numeric(10, 2)" are not being converted (please note
   that "numeric (10, 2)" works, the difference is just one
   space), then i found no explanation of such behaviour,
   no single line of code trying to register conversion
   of "numeric(10,)" or similar.

   even more. if somebody has declaration like

      register_converter("numeric(10,", Decimal)

   then i can bet that he has also

      register_converter("numeric", Decimal)

   i doubt that my patch is breaking anybody's code, as no one
   is going to register "numeric(10," without registering "numeric".
msg63080 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2008-02-27 20:47
It's mentioned here: http://docs.python.org/lib/sqlite3-Module-Contents.html
msg63081 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2008-02-27 20:53
Yes, I acknowledge the backwards incompatibility is rather a one in a
billion case than a one in a million case.

I'll reopen this one and integrate your patch or something similar for
Python 2.6 and 3.0.
msg66208 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2008-05-04 13:43
Implemented in r62702.
History
Date User Action Args
2008-05-04 13:43:12ghaeringsetstatus: open -> closed
resolution: accepted
messages: + msg66208
2008-02-27 20:53:21ghaeringsetstatus: closed -> open
resolution: wont fix -> (no value)
messages: + msg63081
2008-02-27 20:47:46ghaeringsetmessages: + msg63080
2008-02-27 20:44:59wrobellsetmessages: + msg63079
2008-02-27 19:52:39ghaeringsetstatus: open -> closed
nosy: + ghaering
messages: + msg63076
assignee: ghaering
type: enhancement
keywords: + patch
resolution: wont fix
2008-02-21 18:57:02wrobellcreate