QAbstractTableModel (QTableView)中的PyQt - Load SQL (QTableView)，在GUI中使用熊猫DataFrame编辑数据。

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.

我对python非常熟悉，并且使用了WinPython-32bit-2.7.10.3(包括QTDesigner 4.8.7)。我正在尝试为使用sqlite数据库在两个分离项目上使用qtableview编程接口。

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

该算法大致是这样的:连接数据库和转换数据到熊猫。DataFrame—将DataFrame转换为QAbstractTableModel—将QAbstractTableModel应用到tableview。模型-载入对话框。

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 :

我没有得到相同的comportment，这取决于用于创建dataframe的sql:给定一个sql表“parametres”，有三个字段(LIBELLE as varchar, VALEUR as varchar, TEST as boolean)， sql尝试如下:

• 'SELECT LIBELLE AS "Paramètre", VALEUR AS "Valeur" FROM parametres'.encode("utf-8")
• “选择LIBELLE作为参数”，VALEUR从parametres的.encode(“utf-8”)中作为“VALEUR”。
• 'SELECT * FROM parametres'.encode("utf-8")
• 从产品的选择* .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.

对于第一个请求，我可以编辑tableview内的数据。在第二个请求中，我可以选择一个“cell”，编辑它，但是当我提交这个版本(按enter)时，数据会被设置为它的第一个值。

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

在搜索时，我发现setData代码的这一行不会发生事件，不管“任何”值:

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.

您可以通过在主代码中删除第27行开头的#字符来测试sql源的发生。

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

PS:我之后会发布代码，但是我还没有找到方法加入sqlite.db…如果有人能指导我，我很乐意添加;与此同时，我已经在我的google.drive上加入了一个完整的zip文件。

---

EDIT #2 :

编辑# 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

“PANDAS_TO_PYQT的代码。“彼豆”被称为“变形熊猫”。dataframe,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 :

parametrage的代码。py，由QtDesigner创建，包含对话框源:

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

1 个解决方案

#1

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

我终于找到它……但是我仍然不知道为什么仅仅通过更改SQL请求(在read_sql_query过程中)，熊猫的工作方式就不一样了。

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

为了让类工作，我必须更改“PANDAS_TO_PYQT”的代码。py”,取代了

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

by

通过

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

in the setData and data functions.

在setData和data函数中。

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

不知何故，熊猫似乎在这个过程中产生了一份dataframe(对于那些寻找解释的人来说，去看医生)。

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

因此，将dataframe转换为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.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()