[PyQt] Wanted: QSqlQueryModel with parameterized query

[Sibylle Koczian]

Hello,

the next problem with QtSql. I want to use a QSqlQueryModel with a 
parameterized query and I can't get that to work.

I tried with this code:


#################################################################################

#!/usr/bin/env python

import sys

from PyQt5.QtCore import Qt, QCoreApplication
from PyQt5.QtGui import QColor
from PyQt5.QtWidgets import QApplication, QTableView
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel

class TestSqlModel(QSqlQueryModel):

     def __init__(self):
         super().__init__()
         self.currentCat = "---"
         self.setQuery("SELECT idn, category FROM things WHERE category 
= :cat")
         self.refresh()
         self.setHeaderData(0, Qt.Horizontal, "IDN")
         self.setHeaderData(1, Qt.Horizontal, "Category")

     def refresh(self):
         self.query().bindValue(":cat", self.currentCat)
         self.query().exec_()

     def setParam(self, _cat):
         self.currentCat = _cat
         self.refresh()


def createConnection():
     db = QSqlDatabase.addDatabase('QSQLITE')
     db.setDatabaseName(':memory:')
     if not db.open():
         print("Cannot open database, SQLite support needed")
         return False
     query = QSqlQuery()
     query.exec_("CREATE TABLE things(idn INTEGER PRIMARY KEY, "
                 "category CHAR(3))")
     query.prepare("INSERT INTO things (category) VALUES (:cat)")
     for _cat in ["ABA", "CFH", "DAD", "ABA", "DAD"]:
         query.bindValue(":cat", _cat)
         query.exec_()
     return True

def showTableData():
     print("All records:")
     query = QSqlQuery()
     query.exec_("SELECT idn, category FROM things")
     while query.next():
         print("{0} / {1}".format(query.value(0), query.value(1)))

def showModelData(model):
     print("Current parameter value: {0}".format(model.currentCat))
     for recno in range(4):
         print("{0} / {1}".format(model.record(recno).value("idn"),
                                  model.record(recno).value("category")))

def main(args):
     app = QCoreApplication(args)
     if not createConnection():
         sys.exit(1)
     print("Created connection.")
     showTableData()
     testModel = TestSqlModel()
     print("Created model.")
     showModelData(testModel)
     testModel.setParam("ABA")
     print("Changed parameter.")
     showModelData(testModel)

if __name__ == '__main__':
     main(sys.argv)

#################################################################################

And got this (using Windows 7 64bit, Python 3.3.3, PyQt5 5.2, running 
the script in IDLE):

Python 3.3.3 (v3.3.3:c3896275c0f6, Nov 18 2013, 21:19:30) [MSC v.1600 64 
bit (AMD64)] on win32
Type "copyright", "credits" or "license()" for more information.
 >>> ================================ RESTART 
================================
 >>>
Created connection.
All records:
1 / ABA
2 / CFH
3 / DAD
4 / ABA
5 / DAD
Created model.
Current parameter value: ---
None / None
None / None
None / None
None / None
Changed parameter.
Current parameter value: ABA
None / None
None / None
None / None
None / None
 >>>

Same result running the script from a command window (but that's less 
easy to copy). Same result, just for completeness, on ArchLinux.

Is this not possible? Do I have to set a new query for every new 
parameter value? The only example I found so far (C++, not Python) does 
that.

Thank you for help,
Sibylle