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()
The following should be taken into account:
If you are going to show information from a table then you must use a QSqlTableModel. QSqlQueryModel is a read-only model whose objective is to show very particular query information. On the other hand, QSqlTableModel has several methods to handle the tables.
If you are going to modify how the information of the model is shown in a view then you must use a delegate. This makes the modifications more flexible since you can have different models applying the same modifications to the views.
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_())