This is a Python 3.9 script that pulls data from a MySQL database and converts it to a list of named tuples and then passing it to PyQt6.QTreeWidget.

It has to be a list of namedtuples, because I am going to store lists of tags packed in strings dumped from json.dumps in single fields, then using json.loads to unpack the lists. This way, filter by tags can easily done with issubsetmethod.

This will be the main interface of a GUI music player project I am working on, though it is still bare bones, at least the tree hierarchy part is complete.

This is the glimpse of the database:

enter image description here

This is what the window looks like:

enter image description here

And here is the code:

import sys
import mysql.connector
from collections import namedtuple
from PyQt6.QtWidgets import QApplication, QTreeWidget, QTreeWidgetItem

song = namedtuple('song', 'artist title album')

app = QApplication(sys.argv)
conn = mysql.connector.connect(user='Estranger', password=********, host='', port=3306, database='Music')
cursor = conn.cursor()
cursor.execute('select artist, title, album from songs')
songs = [song(*i) for i in cursor.fetchall()]
tree = QTreeWidget()
frame = tree.frameGeometry()
center = tree.screen().availableGeometry().center()
tree.setHeaderLabels(['Name', 'Artist', 'Album', 'Title'])
entries = []
for p in sorted(set([_.artist for _ in songs])):
    artist = QTreeWidgetItem([p])
    for a in sorted(set([_.album for _ in songs if _.artist == p])):
        album = QTreeWidgetItem([a, p, a])
        for s in [_.title for _ in songs if _.artist == p and _.album == a]:
            song = QTreeWidgetItem([s, p, a, s])
tree.insertTopLevelItems(0, entries)

(Password obscured for obvious reasons)

I adapted my code from here: https://doc.qt.io/qtforpython-6/tutorials/basictutorial/treewidget.html

The code there:

import sys
from PySide6.QtWidgets import QApplication, QTreeWidget, QTreeWidgetItem
data = {"Project A": ["file_a.py", "file_a.txt", "something.xls"],
        "Project B": ["file_b.csv", "photo.jpg"],
        "Project C": []}
app = QApplication()
tree = QTreeWidget()
tree.setHeaderLabels(["Name", "Type"])
items = []
for key, values in data.items():
    item = QTreeWidgetItem([key])
    for value in values:
        ext = value.split(".")[-1].upper()
        child = QTreeWidgetItem([value, ext])
tree.insertTopLevelItems(0, items)

In the example, the data is already hierarchical, it is a dictionary structured like a tree, but my data is a list of named tuples which is kind of flat and more suitable for a table.

But I prefer using a tree-like view to present the data.

Because my data is flat, and the example code structure needs the data to be hierarchical, I used list comprehensions plus cast to set plus sorted function to make the hierarchy on the fly, which generates a lot of loops overhead, which is silly.

I am really new to this, this is my first attempt at creating a GUI program, I have Google searched for days and haven't find a way to populate a QTreeView or QTreeWidget with data from a flat list, so I wrote my own.

My question is, what is a smarter, more efficient way to achieve the same result (populate a tree-like structure with data from a list of named tuples), and how can I use QTreeView instead of QTreeWidget(if there is any difference)?

Any help is appreciated.


1 Answer 1

  • You have a pile of unstructured, global code; this needs to be organized into functions and maybe classes
  • Close your cursor once you're done with it, ideally via context management
  • Order your query using the database itself, to make grouping less painful; also query in order of hierarchy (artist, album, song)
  • You don't need entries; just add items one at a time
  • 4 should not be hard-coded
  • Your repeated set comprehensions can be greatly simplified (i.e. abolished) via itertools.groupby

This example code localises the MySQL import because I don't have it; I've generated fake data instead.

import sys
from dataclasses import dataclass
from itertools import groupby
from typing import Iterable, Callable

from PyQt6.QtWidgets import QApplication, QTreeWidget, QTreeWidgetItem

class Song:
    artist: str
    album: str
    title: str

    def from_db(cls) -> Iterable['Song']:
        from mysql.connector import connect

        with connect(
        ) as conn, conn.cursor() as cursor:
                'select artist, album, title from songs '
                'order by artist, album'
            for row in cursor.fetchall():
                yield cls(*row)

    def fake_rows(cls) -> Iterable['Song']:
        return (
            cls('Weird Al Yankovic', 'Running with Scissors', 'Pretty Fly for a Rabbi'),
            cls('Weird Al Yankovic', 'Running with Scissors', 'Albuquerque'),
            cls('Weird Al Yankovic', 'Off the Deep End', "I Can't Watch This"),
            cls('Weird Al Yankovic', 'Off the Deep End', 'Smells like Nirvana'),
            cls('The Arrogant Worms', "C'est Cheese", 'Mounted Animal Nature Trail'),

    def by_artist(self): return self.artist
    def by_album(self): return self.album

class GUI:
    HEADERS = ('Name', 'Artist', 'Album', 'Title')

    def __init__(self, songs):
        app = QApplication(sys.argv)

        tree = QTreeWidget()
        # a reference needs to be held or this will be garbage-collected
        self.tree = tree
        tree.resize(1280, 720)
        frame = tree.frameGeometry()
        center = tree.screen().availableGeometry().center()

        for artist, after_artist in groupby(songs, Song.by_artist):
            artist_node = QTreeWidgetItem((artist,))
            for album, after_album in groupby(after_artist, Song.by_album):
                album_node = QTreeWidgetItem((album, artist, album))
                for song in after_album:
                    song_node = QTreeWidgetItem((song.title, artist, album, song.title))


        self.run: Callable[[], int] = app.exec

def main() -> None:
    songs = Song.fake_rows()
    gui = GUI(songs)

if __name__ == '__main__':

test run


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.