QSqlTableModel and complex queries

[Scott Frankel-3]

Hello,

What's the proper way to perform sql SELECT statements to populate a  
table model, when the statement is more complex than select(),  
filters, and order clauses can handle?


According to the Qt documentation, QSqlTableModel.setQuery should  
"normally" not be called on a QSqlTableModel.  Are there circumstances  
when setQuery() should be used?  Is a non-persistent table a germane  
example?  Or is there a better way?

Note that the attached code file creates a simple example with two  
tables, populates them with dummy data, then attempts to perform a  
select on them with a call to setQuery() -- which fails.

 From the documentation and examples, using the table model's  
setTable() method is quite clear for populating the model with data  
from a persistent table.  In my example, I want to populate the model  
with data from multiple fields taken from several tables.  Am I'm  
missing something obvious?  This seems beyond the scope of  
QSqlRelationalTableModel.

Thanks in advance!
Scott



[queryTest.py]

---

#!/usr/bin/env python


#-------------------------------------------------------------------------------
# imports
#-------------------------------------------------------------------------------
import sys, os
from PyQt4 import QtCore, QtGui, QtSql

MAC   = "qt_mac_set_native_menubar" in dir()

# column enumeration
COLOR_COLOR, ANIMAL_COLOR, ANIMAL_NAME, ANIMAL_DESCRIPTION = range(4)



#-------------------------------------------------------------------------------
# schema
#-------------------------------------------------------------------------------

def createFakeData():

        print "Dropping tables..."
        query = QtSql.QSqlQuery()
        query.exec_("DROP TABLE color")
        query.exec_("DROP TABLE animal")

        print "Creating tables..."
        query.exec_("""CREATE TABLE color (
                                color_id PRIMARY KEY,
                                name VARCHAR(32) UNIQUE NOT NULL,
                                description TEXT NOT NULL)""")

        query.exec_("""CREATE TABLE animal (
                                animal_id PRIMARY KEY,
                                color_id  INTEGER REFERENCES color(color_id) ON UPDATE CASCADE ON DELETE CASCADE,
                                name VARCHAR(32) UNIQUE NOT NULL,
                                description TEXT NOT NULL)""")

        print "Populating tables..."
        query.exec_("INSERT INTO color (name, description) "
                                "VALUES ('red', 'this is red')")

        query.exec_("INSERT INTO color (name, description) "
                                "VALUES ('green', 'this is green')")

        query.exec_("INSERT INTO color (name, description) "
                                "VALUES ('blue', 'this is blue')")

        query.exec_("INSERT INTO animal (name, description) "
                                "VALUES ('lion', 'this is a lion')")

        query.exec_("INSERT INTO animal (name, description) "
                                "VALUES ('tiger', 'this is a tiger')")

        query.exec_("INSERT INTO animal (name, description) "
                                "VALUES ('bear', 'this is a bear')")




#-------------------------------------------------------------------------------
# class
#-------------------------------------------------------------------------------
class QueryTestForm(QtGui.QWidget):
        def __init__(self, parent=None):
                QtGui.QWidget.__init__(self)


                # table model
                # ------------------------------------------------
                self.theModel = QtSql.QSqlTableModel(self)
# self.theModel = QtSql.QSqlRelationalTableModel(self)
                self.theModel.setTable("non_persistent")
               
                self.theModel.setSort(ANIMAL_NAME, QtCore.Qt.AscendingOrder)
                self.theModel.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)

                # column headers
                self.theModel.setHeaderData(COLOR_COLOR, QtCore.Qt.Horizontal, QtCore.QVariant("COLOR_COLOR"))
                self.theModel.setHeaderData(ANIMAL_COLOR, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_COLOR"))
                self.theModel.setHeaderData(ANIMAL_NAME, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_NAME"))
                self.theModel.setHeaderData(ANIMAL_DESCRIPTION, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_DESCRIPTION"))

                query       = QtSql.QSqlQuery("SELECT c.name, a.name, a.description FROM color as c, animal as a WHERE c.color_id = a.color_id")


# select = self.theModel.select()
                select = self.theModel.setQuery(query)
                print "queryTestForm select: ", select


                # table view
                # ------------------------------------------------
                self.theView = QtGui.QTableView()
                self.theView.setModel(self.theModel)
                self.theView.setItemDelegate(QtSql.QSqlRelationalDelegate(self.theView))
                self.theView.setSelectionMode(QtGui.QTableView.SingleSelection)
                self.theView.setSelectionBehavior(QtGui.QTableView.SelectRows)
                self.theView.resizeColumnsToContents()
                self.theView.horizontalHeader().setStretchLastSection(True)


                # table layout
                # ------------------------------------------------
                self.tableLayout = QtGui.QVBoxLayout()
                self.tableLayout.addWidget(self.theView)
                self.setLayout(self.tableLayout)






#-------------------------------------------------------------------------------
# main
#-------------------------------------------------------------------------------
if __name__ == "__main__":
        app = QtGui.QApplication(sys.argv)

        print "Qt Version: ", QtCore.QT_VERSION_STR
        print "PyQt Version: ", QtCore.PYQT_VERSION_STR

        filename = os.path.join(os.path.dirname(__file__), "test.db")
        create = not QtCore.QFile.exists(filename)
        db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(filename)
       
        if not db.open():
                QtGui.QMessageBox.warning(None, "Test",
                        QtCore.QString("Database Error: %1").arg(db.lastError().text()))
                sys.exit(1)
       
        ok  = db.open()
        print "db connection: ", ok

        createFakeData()
        form = QueryTestForm()
        form.setWindowTitle("Query Test Form")
        form.show()
        sys.exit(app.exec_())
               

---

_______________________________________________
PyQt mailing list    PyQt@...
http://www.riverbankcomputing.com/mailman/listinfo/pyqt

[Aaron Digulla]

Scott Frankel schrieb:
>
> This seems beyond the scope of
> QSqlRelationalTableModel.

During my work on UPCScan, I've found that the QSqlRelationalTableModel
and the related table view are too buggy and too limited to be useful.
In the end, I've ripped all the code out again and wrote my own
implementation. Problems that I've encountered:

- When you change anything in the model, the view resets itself. You
lose the current selection and the current scroll position.

- You can't tell the model that you've just added a row to the table.

- I have a column with binary data. There is no way to inject a mapper
to turn this into something useful for the UI.

- I couldn't update the binary data with in setData().

Regards,

--
Aaron "Optimizer" Digulla a.k.a. Philmann Dark
"It's not the universe that's limited, it's our imagination.
Follow me and I'll show you something beyond the limits."
http://darkviews.blogspot.com/          http://www.pdark.de/
_______________________________________________
PyQt mailing list    PyQt@...
http://www.riverbankcomputing.com/mailman/listinfo/pyqt

[Mark Summerfield-2]

On 2008-10-08, Aaron Digulla wrote:

> Scott Frankel schrieb:
> > This seems beyond the scope of
> > QSqlRelationalTableModel.
>
> During my work on UPCScan, I've found that the QSqlRelationalTableModel
> and the related table view are too buggy and too limited to be useful.
> In the end, I've ripped all the code out again and wrote my own
> implementation. Problems that I've encountered:
>
> - When you change anything in the model, the view resets itself. You
> lose the current selection and the current scroll position.

That is intended behaviour. Reset is the model's way of telling
its associated views that some major data change has occurred and that
all data shown must be refetched. So if you want to keep selection and
position info you must handle that yourself.

If you've just changed a one or a few items then emit the dataChanged()
signal with the relevant QModelIndexes; that shouldn't affect seletions
etc. and the views will just refetch what has changed.

> - You can't tell the model that you've just added a row to the table.

That's handled in your QAbstractTableModel subclass's insertRows()
reimplementation, where you call beginInsertRows(), then insert your row
or rows, then call endInsertRows().

> - I have a column with binary data. There is no way to inject a mapper
> to turn this into something useful for the UI.
>
> - I couldn't update the binary data with in setData().

You'd need to implement a custom delegate. A custom delegate you
control over how data is displayed or over how it is edited or both.

--
Mark Summerfield, Qtrac Ltd, www.qtrac.eu
    C++, Python, Qt, PyQt - training and consultancy
        "Rapid GUI Programming with Python and Qt" - ISBN 0132354187

_______________________________________________
PyQt mailing list    PyQt@...
http://www.riverbankcomputing.com/mailman/listinfo/pyqt

[Aaron Digulla]

Quoting Mark Summerfield <mark@...>:

> On 2008-10-08, Aaron Digulla wrote:
>> Scott Frankel schrieb:
>> > This seems beyond the scope of
>> > QSqlRelationalTableModel.
>>
>> During my work on UPCScan, I've found that the QSqlRelationalTableModel
>> and the related table view are too buggy and too limited to be useful.
>> In the end, I've ripped all the code out again and wrote my own
>> implementation. Problems that I've encountered:
>>
>> - When you change anything in the model, the view resets itself. You
>> lose the current selection and the current scroll position.
>
> That is intended behaviour. Reset is the model's way of telling
> its associated views that some major data change has occurred and that
> all data shown must be refetched. So if you want to keep selection and
> position info you must handle that yourself.

When I said "change anything in the model", I was referring to  
setData(), insertRows() and removeRows(). So as it is, the  
QSqlQueryModel is only useful for display of static tables.

It's an open bug in Qt 4.4. I found something about this in the Qt  
support forum.

> If you've just changed a one or a few items then emit the dataChanged()
> signal with the relevant QModelIndexes; that shouldn't affect seletions
> etc. and the views will just refetch what has changed.

Please try it. It won't work as expected.

>> - You can't tell the model that you've just added a row to the table.
>
> That's handled in your QAbstractTableModel subclass's insertRows()
> reimplementation, where you call beginInsertRows(), then insert your row
> or rows, then call endInsertRows().

That's not correct. QSqlQueryModel has its own idea what is in the  
table and there is no way to tell it "there is a new row in the table"  
without calling select() (which in turn resets the view).

>> - I have a column with binary data. There is no way to inject a mapper
>> to turn this into something useful for the UI.
>>
>> - I couldn't update the binary data with in setData().
>
> You'd need to implement a custom delegate. A custom delegate you
> control over how data is displayed or over how it is edited or both.

Maybe but because I couldn't get the UI workflow right, I gave up when  
I ran into these issues and I didn't have an obvious solution. I saw  
it in your book but my solution is much more simple and obvious.

Regards,

--
Aaron "Optimizer" Digulla a.k.a. Philmann Dark
"It's not the universe that's limited, it's our imagination.
Follow me and I'll show you something beyond the limits."
http://www.pdark.de/

_______________________________________________
PyQt mailing list    PyQt@...
http://www.riverbankcomputing.com/mailman/listinfo/pyqt