The world’s leading publication for data science, AI, and ML professionals.

PyQt & Relational Databases – Data Format 2

Present relational database data easily with PyQt. No SQL needed.

DATA

Image by Author and Unsplash
Image by Author and Unsplash

Today, we’ll go deeper into data formatting.

We’ll talk about item data roles used by the view to indicate to the model which type of data it needs.

A few more examples of data formatting and a little bonus at the end will help you understand table data formatting.

This article is part of PyQt & Relational Databases Series:

PyQt __ & Relational Databases Series


Table presentation

What have we learned so far? A lot.

However, it might look a little because we wrote relatively few lines of code. It’s suitable for a developer because it indicates a rapid productivity increase.

Now, you know how to make your life easier by avoiding SQL queries knowledge. No SQL queries have been compiled, and you’re able to populate your table with data from a relational database, SQLite database in this case.

And you understand the Model-View-Controller design pattern, and you are able to use it effectively with Pyqt.

These skills are supported by model customization through QAbstractTableModel.

Taking control over the data presentation model provides endless possibilities to make data human-readable.

For example, we have indicated currency and precision, two essential properties of pricing data.

Item data roles

What else can the presentation model do for us?

I strongly recommend playing with it. You’ll learn a lot, and you’ll discover the possibilities are endless.

The best way is to start with roles. The roles represent a Qt.ItemDataRole enumeration.

Each item in the model has a set of data elements associated with it, each with its role. Custom models should return data in these types.

Think of them as item features, based on which the system understands what we want to do with an item.

Based on the role effect, there are general purpose roles, roles describing appearance and meta-data, accessibility roles, and user roles available.

We will use DisplayRole and DecorationRole today. However, we’ll get back to some more when we need them.

Changing ‘ID’ and ‘Date’ format

Let’s learn a little bit more.

The DisplayRole is very useful.

In our example we’ll format an ID number column and change the date format. Let’s do it!

It’s straightforward. We’ll add a few lines to our data() method within the subclassed QAbstractTableModel.

def data(self, index, role):
    value = self._model.record(index.row()).value(index.column())
    if role == Qt.ItemDataRole.DisplayRole:
        if isinstance(value, int) and index.column() == 0:
            return f'# {value}'
        if isinstance(value, int) and index.column() == 1:
            return "${: ,.2f}".format(value)
        if isinstance(value, str) and index.column() == 4:
            date_object = date.fromisoformat(value)
            return date_object.strftime('%x')
        return value

We’ve added just a few lines in bold font above. In the first column, we add the "#" symbol.

For the dates column we need some imports:

from datetime import date
from time import strftime

As our value is just a plain text (in ISO format), we create the date_object.

We need it for convenient format conversion. The following line returns the plain text from a date converted to the local format.

Adding seven lines of code only, the table changes, and the result is here.

Image by Author
Image by Author

Decoration

As promised, here’s a little bonus at the end.

It demonstrates a DecorationRole power.

Sometimes it’s useful to make a table more appealing using some decoration. It shows two different situations.

If you look at the first column, you notice we add a hashtag "#" symbol in front of an integer order number value.

In case of writing data to the model we’d convert the item to string and this could be a problem when writing data to the database.

To avoid this undesired conversion, it’s better to use a decoration. We have to delete the whole if statement for column 0 in DisplayRole section.

def data(self, index, role):
    value = self._model.record(index.row()).value(index.column())
    if role == Qt.ItemDataRole.DisplayRole:
        if isinstance(value, int) and index.column() == 1:
            return "${: ,.2f}".format(value)
        if isinstance(value, str) and index.column() == 4:
            date_object = date.fromisoformat(value)
            return date_object.strftime('%x')
        return value
    if role == Qt.ItemDataRole.DecorationRole:
        if isinstance(value, int) and index.column() == 0:
            return QtGui.QIcon('data/icons/hashtag_icon.png')
        if isinstance(value, str) and index.column() == 4:
            return QtGui.QIcon('data/icons/calendar.png')

Then we just implement two if statements in DecorationRole section.

First one for column 0, to add a hashtag symbol decoration, and another one for column 4, to add calendar icon decoration.

Image by Author
Image by Author

Relational database data presentation

It is always important to pay attention to data presentation.

PyQt makes it easy to keep the database source untouched and still provides a lot of opportunities to make data presentation compelling for the user.


If you’d like to read more content like this, become a member:

Join Medium with my referral link – Stefan Melo

Or you can subscribe to my newsletter below. Thank you.


Related Articles