PyQt - Load SQL in QAbstractTableModel (QTableView) using pandas DataFrame - editing datas in a GUI
<p>I'm quite new to python and using <code>WinPython-32bit-2.7.10.3</code> (including <code>QTDesigner 4.8.7</code>). I'm trying to program an interface for using a sqlite database on two separates projects, using QtableViews.</p>

<p>The algorithm is roughly so :<br>
- connect to database and convert datas to <code>pandas.DataFrame</code><br>
- convert DataFrame to QAbstractTableModel<br>
- apply the QAbstractTableModel to the <code>tableview.model</code><br>
- load the dialog</p>

<p>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 :</p>

<ul>
<li>'SELECT LIBELLE AS "ParamÃ¨tre", VALEUR AS "Valeur" FROM parametres'.encode("utf-8")</li>
<li>'SELECT * FROM parametres'.encode("utf-8")</li>
</ul>

<p>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. </p>

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

<pre><code>self._data.values[index.row()][index.column()] = "anything"
</code></pre>

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

<p>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 ! </p>

<p>Thanks</p>

<p>PS : I post the code afterward, but I haven't find a way to join the <code>sqlite.db</code>... 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 <a href="https://drive.google.com/file/d/0ByjzDYUpdDswRjhoY1d6WEtmQ3M/view?usp=sharing" rel="nofollow">google.drive</a></p>

<hr>

<p><strong>EDIT #2 :</strong> </p>

<p>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.</p>

<hr>

<p>Main code :</p>

<pre><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></pre>

<p>Code of "PANDAS_TO_PYQT.py", beeing called to transform pandas.dataframe to QtCore.QAbstractTableModel</p>

<pre><code>#Â­*Â­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></pre>

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

<pre><code># -*- 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_())
</code></pre>

<p>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...)</p>

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

<pre><code>self._data.values[index.row()][index.column()]
</code></pre>

<p>by </p>

<pre><code>self._data.iloc[index.row(),index.column()]
</code></pre>

<p>in the setData and data functions.</p>

<p>Somehow, pandas seems to have produced a copy of the dataframe during the process (for those looking for explanations, go to <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy" rel="nofollow">the doc</a>).</p>

<p>So the correct class code for transforming the dataframe to QAbstractTableModel would be :</p>

<pre><code>#Â­*Â­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()
</code></pre>

python python-2.7 pandas pyqt qtableview