The bindValue method of QSqlQuery in PySide6 does not take effect in MySQL
-
The bindValue method of QSqlQuery in PySide6 works in sqlite but not in MySQL.
need help, thanks a lotRunning effect in sqlite
Running effect in MySQL
codeimport 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())
-
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. -
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.
-
@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?! -
And that's the reason why one should always check the return values of e.g. QSqlQuery::prepare() and others...