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()

Threading

from tkinter import *

from time import sleep

from threading import Thread

win = Tk()

win.geometry("500x400")

ctr1 = 0

def func1():

  for i in range(10):

    global ctr1

    ctr1 += 1

    lbl1.config(text=str(ctr1))

    sleep(1)

lbl1 = Label(win);  lbl1.pack()

t1 = Thread(target=func1, args=())

t1.start()

ctr2 = 0

def func2():

  for i in range(10):

    global ctr2

    ctr2 -= 1

    lbl2.config(text=str(ctr2))

    sleep(1)

lbl2 = Label(win);  lbl2.pack()

t2 = Thread(target=func2, args=())

t2.start()

win.mainloop()

Exe using pyinstaller

a) pip install pyinstaller

b) On the command prompt, go to the folder where your .py file is located.

c) Type "pyinstaller --onefile file.py".

d) A number of folders and files will be created. Take the .exe file from the "dist" folder just created and paste it in the same folder as your .py file.

e) Run it.

tkinter & SQLite

from tkinter import *

import sqlite3

win = Tk()

win.title("SQLite Database with tkinter")

win.geometry("400x250")

S1, S2, S3, S4, S5 = StringVar(), StringVar(), StringVar(), StringVar(), StringVar()

def create():

  conn = sqlite3.connect('address_book.db')

  cur = conn.cursor()

  query = '''CREATE TABLE addresses(fname VARCHAR(15), lname VARCHAR(15), city VARCHAR(15), pin INT)'''

  cur.execute(query)

  conn.commit()

  conn.close()

def submit():

  conn = sqlite3.connect('address_book.db')

  fn, ln, ci, pi = entFName.get(), entLName.get(), entCity.get(), int(entPin.get())

  cur = conn.cursor()

  cur.execute('''INSERT INTO addresses(fname, lname, city, pin)

              VALUES(?, ?, ?, ?)''', (fn, ln, ci, pi))

  conn.commit()

  conn.close()

  entFName.delete(0, END)

  entLName.delete(0, END)

  entCity.delete(0, END)

  entPin.delete(0, END)

def display():

  conn = sqlite3.connect('address_book.db')

  cur = conn.cursor()

  cur.execute("SELECT oid, * FROM addresses")

  records = cur.fetchall()

  print_records = ''

  for record in records:

    print_records += str(record) + "\n"

  lblQuery.config(text=print_records)

  conn.commit()

  conn.close()

def delete():

  conn = sqlite3.connect('address_book.db')

  cur = conn.cursor()

  cur.execute("DELETE FROM addresses WHERE oid = " + entID.get())

  conn.commit()

  conn.close()

def update():

  conn = sqlite3.connect('address_book.db')

  cur = conn.cursor()

  cur.execute("SELECT * FROM addresses WHERE oid = " + entID.get())

  record = cur.fetchall()

  S1.set(record[0][0]);  S2.set(record[0][1])

  S3.set(record[0][2]);  S4.set(record[0][3])

  conn.commit()

  conn.close()

create()

lbl01    = Label(win, text="First Name").place(x=0, y=0)

entFName = Entry(win, width=20, textvariable=S1);  entFName.place(x=80, y=0)

lbl02    = Label(win, text="Last Name").place(x=0, y=30)

entLName = Entry(win, width=20, textvariable=S2);  entLName.place(x=80, y=30)

lbl03    = Label(win, text="City").place(x=0, y=60)

entCity  = Entry(win, width=20, textvariable=S3);  entCity.place(x=80, y=60)

lbl04    = Label(win, text="Pin Code").place(x=0, y=90)

entPin   = Entry(win, width=10, textvariable=S4);  entPin.place(x=80, y=90)

btnAdd = Button(win, text="Add Record", command=submit)

btnAdd.place(x=20, y=120)

btnShowAll = Button(win, text="Show All", command=display)

btnShowAll.place(x=120, y=120)

lblQuery = Label(win);  lblQuery.place(x=0, y=170)

S5.set("3")

entID = Entry(win, width=3, textvariable=S5);  entID.place(x=295, y=90)

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

btnDelete.place(x=210, y=120)

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

btnUpdate.place(x=280, y=120)

win.mainloop()

Download YouTube videos

# pip install youtube_dl pafy requests --upgrade

import pafy
 
url = "https://www.youtube.com/watch?v=rKyaBXYv4l4"
video = pafy.new(url)

best = video.getbest(preftype ="mp4")
best.download(filepath=".")