Using QtSql.QSqlQuery() and where to search for a string in a table

PythonQTMarlem

@jsulm Thanks your solution works. But my query still doesn't deliver the desired result.

JonB

@PythonQTMarlem
Do you want to show us your relevant code now? With the correct SELECT, then what do you do after the query.exec() to check its result and read any rows returned?

PythonQTMarlem

I found out. Here my solution:

import os
import sys
from PyQt6 import QtGui, QtSql
from PyQt6.QtSql import QSqlDatabase, QSqlQueryModel
from PyQt6.QtWidgets import QWidget, QApplication, QFormLayout, QTableView, QPushButton, QMessageBox, QLineEdit
from PyQt6.QtCore import Qt, qDebug

class FensterKlasse(QWidget):
    def __init__(self):
        super().__init__()
        filename = os.path.join(os.path.dirname(__file__), "telefonnummern_verwaltung.db")

        db = QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName(filename)
        if db.open():
            self.table_model = QSqlQueryModel()

            # Datenbank-Anzeigen
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung`")
            self.tabellengrid = QTableView()
            self.tabellengrid.setModel(self.table_model)

            # Spaltenüberschriften anpassen
            self.table_model.setHeaderData(1, Qt.Orientation.Horizontal, "Vorname")
            self.table_model.setHeaderData(2, Qt.Orientation.Horizontal, "Nachname")
            self.table_model.setHeaderData(3, Qt.Orientation.Horizontal, "Telefonnummer")

            # Spaltenbreiten anpassen
            self.tabellengrid.setColumnWidth(0, 2)
            self.tabellengrid.setColumnHidden(0, True)
            self.tabellengrid.setColumnWidth(1, 100)
            self.tabellengrid.setColumnWidth(2, 150)
            self.tabellengrid.setColumnWidth(3, 130)

            self.GUI()
            return

    def GUI(self):
        self.setWindowTitle("PyQt6 Telefonnummer-Verwaltung mit Datenbank")
        self.setGeometry(0, 0, 500, 500)
        qtRectangle = self.frameGeometry()
        centerPoint = QtGui.QGuiApplication.primaryScreen().availableGeometry().center()
        qtRectangle.moveCenter(centerPoint)
        self.move(qtRectangle.topLeft())

        self.suchefeld = QLineEdit(self)
        self.suche_starten_button = QPushButton("&Suche starten", self)
        self.suche_starten_button.clicked.connect(self.suche_starten)
        formLayout = QFormLayout()
        formLayout.addRow(self.tabellengrid)
        formLayout.addRow(self.suchefeld, self.suche_starten_button)
        self.setLayout(formLayout)

    def suche_starten(self):
        suchwert = self.suchefeld.text()
        if suchwert == '':
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung`")
        else:
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung` where nachname='" + suchwert + "'")


def programm_beeden(self):
    QApplication.instance().quit()

app = QApplication([])
fenster = FensterKlasse()

if __name__ == '__main__':
    fenster.show()
    sys.exit(app.exec())

The problem is that the result set is not editable. I still have to find out how to search and edit at the same time. But my question in this thread is solved!

JonB

@PythonQTMarlem
To be able to make updates to a table you will want to change from QSqlQueryModel to QSqlTableModel.

PythonQTMarlem

@JonB said in Using QtSql.QSqlQuery() and where to search for a string in a table:

QSqlTableModel

Thank you. But its not so easy.
on this code:

self.table_model.setQuery("SELECT * FROM telefon_verwaltung")

I got the errormessage:
TypeError: setQuery(self, QSqlQuery): argument 1 has unexpected type 'str'

Now I try a typecast:

self.table_model.setQuery(QSqlQuery("SELECT * FROM telefon_verwaltung"))

The application crashed with:
Process finished with exit code -1073740791 (0xC0000409)

Can you please tell me what I have to do?

JonB

@PythonQTMarlem

self.table_model.setTable("telefon_verwaltung")

If you are going to use QSqlTableModel read the docs at https://doc.qt.io/qtforpython/PySide6/QtSql/QSqlTableModel.html#detailed-description.

TypeError: setQuery(self, QSqlQuery): argument 1 has unexpected type 'str'

I do not know why you got this.

PythonQTMarlem

@JonB said in Using QtSql.QSqlQuery() and where to search for a string in a table:

self.table_model.setTable("telefon_verwaltung")

Thank you, but the errormessage stays.
self.table_model.setQuery("SELECT * FROM telefon_verwaltung")
TypeError: setQuery(self, QSqlQuery): argument 1 has unexpected type 'str'

Here my whole code:

import os
import sys
from PyQt6 import QtGui, QtSql
from PyQt6.QtSql import QSqlDatabase, QSqlQueryModel, QSqlTableModel, QSqlQuery
from PyQt6.QtWidgets import QWidget, QApplication, QFormLayout, QTableView, QPushButton, QMessageBox, QLineEdit
from PyQt6.QtCore import Qt, qDebug


class FensterKlasse(QWidget):
    def __init__(self):
        super().__init__()
        filename = os.path.join(os.path.dirname(__file__), "telefonnummern_verwaltung.db")

        db = QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName(filename)
        if db.open():
            self.table_model = QSqlTableModel()
            self.table_model.setTable("telefon_verwaltung")

            # Datenbank-Anzeigen
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung`")
            self.tabellengrid = QTableView()
            self.tabellengrid.setModel(self.table_model)

            # Spaltenüberschriften anpassen
            self.table_model.setHeaderData(1, Qt.Orientation.Horizontal, "Vorname")
            self.table_model.setHeaderData(2, Qt.Orientation.Horizontal, "Nachname")
            self.table_model.setHeaderData(3, Qt.Orientation.Horizontal, "Telefonnummer")

            # Spaltenbreiten anpassen
            self.tabellengrid.setColumnWidth(0, 2)
            self.tabellengrid.setColumnHidden(0, True)
            self.tabellengrid.setColumnWidth(1, 100)
            self.tabellengrid.setColumnWidth(2, 150)
            self.tabellengrid.setColumnWidth(3, 130)

            self.GUI()
            return

    def GUI(self):
        self.setWindowTitle("PyQt6 Telefonnummer-Verwaltung mit Datenbank")
        self.setGeometry(0, 0, 500, 500)
        qtRectangle = self.frameGeometry()
        centerPoint = QtGui.QGuiApplication.primaryScreen().availableGeometry().center()
        qtRectangle.moveCenter(centerPoint)
        self.move(qtRectangle.topLeft())

        self.suchefeld = QLineEdit(self)
        self.suche_starten_button = QPushButton("&Suche starten", self)
        self.suche_starten_button.clicked.connect(self.suche_starten)
        formLayout = QFormLayout()
        formLayout.addRow(self.tabellengrid)
        formLayout.addRow(self.suchefeld, self.suche_starten_button)
        self.setLayout(formLayout)

    def suche_starten(self):
        suchwert = self.suchefeld.text()
        if suchwert == '':
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung`")
        else:
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung` where nachname='" + suchwert + "'")


def programm_beeden(self):
    QApplication.instance().quit()


app = QApplication([])
fenster = FensterKlasse()

if __name__ == '__main__':
    fenster.show()
    sys.exit(app.exec())

JonB

@PythonQTMarlem said in Using QtSql.QSqlQuery() and where to search for a string in a table:

Thank you, but the errormessage stays.

And that's because you retained the old statement in other places, so of course you will get the same error message. Look at the line number(s) the error is reporting.

PythonQTMarlem

Hello,
when I delete this line:

self.table_model.setQuery("SELECT * FROM `telefon_verwaltung`")

The Application starts, but shows no table.

When this executed:

 if suchwert == '':
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung`")
        else:
            self.table_model.setQuery("SELECT * FROM `telefon_verwaltung` where nachname='" + suchwert + "'")

the Application crashed with this errormessage:
Process finished with exit code -1073740791 (0xC0000409)

There is some other things wrong.

PythonQTMarlem

        if db.open():
            self.table_model = QSqlTableModel()
            self.table_model.setTable("telefon_verwaltung")
            self.table_model.select()

Now I see tabledatas.
But as soon as I run a SQL query, the application crashes!

JonB

@PythonQTMarlem
After self.table_model.setTable("telefon_verwaltung") you will need self.table_model.select(). It's all in the docs on the page I suggested you read. [UPDATE I see now you have done this.]

But as soon as I run a SQL query, the application crashes!

I don't know why it is crashing or why you are running a SQL query. And you can't run an arbitrary query against a QSqlTableModel. You asked for an editable table, and that is what it gives you.

PythonQTMarlem

yes, please read above.

PythonQTMarlem

@JonB okay, you are right. Thank you for your help.

PythonQTMarlem

I have the solution:

    def suche_starten(self):
        suchwert = self.suchefeld.text()
        if suchwert == '':
            self.table_model.setFilter("")
        else:
            self.table_model.setFilter("nachname like '" + suchwert + "'")