Displaying Dollars while Storing Cents (PyQt5 SQLite3)

BamboozledBaboon

The sqlite3 database stores currency values as integers in cents. I need to convert this field to dollars for display in views and/or widgets. For example the gui would present $321.12 but this is stored as an integer as 32112 (also see here). The user also needs to input in dollar units and that needs to be converted to cents before storing in the db.

What's the best way (most centralized?) way to go about this? This field might be presented in a TableView, lineEdit, or spinBox depending on the particular situation.

Here is a simple example code where I would like to apply this conversion to the 'amount' column"


import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel


class Ui_Dialog(object):
    def setupUi(self, Dialog):
        Dialog.setObjectName("Dialog")
        Dialog.resize(550, 500)
        self.tableView = QtWidgets.QTableView(Dialog)
        self.tableView.setGeometry(QtCore.QRect(40, 40, 470, 420))
        self.tableView.setObjectName("tableView")

        self.retranslateUi(Dialog)
        QtCore.QMetaObject.connectSlotsByName(Dialog)

        self.model = QSqlTableModel()
        self.model.setTable('test_table')
        self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.select()
        self.tableView.setModel(self.model)

    def retranslateUi(self, Dialog):
        _translate = QtCore.QCoreApplication.translate
        Dialog.setWindowTitle(_translate("Dialog", "Dialog"))


def db_conn(dbname=":memory:"):
    c = QSqlDatabase.addDatabase("QSQLITE")
    c.setDatabaseName(dbname)
    if not c.open():
        print("Unable to connect to the database")
        sys.exit(1)
    return c


def build_test_table():
    q = QSqlQuery()
    q.exec('''
           CREATE TABLE test_table (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                desc TEXT NOT NULL,
                amount INT NOT NULL DEFAULT 0
           );
           ''')
    # insert some sample data
    q.exec('INSERT INTO test_table VALUES (null, "some description", 43212);')
    q.exec('INSERT INTO test_table VALUES (null, "another description", 583);')
    q.exec('INSERT INTO test_table VALUES (null, "final description", 1396500);')


def print_test_table():
    q = QSqlQuery()
    q.exec("SELECT * FROM test_table;")
    while q.next():
        print(q.value(0), q.value(1), q.value(2))


if __name__ == "__main__":

    conn = db_conn() #in memory
    build_test_table()
    print_test_table() #just to check that we're all good so far


    app = QtWidgets.QApplication(sys.argv)
    Dialog = QtWidgets.QDialog()
    ui = Ui_Dialog()
    ui.setupUi(Dialog)
    Dialog.show()
    sys.exit(app.exec_())

-- edit --
Would creating a custom editable QSqlQueryModel be the best way to go? https://doc.qt.io/qt-5/qtsql-querymodel-example.html

JonB

@BamboozledBaboon
Hi, nice nickname! :)

There is so much to say, and so many places you can/could do it, I'm sorry but I'm too tired to type in all the stuff now. So a couple of hints.

What I would say is: before you start, decide whether you intend to store the currency values in Python using its Decimal type. That's what I did. You won't find examples of this in Qt/C++ because they do not have such a type as Python does. Be wary of storing it as a float-type, as you may be in danger of losing precision and/or getting too many (or few) decimal places. Decimal lets you work with 2 fixed decimal places without losing any precision/rounding. One wrinkle is that I used MySQL which has a native decimal type, which I used in the database for the columns, where you are saying SQLite uses an integer-cents instead.

If you are happy working with the integer-cents that is the database representation that might not be a bad idea. I would then suggest doing conversions as near to the UI/widget side as possible, so internally you still work with integers and don't have precision issues.

The other thing is: write a utility class/functions for whatever conversion you may do like integer_cents<->decimal_dollars<->string_with_2_decimals. I found that when you want support for "a TableView, lineEdit, or spinBox" you will want to share conversion code among these different classes, and trying to put it inside one of them (e.g. the data() method of model) did not work out well.

BamboozledBaboon

@JonB
Thank you for the response. It looks like I need to study the Decimal class, at first glance the examples I see are a bit contradictory and confusing.

Anyway, I managed to cobble together a custom editable QSqlQueryModel that first converts decimals to dollars from the database, and then converts back to cents before updating the db. After I did this is when I saw your reply to my post. This approach seems to be the opposite of 'as near to the UI/widget side as possible' :(

I executed print(type(self.index(index.row(),2).data())) to see the datatype for the column and found it is a float datatype. How would I force the data loaded into python via the query model to be of the Decimal class?

For reference this is the class I had come up with:

class MyModel(QSqlQueryModel):
    def __init__(self):
        super(QSqlQueryModel, self).__init__()
        self._filter = ''

    def select(self):
        sql = 'SELECT id, desc, amount/100.0 AS amnt FROM test_table'
        if self._filter != '':
            sql += f' WHERE {self._filter}' 

        self.setQuery(sql)
        self.setHeaderData(0, QtCore.Qt.Horizontal, 'ID')
        self.setHeaderData(1, QtCore.Qt.Horizontal, 'Description')
        self.setHeaderData(2, QtCore.Qt.Horizontal, 'Amount')


    def setData(self, index, value, role=QtCore.Qt.EditRole):
        ok = False
        if role == QtCore.Qt.EditRole:
            pk = self.index(index.row(),0).data()
            q = QSqlQuery()
            if index.column() == 1:
                q.prepare('UPDATE test_table SET desc = ? WHERE id = ?;')
                q.addBindValue(value)
            elif index.column() == 2:
                q.prepare('UPDATE test_table SET amount = ? WHERE id = ?;')
                q.addBindValue(int(round(value*100.0,0)))

            q.addBindValue(pk)
            ok = q.exec()

            if ok:
                self.select()
                self.dataChanged.emit(index, index, [role])

        return ok


    def flags(self, index):
        f = QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled
        if index.column() > 0:
            f |= QtCore.Qt.ItemIsEditable

        return f

    def setFilter(self, fltr):
        self._filter = fltr
        self.select()

JonB

@BamboozledBaboon said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

sql = 'SELECT id, desc, amount/100.0 AS amnt FROM test_table'

q.addBindValue(int(round(value*100.0,0)))

These are just the sort of things I personally would not do. Using decimal in SQL would help, but I don't think SQLite supports it. If you don't choose to use Python Decimal, I would be tempted to stick with the integer-cents to and from the database And just convert to the 2 decimal places at UI input/output stage. But maybe your way is OK, I don't know.

Separately, you are using read-only QSqlQueryModel, and doing your own explicit SQL updates in setData(). Did you look at using QSqlTableModel instead?

SGaist

Hi,

If you want to "lie" to the user with regard to what is shown, this is typically a use case for a custom QStyledItemDelegate.

You do the conversion when showing the data, as well as when populating the editor widget, and writing back to the model.

Don't write a custom QSqlTableModel or QSqlQueryModel. If you really want to do that inside a model, use a QIdentityProxyModel so you ensure separation of concerns between database data and your application specific changes. This will also allow you to more easily reuse your QSqlTableModel if you want to show the raw data from the database in a different view.

BamboozledBaboon

@JonB
I didn't look into customizing a QSqlTableModel class. I just went with the query model class in this case so I could run amount/100 in a query instead of pulling in the raw data from the table.

I'm playing around with the decimal class and writing my own model.data() method. The issue I have is that if I set the value to a Decimal type class then the view doesn't display anything, I have to convert it to float anyway. Also, if I bring in the data as an integer and convert it to float for display purposes, when the user goes to edit the value in the view the editor displays the internal cent-integer value instead of the dollar-float value I'd like the user to see.

So my questions are:

How do I bring in the data and store it as a Decimal class in the back end?
How do I display it as a 'float' seamlessly so the user doesn't know any better?

JonB

@BamboozledBaboon said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

when the user goes to edit the value in the view the editor displays the internal cent-integer value instead of the dollar-float value I'd like the user to see.

You have to write the input editor side too. QStyledItemDelegate subclass has createEditor(). As well as displayText() for output. You'll have to do something anyway to ensure 2 decimal place input/output, and the $ symbol if you want it.

BamboozledBaboon

@SGaist @JonB
Okay I've fumbled this together and it appears to function as I need. The data is still stored as in integer in the model, is that a problem or do I need to find a way to force it into Decimal class?

class CurrencyDelegate(QtWidgets.QStyledItemDelegate):

    def displayText(self, value, locale):
        return str(Decimal(value)/Decimal(100))

    def setEditorData(self, editor, index):
        editor.setText(str(Decimal(index.data())/Decimal(100)))

    def setModelData(self, editor, model, index):
        model.setData(index, Decimal(editor.text())*Decimal(100))

note: per JonB's suggestion I do intend to create a functions or class to handle conversions rather than doing decimal(x)/decimal(y) all over the place

SGaist

Aren't you using a spinbox to edit your number ?

BamboozledBaboon

@SGaist said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

Aren't you using a spinbox to edit your number ?

I would like to. I havn't explicitly defined the editor control and I'm getting funny results. Sometimes the tableview will present me with a lineEdit and sometimes a spinbox.

I suppose for a spinbox I need editor.setValue rather than setText.

How do specify to always use a doubleSpinBox?

(I've also switched back to a standard QSqlTableModel rather than my custom query model)

SGaist

Take a look at the Spin Box Delegate example. It's in C++ but easily translatable in Python. And just replace the QSpinBox by a QDoubleSpinBox.

BamboozledBaboon

@SGaist said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

Take a look at the Spin Box Delegate example. It's in C++ but easily translatable in Python. And just replace the QSpinBox by a QDoubleSpinBox.

Ah yes, I was just there!

How does this look? It appears to be functional.

I'm still using floats but it's in the presentation so I guess it doesn't matter?
The model's data is still int rather than decimal, does that matter?

class CurrencyDelegate(QtWidgets.QStyledItemDelegate):
    def createEditor(self, parent, option, index):
        editor = QtWidgets.QDoubleSpinBox(parent)
        editor.setMinimum(-100000000)
        editor.setMaximum( 100000000)
        return editor

    def displayText(self, value, locale):
        return str(Decimal(value)/Decimal(100))

    def setEditorData(self, editor, index):
        d = Decimal(index.data()) / Decimal(100)
        editor.setValue(float(d))

    def setModelData(self, editor, model, index):
        model.setData(index, int(round(Decimal(editor.text())*Decimal(100))))

JonB

@BamboozledBaboon said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

I'm still using floats but it's in the presentation so I guess it doesn't matter?
The model's data is still int rather than decimal, does that matter?

Both good.

SGaist

if it has enough precision for what you want, yes.
that's for you to decide. This article presents a pretty interesting dive into the decimal VS bigint use for monetary values.

BamboozledBaboon

@JonB said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

One wrinkle is that I used MySQL which has a native decimal type, which I used in the database for the columns, where you are saying SQLite uses an integer-cents instead.

@SGaist said in Displaying Dollars while Storing Cents (PyQt5 SQLite3):

that's for you to decide. This article presents a pretty interesting dive into the decimal VS bigint use for monetary values.

Thank you both for your help! Unfortunately sqlite3 doesn't support decimal datatypes, only integer and real (float), so as far as I can tell I'm forced to either use integer or find another storage solution.

JonB

@BamboozledBaboon
We have said that's fine to use SQLite's integer instead of some decimal. The real point is not to use float. Either way you have a database storage type which does not match the input/output type you will have to use in Qt, and hence must do some kind of conversion at I/O stage in the UI. Using QStyledItemDelegate as you show is the right way to do that.