tgrandje tgrandje - 1 month ago 26
Python Question

PyQt - QtableView initialized with sql (via pandas.dataframe) - can't update datas, depending of the sql source

I'm quite new to python and using

WinPython-32bit-2.7.10.3
(including
QTDesigner 4.8.7
). I'm trying to program an interface for using a sqlite database on two separates projects, using QtableViews.

The algorithm is roughly so :

- connect to database and convert datas to
pandas.DataFrame


- convert DataFrame to QAbstractTableModel

- apply the QAbstractTableModel to the
tableview.model


- load the dialog

I don't get the same comportment, depending of the sql used to create the dataframe :
given a SQL table "parametres", with 3 fields (LIBELLE as varchar, VALEUR as varchar, TEST as boolean), the sql tried are :


  • 'SELECT LIBELLE AS "Paramètre", VALEUR AS "Valeur" FROM parametres'.encode("utf-8")

  • 'SELECT * FROM parametres'.encode("utf-8")



With first request, I can edit the datas inside the tableview. With second request, I can select a "cell", edit it, but when I commit the edition (pressing enter), the data is set back to it's first value.

While searching, I saw that this line of the setData code wouldn't event work, whatever the "anything" value :

self._data.values[index.row()][index.column()] = "anything"


You can test the incidence of the sql source by deleting the # character at the beginning of line 27, in the main code.

I've truncated the code to the strict minimum (being very close to the original working code of my first project) and I'm seriously confused. If anybody has an idea, that would be great !

Thanks

PS : I post the code afterward, but I haven't find a way to join the
sqlite.db
... If anyone can guide me, I'll be glad to add it ; in the meantime, I've joined a whole zip of the lot on my google.drive




EDIT #2 :

Still can't understand what is wrong there, but I've just found that I can't commit the data to the model, once it has been loaded. I am pretty sure this is the core of my problem and have subsequently updated both question and title.




Main code :

#­*­coding: utf­8 ­*­

from PyQt4 import QtCore, QtGui

import os,sys
from parametrage import Ui_WinParam
from PANDAS_TO_PYQT import PandasModel

import pandas as pd
import sqlite3

class window_parametreur(QtGui.QDialog, Ui_WinParam):
def __init__(self, dataframemodel, parent=None):
QtGui.QDialog.__init__(self, parent)
# Set up the user interface from Designer.
self.ui = Ui_WinParam()
self.ui.setupUi(self)
self.setModal(True)
self.ui.tableView.setModel(dataframemodel)
self.ui.tableView.resizeColumnsToContents()

def OpenParametreur(self, db_path):

#connecting to database and getting datas as pandas.dataframe
con = sqlite3.connect(db_path)
strSQL = u'SELECT LIBELLE AS "Paramètre", VALEUR AS "Valeur" FROM parametres'.encode("utf-8")
#strSQL = u'SELECT * FROM parametres'.encode("utf-8")
data = pd.read_sql_query(strSQL, con)
con.close()

#converting to QtCore.QAbstractTableModel
model = PandasModel(data)

#loading dialog
self.f=window_parametreur(model)
self.f.exec_()

if __name__=="__main__":
a=QtGui.QApplication(sys.argv)
f=QtGui.QMainWindow()
print OpenParametreur(f, ".\SQLiteDataBase.db")


Code of "PANDAS_TO_PYQT.py", beeing called to transform pandas.dataframe to QtCore.QAbstractTableModel

#­*­coding: utf­8 ­*­

from PyQt4 import QtCore, QtGui

class PandasModel(QtCore.QAbstractTableModel):
def __init__(self, data, parent=None):
QtCore.QAbstractTableModel.__init__(self, parent)
self._data = data

def rowCount(self, parent=None):
return len(self._data.values)

def columnCount(self, parent=None):
return self._data.columns.size

def data(self, index, role=QtCore.Qt.DisplayRole):
if index.isValid():
if role == QtCore.Qt.DisplayRole or role == QtCore.Qt.EditRole:
return QtCore.QVariant(unicode(
self._data.values[index.row()][index.column()]))
return QtCore.QVariant()

def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
if role != QtCore.Qt.DisplayRole:
return None
if orientation == QtCore.Qt.Horizontal:
try:
return '%s' % unicode(self._data.columns.tolist()[section], encoding="utf-8")
except (IndexError, ):
return QtCore.QVariant()
elif orientation == QtCore.Qt.Vertical:
try:
return '%s' % self._data.index.tolist()[section]
except (IndexError, ):
return QtCore.QVariant()

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

def setData(self, index, value, role=QtCore.Qt.EditRole):
if index.isValid():

print "data set with keyboard : " + value.toByteArray().data().decode("latin1")
self._data.values[index.row()][index.column()] = "anything"
print "data committed : " +self._data.values[index.row()][index.column()]

self.dataChanged.emit(index, index)
return True
return QtCore.QVariant()


Code of parametrage.py, beeing created by QtDesigner, and containing the dialog source :

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'parametrage.ui'
#
# Created by: PyQt4 UI code generator 4.11.4
#
# WARNING! All changes made in this file will be lost!

from PyQt4 import QtCore, QtGui

try:
_fromUtf8 = QtCore.QString.fromUtf8
except AttributeError:
def _fromUtf8(s):
return s

try:
_encoding = QtGui.QApplication.UnicodeUTF8
def _translate(context, text, disambig):
return QtGui.QApplication.translate(context, text, disambig, _encoding)
except AttributeError:
def _translate(context, text, disambig):
return QtGui.QApplication.translate(context, text, disambig)

class Ui_WinParam(object):
def setupUi(self, WinParam):
WinParam.setObjectName(_fromUtf8("WinParam"))
WinParam.resize(608, 279)
icon = QtGui.QIcon()
icon.addPixmap(QtGui.QPixmap(_fromUtf8("../../pictures/EAUX.png")), QtGui.QIcon.Normal, QtGui.QIcon.Off)
WinParam.setWindowIcon(icon)
self.gridLayout = QtGui.QGridLayout(WinParam)
self.gridLayout.setObjectName(_fromUtf8("gridLayout"))
self.ButtonBox = QtGui.QDialogButtonBox(WinParam)
self.ButtonBox.setOrientation(QtCore.Qt.Horizontal)
self.ButtonBox.setStandardButtons(QtGui.QDialogButtonBox.Cancel|QtGui.QDialogButtonBox.Ok)
self.ButtonBox.setCenterButtons(True)
self.ButtonBox.setObjectName(_fromUtf8("ButtonBox"))
self.gridLayout.addWidget(self.ButtonBox, 1, 0, 1, 1)
self.tableView = QtGui.QTableView(WinParam)
self.tableView.setEditTriggers(QtGui.QAbstractItemView.DoubleClicked)
self.tableView.setSortingEnabled(False)
self.tableView.setObjectName(_fromUtf8("tableView"))
self.gridLayout.addWidget(self.tableView, 0, 0, 1, 1)

self.retranslateUi(WinParam)
QtCore.QObject.connect(self.ButtonBox, QtCore.SIGNAL(_fromUtf8("accepted()")), WinParam.accept)
QtCore.QObject.connect(self.ButtonBox, QtCore.SIGNAL(_fromUtf8("rejected()")), WinParam.reject)
QtCore.QMetaObject.connectSlotsByName(WinParam)

def retranslateUi(self, WinParam):
WinParam.setWindowTitle(_translate("WinParam", "Paramétrage", None))


if __name__ == "__main__":
import sys
app = QtGui.QApplication(sys.argv)
WinParam = QtGui.QDialog()
ui = Ui_WinParam()
ui.setupUi(WinParam)
WinParam.show()
sys.exit(app.exec_())

Answer

I finally figured it... But I still don't know why pandas worked differently just by changing the SQL request (must be something inside the read_sql_query process...)

For the class to work, I had to change the code of "PANDAS_TO_PYQT.py", replacing the

self._data.values[index.row()][index.column()]

by

self._data.iloc[index.row(),index.column()]

in the setData and data functions.

Somehow, pandas seems to have produced a copy of the dataframe during the process (for those looking for explanations, go to the doc).

So the correct class code for transforming the dataframe to QAbstractTableModel would be :

#­*­coding: utf­8 ­*­

from PyQt4 import QtCore, QtGui

class PandasModel(QtCore.QAbstractTableModel):
    def __init__(self, data, parent=None):
        QtCore.QAbstractTableModel.__init__(self, parent)
        self._data = data

    def rowCount(self, parent=None):
        return len(self._data.values)

    def columnCount(self, parent=None):
        return self._data.columns.size

    def data(self, index, role=QtCore.Qt.DisplayRole):
        if index.isValid():
            if role == QtCore.Qt.DisplayRole or role == QtCore.Qt.EditRole:
                return QtCore.QVariant(unicode(
                    self._data.iloc[index.row(),index.column()]))
        return QtCore.QVariant()

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if role != QtCore.Qt.DisplayRole:
            return None
        if orientation == QtCore.Qt.Horizontal:
            try:
                return '%s' % unicode(self._data.columns.tolist()[section], encoding="utf-8")
            except (IndexError, ):
                return QtCore.QVariant()
        elif orientation == QtCore.Qt.Vertical:
            try:
                return '%s' % self._data.index.tolist()[section]
            except (IndexError, ):
                return QtCore.QVariant()

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

    def setData(self, index, value, role=QtCore.Qt.EditRole):
        if index.isValid():
            self._data.iloc[index.row(),index.column()] = value.toByteArray().data().decode("latin1")
            if self.data(index,QtCore.Qt.DisplayRole) == value.toByteArray().data().decode("latin1"):
                self.dataChanged.emit(index, index)
                return True
        return QtCore.QVariant()
Comments