qtableview add rows from a query and edit them in python

Question

i use Python 2.7, MariaDB and Qt4. I have a table in which i want to insert rows from a query. I tried self.model = QtSql.QSqlQueryModel() but i found that this is only read only. Then i moved to something else.

My Model() class looks like this:

class Model(QAbstractTableModel):
    def __init__(self, parent=None, *args):
        QAbstractTableModel.__init__(self, parent, *args)
        query = QtSql.QSqlQuery()
        query.prepare("SELECT denumire,pret_in,pret_out,cantitate,unitate_masura,tax FROM produse WHERE denumire='"+str(self.run_denumire())+"';")
        query.exec_()
        while(query.next()):
            nume_produs = str(query.value(0).toString())
            pret_in = str(query.value(1).toString())
            pret_out = str(query.value(2).toString())
            cantitate = str(query.value(3).toString())
            unitate_masura = str(query.value(4).toString())
            tax_tva = str(query.value(5).toString())
        self.items = [nume_produs,pret_in,pret_out,cantitate,unitate_masura]

    def rowCount(self, parent=QModelIndex()):
        return 1

    def columnCount(self, parent=QModelIndex()):
        return len(self.items)

    def data(self, index, role):
        if not index.isValid(): return QVariant()
        elif role != Qt.DisplayRole:
            return QVariant()

        column=index.column()
        if column<len(self.items):
            return QVariant(self.items[column])
        else:
            return QVariant()

    def flags(self, index):
        return Qt.ItemIsEnabled | Qt.ItemIsSelectable | Qt.ItemIsEditable

I am new to Qt and i don't know how to insert rows one by one to appear on table. What you see here is a code found by me here on stackoverflow and i modified it with a query . I need some help on this .

My main code is like this:

import time,os.path, os,module
from PyQt4 import QtGui, uic,QtSql,QtCore
from PyQt4.QtCore import *
from PyQt4.QtGui import *

class dialog_receptie(QtGui.QDialog):
    def __init__(self):
        QtGui.QDialog.__init__(self)
        file_path = os.path.abspath("ui/receptie.ui")
        uic.loadUi(file_path, self)
        self.move(QtGui.QApplication.desktop().screen().rect().center() - self.rect().center())

My ui file is:file The file was made by Qt Designer. Thank you.

UPDATE 1:

I inserted in class dialog_receptie a function. It looks like this:

def show_lista_receptie(self):
    # self.model = QtSql.QSqlQueryModel()
    # self.model.setQuery("SELECT den_produs,concat(pret_in,' Lei'),concat(pret_out,' Lei'),val_tva,cantitate,um FROM receptie_temp;")
    self.model = QtSql.QSqlTableModel()
    self.model.setTable("produse")
    self.model.setQuery("SELECT * FROM receptie_temp;")
    self.model.setHeaderData(1, QtCore.Qt.Horizontal, self.tr("Produs         "))
    self.model.setHeaderData(4, QtCore.Qt.Horizontal, self.tr("Pret cumparare "))
    self.model.setHeaderData(5, QtCore.Qt.Horizontal, self.tr("Pret vanzare   "))
    self.model.setHeaderData(6, QtCore.Qt.Horizontal, self.tr("TVA Produs     "))
    self.model.setHeaderData(7, QtCore.Qt.Horizontal, self.tr("Cantitate      "))
    self.model.setHeaderData(11, QtCore.Qt.Horizontal, self.tr("UM             "))
    self.produse_view.setModel(self.model)
    self.produse_view.hideColumn(0)  # hide id column
    self.produse_view.hideColumn(2)
    self.produse_view.hideColumn(3)
    self.produse_view.hideColumn(8)
    self.produse_view.hideColumn(9)
    self.produse_view.hideColumn(10)
    self.produse_view.hideColumn(12)

If i use this line self.model.setQuery("SELECT * FROM receptie_temp;") i get an error message:

File "E:\onedrive\Documents\optimpos\module\receptie.py", line 64, in show_lista_receptie
    self.model.setQuery("SELECT * FROM receptie_temp;")
TypeError: setQuery(self, QSqlQuery): argument 1 has unexpected type 'str'

How can query data from receptie_temp table without using an array? And how are the edited values in the table updated to the sql table?

Thank You.

Answer 1

I recommend using the QSqlTableModel class:

The QSqlTableModel class provides an editable data model for a single database table.

In your case:

import os
import sys

from PyQt4 import QtGui, uic, QtSql
from PyQt4.QtGui import *


class dialog_receptie(QtGui.QDialog):
    def __init__(self):
        QtGui.QDialog.__init__(self)
        file_path = os.path.abspath("ui/receptie.ui")
        uic.loadUi(file_path, self)
        # self.move(QtGui.QApplication.desktop().screen().rect().center() - self.rect().center())
        db = QtSql.QSqlDatabase.addDatabase('QMYSQL')
        db.setHostName(HOSTNAME);
        db.setDatabaseName(DATABASE);
        db.setUserName(USER);
        db.setPassword(PASSWORD)
        self.model = QtSql.QSqlTableModel()
        self.model.setTable("produse")

        self.produse_view.setModel(self.model)
        self.produse_view.hideColumn(0) # hide id column
        self.addData(["a", "b", "c", "d", "e", "f"])

    def addData(self, data):

        rec = self.model.record()

        for i in range(6):
            rec.setValue(rec.field(i+1).name(), data[i])
        self.model.insertRecord(-1, rec)


if __name__ == '__main__':
    app = QApplication(sys.argv)
    w = dialog_receptie()
    w.show()
    sys.exit(app.exec_())