Python-MySQL Interface project (tkinter)

from tkinter import *

from tkinter import ttk

from tkinter import messagebox

import mysql.connector

import csv

win = Tk()

win.geometry("300x350+0+0")

win.title("Customer Database") 

################## Notebook & Tabs #######################

Label(win, text="CUSTOMER DATABASE", font=("Arial", 16, "bold")).place(x=25, y=10) 

nbk = ttk.Notebook(win)

tabAdd = Frame(nbk, width=300, height=270, bg="#FFFF5A")

tabDisplay = Frame(nbk, width=300, height=290, bg="#ED93A8")

tabSearch = Frame(nbk, width=300, height=290, bg="#7ECFE8")

tabDelete = Frame(nbk, width=300, height=290, bg="#66FF66")

tabUpdate = Frame(nbk, width=300, height=290, bg="#FFA500")

nbk.add(tabAdd, text ='Add')

nbk.add(tabDisplay, text ='Display')

nbk.add(tabSearch, text ='Search')

nbk.add(tabDelete, text ='Delete')

nbk.add(tabUpdate, text ='Update')

nbk.place(x=0, y=40) 

lblAdd = Label(tabAdd, text="Add Records", font=("Arial", 12, "bold"), bg="#FFFF5A")

lblAdd.place(x=90, y=5)   

lblDisplay = Label(tabDisplay, text="Display Records", font=("Arial", 12, "bold"), bg="#ED93A8")

lblDisplay.place(x=76, y=5) 

lblSearch = Label(tabSearch, text="Search", font=("Arial", 12, "bold"), bg="#7ECFE8")

lblSearch.place(x=112, y=5)

lblDelete = Label(tabDelete, text="Delete", font=("Arial", 12, "bold"), bg="#66FF66")

lblDelete.place(x=112, y=5)

lblUpdate = Label(tabUpdate, text="Update", font=("Arial", 12, "bold"), bg="#FFA500")

lblUpdate.place(x=112, y=5)

################## clearFieldsAdd Function #################

def clearFieldsAdd():

  entCIdAdd.delete(0, END);    entFNameAdd.delete(0, END)

  entLNameAdd.delete(0, END);  entCityAdd.delete(0, END)

  entPinAdd.delete(0, END);

################## addCustomer Function ##################

def addCustomer():

  conn = mysql.connector.connect(

    host="localhost", user="root", passwd="bont", database="binoy")

  sql = "INSERT INTO customers(cid, fname, lname, city, pin) VALUES(%s, %s, %s, %s, %s)"

  values = (entCIdAdd.get(), entFNameAdd.get(), entLNameAdd.get(), entCityAdd.get(), entPinAdd.get())

  cur = conn.cursor()

  if entCIdAdd.get() != "" and entFNameAdd.get() != "" and entLNameAdd.get() != ""\

    and entCityAdd.get() != "" and entPinAdd.get() != "":

    cur.execute(sql, values)

  else:

    messagebox.showinfo("WARNING", "No field can be empty")

  conn.commit()

  clearFieldsAdd()

  conn.close()

################## displayAll Function ##################

# Dummy label to accommodate heading label

lblDummy1 = Label(tabDisplay, bg="#ED93A8", font=("Arial", 24))

lblDummy1.grid(row=0)

def displayAll():

  conn = mysql.connector.connect(

    host="localhost", user="root", passwd="bont", database="binoy")

  cur = conn.cursor()

  cur.execute("SELECT * FROM customers ORDER BY cid")

  records = cur.fetchall()

  for index, record in enumerate(records):

    num = 0

    for field in record:

      lookupLabel = Label(tabDisplay, text=field,  bg="#ED93A8", )

      lookupLabel.grid(row=index+1, column=num)  # +1 to accommodate dummy label

      num += 1

  conn.close()

################## search Function ##################

def search():

  conn = mysql.connector.connect(

    host="localhost", user="root", passwd="bont", database="binoy")

  cur = conn.cursor()

  cur.execute("SELECT * FROM customers WHERE lname = '" + entlname.get() + "'")

  records = cur.fetchall()

  with open("Search.csv", 'w', newline='') as out:

    w = csv.writer(out, dialect='excel')

    for record in records:

      w.writerow(record)

  conn.close()  

  import os

  os.system('start Search.csv')

################## delete Function ##################

def delete():

  if entCIdDelete.get() == "":

    messagebox.showinfo("WARNING", "Entry box cannot be blank")

    return

  conn = mysql.connector.connect(

    host="localhost", user="root", passwd="bont", database="binoy")

  cur = conn.cursor()

  cur.execute("SELECT * FROM customers WHERE cid = '" + entCIdDelete.get() + "'")

  record = cur.fetchone()

  if not record:

    messagebox.showinfo("WARNING", "Record does not exist")

  else:

    s = "Are you sure you want to delete\n"

    for field in record:

      s += str(field) + " "

    s += "?"

    ans = messagebox.askyesno("WARNING", s)

    if ans == True:

      cur.execute("DELETE FROM customers WHERE cid = '" + entCIdDelete.get() + "'")

      conn.commit()

  conn.close()

################## clearFieldsUpdate Function #################

def clearFieldsUpdate():

  entCIdUpdate.delete(0, END);    entFNameUpdate.delete(0, END)

  entLNameUpdate.delete(0, END);  entCityUpdate.delete(0, END)

  entPinUpdate.delete(0, END);

################## update Function ##################

def show():

  entCIdUpdate.config(state=DISABLED)

  cid = entCIdUpdate.get()

  if cid == "":

    messagebox.showinfo("WARNING", "Entry box cannot be blank")

    return

  conn = mysql.connector.connect(

    host="localhost", user="root", passwd="bont", database="binoy")

  cur = conn.cursor()

  cur.execute("SELECT * FROM customers WHERE cid = '" + cid + "'")

  record = cur.fetchone()

  if not record:

    messagebox.showinfo("WARNING", "Record does not exist")

  else:

    entFNameUpdate.delete(0, END);  entFNameUpdate.insert(0, record[1])

    entLNameUpdate.delete(0, END);  entLNameUpdate.insert(0, record[2])

    entCityUpdate.delete(0, END);   entCityUpdate.insert(0, record[3])

    entPinUpdate.delete(0, END);    entPinUpdate.insert(0, record[4])

  conn.close()

################## update Function ##################

def update():

  cid = entCIdUpdate.get()

  conn = mysql.connector.connect(

    host="localhost", user="root", passwd="bont", database="binoy")

  cur = conn.cursor()

  ans = messagebox.askyesno("WARNING", "Are you sure you wish to update?")

  if ans == True:

    cur.execute("UPDATE customers SET fname = '" + entFNameUpdate.get() + "' WHERE cid = '" + cid + "'")

    cur.execute("UPDATE customers SET lname = '" + entLNameUpdate.get() + "' WHERE cid = '" + cid + "'")

    cur.execute("UPDATE customers SET city  = '" + entCityUpdate.get()  + "' WHERE cid = '" + cid + "'")

    cur.execute("UPDATE customers SET pin   = '" + entPinUpdate.get()   + "' WHERE cid = '" + cid + "'")

    conn.commit()

  conn.close()

  entCIdUpdate['state'] = NORMAL

  clearFieldsUpdate()

################## "addRecords" Widgets ##################

lblCIdAdd = Label(tabAdd, text="Customer Id:", font=("Arial", 11), bg="#FFFF5A")

lblCIdAdd.place(x=0, y=38)

entCIdAdd = Entry(tabAdd, font=("Arial", 11))

entCIdAdd.place(x=100, y=38)

lblFNameAdd = Label(tabAdd, text="First Name:", font=("Arial", 11), bg="#FFFF5A")

lblFNameAdd.place(x=0, y=73)

entFNameAdd = Entry(tabAdd, font=("Arial", 11))

entFNameAdd.place(x=100, y=73)

lblLNameAdd = Label(tabAdd, text="Last Name:", font=("Arial", 11), bg="#FFFF5A")

lblLNameAdd.place(x=0, y=108)

entLNameAdd = Entry(tabAdd, font=("Arial", 11))

entLNameAdd.place(x=100, y=108)

lblCityAdd = Label(tabAdd, text="City:", font=("Arial", 11), bg="#FFFF5A")

lblCityAdd.place(x=0, y=143)

entCityAdd = Entry(tabAdd, font=("Arial", 11))

entCityAdd.place(x=100, y=143)

lblPinAdd = Label(tabAdd, text="Pin Code:", font=("Arial", 11), bg="#FFFF5A")

lblPinAdd.place(x=0, y=178)

entPinAdd = Entry(tabAdd, font=("Arial", 11))

entPinAdd.place(x=100, y=178)

btnAdd = Button(tabAdd, text="Add Record", command=addCustomer)

btnAdd.place(x=55, y=230)

btnClearAdd = Button(tabAdd, text="Clear Fields", command=clearFieldsAdd)

btnClearAdd.place(x=155, y=230)

################## "displayAll" Widgets ##################

btnDisplay = Button(tabDisplay, text="Display All", command=displayAll)

btnDisplay.place(x=120, y=230)

################## "search" Widgets ##################

btnSearch = Button(tabSearch, text="Search", command=search)

btnSearch.place(x=110, y=230)

Label(tabSearch, text="Enter last name:", font=("Arial", 11), bg="#7ECFE8").place(x=15, y=50)

entlname = Entry(tabSearch, font=("Arial", 11), width=12)

entlname.place(x=135, y=50)

################## "delete" Widgets ##################

btnDelete = Button(tabDelete, text="Delete", command=delete)

btnDelete.place(x=110, y=230)

Label(tabDelete, text="Enter customer id:", font=("Arial", 11), bg="#66FF66").place(x=5, y=50)

entCIdDelete = Entry(tabDelete, font=("Arial", 11), width=12)

entCIdDelete.place(x=135, y=50)

################## "update" Widgets ##################

lblCIdUpdate = Label(tabUpdate, text="Customer Id:", font=("Arial", 11), bg="#FFA500")

lblCIdUpdate.place(x=0, y=38)

entCIdUpdate = Entry(tabUpdate, font=("Arial", 11))

entCIdUpdate.place(x=100, y=38)

lblFNameUpdate = Label(tabUpdate, text="First Name:", font=("Arial", 11), bg="#FFA500")

lblFNameUpdate.place(x=0, y=73)

entFNameUpdate = Entry(tabUpdate, font=("Arial", 11))

entFNameUpdate.place(x=100, y=73)

lblLNameUpdate = Label(tabUpdate, text="Last Name:", font=("Arial", 11), bg="#FFA500")

lblLNameUpdate.place(x=0, y=108)

entLNameUpdate = Entry(tabUpdate, font=("Arial", 11))

entLNameUpdate.place(x=100, y=108)

lblCityUpdate = Label(tabUpdate, text="City:", font=("Arial", 11), bg="#FFA500")

lblCityUpdate.place(x=0, y=143)

entCityUpdate = Entry(tabUpdate, font=("Arial", 11))

entCityUpdate.place(x=100, y=143)

lblPinUpdate = Label(tabUpdate, text="Pin Code:", font=("Arial", 11), bg="#FFA500")

lblPinUpdate.place(x=0, y=178)

entPinUpdate = Entry(tabUpdate, font=("Arial", 11))

entPinUpdate.place(x=100, y=178)

btnShow = Button(tabUpdate, text="Show Record", command=show)

btnShow.place(x=15, y=230)

btnUpdate = Button(tabUpdate, text="Update Record", command=update)

btnUpdate.place(x=105, y=230)

btnClearUpdate = Button(tabUpdate, text="Clear Fields", command=clearFieldsUpdate)

btnClearUpdate.place(x=210, y=230)

win.mainloop()