How to write a database search with a partial match?

Question

Python

I have the code (I'm posting the complete one), however I can't figure out how to write a partial match database search like А.С.Пушкин = Пушкин А.С.

Here is the code:

import sqlite3  # импорт модуля SQLite
import tkinter as tk  # импорт библиотеки tkinter
from tkinter import ttk # импорт модуля TTk

class Main(tk.Frame):
    def __init__(self, root):
        super().__init__(root)
        self.init_main()
        self.db = db
        self.view_records()

    def init_main(self): # главное окно
        toolbar = tk.Frame(bg='#d7d8e0', bd=2)
        toolbar.pack(side=tk.TOP, fill=tk.X)

        self.add_img = tk.PhotoImage('')
        btn_open_dialog = tk.Button(toolbar, text='Добавить позицию', command=self.open_dialog, bg='#d7d8e0', bd=0,
                                    compound=tk.TOP, image=self.add_img)
        btn_open_dialog.pack(side=tk.LEFT)

        self.update_img = tk.PhotoImage('')
        btn_edit_dialog = tk.Button(toolbar, text='Редактировать', bg='#d7d8e0', bd=0, image=self.update_img,
                                    compound=tk.TOP, command=self.open_update_dialog)
        btn_edit_dialog.pack(side=tk.LEFT)

        self.delete_all_img = tk.PhotoImage('')
        btn_delete_all_records = tk.Button(toolbar, text='Очистить', bg='#d7d8e0', bd=0, image=self.delete_all_img,
                                      compound=tk.TOP, command=self.delete_all_records)
        btn_delete_all_records.pack(side=tk.RIGHT)

        self.search_img = tk.PhotoImage('')
        btn_open_search_dialog = tk.Button(toolbar, text='Поиск', bg='#d7d8e0', bd=0, image=self.search_img,
                                      compound=tk.TOP, command=self.open_search_dialog)
        btn_open_search_dialog.pack(side=tk.RIGHT)

        self.tree = ttk.Treeview(self, columns=('ID', 'authors', 'name', 'ISBN', 'publisher', 'year','quantity'), height=15, show='headings')

        self.tree.column('ID', width=30, anchor=tk.CENTER)
        self.tree.column('authors', width=92+31+21+21, anchor=tk.CENTER)
        self.tree.column('name', width=92+31+21+21, anchor=tk.CENTER)
        self.tree.column('ISBN', width=50, anchor=tk.CENTER)
        self.tree.column('publisher', width=92+62, anchor=tk.CENTER)
        self.tree.column('year', width=50, anchor=tk.CENTER)
        self.tree.column('quantity', width=30, anchor=tk.CENTER)

        self.tree.heading('ID', text='ID')
        self.tree.heading('authors', text='Автор(ы)')
        self.tree.heading('name', text='Название')
        self.tree.heading('ISBN', text='ISBN')
        self.tree.heading('publisher', text='Издательство')
        self.tree.heading('year', text='Год издания')
        self.tree.heading('quantity', text='Количество')

        self.tree.pack(expand = True, fill=tk.BOTH)

    def records(self, authors, name, ISBN, publisher, year, quantity):
        self.db.insert_data(authors, name, ISBN, publisher, year, quantity)
        self.view_records()

    def update_record(self, authors, name, ISBN, publisher, year, quantity):
        self.db.c.execute('''UPDATE books SET authors=?, name=?, ISBN=?, publisher=?, year=?, quantity=? WHERE ID=?''',
                          (authors, name, ISBN, publisher, year, quantity, self.tree.set(self.tree.selection()[0], '#1')))
        self.db.conn.commit()
        self.view_records()

    def view_records(self):
        self.db.c.execute('''SELECT * FROM books''')
        [self.tree.delete(i) for i in self.tree.get_children()]
        [self.tree.insert('', 'end', values=row) for row in self.db.c.fetchall()]

    def search(self):
        self.db.c.execute('''''')

    def delete_all_records(self):
        self.db.c.execute('''DELETE FROM books''')
        self.db.conn.commit()
        self.view_records()

    def open_dialog(self):
        Child() # вызов дочернего окна

    def open_update_dialog(self):
        Update()

    def open_search_dialog(self):
        Search()


class Child(tk.Toplevel):
    def __init__(self):
        super().__init__(root)
        self.init_child()
        self.view = app

    def init_child(self):
        self.title('Добавить книгу')
        self.geometry('400x220+400+300')
        self.resizable(False, False)

        label_authors = tk.Label(self, text='Автор(ы):')
        label_authors.place(x=50, y=10)

        label_name = tk.Label(self, text='Название:')
        label_name.place(x=50, y=40)

        label_ISBN = tk.Label(self, text='ISBN:')
        label_ISBN.place(x=50, y=70)

        label_publisher = tk.Label(self, text = 'Издательство:')
        label_publisher.place(x=50, y=100)

        label_year = tk.Label(self, text = 'Год издания:')
        label_year.place(x=50, y=130)

        label_quantity = tk.Label(self, text = 'Количество:')
        label_quantity.place(x=50, y=160)


        self.entry_authors = ttk.Entry(self)
        self.entry_authors.place(x=150, y=10)

        self.entry_name = ttk.Entry(self)
        self.entry_name.place(x=150, y=40)

        self.entry_ISBN = ttk.Entry(self)
        self.entry_ISBN.place(x=150, y=70)

        self.entry_publisher = ttk.Entry(self)
        self.entry_publisher.place(x=150, y=100)

        self.entry_year = ttk.Entry(self)
        self.entry_year.place(x=150, y=130)

        self.entry_quantity = ttk.Entry(self)
        self.entry_quantity.place(x=150, y=160)

        btn_cancel = ttk.Button(self, text='Закрыть', command=self.destroy)
        btn_cancel.place(x=320, y=190)

        self.btn_ok = ttk.Button(self, text='Добавить')
        self.btn_ok.place(x=240, y=190)
        self.btn_ok.bind('<Button-1>', lambda event: self.view.records(self.entry_authors.get(),
                                                                       self.entry_name.get(),
                                                                       self.entry_ISBN.get(),
                                                                       self.entry_publisher.get(),
                                                                       self.entry_year.get(),
                                                                       self.entry_quantity.get()))

        self.grab_set() # перехват всех событий, происходящих в приложении
        self.focus_set() # захват и удержание фокуса


class Update(Child):
    def __init__(self):
        super().__init__()
        self.init_edit()
        self.view = app

    def init_edit(self):
        self.title('Редактировать позицию')
        btn_edit = ttk.Button(self, text='Редактировать')
        btn_edit.place(x=230, y=190)
        btn_edit.bind('<Button-1>', lambda event: self.view.update_record(self.entry_authors.get(),
                                                                       self.entry_name.get(),
                                                                       self.entry_ISBN.get(),
                                                                       self.entry_publisher.get(),
                                                                       self.entry_year.get(),
                                                                       self.entry_quantity.get()))
        self.btn_ok.destroy()

class Search(Child):
    def __init__(self):
        super().__init__()
        self.init_search()
        self.view = app

    def init_search(self):
        self.title('Поиск')
        btn_edit = ttk.Button(self, text='Найти')
        btn_edit.place(x=240, y=190)
        btn_edit.bind('<Button-1>', lambda event: self.view.update_record())

        self.btn_ok.destroy()

class DB:
    def __init__(self):
        self.conn = sqlite3.connect('books.db')
        self.c = self.conn.cursor()
        self.c.execute('''CREATE TABLE IF NOT EXISTS books 
                            (id integer primary key, 
                            authors text, 
                            name text, 
                            ISBN text,
                            publisher text,
                            year integer
                            quantity integer)''')
        self.conn.commit()

    def insert_data(self, authors, name, ISBN, publisher, year, quantity):
        self.c.execute('''INSERT INTO books(authors, name, ISBN) VALUES (?, ?, ?)''',
                       (authors, name, ISBN))
        self.conn.commit()


if __name__ == "__main__":
    root = tk.Tk() # корневое окно программы
    db = DB() # экземпляр класса DB
    app = Main(root)
    app.pack(expand = True, fill = tk.BOTH)
    root.title("Household books") # название окна
    root.geometry("700x400") # размеры окна
    root.resizable(True, True) # фиксация окна по обеим осям
    root.mainloop() # запуск главного цикла обработки событий

Answer 1

I think in your case it is more correct to solve the problem once analytically and not allow incorrect data to be entered into the database anymore. Ideally, create separate fields for the first and middle name.