Python sqlite поиск по базе

Как сделать поиск по таблице SQLite3 по нескольким столбцам?

Делаю программку с помощью python tkinter для себя и конечно же я новичок в этом деле.
В БД одна таблица(пока) имеет данные в 5 столбцах(первый id). Необходимо чтобы она выдавала результаты поиска(при нажатии на кнопку) по ним, то есть пользователь вводит к примеру: имя продукта, размеры (длина/ширина/толщина).
Вот сама функция обращения к бд:

def search_records(self, product, lenght, width, thickness): product = ('%' + product + '%',) lenght = ('%' + lenght + '%',) width = ('%' + width + '%',) thickness = ('%' + thickness + '%',) self.db.c.execute('''SELECT * FROM massa WHERE product LIKE ? AND lenght LIKE ? AND width LIKE ? AND thickness LIKE ?''', product, lenght, width, thickness) [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 init_search(self): self.title('Поиск') self.geometry ('500x300+400+300') self.resizable(False, False) label_search = tk.Label(self, text = 'Поиск по таблице') label_search.place(x=250,y=20) label_product_s = tk.Label(self, text='Наименование продукции:') label_product_s.place(x=50, y=80) self.entry_product_s = ttk.Entry(self) self.entry_product_s.place(x=300, y=80) label_lenght_s = tk.Label(self, text='Длина, мм:') label_lenght_s.place(x=50, y=110) self.entry_lenght_s = ttk.Entry(self) self.entry_lenght_s.place(x=300, y=110) label_width_s = tk.Label(self, text='Ширина, мм:') label_width_s.place(x=50, y=140) self.entry_width_s= ttk.Entry(self) self.entry_width_s.place(x=300, y=140) label_thickness_s = tk.Label(self, text='Толщина или высота, мм:') label_thickness_s.place(x=50, y=170) self.entry_thickness_s = ttk.Entry(self) self.entry_thickness_s.place(x=300, y=170) btn_cansel = ttk.Button(self, text='Закрыть', command=self.destroy) btn_cansel.place(x=185, y=230) btn_search = ttk.Button (self, text='Поиск') btn_search.place(x=105, y=230) btn_search.bind('', lambda event:self.view.search_records(self.entry_product_s.get(),self.entry_lenght_s.get(), self.entry_width_s.get(), self.entry_thickness_s.get())) btn_search.bind('', lambda event:self.destroy(), add='+')

А это ошибка, которая выпадает при запуске поиска:

Exception in Tkinter callback Traceback (most recent call last): File "C:\Program Files (x86)\Python38-32\lib\tkinter\__init__.py", line 1883, in __call__ return self.func(*args) File "D:/Phyton/App_big_project/Main_app.py", line 231, in btn_search.bind('', lambda event:self.view.search_records(self.entry_product_s.get(),self.entry_lenght_s.get(), self.entry_width_s.get(), self.entry_thickness_s.get())) File "D:/Phyton/App_big_project/Main_app.py", line 80, in search_records self.db.c.execute('''SELECT * FROM massa WHERE product LIKE ? AND lenght LIKE ? AND width LIKE ? AND thickness LIKE ?''', product, lenght, width, thickness) TypeError: function takes at most 2 arguments (5 given)

Поэтому вопрос, что мне необходимо исправить в функции поиска или в кнопке, чтобы выходил нужны результат. Или это вообще не реализовать в данных библиотеках?)

Источник

Python — Search Query In SQLite

In this tutorial we will create a Search Query In SQLite using Python. This code will search a data in the table of SQLite database when user submit the required information. The code use tkinter module to design a layout and call a specific functions. A function will automatically call when you the designated entry widget then it will run the SQLite SELECT query with a condition of LIKE in WHERE clause. We will be using Python programming language because it is widely used as an advance-level programming language for a general technique to the developer. Beginners find Python a clean syntax and indentation structure based, and it is easy to learn because of its less semi colon problem.

Getting started:

First you will have to download & install the Python IDLE’s, here’s the link for the Integrated Development And Learning Environment for Python https://www.python.org/downloads/.

Installing SQLite Browser

After you installed Python, we will now then install the SQLite, here’s the link for the DB Browser for SQLite http://sqlitebrowser.org/.

Importing Modules

After setting up the installation and the database, run the IDLE and click file and then new file. After that a new window will appear containing a black file this will be the text editor for the python. Then copy code that I provided below and paste it inside the IDLE text editor.

Setting up the Main Frame

After importing the modules, we will now then create the main frame for the application. To do that just copy the code below and paste it inside the IDLE text editor.

Creating the Database Connection

Then after setting up the design we will now create the database function. To do that just simply copy the code below and paste it inside the IDLE text editor.

Assigning Variables

This is where the we will assign the variables. This code will assign each of the variables to be use later in the application.

Designing the Layout

After creating the Main Frame we will now add some layout to the application. Just kindly copy the code below and paste it inside the IDLE text editor.

Creating the Main Function

This is where the code that contains the main funcitions. This code will search a SQLite data when the button is clicked. To do that just copy and write these blocks of code.

Initializing the Application

After finishing the function save the application as index.py. This function will run the code and check if the main application is initialized properly. To do that copy the code below and paste it inside the IDLE text editor.

There you have it we just created a Search Query In SQLite Using Python. I hope that this simple tutorial help you for what you are looking for. For more updates and tutorials just kindly visit this site. Enjoy Coding!!

Источник

How to Search data from SQLite in Python Tkinter | SQLite3 in Python

Hello friends how are you, Today in this post «How to Search data from SQLite in Python» you will learn that how you can create database in python, store data in database , display data in Python tkinter GUI interface and search particular data from SQLite and display in GUI interface. If you want to create a GUI Application in python using Database then this post will help you definitely. if you don’t know how to handle SQLite in Python then i will suggest you first visit the below links if you want to be master in programming😊.

The first step is to create database and here i am going to create a database with name student . Open or Create any python file and and type the following code to create SQLite database.

import sqlite3 conn=sqlite3.connect("student.db") print("Database created successfully") 

Now the second step is to create table inside database and there is no need to create a separate python file to do this you can run these programs in same python file .So Just type the following code into your python file and run this code to create table STUD_REGISTRATION inside your student database.

import sqlite3 conn=sqlite3.connect("student.db") print("Database Opened successfully") conn.execute(""" CREATE TABLE STUD_REGISTRATION( STU_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , STU_NAME TEXT NOT NULL, STU_CONTACT TEXT, STU_EMAIL TEXT, STU_ROLLNO TEXT NOT NULL, STU_BRANCH TEXT NOT NULL) """) print ("Table STUD_REGISTRATION created successfully") """ ###Output### Database Opened successfully Table STUD_REGISTRATION created successfully """ 

Now its time to insert some records into our database so we can perform search operation on it. Here i am inserting two records into table but you can insert as many as you can. Following is the code to insert data into table.

import sqlite3 conn=sqlite3.connect("student.db") print("Database Opened successfully") conn.execute("INSERT INTO STUD_REGISTRATION (STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH) \ VALUES ('Raju', '9179876567', 'raju@gmail.com','MCA204', 'CA')"); conn.execute("INSERT INTO STUD_REGISTRATION (STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH) \ VALUES ('Nancy', '9179785695', 'nancy@gmail.com','MCA225', 'CA')"); conn.commit() print ("Records inserted successfully") conn.close() """ ###Output### Database Opened successfully Records inserted successfully """ 

Here is the complete code of Python that will access and display all the records of registered students from database and you can search record of any particular students by typing his/her name in a textbox.

#import library #import library for creating GUI from tkinter import * import tkinter.ttk as ttk #import library for handling SQLite database import sqlite3 #defining function for creating GUI Layout def DisplayForm(): #creating window display_screen = Tk() #setting width and height for window display_screen.geometry("800x200") #setting title for window display_screen.title("krazyprogrammer.com") global tree global SEARCH SEARCH = StringVar() #creating frame TopViewForm = Frame(display_screen, width=600, bd=1, relief=SOLID) TopViewForm.pack(side=TOP, fill=X) LeftViewForm = Frame(display_screen, width=600) LeftViewForm.pack(side=LEFT, fill=Y) MidViewForm = Frame(display_screen, width=600) MidViewForm.pack(side=RIGHT) lbl_text = Label(TopViewForm, text="SQLite Database Student Records", font=('verdana', 18), width=600,bg="#1C2833",fg="white") lbl_text.pack(fill=X) lbl_txtsearch = Label(LeftViewForm, text="Search", font=('verdana', 15)) lbl_txtsearch.pack(side=TOP, anchor=W) search = Entry(LeftViewForm, textvariable=SEARCH, font=('verdana', 15), width=10) search.pack(side=TOP, padx=10, fill=X) btn_search = Button(LeftViewForm, text="Search", command=SearchRecord) btn_search.pack(side=TOP, padx=10, pady=10, fill=X) btn_search = Button(LeftViewForm, text="View All", command=DisplayData) btn_search.pack(side=TOP, padx=10, pady=10, fill=X) #setting scrollbar scrollbarx = Scrollbar(MidViewForm, orient=HORIZONTAL) scrollbary = Scrollbar(MidViewForm, orient=VERTICAL) tree = ttk.Treeview(MidViewForm,columns=("Student Id", "Name", "Contact", "Email","Rollno","Branch"), selectmode="extended", height=100, yscrollcommand=scrollbary.set, xscrollcommand=scrollbarx.set) scrollbary.config(command=tree.yview) scrollbary.pack(side=RIGHT, fill=Y) scrollbarx.config(command=tree.xview) scrollbarx.pack(side=BOTTOM, fill=X) #setting headings for the columns tree.heading('Student Id', text="Student Id", anchor=W) tree.heading('Name', text="Name", anchor=W) tree.heading('Contact', text="Contact", anchor=W) tree.heading('Email', text="Email", anchor=W) tree.heading('Rollno', text="Rollno", anchor=W) tree.heading('Branch', text="Branch", anchor=W) #setting width of the columns tree.column('#0', stretch=NO, minwidth=0, width=0) tree.column('#1', stretch=NO, minwidth=0, width=100) tree.column('#2', stretch=NO, minwidth=0, width=150) tree.column('#3', stretch=NO, minwidth=0, width=80) tree.column('#4', stretch=NO, minwidth=0, width=120) tree.pack() DisplayData() #function to search data def SearchRecord(): #checking search text is empty or not if SEARCH.get() != "": #clearing current display data tree.delete(*tree.get_children()) #open database conn = sqlite3.connect('student.db') #select query with where clause cursor=conn.execute("SELECT * FROM STUD_REGISTRATION WHERE STU_NAME LIKE ?", ('%' + str(SEARCH.get()) + '%',)) #fetch all matching records fetch = cursor.fetchall() #loop for displaying all records into GUI for data in fetch: tree.insert('', 'end', values=(data)) cursor.close() conn.close() #defining function to access data from SQLite database def DisplayData(): #clear current data tree.delete(*tree.get_children()) # open databse conn = sqlite3.connect('student.db') #select query cursor=conn.execute("SELECT * FROM STUD_REGISTRATION") #fetch all data from database fetch = cursor.fetchall() #loop for displaying all data in GUI for data in fetch: tree.insert('', 'end', values=(data)) cursor.close() conn.close() #calling function DisplayForm() if __name__=='__main__': #Running Application mainloop() 

You just type this above code into your python file or you can copy this code for your personal use. When you will run this code you will get a screen like below

Here as you can see that by default the output screen will display all the students details. If you want to search the record of a student then type the name of student into textbox and click on Search button. For example if you want to search the record of student Nancy then you can type Nancy or Nanc or Nan etc because i am using LIKE operator in SQL query that will return all records that match with given Character or word. For better understanding see the below screenshots.

I hope now you can fetch and display data from SQLite and can perform search operation with Python Tkinter.

Источник