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

PythonQTMarlem

Hello,
Here my code:

def sql_statement(self):
        query = QtSql.QSqlQuery()
        suchstring = "Brunhilde"
        abfrage = "SELECT * FROM telefon_verwaltung WHERE vorname=" + suchstring
        query.exec(abfrage)
        print("SQL Statement abgesetzt!")

There is no error message, but the select is not executed either.

Question: how does it work properly?

SGaist

Hi,

There's no error message because you are not doing any error checks nor printing any results.

JonB

@PythonQTMarlem
If you qDebug() << abfrage and look at it (and think about it) you might even see what is wrong in the query above.

PythonQTMarlem

Thank you for your answer.
I try this:

    def sql_statement(self):
        query = QtSql.QSqlQuery()
        suchstring = "Brunhilde"
        abfrage = "SELECT * FROM telefon_verwaltung WHERE vorname=" + suchstring
        try:
          query.exec(abfrage)
        except Exception as err:
            qDebug(str(err))
        print("SQL Statement abgesetzt!")

But there is no Error Messaage.

Here my whole code:

import sys
from PyQt6 import QtGui, QtSql
from PyQt6.QtSql import QSqlDatabase, QSqlRelation, QSqlTableModel, QSqlRelationalTableModel
from PyQt6.QtWidgets import QWidget, QApplication, QFormLayout, QTableView, QPushButton
from PyQt6.QtCore import Qt, qDebug


class FensterKlasse(QWidget):
    def __init__(self):
        super().__init__()
        self.table_model = QSqlRelationalTableModel()
        self.table_model.setTable("telefon_verwaltung")

        # 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")

        # Datenbank-Anzeigen
        self.table_model.select()
        self.tabellengrid = QTableView()
        self.tabellengrid.setModel(self.table_model)

        # 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()

    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())

        formLayout = QFormLayout()
        btnSuchen = QPushButton("&Suchen", self)
        btnSuchen.clicked.connect(self.sql_statement)

        formLayout.addRow(self.tabellengrid)
        formLayout.addRow(btnSuchen)
        self.setLayout(formLayout)

    def sql_statement(self):
        query = QtSql.QSqlQuery()
        suchstring = "Brunhilde"
        abfrage = "SELECT * FROM telefon_verwaltung WHERE vorname=" + suchstring
        try:
          query.exec(abfrage)
        except Exception as err:
            qDebug(str(err))
        print("SQL Statement abgesetzt!")


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


app = QApplication([])
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("telefonnummern_verwaltung.db")

fenster = FensterKlasse()

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

My suspicion is that the query has no connection to the database at all. Can this be?

jsulm

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

But there is no Error Messaage.

Qt does not use exceptions.
If you would have spent a minute to read documentation you would have found this: https://doc.qt.io/qt-6/qsqlquery.html#lastError
Did you aopen the default connection before executing the query?

JonB

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

My suspicion is that the query has no connection to the database at all. Can this be?

That is as may be (see @jsulm's reply), but as I said if you look at your generated SELECT statement it is syntactically incorrect so it is not going to work anyway....

PythonQTMarlem

@jsulm
I've spent a lot of time reading Qt documentation, but you have to find the right places. Also, I was just trying to implement what SGaist advised me to do. Since data is displayed in the QTableView at the time the query is executed, I assumed that the connection to the database was established.

PythonQTMarlem

@JonB Yes, but that was exactly the reason why I asked this question here. I couldn't find an example via google using select where and querying for a string.

PythonQTMarlem

I try this in SQL_LightStudio:

SELECT * FROM telefon_verwaltung WHERE vorname='Brunhilde'

it Works. But I don't know how to write this in Python, 'cause I found no example.

JonB

@PythonQTMarlem
You don't need an example! Why do you need an example for everything? You just need to change your line to produce that.....

SGaist

So:

SELECT * FROM telefon_verwaltung WHERE vorname='Brunhilde'

This is the query you want.

Now:

suchstring = "Brunhilde"
abfrage = "SELECT * FROM telefon_verwaltung WHERE vorname=" + suchstring

This produces:

SELECT * FROM telefon_verwaltung WHERE vorname=Brunhilde

Do you spot the issue ?

PythonQTMarlem

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

suchstring

Yes, I'm missing the quotation marks before and after suchstring. But I don't know how to do that and I couldn't find anything on google that worked for me.

PythonQTMarlem

@JonB If you don't know something, the best way to learn is through examples. I know Java, C# and Python, but I just don't always understand what the documentation says. If I see an example, then I understand :)

jsulm

@PythonQTMarlem

abfrage = "SELECT * FROM telefon_verwaltung WHERE vorname='" + suchstring + "'"

JonB

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

Yes, I'm missing the quotation marks before and after suchstring.

I know Java, C# and Python

Sorry, but then you do know how to put quotation marks into a Python literal string. It's also the same in C#, Java & C++.

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?