QSqlDatabase, SQLite and Decimal (on python)
-
wrote on 17 Aug 2021, 06:04 last edited by
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 offloat
datatype instead ofDecimal
intentionally due to SQLite usage. But I see more and more reasons to swich toDecimal
. 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()
andregister_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?
-
-
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 offloat
datatype instead ofDecimal
intentionally due to SQLite usage. But I see more and more reasons to swich toDecimal
. 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()
andregister_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?
wrote on 17 Aug 2021, 07:22 last edited by 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
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?
wrote on 17 Aug 2021, 08:43 last edited by@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 fromREAL
toINTEGER
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
toint
on python side, changing delegates and routines that usefloat
now. But I suppose a code might look weird.
I just have a gut feeling that python code withDecimal
will require minor adaptation fromfloat
, will work and look good. On the other side switching python code fromfloat
toint
doesn't feel as a good idea to me. -
@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 fromREAL
toINTEGER
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
toint
on python side, changing delegates and routines that usefloat
now. But I suppose a code might look weird.
I just have a gut feeling that python code withDecimal
will require minor adaptation fromfloat
, will work and look good. On the other side switching python code fromfloat
toint
doesn't feel as a good idea to me.wrote on 17 Aug 2021, 08:48 last edited by@StarterKit
From Python I usedDecimal
, and declaredDECIMAL
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,. -
wrote on 17 Aug 2021, 09:42 last edited by
@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 toregister_adapter()
andregister_converter()
provided bysqlite3
) -
@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 toregister_adapter()
andregister_converter()
provided bysqlite3
)wrote on 17 Aug 2021, 10:19 last edited by@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 PythonDecimal
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
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 PythonDecimal
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.
wrote on 17 Aug 2021, 10:35 last edited by 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 modifyQSqlQuery
to handle data updates properly. I'll think about it.Thank you very much for information.
1/7