pythonpyqtpyqt5qsqlquerymodel

edit added data to sublcassed QSqlQueryModel?


i want to display in a QlistView the index and the file name, so i subclassed QSqlQueryModel to override the data() method but i'm always getting None, it seems like i'm displaying the data befor adding it or some thing like that here is the concerned part of my code :

from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *

import sys, os
import pathlib

CURRENT_PATH = pathlib.Path(__file__).parent

connection = QSqlDatabase.addDatabase("QSQLITE")
connection.setDatabaseName("medias.sqlite")
connection.open()
print(connection.open())
createTableQuery = QSqlQuery()
createTableQuery.exec(
    """
    CREATE TABLE fichiers (
        id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
        path VARCHAR(300) NOT NULL
    )
    """
)
print(connection.tables())

class PlaylistModel(QSqlQueryModel):
    def __init__(self, playlist,*args, **kwargs):
        super(PlaylistModel, self).__init__(*args, **kwargs)
        self.playlist = playlist or  [[]]
    def data(self, index, role):
        row = index.row()
        if role == Qt.DisplayRole:
            try:
                text = self.playlist[index.row()][1]
            except IndexError:
                text = None
            return text  # always getting None

class MainWindow(QMainWindow):

    def __init__(self, *args, **kwargs):
        super(MainWindow, self).__init__(*args, **kwargs)
        self.play_list = []
        self.setGeometry(900,180,800,600)
        self.setWindowTitle("Media Display")
        self.model = PlaylistModel(self.play_list)
        self.model.setQuery("SELECT path FROM fichiers")
        
        self.listview = QListView()
        self.listview.setModel(self.model)
        self.listview.setModelColumn(1)
        self.main_layout()
        self.DbConnect()

    def DbConnect(self):
        self.connection = QSqlDatabase.addDatabase("QSQLITE")
        self.connection.setDatabaseName("medias.sqlite")
        self.connection.open()
        createTableQuery = QSqlQuery()
        createTableQuery.exec(
            """ CREATE TABLE fichiers (
            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
            path VARCHAR(300) NOT NULL
            )
            """
            )
        self.model.setQuery("SELECT path FROM fichiers")
        return True

    def addToPlaylist(self):
        self.play_list.clear()
        model = self.listview.model()
        for row in range(model.rowCount()):
            index = model.index(row , 0)
            item = model.data(index, Qt.DisplayRole)
            self.play_list.append(item)
        print('the playlist',self.play_list)
    def addImage(self):
        fichier_base, _ = QFileDialog.getOpenFileName(self, 'select video', QDir.homePath(),"Images (*.png *.xpm *.jpg *.jpeg)")

        if fichier_base:
            query = QSqlQuery()
            query.prepare("""INSERT INTO fichiers (path) VALUES (?)""")
            query.addBindValue(fichier_base)
            if query.exec_():
                last_query = self.model.query().executedQuery()
                self.model.setQuery("")
                self.model.setQuery(last_query)

            else:
                print(query.lastError().text())
      
    def clearDb(self):
        query = QSqlQuery(self.connection)
        if self.connection.open():
            query.exec("DELETE FROM fichiers")
            query.clear()
            last_query = self.model.query().executedQuery()
            self.model.setQuery("")
            self.model.setQuery(last_query)

    def main_layout(self):

        self.add_img_btn = QPushButton("Add image ")
        self.add_img_btn.setFixedWidth(150)
        self.add_img_btn.clicked.connect(self.addImage)

        self.clear_db_btn = QPushButton("clear DB")
        self.clear_db_btn.setFixedWidth(150)
        self.clear_db_btn.clicked.connect(self.clearDb)

        self.refresh_btn = QPushButton("refresh")
        self.refresh_btn.setFixedWidth(150)
        self.refresh_btn.clicked.connect(self.addToPlaylist)

        group_btns = QHBoxLayout()

        main_app = QVBoxLayout()

        main_app.addWidget(self.listview)
        main_app.addLayout(group_btns)

        group_btns.addWidget(self.add_img_btn)
        group_btns.addWidget(self.clear_db_btn)
        group_btns.addWidget(self.refresh_btn)

        vboxlay = QHBoxLayout()
        vboxlay.addLayout(main_app)

        widget = QWidget(self)
        self.setCentralWidget(widget)
        widget.setLayout(vboxlay)

if __name__ == '__main__':
    app= QApplication(sys.argv)
    window = MainWindow()
    window.setStyleSheet('background-color:#fff;')
    window.show()
    sys.exit(app.exec_())

on a previous app i subclassed the QAbstractListModel Class and i did it like that

class PlaylistModel(QAbstractListModel):
    def __init__(self, playlist, *args, **kwargs):
        super(PlaylistModel, self).__init__(*args, **kwargs)
        self.playlist = playlist

    def data(self, index, role):
        if role == Qt.DisplayRole:
            media = self.playlist.media(index.row())
            print('mediaaaaaaa', media )
            print('plaaaaaylist', self.playlist )
            name_video = media.canonicalUrl().fileName()
            i = index.row() + 1
            return f"{i} -          {name_video}"

    def rowCount(self, index):
        return self.playlist.mediaCount()

Solution

  • The following should be taken into account:

    Considering the above, the solution is:

    import sys
    
    from PyQt5.QtCore import QDir
    from PyQt5.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery
    from PyQt5.QtWidgets import (
        QApplication,
        QFileDialog,
        QHBoxLayout,
        QListView,
        QMainWindow,
        QPushButton,
        QStyledItemDelegate,
        QVBoxLayout,
        QWidget,
    )
    
    
    def create_connection():
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("medias.sqlite")
        if not db.open():
            print(db.lastError().text())
            return False
    
        q = QSqlQuery()
        if not q.exec(
            """
        CREATE TABLE IF NOT EXISTS fichiers (
            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
            path VARCHAR(300) NOT NULL
        )
        """
        ):
            print(q.lastError().text())
            return False
        print(db.tables())
        return True
    
    
    class StyledItemDelegate(QStyledItemDelegate):
        def initStyleOption(self, option, index):
            super().initStyleOption(option, index)
            option.text = f"{index.row() + 1} -          {index.data()}"
    
    
    class MainWindow(QMainWindow):
        def __init__(self, *args, **kwargs):
            super(MainWindow, self).__init__(*args, **kwargs)
            self.play_list = []
            self.setGeometry(900, 180, 800, 600)
            self.setWindowTitle("Media Display")
    
            self.model = QSqlTableModel()
            self.model.setTable("fichiers")
            self.model.select()
    
            self.listview = QListView()
            delegate = StyledItemDelegate(self.listview)
            self.listview.setItemDelegate(delegate)
            self.listview.setModel(self.model)
            self.listview.setModelColumn(1)
    
            self.init_ui()
    
        def addImage(self):
            fichier_base, _ = QFileDialog.getOpenFileName(
                self, "select video", QDir.homePath(), "Images (*.png *.xpm *.jpg *.jpeg)"
            )
    
            if fichier_base:
                rec = self.model.record()
                rec.setValue("path", fichier_base)
                self.model.insertRecord(-1, rec)
                self.model.select()
    
        def clearDb(self):
            query = QSqlQuery()
            query.exec("DELETE FROM fichiers")
            self.model.select()
    
        def init_ui(self):
    
            self.add_img_btn = QPushButton("Add image ")
            self.add_img_btn.setFixedWidth(150)
            self.add_img_btn.clicked.connect(self.addImage)
    
            self.clear_db_btn = QPushButton("clear DB")
            self.clear_db_btn.setFixedWidth(150)
            self.clear_db_btn.clicked.connect(self.clearDb)
    
            self.refresh_btn = QPushButton("refresh")
            self.refresh_btn.setFixedWidth(150)
    
            group_btns = QHBoxLayout()
    
            main_app = QVBoxLayout()
    
            main_app.addWidget(self.listview)
            main_app.addLayout(group_btns)
    
            group_btns.addWidget(self.add_img_btn)
            group_btns.addWidget(self.clear_db_btn)
            group_btns.addWidget(self.refresh_btn)
    
            widget = QWidget()
            vboxlay = QHBoxLayout(widget)
            vboxlay.addLayout(main_app)
    
            self.setCentralWidget(widget)
    
    
    if __name__ == "__main__":
        app = QApplication(sys.argv)
    
        if not create_connection():
            sys.exit(-1)
        window = MainWindow()
        window.setStyleSheet("background-color:#fff;")
        window.show()
        sys.exit(app.exec_())