Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. The bindValue method of QSqlQuery in PySide6 does not take effect in MySQL
Forum Updated to NodeBB v4.3 + New Features

The bindValue method of QSqlQuery in PySide6 does not take effect in MySQL

Scheduled Pinned Locked Moved Solved General and Desktop
6 Posts 4 Posters 480 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.
  • J Offline
    J Offline
    jerry0305
    wrote on last edited by jerry0305
    #1

    The bindValue method of QSqlQuery in PySide6 works in sqlite but not in MySQL.
    need help, thanks a lot

    Running effect in sqlite
    5fc96f87-31de-4b01-a4e6-cc83cabb8e09-image.png
    Running effect in MySQL
    c1be9298-d657-4df1-bd99-33203c195597-image.png
    code

    import sys
    
    from PySide6.QtCore import QSize, Qt
    from PySide6.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel
    from PySide6.QtWidgets import (
        QApplication,
        QHBoxLayout,
        QLineEdit,
        QMainWindow,
        QTableView,
        QVBoxLayout,
        QWidget,
    )
    
    
    # db = QSqlDatabase("QSQLITE")
    # db.setDatabaseName("chinook.sqlite")
    # db.open()
    # db.setDatabaseName(":memory:")
    
    db = QSqlDatabase.addDatabase('QMYSQL')
    db.setPort(3306)
    db.setHostName('localhost')
    db.setDatabaseName('chinook')
    db.setUserName('root')
    db.setPassword('******')
    db.open()
    
    
    class MainWindow(QMainWindow):
        def __init__(self):
            super().__init__()
    
            container = QWidget()
            layout_search = QHBoxLayout()
    
            self.track = QLineEdit()
            self.track.setPlaceholderText("Track name...")
            self.track.textChanged.connect(self.update_query)
    
            self.composer = QLineEdit()
            self.composer.setPlaceholderText("Artist name...")
            self.composer.textChanged.connect(self.update_query)
    
            self.album = QLineEdit()
            self.album.setPlaceholderText("Album name...")
            self.album.textChanged.connect(self.update_query)
    
            layout_search.addWidget(self.track)
            layout_search.addWidget(self.composer)
            layout_search.addWidget(self.album)
    
            layout_view = QVBoxLayout()
            layout_view.addLayout(layout_search)
    
            self.table = QTableView()
    
            layout_view.addWidget(self.table)
    
            container.setLayout(layout_view)
    
            self.model = QSqlQueryModel()
            self.table.setModel(self.model)
    
            self.query = QSqlQuery(db=db)
            # sqllite
            # self.query.prepare(
            #     "SELECT Name, Composer, Album.Title FROM Track "
            #     "INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE "
            #     "Track.Name LIKE '%' || :track_name || '%' AND "
            #     "Track.Composer LIKE '%' || :track_composer || '%' AND "
            #     "Album.Title LIKE '%' || :album_title || '%'"
            # )
            # MySQL
            self.query.prepare(
                "SELECT Name, Composer, Album.Title FROM Track "
                "INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE "
                "Track.Name LIKE '%' :track_name '%' AND "
                "Track.Composer LIKE '%' || :track_composer || '%' AND "
                "Album.Title LIKE '%' :album_title '%'"
            )
            
    
            self.update_query()
    
            self.setMinimumSize(QSize(1024, 600))
            self.setCentralWidget(container)
    
        def update_query(self, s=None):
    
            # Get the text values from the widgets.
            track_name = self.track.text()
            track_composer = self.composer.text()
            album_title = self.album.text()
    
            self.query.bindValue(":track_name", track_name)
            self.query.bindValue(":track_composer", track_composer)
            self.query.bindValue(":album_title", album_title)
    
            self.query.exec()
            self.model.setQuery(self.query)
    
    
    app = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec())
    
    
    JonBJ 1 Reply Last reply
    0
    • Christian EhrlicherC Online
      Christian EhrlicherC Online
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on last edited by
      #2

      Please format your code properly with the code tags so it's readable for others.
      Why do you open a sqlite db first and then a mysql connection? Please check the return value of QSqlDatabase::open() and use the appropriate error functions.

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      1 Reply Last reply
      0
      • M Offline
        M Offline
        mchinand
        wrote on last edited by
        #3

        It might be due to a case-sensitivity issue with your column names; try adding escaped double quotes around your column names. I'm more familiar with Postgres and am not sure if MySQL behaves similarly. I define my tables to have column names that are all lower case to avoid having to put quotes around the column names in queries. Also, see if your query string works when used in another MySQL client.

        1 Reply Last reply
        0
        • J jerry0305

          The bindValue method of QSqlQuery in PySide6 works in sqlite but not in MySQL.
          need help, thanks a lot

          Running effect in sqlite
          5fc96f87-31de-4b01-a4e6-cc83cabb8e09-image.png
          Running effect in MySQL
          c1be9298-d657-4df1-bd99-33203c195597-image.png
          code

          import sys
          
          from PySide6.QtCore import QSize, Qt
          from PySide6.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel
          from PySide6.QtWidgets import (
              QApplication,
              QHBoxLayout,
              QLineEdit,
              QMainWindow,
              QTableView,
              QVBoxLayout,
              QWidget,
          )
          
          
          # db = QSqlDatabase("QSQLITE")
          # db.setDatabaseName("chinook.sqlite")
          # db.open()
          # db.setDatabaseName(":memory:")
          
          db = QSqlDatabase.addDatabase('QMYSQL')
          db.setPort(3306)
          db.setHostName('localhost')
          db.setDatabaseName('chinook')
          db.setUserName('root')
          db.setPassword('******')
          db.open()
          
          
          class MainWindow(QMainWindow):
              def __init__(self):
                  super().__init__()
          
                  container = QWidget()
                  layout_search = QHBoxLayout()
          
                  self.track = QLineEdit()
                  self.track.setPlaceholderText("Track name...")
                  self.track.textChanged.connect(self.update_query)
          
                  self.composer = QLineEdit()
                  self.composer.setPlaceholderText("Artist name...")
                  self.composer.textChanged.connect(self.update_query)
          
                  self.album = QLineEdit()
                  self.album.setPlaceholderText("Album name...")
                  self.album.textChanged.connect(self.update_query)
          
                  layout_search.addWidget(self.track)
                  layout_search.addWidget(self.composer)
                  layout_search.addWidget(self.album)
          
                  layout_view = QVBoxLayout()
                  layout_view.addLayout(layout_search)
          
                  self.table = QTableView()
          
                  layout_view.addWidget(self.table)
          
                  container.setLayout(layout_view)
          
                  self.model = QSqlQueryModel()
                  self.table.setModel(self.model)
          
                  self.query = QSqlQuery(db=db)
                  # sqllite
                  # self.query.prepare(
                  #     "SELECT Name, Composer, Album.Title FROM Track "
                  #     "INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE "
                  #     "Track.Name LIKE '%' || :track_name || '%' AND "
                  #     "Track.Composer LIKE '%' || :track_composer || '%' AND "
                  #     "Album.Title LIKE '%' || :album_title || '%'"
                  # )
                  # MySQL
                  self.query.prepare(
                      "SELECT Name, Composer, Album.Title FROM Track "
                      "INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE "
                      "Track.Name LIKE '%' :track_name '%' AND "
                      "Track.Composer LIKE '%' || :track_composer || '%' AND "
                      "Album.Title LIKE '%' :album_title '%'"
                  )
                  
          
                  self.update_query()
          
                  self.setMinimumSize(QSize(1024, 600))
                  self.setCentralWidget(container)
          
              def update_query(self, s=None):
          
                  # Get the text values from the widgets.
                  track_name = self.track.text()
                  track_composer = self.composer.text()
                  album_title = self.album.text()
          
                  self.query.bindValue(":track_name", track_name)
                  self.query.bindValue(":track_composer", track_composer)
                  self.query.bindValue(":album_title", album_title)
          
                  self.query.exec()
                  self.model.setQuery(self.query)
          
          
          app = QApplication(sys.argv)
          window = MainWindow()
          window.show()
          sys.exit(app.exec())
          
          
          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #4

          @jerry0305
          What are these constructs:

                  "Track.Name LIKE '%' || :track_name || '%' AND "
                  "Track.Composer LIKE '%' || :track_composer || '%' AND "
                  "Album.Title LIKE '%' || :album_title || '%'"
          

          ? How does that || work (in MySQL)?

          P.S.
          An interesting selection of tracks in your screenshots! ;-)
          Are you an AC/DC fan?
          And have you ever heard that I Put A Spell On You track version by Audience?
          Or are these random selections?!

          J 1 Reply Last reply
          0
          • JonBJ JonB

            @jerry0305
            What are these constructs:

                    "Track.Name LIKE '%' || :track_name || '%' AND "
                    "Track.Composer LIKE '%' || :track_composer || '%' AND "
                    "Album.Title LIKE '%' || :album_title || '%'"
            

            ? How does that || work (in MySQL)?

            P.S.
            An interesting selection of tracks in your screenshots! ;-)
            Are you an AC/DC fan?
            And have you ever heard that I Put A Spell On You track version by Audience?
            Or are these random selections?!

            J Offline
            J Offline
            jerry0305
            wrote on last edited by
            #5

            @JonB
            The problem is indeed caused by the "||" symbol, remove the "||" symbol and the code works fine. Thanks a lot!

            1 Reply Last reply
            0
            • Christian EhrlicherC Online
              Christian EhrlicherC Online
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on last edited by
              #6

              And that's the reason why one should always check the return values of e.g. QSqlQuery::prepare() and others...

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              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