The Python project introduced in this article builds an interactive “.exe” application which stores the book information including title, author, year and IBSN. The user will be able to use the “.exe” application to enter and modify book info in any Windows system. The book information is stored in database and can be accessed without Python installed on the user’s computer.
The actual “.exe” program that built with this program can be viewed here:
https://drive.google.com/file/d/12SdTrqu96K_k4ZiePs8Jgwzu2293ogq0/view?usp=sharing
(Object Oriented)
Frontend.py
from tkinter import * from backend import Database database=Database("books.db") def get_selected_row(event): global selected_tuple index=list1.curselection()[0] selected_tuple=list1.get(index) e1.delete(0,END) e1.insert(END,selected_tuple[1]) e2.delete(0,END) e2.insert(END,selected_tuple[2]) e3.delete(0,END) e3.insert(END,selected_tuple[3]) e4.delete(0,END) e4.insert(END,selected_tuple[4]) def view_command(): list1.delete(0,END) for row in database.view(): list1.insert(END,row) def search_command(): list1.delete(0,END) for row in database.search(title_text.get(),author_text.get(),year_text.get(),isbn_text.get()): list1.insert(END,row) def add_command(): database.insert(title_text.get(),author_text.get(),year_text.get(),isbn_text.get()) list1.delete(0,END) list1.insert(END,(title_text.get(),author_text.get(),year_text.get(),isbn_text.get())) def delete_command(): database.delete(selected_tuple[0]) def update_command(): database.update(selected_tuple[0],title_text.get(),author_text.get(),year_text.get(),isbn_text.get()) window=Tk() window.wm_title("BookStore") l1=Label(window,text="Title") l1.grid(row=0,column=0) l2=Label(window,text="Author") l2.grid(row=0,column=2) l3=Label(window,text="Year") l3.grid(row=1,column=0) l4=Label(window,text="ISBN") l4.grid(row=1,column=2) title_text=StringVar() e1=Entry(window,textvariable=title_text) e1.grid(row=0,column=1) author_text=StringVar() e2=Entry(window,textvariable=author_text) e2.grid(row=0,column=3) year_text=StringVar() e3=Entry(window,textvariable=year_text) e3.grid(row=1,column=1) isbn_text=StringVar() e4=Entry(window,textvariable=isbn_text) e4.grid(row=1,column=3) list1=Listbox(window, height=6,width=35) list1.grid(row=2,column=0,rowspan=6,columnspan=2) sb1=Scrollbar(window) sb1.grid(row=2,column=2,rowspan=6) list1.configure(yscrollcommand=sb1.set) sb1.configure(command=list1.yview) list1.bind('<<ListboxSelect>>',get_selected_row) b1=Button(window,text="View all", width=12,command=view_command) b1.grid(row=2,column=3) b2=Button(window,text="Search entry", width=12,command=search_command) b2.grid(row=3,column=3) b3=Button(window,text="Add entry", width=12,command=add_command) b3.grid(row=4,column=3) b4=Button(window,text="Update selected", width=12,command=update_command) b4.grid(row=5,column=3) b5=Button(window,text="Delete selected", width=12,command=delete_command) b5.grid(row=6,column=3) b6=Button(window,text="Close", width=12,command=window.destroy) b6.grid(row=7,column=3) window.mainloop()
Backend.py
import sqlite3 class Database: def __init__(self, db): self.conn=sqlite3.connect(db) self.cur=self.conn.cursor() self.cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)") self.conn.commit() def insert(self,title,author,year,isbn): self.cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title,author,year,isbn)) self.conn.commit() def view(self): self.cur.execute("SELECT * FROM book") rows=self.cur.fetchall() return rows def search(self,title="",author="",year="",isbn=""): self.cur.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?", (title,author,year,isbn)) rows=self.cur.fetchall() return rows def delete(self,id): self.cur.execute("DELETE FROM book WHERE id=?",(id,)) self.conn.commit() def update(self,id,title,author,year,isbn): self.cur.execute("UPDATE book SET title=?, author=?, year=?, isbn=? WHERE id=?",(title,author,year,isbn,id)) self.conn.commit() def __del__(self): self.conn.close() #insert("The Sun","John Smith",1918,913123132) #delete(3) #update(4,"The moon","John Smooth",1917,99999) #print(view()) #print(search(author="John Smooth"))
(Preliminary)
Frontend.py
""" frontend.py This file builds an interface that stores this book information: Title, Author Year, ISBN User can: View all records Search an entry Add entry Update entry Delete Close """ from tkinter import * import backend def get_selected_row(event): try: global selected_tuple index=list1.curselection()[0] selected_tuple=list1.get(index) e1.delete(0,END) e1.insert(END,selected_tuple[1]) e2.delete(0,END) e2.insert(END,selected_tuple[2]) e3.delete(0,END) e3.insert(END,selected_tuple[3]) e4.delete(0,END) e4.insert(END,selected_tuple[4]) except IndexError: pass def view_command(): list1.delete(0,END) for row in backend.view(): list1.insert(END,row) def search_command(): list1.delete(0,END) for row in backend.search(title_text.get(),author_text.get(),year_text.get(),isbn_text.get()): list1.insert(END,row) def add_command(): backend.insert(title_text.get(),author_text.get(),year_text.get(),isbn_text.get()) list1.delete(0,END) list1.insert(END,(title_text.get(),author_text.get(),year_text.get(),isbn_text.get())) def delete_command(): backend.delete(selected_tuple[0]) def update_command(): backend.update(selected_tuple[0],title_text.get(),author_text.get(),year_text.get(),isbn_text.get()) window=Tk() window.wm_title("Bookstore") # Create labels for title, author, year and ISBN l1=Label(window,text="Title") l1.grid(row=0,column=0) l2=Label(window,text="Author") l2.grid(row=0,column=2) l3=Label(window,text="Year") l3.grid(row=1,column=0) l4=Label(window,text="ISBN") l4.grid(row=1,column=2) # Create entries for title, author, year and ISBN title_text=StringVar() e1=Entry(window,textvariable=title_text) e1.grid(row=0,column=1) author_text=StringVar() e2=Entry(window,textvariable=author_text) e2.grid(row=0,column=3) year_text=StringVar() e3=Entry(window,textvariable=year_text) e3.grid(row=1,column=1) isbn_text=StringVar() e4=Entry(window,textvariable=isbn_text) e4.grid(row=1,column=3) # Create list box list1=Listbox(window,height=6,width=35) list1.grid(row=2,column=0,rowspan=6,columnspan=2) # Create a scroll bar sb1=Scrollbar(window) sb1.grid(row=2,column=2,rowspan=6) # Create a list box list1.configure(yscrollcommand=sb1.set) sb1.configure(command=list1.yview) list1.bind('<<ListboxSelect>>',get_selected_row) # Create 6 buttons b1=Button(window,text="View all", width=12,command=view_command) b1.grid(row=2,column=3) b2=Button(window,text="Search entry", width=12,command=search_command) b2.grid(row=3,column=3) b3=Button(window,text="Add entry", width=12, command=add_command) b3.grid(row=4,column=3) b4=Button(window,text="Update", width=12,command=update_command) b4.grid(row=5,column=3) b5=Button(window,text="Delete", width=12,command=delete_command) b5.grid(row=6,column=3) b6=Button(window,text="Close", width=12, command=window.destroy) b6.grid(row=7,column=3) window.mainloop() # Terminal: #python -m PyInstaller --onefile --windowed frontend.py
Backend.py
""" Backend.py The file contains all the functions that supports the interface built with frontend.py """ import sqlite3 def connect(): conn=sqlite3.connect("books.db") cur=conn.cursor() cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)") conn.commit() conn.close() def insert(title,author,year,isbn): conn=sqlite3.connect("books.db") cur=conn.cursor() cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title,author,year,isbn)) conn.commit() conn.close() def view(): conn=sqlite3.connect("books.db") cur=conn.cursor() cur.execute("SELECT * FROM book") rows=cur.fetchall() conn.close() return rows def search(title="",author="",year="",isbn=""): conn=sqlite3.connect("books.db") cur=conn.cursor() cur.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?",(title,author,year,isbn)) rows=cur.fetchall() conn.close() return rows def delete(id): conn=sqlite3.connect("books.db") cur=conn.cursor() cur.execute("DELETE FROM book WHERE id=?",(id,)) conn.commit() conn.close() def update(id,title,author,year,isbn): conn=sqlite3.connect("books.db") cur=conn.cursor() cur.execute("UPDATE book SET title=?,author=?,year=?,isbn=? WHERE id=?",(title,author,year,isbn,id)) conn.commit() conn.close() connect() #insert("The Earth","Peter",1910,9958645132) #delete(3) #update(4,"The Moon","Sophie Su",1984,2001908) #print(view()) #print(search(author="Peter"))