Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. Qt for Python
  4. Using QtSql.QSqlQuery() and where to search for a string in a table
Forum Updated to NodeBB v4.3 + New Features

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

Scheduled Pinned Locked Moved Unsolved Qt for Python
29 Posts 4 Posters 2.4k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • JonBJ 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.....

    P Offline
    P Offline
    PythonQTMarlem
    wrote on last edited by
    #13

    @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 :)

    jsulmJ 1 Reply Last reply
    0
    • P 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 :)

      jsulmJ Offline
      jsulmJ Offline
      jsulm
      Lifetime Qt Champion
      wrote on last edited by
      #14

      @PythonQTMarlem

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

      https://forum.qt.io/topic/113070/qt-code-of-conduct

      P 1 Reply Last reply
      0
      • P 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.

        JonBJ Offline
        JonBJ Offline
        JonB
        wrote on last edited by JonB
        #15

        @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++.

        1 Reply Last reply
        0
        • jsulmJ jsulm

          @PythonQTMarlem

          abfrage = "SELECT * FROM telefon_verwaltung WHERE vorname='" + suchstring + "'"
          
          P Offline
          P Offline
          PythonQTMarlem
          wrote on last edited by
          #16

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

          JonBJ 1 Reply Last reply
          0
          • P PythonQTMarlem

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

            JonBJ Offline
            JonBJ Offline
            JonB
            wrote on last edited by
            #17

            @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?

            1 Reply Last reply
            0
            • P Offline
              P Offline
              PythonQTMarlem
              wrote on last edited by
              #18

              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!

              JonBJ 1 Reply Last reply
              0
              • P 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!

                JonBJ Offline
                JonBJ Offline
                JonB
                wrote on last edited by
                #19

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

                1 Reply Last reply
                1
                • P Offline
                  P Offline
                  PythonQTMarlem
                  wrote on last edited by
                  #20

                  @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?

                  JonBJ 1 Reply Last reply
                  0
                  • P 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?

                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote on last edited by JonB
                    #21

                    @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.

                    1 Reply Last reply
                    1
                    • P Offline
                      P Offline
                      PythonQTMarlem
                      wrote on last edited by
                      #22

                      @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())
                      
                      JonBJ 1 Reply Last reply
                      0
                      • P 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())
                        
                        JonBJ Offline
                        JonBJ Offline
                        JonB
                        wrote on last edited by
                        #23

                        @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.

                        1 Reply Last reply
                        1
                        • P Offline
                          P Offline
                          PythonQTMarlem
                          wrote on last edited by PythonQTMarlem
                          #24

                          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.

                          1 Reply Last reply
                          0
                          • P Offline
                            P Offline
                            PythonQTMarlem
                            wrote on last edited by
                            #25
                                    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!

                            JonBJ 1 Reply Last reply
                            0
                            • P 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!

                              JonBJ Offline
                              JonBJ Offline
                              JonB
                              wrote on last edited by JonB
                              #26

                              @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.

                              P 1 Reply Last reply
                              1
                              • P Offline
                                P Offline
                                PythonQTMarlem
                                wrote on last edited by
                                #27

                                yes, please read above.

                                1 Reply Last reply
                                0
                                • JonBJ 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.

                                  P Offline
                                  P Offline
                                  PythonQTMarlem
                                  wrote on last edited by
                                  #28

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

                                  1 Reply Last reply
                                  0
                                  • P Offline
                                    P Offline
                                    PythonQTMarlem
                                    wrote on last edited by
                                    #29

                                    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 + "'")
                                    
                                    1 Reply Last reply
                                    1

                                    • Login

                                    • Login or register to search.
                                    • First post
                                      Last post
                                    0
                                    • Categories
                                    • Recent
                                    • Tags
                                    • Popular
                                    • Users
                                    • Groups
                                    • Search
                                    • Get Qt Extensions
                                    • Unsolved