How to format a column with decimal numbers in QSqlTableModel

Question

I have got a QSqlTableModel with decimal numbers in one column. How can I format this column to have numbers with 4 decimal places (e.g.: 2,3 --> 2,3000; 4,567891 --> 4,5679). I am using pyqt5.

Edit:

I tried to subclass QSqlTableModel like this:

class AlignmentTable(QSqlTableModel):
    def data(self, index, role=Qt.DisplayRole):
       if role == Qt.DisplayRole and index.column() == 4:
          value = '{:01.4f}'.format(self.data(index))
          return value

But I get the error: RecursionError: maximum recursion depth exceeded in comparison

Edit:

First I load the model like this:

def load_sitesizes(self):

    self.mod_site_sizes = AlignmentTable(parent=None, db=dbtools.ProjectDB.use_project_db(self))
    self.mod_site_sizes.setTable("vSiteSizes")

    site_id = str(self.item_id)
    self.mod_site_sizes.setFilter("SiteKey='"+site_id+"'")

    self.mod_site_sizes.select()

    self.mod_site_sizes.setEditStrategy(QSqlTableModel.OnFieldChange)

    self.tblSiteSizes.setModel(self.mod_site_sizes)

and than your code in a subclass:

class AlignmentTable(QSqlTableModel):

def data(self, item, role):

    if role == Qt.DisplayRole:
        if item.column() == 4:
            val = QSqlTableModel.data(self, item, Qt.DisplayRole)
            if not isinstance(val, float):
                val = float(val)
            return '{:.4f}'.format(round(val, 4))

Consider using `QString::arg ( double a, int fieldWidth = 0, char format = 'g', int precision = -1, const QChar & fillChar = QLatin1Char( ' ' ) ) ` function in your model. — vahancho, Jul 27 '17 at 07:16
Maybe you should read http://doc.qt.io/qt-5/model-view-programming.html — Fritz, Jul 27 '17 at 07:24
Numbers with decimals are usually separated by periods, not by commas. — eyllanesc, Jul 27 '17 at 07:27
@eyllanesc, depends on locale. In Germany, for example, it's comma. — vahancho, Jul 27 '17 at 07:29
@vahancho As you are reading from a database, so I ask if they are floating or are strings representing floating. — eyllanesc, Jul 27 '17 at 07:30
I get the data from a SQLite-database. In my model the numbers are shown like this: 2,3 — vezzon, Jul 27 '17 at 07:52
@vezzon You could show an image of what you get when editing the content please. — eyllanesc, Jul 27 '17 at 08:31

eyllanesc · Accepted Answer · 2017-07-27T10:48:09.840

3

You should not access the self.data(item) function since you are calling the same function, you must access through the parent: QSqlTableModel.data(self, item, Qt.DisplayRole).

period:

def data(self, item, role):
    if role == Qt.DisplayRole:
        if item.column() == 4:
            val = QSqlTableModel.data(self, item, Qt.DisplayRole)
            if not isinstance(val, float):
                val = float(val)
            return '{:.4f}'.format(round(val, 4))

commas:

def data(self, item, role):
    if role == Qt.DisplayRole:
        if item.column() == 4:
            number = round(float(QSqlTableModel.data(self, item, Qt.DisplayRole).replace(",", ".")),4)
            return ('{:.4f}'.format(number)).replace(".", ",")
    return QSqlTableModel.data(self, item, role)

According to what I observe in your database, the field of column 4 is of decimal type and the problem is that pyqt recognizes it as integer, the solution is simple, you must convert it to floating:

class CustomSqlModel(QSqlTableModel):
    def __init__(self, parent=None):
        QSqlTableModel.__init__(self, parent=parent)
        self.setTable("SiteSizes")
        self.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.select()

    def data(self, item, role):
        val = QSqlTableModel.data(self, item, role)
        if role == Qt.DisplayRole:
            if item.column() == 4:
                try:
                    return '{:.4f}'.format(round(float(val), 4))
                except ValueError:
                    pass
        if role == Qt.EditRole:
            if item.column() == 4:
                try:
                    return float(val)
                except ValueError:
                    pass
        return val

if __name__ == '__main__':
    import sys

    app = QApplication(sys.argv)

    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName("zzProjekt1.db")

    model = CustomSqlModel()

    view = QTableView()
    view.resizeColumnsToContents()
    view.setModel(model)
    view.setWindowTitle("Custom Model")

    view.show()

    sys.exit(app.exec_())

Another additional thing is that when editing the default number can be edited 2 decimals, to change it to 4 decimals you can use the following class:

class NumberFormatDelegate(QItemDelegate):
    def __init__(self, parent=None):
        QItemDelegate.__init__(self, parent)

    def createEditor(self, parent, option, index):
        editor = QDoubleSpinBox(parent)
        editor.setMaximum(10**10)
        editor.setDecimals(4)
        return editor
[...]
view.setItemDelegateForColumn(4, NumberFormatDelegate())

Note: Another thing I've noticed from your database is that vSiteSizes is not a Table, it's a View, and it's read-only, so you can not edit it.

edited Jul 27 '17 at 10:48

answered Jul 27 '17 at 07:52

eyllanesc

235,170
19
170
241

@ eyllanesc: I have tried your first suggestion, and it works. But: My table is editable. When I doublechlick the cell to edit the value the cell is emty. – vezzon Jul 27 '17 at 08:09
Could you tell me what you get by executing the following: `print(type(QSqlTableModel.data(self, item, Qt.DisplayRole)))` – eyllanesc Jul 27 '17 at 08:13
– vezzon Jul 27 '17 at 08:16
I do not see anything, edit your question add it there please. – eyllanesc Jul 27 '17 at 08:24
Also you could tell me how you created the table in your database to test it. – eyllanesc Jul 27 '17 at 08:27
I have edited my question a second time. I create the table (vSiteSizes) in my database with a QSQlite-manager (SQLiteStudio). Your second suggestion creates an emty cell. – vezzon Jul 27 '17 at 08:32
What type of field is column 4? – eyllanesc Jul 27 '17 at 08:33
Could you share your database? – eyllanesc Jul 27 '17 at 08:33
Okay. One moment please. I will add the picture and the database. – vezzon Jul 27 '17 at 08:35
If you could share the database through drive, dropbox or similar would be great. – eyllanesc Jul 27 '17 at 08:36
Add the following and tell me what you get, please. `if role == Qt.EditRole: if item.column() == 4: print(QSqlTableModel.data(self, item, Qt.EditRole)) print(type(QSqlTableModel.data(self, item, Qt.EditRole)))` – eyllanesc Jul 27 '17 at 08:41
2 – vezzon Jul 27 '17 at 09:10
Here you can find a picture editing the table and the SQLite-database: https://www.dropbox.com/sh/frxg4yuzsga1esw/AABzbhI-YQavZlxNL5RHcOcza?dl=0 – vezzon Jul 27 '17 at 09:12
Yes, it is a view, but it is editable. But if I change to the table "sitesize" I have the same result. – vezzon Jul 27 '17 at 10:24
I've updated my code, but test it with the SiteSizes table. – eyllanesc Jul 27 '17 at 10:29
I have allread set a itemdelegate with 4 decimals to this column. it works great with your code. Thank you! – vezzon Jul 27 '17 at 10:52

How to format a column with decimal numbers in QSqlTableModel

1 Answers1