[SOLVED] Overload QtSql.QSqlRelationalTableModel data and setData methods

[lukeQt]

Hi Everyone,

I think I need to reimplement data and setData methods for my QSqlRelationalTableModel. I was able to find the source code for QSqlRelationalTableModel in C++ here https://qt.gitorious.org/qt/qt/source/de07df9001586cc18ae267591359541b7ea494a0:src/sql/models/qsqltablemodel.cpp#L809, but I was hoping to find the same code in PyQt. Does anyone know if that code exists or have an example of overloading the methods?

What I need to do is calculate a value from two columns and then place it in a third column. How would you handle this using QSqlRelationalTableModel?

Thank you,
Luke

[SGaist]

Hi,

You should rather add a proxy model between you model and your view. In that proxy you can do what you need and keep the original Sql part intact. You would only need to overload the columnCount and data methods and you should be good to go

Hope it helps

[lukeQt]

Thank you SGaist. Do you have code to do this?

[lukeQt]

Is this the best way to add a pushbutton inside the model? The goal is to add a pushbutton to open a new dialog. I then need to save the users selection in the new dialog and then add that value to the model. Is there a way to do this all within the delegate? How would I get the push button to always be visible?

@
class NaturalResourceDelegate(QtSql.QSqlRelationalDelegate):
def init(self, database, parent=None):
super(NaturalResourceDelegate, self).init(parent)
self._dbfname = database

def createEditor(self, parent, option, index):
    column = index.column()
    if column in [DESCRIPTION, SPATIAL_WEIGHT]:
        editor = QtGui.QLineEdit(parent)
        #editor.returnPressed.connect(self.commitAndCloseEditor)
        return editor
    if column == IMPORT_SPATIAL:
        #test = QtGui.QFileDialog.getOpenFileName()
        button = QtGui.QPushButton("select Spatial Data", parent)
        button.clicked.connect(self.parent().open_spatial)
        return button
    else: 
        return QtSql.QSqlRelationalDelegate.createEditor(self, parent, option, index)

class NaturalResourceTool(QtGui.QDialog, ui_Grit_tool_3.Ui_natural_resource_Dialog):

def __init__(self, database = None, parent=None):
    super(NaturalResourceTool, self).__init__(parent)
    self.setupUi(self)
    self._dbfname = database
    self.file_browser = importgtlf.ImportGTLF(database = self._dbfname, parent = self)

    create = not QtCore.QFile(self._dbfname)
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(self._dbfname)
    if not db.open():
        QtGui.QMessageBox.warning(None, os.path.basename(self._dbfname), 
                                    QtCore.QString("Database Error: %1").arg(db.lastError().text()))
        sys.exit(100)

    self.natural_resource_tableView.setWordWrap(True)
    self.model = QtSql.QSqlRelationalTableModel(self)
    self.model.setEditStrategy(QtSql.QSqlRelationalTableModel.OnFieldChange) 
    self.model.setTable("natural_resource")
    self.model.setRelation( MAP_RATIO, QtSql.QSqlRelation("nssda_accuracy", "id", "scale")) 

    self.model.setHeaderData(ID, QtCore.Qt.Horizontal, QtCore.QVariant("ID"))
    self.model.setHeaderData(IMPORT_SPATIAL, QtCore.Qt.Horizontal, QtCore.QVariant("Import Spatial"))
    self.model.setHeaderData(SPATIAL_PATH, QtCore.Qt.Horizontal, QtCore.QVariant("Spatial Path"))
    self.model.setHeaderData(VECTOR_TYPE, QtCore.Qt.Horizontal, QtCore.QVariant("Vector Type"))
    self.model.setHeaderData(DESCRIPTION, QtCore.Qt.Horizontal, QtCore.QVariant("Description"))
    self.model.setHeaderData(SPATIAL_WEIGHT, QtCore.Qt.Horizontal, QtCore.QVariant("spatial Weight"))
    self.model.setHeaderData(MAP_RATIO, QtCore.Qt.Horizontal, QtCore.QVariant("Map Ratio Scale"))
    self.model.setHeaderData(NSSDA, QtCore.Qt.Horizontal, QtCore.QVariant("NSSDA Horizontal Positional Accuracy"))
    self.model.setHeaderData(APPLY_BUFFER, QtCore.Qt.Horizontal, QtCore.QVariant("Apply Buffer"))

    self.model.select()
    self.delagate = NaturalResourceDelegate(database = self._dbfname, parent = self)
    self.natural_resource_tableView.setModel(self.model)
    self.natural_resource_tableView.setAlternatingRowColors(True)
    self.natural_resource_tableView.setItemDelegate(self.delagate)
    self.selection_model = self.natural_resource_tableView.selectionModel()
    self.selection_model.currentChanged.connect(self.item)
    self.index = []

    for row in range(self.model.rowCount()):
        self.natural_resource_tableView.openPersistentEditor(self.model.index(row, 6))
        self.natural_resource_tableView.openPersistentEditor(self.model.index(row, 1))

def item(self):
    """
    We needed to capture the index in order to use the QDialog for selecting a file.
        The dialog cannot be opened from the delegate. Therefore we are currentChanged slot
        to capture the current index. However, clicking sometimes returns an empty list.
    As a result, this method is called to capture the last valid index (ie not empty) 
        in this list. Then open_spatial is a slot called from setModelData in the delegate,
        which opens the file dialog, retrieves the path, then passes this path and 
        the selected index back to the data model using setData.
    """
    if len(self.selection_model.selectedIndexes()) > 0:
        self.index.append(self.selection_model.selectedIndexes()[-1])

def open_spatial(self):
    self.item()
    if self.file_browser.exec_():
        self.model.setData(self.index[0], self.file_browser.path, role=QtCore.Qt.EditRole )
        self.index = []

@

[SGaist]

So currently, you get the button when double-clicking on a cell in that column, correct ?

[lukeQt]

It is four clicks in total to open the new dialog. The first click selects the cell. Then you must double click to open the editor and finally a last click to open the dialog.

Also is the way I instantiate the new window in the view correct? I use a signal and slot for this. Is there a way to do this all in the delegate though? I use button.clicked.connect(self.parent().open_spatial).

The new dialog that opens is a custom file browser. So they select the path and then that is written to the database. Is there a different way to do this?

I have a combobox that is populated with names from the selected text file. How do I populate the combobox in the create editor because the combobox has to know the selected text file path?

Thank you for all your help SGaist!! Next time you are in Colorado I owe you a beer.

[SGaist]

So your workflow would be something like: double click on a cell (let's call it C1) to open a QFileDialog then store the return value in that cell. Once that done, double click on the adjacent cell (or another in the same line) which should open a QComboBox with its list populated from the file which path is to be found in C1. Correct ?

If so, in setEditorData, you can access C1, get the path, open and parse the file and fill the QComboBox with the result of the parsing.

Colorado is a bit far away currently ;) But thanks for the invitation :)

[lukeQt]

Is the way I am using a signal and slot correct to open the new dialog when the pushbutton is clicked? Can this all be done in the delegate?

@class NaturalResourceDelegate(QtSql.QSqlRelationalDelegate):
def init(self, parent=None):
super(NaturalResourceDelegate, self).init(parent)

def createEditor(self, parent, option, index):
    column = index.column()
    if column == IMPORT_SPATIAL:
        button = QtGui.QPushButton("select Spatial Data", parent)
        button.clicked.connect(self.parent().open_spatial)
        return button

class NaturalResourceTool(QtGui.QDialog, ui_Grit_tool_3.Ui_natural_resource_Dialog):

def __init__(self, database = None, table = None, parent=None):
    super(NaturalResourceTool, self).__init__(parent)
    self.setupUi(self)
    self._dbfname = database
    self._table = table
    self.file_browser = importgtlf.ImportGTLF(database = self._dbfname, parent = self)
    self.index = []
            
    self.selection_model = self.natural_resource_tableView.selectionModel()
    self.selection_model.currentChanged.connect(self.item)

def item(self):
    """
    We needed to capture the index in order to use the QDialog for selecting a file.
        The dialog cannot be opened from the delegate. Therefore we are currentChanged slot
        to capture the current index. However, clicking sometimes returns an empty list.
    As a result, this method is called to capture the last valid index (ie not empty) 
        in this list. Then open_spatial is a slot called from setModelData in the delegate,
        which opens the file dialog, retrieves the path, then passes this path and 
        the selected index back to the data model using setData.
    """
    if len(self.selection_model.selectedIndexes()) > 0:
        self.index.append(self.selection_model.selectedIndexes()[-1])
    
def open_spatial(self):
    self.item()
    if self.file_browser.exec_():
        self.model.setData(self.index[0], self.file_browser.path, role=QtCore.Qt.EditRole )
        desc = arcpy.Describe(str(self.file_browser.path))
        desc.shapeType
        index = self.model.index(self.index[0].row(), VECTOR_TYPE)
        self.model.setData(index,  desc.shapeType, role=QtCore.Qt.EditRole)
        self.fieldlist = []
        field_list = arcpy.ListFields(str(self.file_browser.path), '', ["Double", "Interger", "String", "Single"] )
        for field in field_list:
            self.fieldlist.append(field.name)
        print self.fieldlist
        self.index = []

@

Thank you

[SGaist]

Not really clean, you should rather implement a custom editor (even if it's a QPushButton subclass) so that everything is handled through the editor, even the model update. In your model add a Q_PROPERTY named e.g. path with USER true so you don't even need to re-implement setModelData

[lukeQt]

Don't I need setModelData to validate that the line edit is actually a float for a different column?

[lukeQt]

What does the custom editor look like?

[SGaist]

No, the editor is responsible for accepting only adequate data e.g. through a QValidator.

A custom editor looks the way you want it to. In your case probably QPushButton

[lukeQt]

I'm lost then. I was trying to follow this example: http://www.gulon.co.uk/2013/01/30/button-delegate-for-qtableviews/.

I have the createEditor method and that is where I setup the pushbutton.

[SGaist]

Ok I see what you mean, that's an alternative technique which doesn't use a real editor as when using a custom delegate implementation like "this C++":http://qtadventures.wordpress.com/2012/02/04/adding-button-to-qviewtable/ implementation.

I was more focused on a 100% custom delegate for a better integration to the model/view paradigm. Also, depending on the size of your table, having so many widgets can have performance implications

[lukeQt]

Thank you SGaist.

[lukeQt]

For anyone that is struggling to subclass SqlTableModel this should help you. This is the subclass for data() and setData(). I wish I had this information.

@
ID, IMPORT_SPATIAL, VECTOR_TYPE, DESCRIPTION, SPATIAL_WEIGHT, MAP_RATIO, NSSDA = range(7)
def data(self, index, role=QtCore.Qt.DisplayRole):
'''Returns the data stored under the given role for the item referred to by the index. '''
row = index.row()
column = index.column()
if not index.isValid():
return QtCore.QVariant()
if role == QtCore.Qt.DisplayRole:
if column == ID:
id = super(model, self).data(index, role)
return QtCore.QVariant(id)
elif column == IMPORT_SPATIAL:
import_spatial = super(model, self).data(index, role)
return QtCore.QVariant(import_spatial)
elif column == VECTOR_TYPE:
vector_type = super(model, self).data(index, role)
return QtCore.QVariant(vector_type)
elif column == DESCRIPTION:
desc = super(model, self).data(index, role)
return QtCore.QVariant(desc)
elif column == SPATIAL_WEIGHT:
spatial_weight = super(model, self).data(index, role)
return QtCore.QVariant(spatial_weight)
elif column == MAP_RATIO:
map_ratio = super(model, self).data(index, role)
return QtCore.QVariant(map_ratio)
elif column == NSSDA:
nssda = super(model, self).data(index, role)
return QtCore.QVariant(nssda)

    elif role == QtCore.Qt.BackgroundColorRole:
        if column == 0:
            return QtCore.QVariant(QtGui.QColor(224, 224, 224))
    return super(model, self).data(index, role)


def setData(self, index, value, role=QtCore.Qt.EditRole):
    '''The base class implementation returns false. This function and data() must be reimplemented for editable models. '''
    column = index.column()
    if index.isValid():
        if column == IMPORT_SPATIAL:
            return super(model, self).setData(index, value, role)
        elif column == VECTOR_TYPE:
            return super(model, self).setData(index, value, role)   
        elif column == DESCRIPTION:
            return super(model, self).setData(index, value, role)
        elif column == SPATIAL_WEIGHT:
            return super(model, self).setData(index, value, role)
        elif column ==  MAP_RATIO:
            return super(model, self).setData(index, value, role)
        elif column == NSSDA:
            return super(model, self).setData(index, value, role)
        else:
            return super(model, self).setData(index, value, role)

@