QSqlDatabase, SQLite and Decimal (on python)

StarterKit

Hi all,

I have a python GUI application based on Qt. It works well and stores data in local SQLite database.
I stuck to usage of float datatype instead of Decimal intentionally due to SQLite usage. But I see more and more reasons to swich to Decimal. Unfortunately SQLite doesn't have its support out of the box.

I see 2 possible way forward but both are not clear for me.

Native python sqlite3 library has methods register_adapter() and register_converter() that allow custom datatype converstion to/from SQLite datatype on the fly (I found example of usage on stackoverflow). This might be a good option for me but I have no idea how to do something similar with QSqlDatabase. Is it possible?
Another option is to switch from SQLite to some other DB engine. But here I would like to keep user experience without change. My application takes care of all SQLite operations by itself, also it may store DB file in application folder that allows it to be almost portable (you simply need to copy a folder to another PC with python and libs installed). Plus, I have a lot of SQL queries under the hood so I think JSON-like and other options are not good idea here. Are there any other SQL DB which will allow similar usecases?

Can someone give some hint/advice for this situation?

JonB

@StarterKit
I would not want to change over from SQLite, which is working well for you, to some other RDBMS just for the sake of support of decimal.

Since SQLite does not support decimal it does not matter too much that Qt (nor C++ natively) does not either. Why can't you manage with float/real as you are now?

StarterKit

@JonB , it works well with float but some rounding errors are accumulated.
Nothing really critical for the moment that can't be fixed by proper output formatting. But it might become a problem in future as my data have financial nature. So I'm thinking about possible options.

Yes, I know that there is no support for DECIMAL(X, Y) in SQLite. But I feel 8-byte interger might be enough for my purpose (I assume it will allow to have 10 digits before and 8 digits after decimal point). Switch from REAL to INTEGER may require a review of aggregations in SQL queries but I don't expect very big problems with it.

I may do it vise versa - switch from float to int on python side, changing delegates and routines that use float now. But I suppose a code might look weird.
I just have a gut feeling that python code with Decimal will require minor adaptation from float, will work and look good. On the other side switching python code from float to int doesn't feel as a good idea to me.

JonB

@StarterKit
From Python I used Decimal, and declared DECIMAL columns for storage using MySQL. (I still had to do some work, as Qt classes for models don't know about decimal.) But that requires a MySQL server. I still would not wish to change over from SQLite, if that was what I was otherwise happy with, just for this issue (unless it's critical, I guess). Presumably loads of other people manage with SQLite's types for this. Anyway that's my thought,.

StarterKit

@JonB, yes, I agree with you that switching from SQLite doesn't look as a thing I need. So we may put it aside unless someone won't bring some fresh idea about it.

When you wrote:

I still had to do some work, as Qt classes for models don't know about decimal.

what expactly you meant? Does it mean you had written your own conversion inside models anyway? (I just started to think about doing python Decimal <-> INTEGER SQLite conversion in classes derived from QSqlTabel, QSqlQuery... - it appears to be the only possible alternative to register_adapter() and register_converter() provided by sqlite3)

JonB

@StarterKit
By the time MySQL's decimal type hit Qt's model it gets converted to float, even though Python (not C++) has a decimal type. I had to call code to convert to/from Python Decimal for my program's internal use, and for displaying with two decimal places in views attached to tables, etc.

There are pros and cons to whether you store your fixed-point decimals as approximate float or as integer to be divided by 100 in the database backend.

StarterKit

@JonB said in QSqlDatabase, SQLite and Decimal (on python):

By the time MySQL's decimal type hit Qt's model it gets converted to float, even though Python (not C++) has a decimal type

Oh, thanks for mentioning this fact. I didn't know it and expected that Qt would be able to handle Decimal itself.
This way it appears I'll need to do similar job and also modify QSqlQuery to handle data updates properly. I'll think about it.

Thank you very much for information.