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
#!/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@riverbankcomputing.com
http://www.riverbankcomputing.com/mailman/listinfo/pyqt
