Home > Software engineering >  Why is my search function not working properly in my Python 3 database and table creation project? I
Why is my search function not working properly in my Python 3 database and table creation project? I

Time:11-19

Below is all of my code, created a database, and also a table. Is there anything you can see wrong with my get functions maybe, or my search button code? Every time I try to input a new student id into the database and then search for it, I get the result that the student was found, but it never prints out into the graphical interface. Any clue what could be wrong with my code? Why my display window isn't showing any of the things I am uploading to the database? Have been trying for hours to try to figure something out. Thank you in advance!

import signal ## for giving killing signal
from tkinter import * # importing tkinter
# importing each needed from tkinter
from tkinter import Label 
from tkinter import StringVar
from tkinter import IntVar
from tkinter import Entry
from tkinter import OptionMenu
from tkinter import Frame
from tkinter import Button
from tkinter import ttk
from tkinter import messagebox
# importing squlite3 as sq
import sqlite3 as sq

window = Tk() #to declare window
window.title("Student Management System") # title of the window
screen_width = window.winfo_screenwidth() ## getting screen width
screen_height = window.winfo_screenheight() ## getting screen height
x_cord = int((screen_width / 2) - 400) ## x cordinate of window
y_cord = int((screen_height / 2) - 300) ## y cordinate of window
window.geometry('%dx%d %d %d' % (800, 600, x_cord, y_cord)) ## setting window in the center
header = Label(window, text="Student Management System", font=("arial", 24, "bold"),).pack() ## header label with suitable font and colour

conn = sq.connect('database.db')  ## connecting with the database
c = conn.cursor()  #creating a cursor object

L1 = Label(window, text="StudentID",font=("arial", 16)).place(x=100, y=60) ## label for student id at position 100,60
L2 = Label(window, text="FirstName", font=("arial", 16)).place(x=100, y=100) ## label for firstname at position 100,100
L3 = Label(window, text="LastName", font=("arial", 16)).place(x=100, y=140) ## label for lastname at position 100,140
L4 = Label(window, text="Contact No.",font=("arial", 16)).place(x=100, y=180) ## label for contact no. at position 100,180
L5 = Label(window, text="City", font=("arial", 16)).place(x=100, y=220) ## label for city at position 100,220
L6 = Label(window, text="Enter State", font=("arial", 16)).place(x=100, y=260) ## label for state at position 100,260
L7 = Label(window, text="Please select one record below to update or delete", font=("arial", 12)).place(x=100, y=360) ## label for state at position 100,260
L8 = Label(window, text="Enter Student ID", font=("arial", 12)).place(x=100, y=540) ## label for sid at position 100,540

Sid = IntVar(window) ## type of studentID as int
Fname = StringVar(window) ## type of first name as string
Lname = StringVar(window) ## type of last name as string
Contact = IntVar(window) ## type of contact as int
City = StringVar(window) ## type of city as string
State = StringVar(window) ## type of state as string
Search_val = IntVar(window) ## type of searchVal as int

def clear(): ## for clearing all Entry
    Sid.set('')
    Fname.set('')
    Lname.set('')
    Contact.set('')
    City.set('')
    State.set('Choose from List')
    Search_val.set('')
clear() ## clearing everything initially

StateD = {'Alabama','Alaska','Arizona','Arkansas','California','Colorado','Connecticut','Delaware','Florida','Georgia','Hawaii','Idaho','Illinois','Indiana','Iowa','Kansas','Kentucky','Louisiana','Maine','Maryland','Massachusetts','Michigan','Minnesota','Mississippi','Missouri','Montana','Nebraska','Nevada','New Hampshire','New Jersey','New Mexico','New York','North Carolina','North Dakota','Ohio','Oklahoma','Oregon','Pennsylvania','Rhode Island','South Carolina','South Dakota','Tennessee','Texas','Utah','Vermont','Virginia','Washington','West Virginia','Wisconsin','Wyoming'} ## defining list of state in america to provide list to user using OptionMenu
## defining entry widget for each variable with width of whitespace and cordinate to which placed
SidE = Entry(window, textvariable=Sid, width=30) 
SidE.place(x=250, y=60)

FnameE = Entry(window, textvariable=Fname, width=30)
FnameE.place(x=250, y=100)

LnameE = Entry(window, textvariable=Lname, width=30)
LnameE.place(x=250, y=140)

ContactE = Entry(window, textvariable=Contact, width=30)
ContactE.place(x=250, y=180)

CityE = Entry(window, textvariable=City, width=30)
CityE.place(x=250, y=220)

StateE = OptionMenu(window, State, *StateD)
StateE.place(x=250, y=260)

Search_valE = Entry(window, textvariable=Search_val, width=30) 
Search_valE.place(x=250, y=540)

frame = Frame(window) ## using frame for showing content as treeview
frame.place(x=20, y=400) ## placing the frame
tree = ttk.Treeview(frame, columns=(1, 2, 3, 4, 5, 6), height=5, show="headings") ## creating treeview with 6 columns
tree.pack(side='left') ## using side as left
## adding scroll bar to the data  
scroll = ttk.Scrollbar(frame, orient="vertical", command=tree.yview) ## vertical scrolling
scroll.pack(side='right', fill='y') ## placing scroll bar
tree.configure(xscrollcommand=scroll.set) ## added scroll bar
tree.delete(*tree.get_children()) ## to clear the tree view
tree.heading(1, text="StudentID") 
tree.column(1, minwidth=0, width=125, stretch='NO',anchor='center') ## placing in center with no stretch
tree.heading(2, text="FirstName")
tree.column(2, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(3, text="LastName")
tree.column(3, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(4, text="Contact No.")
tree.column(4, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(5, text="City")
tree.column(5, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(6, text="State")
tree.column(6, minwidth=0, width=125, stretch='NO', anchor='center')
data = c.fetchall() ## fetching the data
for val in data: ## inserting each row
    tree.insert('', 'end', values=(val[0], val[1], val[2], val[3], val[4], val[5])) 
conn.commit() ## commiting the connection

def get(): ## for inserting the data
    c.execute('CREATE TABLE IF NOT EXISTS database (Sid INTEGER NOT NULL UNIQUE,Fname TEXT,Lname TEXT, Contact INTEGER UNIQUE,City TEXT,State TEXT)') ## create the table with contraint Sid unique and not null and contact unique
    c.execute('INSERT INTO database (Sid,Fname,Lname,Contact,City,State) VALUES (?, ?, ?, ?, ?, ?)',(Sid.get(), Fname.get(), Lname.get(), Contact.get(), City.get(), State.get())) ## inserting the data
    c.commit()  ## commiting the connection
    clear() ## clearing all labels

def selectItem(a): ## when a row of treeview is selected
    curItem = tree.focus() ## focus on current row
    for i in tree.item(curItem).values():
      if isinstance(i, list) and len(i)==6: ## if given data is list
        ## setting all data back on the labels
        Sid.set(i[0])
        Fname.set(i[1])
        Lname.set(i[2])
        Contact.set(i[3])
        City.set(i[4])
        State.set(i[5])
  
def open_db(): ## for displaying data using treeView
    c.execute('SELECT * FROM database')  ## selecting data from database
    tree.delete(*tree.get_children()) ## to clear the tree view
    tree.heading(1, text="StudentID") 
    tree.column(1, minwidth=0, width=125, stretch='NO',anchor='center') ## placing in center with no stretch
    tree.heading(2, text="FirstName")
    tree.column(2, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(3, text="LastName")
    tree.column(3, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(4, text="Contact No.")
    tree.column(4, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(5, text="City")
    tree.column(5, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(6, text="State")
    tree.column(6, minwidth=0, width=125, stretch='NO', anchor='center')
    data = c.fetchall() ## fetching the data
    for val in data: ## inserting each row
        tree.insert('', 'end', values=(val[0], val[1], val[2], val[3], val[4], val[5])) 
    conn.commit() ## commiting the connection

tree.bind('<<TreeviewSelect>>', selectItem) ## when a item is selected on treeview

def update_db(): ## updating the data using id of the student
    c.execute('UPDATE database SET Fname=?,Lname=?,Contact=?,City=?,State=? WHERE Sid=?',(Fname.get(),Lname.get(),Contact.get(),City.get(),State.get(),Sid.get()))
    conn.commit()
    clear()

def delete_db():  ## delete the data using id of student
    c.execute('DELETE FROM database WHERE Sid=?',(Sid.get(),))
    conn.commit()
    clear()

def terminate():
    os.kill(os.getpid(),signal.SIGTERM) 

def search():
    c.execute('SELECT * FROM database')
    data = c.fetchall()
    for d in data: ## checking in the data
      if(Search_val.get()==d[0]): ## matching the student id
        messagebox.showinfo("showinfo","Student is found")
        return
    messagebox.showerror("showerror","Student is not founnd")
        

def create_button(txt, x_cord, y_cord, cmd): ## designing buttion show it will change color on hovering
    def on_enter(e): ## on coming cursor invert the color
        button['activebackground'] = 'orange'
        button['activeforeground'] = 'white'

    def on_leave(e): ## on going cursor invert the color
        button['activebackground'] = 'white'
        button['activeforeground'] = 'orange'

    button = Button(window,text=txt,font=("arial", 12),command=cmd,bg='white',fg='chocolate1',bd=0.1,highlightbackground='orange')
    button.place(x=x_cord, y=y_cord)
    button.bind("<Enter>", on_enter)
    button.bind("<Leave>", on_leave)
    return button

## adding buttons
button_1 = create_button('Register',100,320, get)
butoon_2 = create_button('Update', 200,320,update_db)
butoon_3 = create_button('Delete', 300,320,delete_db)
button_4 = create_button('Clear', 400,320, clear)
button_5 = create_button('Show All', 500,320, open_db)
button_6 = create_button('Search',500,540,search)
button_7 = create_button('EXIT',350,580,terminate)

window.mainloop()```

CodePudding user response:

It doesn't print Student because you don't have code for this. You have to put data in widgets.

    c.execute('SELECT * FROM database WHERE Sid = ?', (sid,))
    data = c.fetchall()
    number = len(data)
    if number == 1: 
        i = data[0]
        Sid.set(i[0])
        Fname.set(i[1])
        Lname.set(i[2])
        Contact.set(i[3])
        City.set(i[4])
        State.set(i[5])
        messagebox.showinfo("showinfo", "Student is found")

Working example with other changes.

import signal ## for giving killing signal
from tkinter import * # importing tkinter
# importing each needed from tkinter
from tkinter import Label 
from tkinter import StringVar
from tkinter import IntVar
from tkinter import Entry
from tkinter import OptionMenu
from tkinter import Frame
from tkinter import Button
from tkinter import ttk
from tkinter import messagebox
# importing squlite3 as sq
import sqlite3 as sq
import os
#

def clear(): ## for clearing all Entry
    Sid.set('')
    Fname.set('')
    Lname.set('')
    Contact.set('')
    City.set('')
    State.set('Choose from List')
    Search_val.set('')
    
def get(): ## for inserting the data
    c.execute('CREATE TABLE IF NOT EXISTS database (Sid INTEGER NOT NULL UNIQUE,Fname TEXT,Lname TEXT, Contact INTEGER UNIQUE,City TEXT,State TEXT)') ## create the table with contraint Sid unique and not null and contact unique
    c.execute('INSERT INTO database (Sid,Fname,Lname,Contact,City,State) VALUES (?, ?, ?, ?, ?, ?)',(Sid.get(), Fname.get(), Lname.get(), Contact.get(), City.get(), State.get())) ## inserting the data
    conn.commit()  ## commiting the connection
    clear() ## clearing all labels
    # --- instert data to treeview
    tree.insert('', 'end', values=(Sid.get(), Fname.get(), Lname.get(), Contact.get(), City.get(), State.get()))

def selectItem(a): ## when a row of treeview is selected
    curItem = tree.focus() ## focus on current row
    for i in tree.item(curItem).values():
      if isinstance(i, list) and len(i)==6: ## if given data is list
        ## setting all data back on the labels
        Sid.set(i[0])
        Fname.set(i[1])
        Lname.set(i[2])
        Contact.set(i[3])
        City.set(i[4])
        State.set(i[5])
  
def open_db(): ## for displaying data using treeView
    c.execute('SELECT * FROM database')  ## selecting data from database
    tree.delete(*tree.get_children()) ## to clear the tree view
    
    tree.heading(1, text="StudentID") 
    tree.column(1, minwidth=0, width=125, stretch='NO',anchor='center') ## placing in center with no stretch
    tree.heading(2, text="FirstName")
    tree.column(2, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(3, text="LastName")
    tree.column(3, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(4, text="Contact No.")
    tree.column(4, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(5, text="City")
    tree.column(5, minwidth=0, width=125, stretch='NO', anchor='center')
    tree.heading(6, text="State")
    tree.column(6, minwidth=0, width=125, stretch='NO', anchor='center')
    data = c.fetchall() ## fetching the data
    for val in data: ## inserting each row
        tree.insert('', 'end', values=(val[0], val[1], val[2], val[3], val[4], val[5])) 
    conn.commit() ## commiting the connection

def update_db(): ## updating the data using id of the student
    c.execute('UPDATE database SET Fname=?,Lname=?,Contact=?,City=?,State=? WHERE Sid=?',(Fname.get(),Lname.get(),Contact.get(),City.get(),State.get(),Sid.get()))
    conn.commit()
    clear()

def delete_db():  ## delete the data using id of student
    c.execute('DELETE FROM database WHERE Sid=?',(Sid.get(),))
    conn.commit()
    clear()

def terminate():
    os.kill(os.getpid(),signal.SIGTERM) 

def search():
    sid = Search_val.get()
    print(sid)
    c.execute('SELECT * FROM database WHERE Sid = ?', (sid,))
    data = c.fetchall()
    number = len(data)
    if number == 1: 
        i = data[0]
        Sid.set(i[0])
        Fname.set(i[1])
        Lname.set(i[2])
        Contact.set(i[3])
        City.set(i[4])
        State.set(i[5])
        messagebox.showinfo("showinfo", "Student is found")
    elif number == 0: ## matching the student id
        messagebox.showerror("showerror", "Student is not founnd")
    elif number > 1:
        messagebox.showerror("showerror","Mistake in database: Too much students with the same ID")

def create_button(txt, x_cord, y_cord, cmd): ## designing buttion show it will change color on hovering
    def on_enter(e): ## on coming cursor invert the color
        button['activebackground'] = 'orange'
        button['activeforeground'] = 'white'

    def on_leave(e): ## on going cursor invert the color
        button['activebackground'] = 'white'
        button['activeforeground'] = 'orange'

    button = Button(window,text=txt,font=("arial", 12),command=cmd,bg='white',fg='chocolate1',bd=0.1,highlightbackground='orange')
    button.place(x=x_cord, y=y_cord)
    button.bind("<Enter>", on_enter)
    button.bind("<Leave>", on_leave)
    return button

# --- main ---

conn = sq.connect('database.db')  ## connecting with the database
c = conn.cursor()  #creating a cursor object

window = Tk() #to declare window
window.title("Student Management System") # title of the window

screen_width = window.winfo_screenwidth() ## getting screen width
screen_height = window.winfo_screenheight() ## getting screen height
x_cord = int((screen_width / 2) - 400) ## x cordinate of window
y_cord = int((screen_height / 2) - 300) ## y cordinate of window
window.geometry('%dx%d %d %d' % (800, 600, x_cord, y_cord)) ## setting window in the center

header = Label(window, text="Student Management System", font=("arial", 24, "bold"),).pack() ## header label with suitable font and colour

L1 = Label(window, text="StudentID",font=("arial", 16)).place(x=100, y=60) ## label for student id at position 100,60
L2 = Label(window, text="FirstName", font=("arial", 16)).place(x=100, y=100) ## label for firstname at position 100,100
L3 = Label(window, text="LastName", font=("arial", 16)).place(x=100, y=140) ## label for lastname at position 100,140
L4 = Label(window, text="Contact No.",font=("arial", 16)).place(x=100, y=180) ## label for contact no. at position 100,180
L5 = Label(window, text="City", font=("arial", 16)).place(x=100, y=220) ## label for city at position 100,220
L6 = Label(window, text="Enter State", font=("arial", 16)).place(x=100, y=260) ## label for state at position 100,260
L7 = Label(window, text="Please select one record below to update or delete", font=("arial", 12)).place(x=100, y=360) ## label for state at position 100,260
L8 = Label(window, text="Enter Student ID", font=("arial", 12)).place(x=100, y=540) ## label for sid at position 100,540

Sid = IntVar(window) ## type of studentID as int
Fname = StringVar(window) ## type of first name as string
Lname = StringVar(window) ## type of last name as string
Contact = IntVar(window) ## type of contact as int
City = StringVar(window) ## type of city as string
State = StringVar(window) ## type of state as string
Search_val = IntVar(window) ## type of searchVal as int

StateD = {'Alabama','Alaska','Arizona','Arkansas','California','Colorado','Connecticut','Delaware','Florida','Georgia','Hawaii','Idaho','Illinois','Indiana','Iowa','Kansas','Kentucky','Louisiana','Maine','Maryland','Massachusetts','Michigan','Minnesota','Mississippi','Missouri','Montana','Nebraska','Nevada','New Hampshire','New Jersey','New Mexico','New York','North Carolina','North Dakota','Ohio','Oklahoma','Oregon','Pennsylvania','Rhode Island','South Carolina','South Dakota','Tennessee','Texas','Utah','Vermont','Virginia','Washington','West Virginia','Wisconsin','Wyoming'} ## defining list of state in america to provide list to user using OptionMenu
## defining entry widget for each variable with width of whitespace and cordinate to which placed
SidE = Entry(window, textvariable=Sid, width=30) 
SidE.place(x=250, y=60)

FnameE = Entry(window, textvariable=Fname, width=30)
FnameE.place(x=250, y=100)

LnameE = Entry(window, textvariable=Lname, width=30)
LnameE.place(x=250, y=140)

ContactE = Entry(window, textvariable=Contact, width=30)
ContactE.place(x=250, y=180)

CityE = Entry(window, textvariable=City, width=30)
CityE.place(x=250, y=220)

StateE = OptionMenu(window, State, *StateD)
StateE.place(x=250, y=260)

Search_valE = Entry(window, textvariable=Search_val, width=30) 
Search_valE.place(x=250, y=540)

frame = Frame(window) ## using frame for showing content as treeview
frame.place(x=20, y=400) ## placing the frame
tree = ttk.Treeview(frame, columns=(1, 2, 3, 4, 5, 6), height=5, show="headings") ## creating treeview with 6 columns
tree.pack(side='left') ## using side as left
## adding scroll bar to the data  
scroll = ttk.Scrollbar(frame, orient="vertical", command=tree.yview) ## vertical scrolling
scroll.pack(side='right', fill='y') ## placing scroll bar
tree.configure(xscrollcommand=scroll.set) ## added scroll bar
tree.delete(*tree.get_children()) ## to clear the tree view
tree.heading(1, text="StudentID") 
tree.column(1, minwidth=0, width=125, stretch='NO',anchor='center') ## placing in center with no stretch
tree.heading(2, text="FirstName")
tree.column(2, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(3, text="LastName")
tree.column(3, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(4, text="Contact No.")
tree.column(4, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(5, text="City")
tree.column(5, minwidth=0, width=125, stretch='NO', anchor='center')
tree.heading(6, text="State")
tree.column(6, minwidth=0, width=125, stretch='NO', anchor='center')

tree.bind('<<TreeviewSelect>>', selectItem) ## when a item is selected on treeview

## adding buttons
button_1 = create_button('Register',100,320, get)
butoon_2 = create_button('Update', 200,320,update_db)
butoon_3 = create_button('Delete', 300,320,delete_db)
button_4 = create_button('Clear', 400,320, clear)
button_5 = create_button('Show All', 500,320, open_db)
button_6 = create_button('Search',500,540,search)
button_7 = create_button('EXIT',350,580,terminate)

# --- filling table ---

c.execute('SELECT * FROM database')
data = c.fetchall() ## fetching the data

for val in data: ## inserting each row
    tree.insert('', 'end', values=(val[0], val[1], val[2], val[3], val[4], val[5])) 

# ---

window.mainloop()
  • Related