Pagination Problem querying sqlite with SELECT – WHERE – LIKE

I´m trying to build a small private app to better organize our workshop. I have many parts in numbered boxes. All information goes into a sqlite.db. Images are stored separately – only the image-paths are stored in the database. Now I´m trying to query the database with pagination. But there is a mistake in this line of code I think:

limit_search=('SELECT * from KTRmini_table WHERE discription LIKE ?, (search_term,) LIMIT ')   str(offset)   ','   str(limit)

It would be very kind if someone could give me a hint what to change. Thank you.

That´s the relevant part of the code.

def find_search_term_and_total_number_of_matches_if_any(self, search_term):
    search_term = self.search_entry.get()
    search_term = ("%" search_term "%")
    conn=sqlite3.connect("KTRmini.db")
    c = conn.cursor() 
    c.execute("SELECT count(*) FROM KTRmini_table WHERE discription LIKE ?", (search_term,))
    total_number_of_matches=c.fetchone()[0]
    print("Found "   str(total_number_of_matches)   " Matches in Database")
    limit = 5;
                    
    class Matches_window(tk.Toplevel):
        def __init__(self, parent):
            super().__init__(parent)
            self.title('KTR-Matches')
            self.configure(background='#587a8f')
            self.geometry("1280x720")
            self.iconbitmap("icon.ico")
            
    Startpage.withdraw(self)
    matches_window = Matches_window(self)
    matches_window.grab_set()

    def paged_matches(offset):
        limit_search=('SELECT * from KTRmini_table WHERE discription LIKE ?, (search_term,) LIMIT ')   str(offset)   ','   str(limit)
        r_set=c.execute(limit_search)
        i=0
                  
        for each_match in r_set:
            for number_of_database_columns in range(len(each_match)):
                e = Entry(Matches_window, width=10, fg='blue', font=('Arial', 16)) 
                e.grid(row=i, column=j)
                e.insert(END, each_match[j])
                b = Button(text= "Button", height=1, font=('Arial', 10))
                b.grid(row=i, column=j 1)
            i=i 1
            
    paged_matches(0) # initial offset of 0

This is the error message:

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Program Files (x86)\Thonny\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
  File "C:\Users\Marcel\Documents\MARCEL\PYTHON_PROJECTS\gui\KTR_mini\KTRmini_220215_028 Pagination.py", line 93, in find_search_term_and_total_number_of_matches_if_any
    paged_matches(0) # initial offset of 0
  File "C:\Users\Marcel\Documents\MARCEL\PYTHON_PROJECTS\gui\KTR_mini\KTRmini_220215_028 Pagination.py", line 81, in paged_matches
    r_set=c.execute(limit_search)
sqlite3.OperationalError: near ",": syntax error

CodePudding user response：

The syntax of execute() is execute(SQL_string, *args) where SQL_string is the SQL statement and *args are the optional arguments.

Putting the argument search_term inside the SQL statement is incorrect.

Correct syntax is:

limit_search = 'SELECT * FROM KTRmini_table WHERE discription LIKE ? LIMIT ? OFFSET ?'
r_set = c.execute(limit_search, (search_term, limit, offset))