How can I filter an ms-access databse, using QSqlTableModel and QLineEdit?

Question

I'm building a GUI that allows users to search information in a ms access database (yup. It has to be the ms access) The user has a textfield where he can type his search and the Tableview should update instantly. At the moment the DB disappears whenever you type a letter in the field.

Took me a while to figure out the problem: my SQL statement is simply not right. (Thanks to model.lastError)

The whole function looks like this:

    self.ui.Kistenschild_suchen.textChanged.connect(self.update_filter)

    
def update_filter(self, s):
    s = re.sub("[\W_]+", "", s)
    filter_str = 'Produkt LIKE %{}%"'.format(s)
    self.ui.model.setFilter(filter_str)
    print(self.ui.model.lastError())

In this case I typed k

The errormessage is:

PySide6.QtSql.QSqlError("-3100", "QODBC: Unable to execute statement", "[Microsoft][ODBC-Treiber für Microsoft Access] Syntaxfehler in Abfrageausdruck 'Produkt LIKE (%h%\")'.") at 0x000001CA4FB33B88>

Point of interest should be the '%h%")'."' Since it shows more characters than typed

I tried to change in several ways, like changing the % to * and? Still nothing

EDIT:

Here is the minimal reproducible example:

import re
import sys

from PySide6.QtCore import QSize, Qt
from PySide6.QtSql import QSqlDatabase, QSqlTableModel
from PySide6.QtWidgets import (
    QApplication,
    QLineEdit,
    QMainWindow,
    QTableView,
    QVBoxLayout,
    QWidget,
)

Driver= r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\scripts\python\pyside_Tutorials\databases\chinook.accdb'

db = QSqlDatabase("QODBC")
db.setDatabaseName(Driver)
db.open()






class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        self.search = QLineEdit()
        self.search.textChanged.connect(self.update_filter)
        self.table = QTableView()

        layout.addWidget(self.search)
        layout.addWidget(self.table)
        container.setLayout(layout)

        self.model = QSqlTableModel(db=db)

        self.table.setModel(self.model)

        self.model.setTable("Track")
        self.model.select()

        self.setMinimumSize(QSize(1024, 600))
        self.setCentralWidget(container)
        
        
        
     # tag::filter[]
    def update_filter(self, s):
        s = re.sub("[\W_]+", "", s)
        filter_str = 'Name LIKE "%{}%"'.format(s)
        self.model.setFilter(filter_str)
        print(self.model.lastError())
        print(s,type(s))

    # end::filter[]


        
        
app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec()

This code gives me the errormessage: <PySide6.QtSql.QSqlError("-3010", "QODBC: Unable to execute statement", "[Microsoft][ODBC-Treiber für Microsoft Access] 1 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben.") at 0x0000016FC7535108>

Which means something like: "1 parameter was expected but too few parameters were passed"

thanks @eyllanesc. I changed the TYPO. But the error remains the same. As I mentioned, I tried different ways to find a solution. Here is a example from a specific Pyside6-Book: '''filter_str = 'Name LIKE "%{}%"'.format(s)''' But in the example it is a QSqlite Databse. I think, that the difference is crucial for the problem. — Nullschall, May 25 '21 at 22:17
The error cannot be exactly the same, show the new error message in addition to providing a [mre] — eyllanesc, May 25 '21 at 22:20
Thank you @eyllanesc. The Error changed to `` In english it means something like: One parameter was expected, but there were not enough provided. I would like to set up a minimal reproducible example. Do you have any recommendations on how to do that because of the GUI and Databse? — Nullschall, May 25 '21 at 22:42
what is the value of `s` after `s = re.sub("[\W_]+", "", s)`?: `s = re.sub("[\W_]+", "", s)` `print(s, type(s))` — eyllanesc, May 25 '21 at 22:47
@eyllanesc `print(s, type(s))` gives `` back. Please see the Edit on the Post. Thank you in advance. — Nullschall, May 27 '21 at 19:19
1) That print must print 2 things, not just 1. 2) The MRE must be in your post, it must not be an external link. Please read [ask] and review the [tour] — eyllanesc, May 27 '21 at 19:22
Are you still getting the error message? in your MRE there is nothing about it — eyllanesc, May 27 '21 at 20:03
@eyllanesc Yes, it is still the same. Thanks for pointing that out. I added it to the question. — Nullschall, May 27 '21 at 20:09
You show the error print but in the MRE I don't see any print — eyllanesc, May 27 '21 at 20:20
I added the printstatement. hopefully I understood what you wanted. — Nullschall, May 27 '21 at 20:29

score 0 · Accepted Answer · answered Jun 10 '21 at 12:42

0

MS-Access needs a double apostrophe like:

def update_filter(self, s):
        s = re.sub(r"[\W_]+", "", s)
        filter_str = f"Produkt '%{s}%'"
        self.model.setFilter(filter_str)```

answered Jun 10 '21 at 12:42

Nullschall

11
6

How can I filter an ms-access databse, using QSqlTableModel and QLineEdit?

1 Answers1

Linked