CRUD Operations In Python With Source Code

CRUD Operations In Python is abbreviated as Create, Read, Update, and Delete.

In a database, these four processes are essential. We will develop a sample database and perform various operations on it.

Project Details and Technology

About CRUD Operations Project

This CRUD Operations in Python are written in Python and use the MySQL database. In this tutorial, I’ll show you how to develop a Python crud operation that uses MySQL.

Create, Read, Update, and Delete is abbreviated as CRUD. In a database, these four processes are essential.

Major Functionalities of The Project

• CREATE – This refers to the insertion of new data into the table. Data is inserted in the form of a tuple. The number of attributes in the tuple must be equal to that defined in the relation schema while creating the table.
  
• READ – This refers to reading data from a database. A read statement has three clauses:
  SELECT: Takes as the predicate the attributes to be queried, use * for all attributes.
  FROM: Takes as the predicate a relation.
  WHERE: Takes as the predicate a condition, this is not compulsory.
  
• UPDATE – This refers to the updating of tuple values already present in the table.
  
• DELETE – This refers to the deletion of the tuple present in the table.

How do you perform a CRUD operation in Python?

1. Create a connection under PyCharm and MySQL Database / Database Connectivity.

First, create a connection under PyCharm and MySQL Database, and named it as “CRUD“.

import mysql.connector
from mysql.connector import Error
try:
conn = mysql.connector.connect(host='localhost',
database='CRUD',
user='root',
password='')
mycursor = conn.cursor()

2. Main functions.

Next, create a Graphical User Interface (GUI) to manage all function of CRUD Operations.

from tkinter import *
from tkinter import messagebox
import os
import sys
from tkinter import ttk

import mysql.connector
from mysql.connector import Error

py=sys.executable

#creating window
class MainWin(Tk):
    def __init__(self):
        super().__init__()
        self.configure(bg='gray')
        self.canvas = Canvas(width=1366, height=768, bg='gray')
        self.canvas.pack()
        self.maxsize(1320, 768)
        self.minsize(1320,768)
        self.state('zoomed')
        self.title('CRUD Operation In Python')
        self.a = StringVar()
        self.b = StringVar()
        self.mymenu = Menu(self)
#calling scripts

        def ib():
            os.system('%s %s' % (py, 'Update.py'))

        def ret():
            os.system('%s %s' % (py, 'Delete.py'))

        def sea():
            os.system('%s %s' % (py,'Add.py'))


#creating table

        self.listTree = ttk.Treeview(self,height=14,columns=('First Name','Last Name','Gender','Address','Contact Number','Course'))
        self.vsb = ttk.Scrollbar(self,orient="vertical",command=self.listTree.yview)
        self.hsb = ttk.Scrollbar(self,orient="horizontal",command=self.listTree.xview)
        self.listTree.configure(yscrollcommand=self.vsb.set,xscrollcommand=self.hsb.set)
        self.listTree.heading("#0", text='ID')
        self.listTree.column("#0", width=50,minwidth=50,anchor='center')
        self.listTree.heading("First Name", text='First Name')
        self.listTree.column("First Name", width=200, minwidth=200,anchor='center')
        self.listTree.heading("Last Name", text='Last Name')
        self.listTree.column("Last Name", width=200, minwidth=200,anchor='center')
        self.listTree.heading("Gender", text='Gender')
        self.listTree.column("Gender", width=125, minwidth=125,anchor='center')
        self.listTree.heading("Address", text='Address')
        self.listTree.column("Address", width=125, minwidth=125, anchor='center')
        self.listTree.heading("Contact Number", text='Contact Number')
        self.listTree.column("Contact Number", width=125, minwidth=125, anchor='center')
        self.listTree.heading("Course", text='Course')
        self.listTree.column("Course", width=125, minwidth=125, anchor='center')
        self.listTree.place(x=200,y=360)
        self.vsb.place(x=1150,y=361,height=287)
        self.hsb.place(x=200,y=650,width=966)
        ttk.Style().configure("Treeview",font=('Times new Roman',15))


        def ser():
             try:
                

                cursor.execute("Select * from tbl_student")
                pc = cursor.fetchall()
                if pc:
                    self.listTree.delete(*self.listTree.get_children())
                    for row in pc:
                        self.listTree.insert("",'end',text=row[0] ,values = (row[1],row[2],row[3],row[4],row[5],row[6]))
                else:
                    messagebox.showinfo("Error", "No Student!")
             except Error:
                #print(Error)
              messagebox.showerror("Error","Something Goes Wrong")

        def check():

                    #label and input box
                    self.label3 = Label(self, text='CRUD Operation In Python',fg='black',bg="gray" ,font=('Courier new', 30, 'bold'))
                    self.label3.place(x=350, y=22)
                    self.label6 = Label(self, text="STUDENT INFORMATION DETAILS",bg="gray",  font=('Courier new', 15, 'underline', 'bold'))
                    self.label6.place(x=560, y=300)
                    self.button = Button(self, text='View Student(s)', width=25,bg='blue', font=('Courier new', 10), command=ser).place(x=240,y=250)
                    self.button = Button(self, text='Add Student', width=25,bg='green', font=('Courier new', 10), command=sea).place(x=520,y=250)
                    self.brt = Button(self, text="Update Student", width=15,bg='orange', font=('Courier new', 10), command=ib).place(x=800, y=250)
                    self.brt = Button(self, text="Delete Student", width=15,bg='red', font=('Courier new', 10), command=ret).place(x=1000, y=250)

        check()

MainWin().mainloop()

3. Create form for Adding Data into MySQL Database.

Next, Create a form and function for inserting data into MySQL Database.

from tkinter import *
from tkinter import messagebox
from tkinter import filedialog
import os
import sys
import mysql.connector
from mysql.connector import Error
py = sys.executable

#creating window
class Add(Tk):
    def __init__(self):
        super().__init__()
        self.maxsize(500,417)
        self.minsize(500,417)
        self.title('Add Student')
        self.canvas = Canvas(width=500, height=417, bg='gray')
        self.canvas.pack()
        fname = StringVar()
        lname = StringVar()
        cn = StringVar()
        gender = StringVar()
        add = StringVar()
        c = StringVar()
#verifying input
        def asi():
                try:
                    first = fname.get()
                    last = lname.get()
                    contact = cn.get()
                    gend = gender.get()
                    address = add.get()
                    course = c.get()
                    self.myCursor.execute("Insert into tbl_student(FirstName,LastName,Gender,Address,ContactNumber,Course) values (%s,%s,%s,%s,%s,%s)",[first,last,gend,address,contact,course])
                    self.conn.commit()
                    messagebox.showinfo("Done","Student Inserted Successfully")
                    ask = messagebox.askyesno("Confirm","Do you want to add another student?")
                    if ask:
                     self.destroy()
                     os.system('%s %s' % (py, 'Add.py'))
                    else:
                     self.destroy()
                     self.myCursor.close()
                     self.conn.close()
                except Error:
                    messagebox.showerror("Error","Something goes wrong")

        # label and input box
        Label(self, text='Student Details',bg='gray', fg='white', font=('Courier new', 25, 'bold')).pack()
        Label(self, text='First Name:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=102)
        Entry(self, textvariable=fname, width=30).place(x=200, y=104)
        Label(self, text='Last Name:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=150)
        Entry(self, textvariable=lname, width=30).place(x=200, y=152)
        Label(self, text='Gender:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=200)
        Entry(self, textvariable=gender, width=30).place(x=200, y=202)
        Label(self, text='Address:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=250)
        Entry(self, textvariable=add, width=30).place(x=200, y=250)
        Label(self, text='Contact Number:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=300)
        Entry(self, textvariable=cn, width=30).place(x=200, y=300)
        Label(self, text='Course:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=350)
        Entry(self, textvariable=c, width=30).place(x=200, y=350)
        Button(self, text="Save", bg='blue', width=15, command=asi).place(x=230, y=380)

Add().mainloop()

4. Create a form for updating data.

Next, create a form and function for updating data from MySQL Database.

#import all the modules
from tkinter import *
import mysql.connector
from mysql.connector import Error
import tkinter.messagebox


class Database:
    def __init__(self,master,*args,**kwargs):
         self.master=master
         self.heading=Label(master,text="Update Student Information",font=('Courier new', 40),fg='black')
         self.heading.place(x=100,y=0)

         #label and entry for id
         self.id_le=Label(master,text="Enter ID",font=('Courier new', 10))
         self.id_le.place(x=0,y=70)

         self.id_leb=Entry(master,font=('Courier new', 10),width=10)
         self.id_leb.place(x=380,y=70)

         self.btn_search=Button(master,text="search",width=8,height=0,bg='orange',command=self.search)
         self.btn_search.place(x=500,y=70)

         #lables  for the window
         self.first=Label(master,text="Enter First Name",font=('Courier new', 10,'bold'))
         self.first.place(x=0,y=120)

         self.last=Label(master,text="Enter Last Name",font=('Courier new', 10,'bold'))
         self.last.place(x=0,y=170)

         self.gender = Label(master, text="Enter Gender ", font=('Courier new', 10,'bold'))
         self.gender.place(x=0, y=220)

         self.address = Label(master, text="Enter Address", font=('Courier new', 10,'bold'))
         self.address.place(x=0, y=270)

         self.contact = Label(master, text="Enter Contact", font=('Courier new', 10,'bold'))
         self.contact.place(x=0, y=320)

         self.course = Label(master, text="Enter Course ", font=('Courier new', 10,'bold'))
         self.course.place(x=0, y=370)


        #enteries for window

         self.first=Entry(master,width=25,font=('Courier new', 10,'bold'))
         self.first.place(x=380,y=120)

         self.last = Entry(master, width=25, font=('Courier new', 10,'bold'))
         self.last.place(x=380, y=170)

         self.gender = Entry(master, width=25, font=('Courier new', 10,'bold'))
         self.gender.place(x=380, y=220)

         self.address = Entry(master, width=25, font=('Courier new', 10,'bold'))
         self.address.place(x=380, y=270)

         self.contact = Entry(master, width=25, font=('Courier new', 10,'bold'))
         self.contact.place(x=380, y=320)

         self.course = Entry(master, width=25, font=('Courier new', 10,'bold'))
         self.course.place(x=380, y=370)


         #button to add to the database
         self.btn_add=Button(master,text='Update Student',width=27,height=1,bg='green',fg='white',command=self.update)
         self.btn_add.place(x=380,y=420)


    def search(self, *args, **kwargs):
         mycursor.execute("SELECT * FROM tbl_student WHERE Stud_ID=%s",[self.id_leb.get()])
         result = mycursor.fetchall()
         for r in result:
              self.n1 = r[1]
              self.n2 = r[2]
              self.n3 = r[3]
              self.n4 = r[4]
              self.n5 = r[5]
              self.n6 = r[6]
         conn.commit()

          #inster into the enteries to update
         self.first.delete(0,END)
         self.first.insert(0, str(self.n1))

         self.last.delete(0, END)
         self.last.insert(0, str(self.n2))

         self.gender.delete(0, END)
         self.gender.insert(0, str(self.n3))

         self.address.delete(0, END)
         self.address.insert(0, str(self.n4))

         self.contact.delete(0, END)
         self.contact.insert(0, str(self.n5))

         self.course.delete(0, END)
         self.course.insert(0, str(self.n6))

    def update(self,*args,**kwargs):
          self.u1=self.first.get()
          self.u2 = self.last.get()
          self.u3 = self.gender.get()
          self.u4 = self.address.get()
          self.u5 = self.contact.get()
          self.u6 = self.course.get()


          mycursor.execute("UPDATE tbl_student SET FirstName=%s,LastName=%s,Gender=%s,Address=%s,ContactNumber=%s,Course=%s WHERE Stud_ID=%s",[self.u1,self.u2,self.u3,self.u4,self.u5,self.u6,self.id_leb.get()])
          conn.commit()
          tkinter.messagebox.showinfo("Success","Update Student successfully")


root=Tk()
b=Database(root)
root.geometry("1000x760+0+0")
root.title("Update Student Information")
root.mainloop()

5. Create a form for deleting data.

Last, create a form and function to delete data from MySQL Database.

from tkinter import *
from tkinter import messagebox
import mysql.connector
from mysql.connector import Error
#creating widow
class Rem(Tk):
    def __init__(self):
        super().__init__()
        self.maxsize(400, 200)
        self.minsize(400, 200)
        self.title("Delete Student")
        self.canvas = Canvas(width=1366, height=768, bg='gray')
        self.canvas.pack()
        a = StringVar()
        def ent():
            if len(a.get()) ==0:
                messagebox.showinfo("Error","Please Enter A Valid Id")
            else:
                d = messagebox.askyesno("Confirm", "Are you sure you want to delete the Student?")
                if d:
                    try:
                        self.myCursor.execute("Delete from tbl_student where Stud_ID = %s",[a.get()])
                        self.conn.commit()
                        self.myCursor.close()
                        self.conn.close()
                        messagebox.showinfo("Confirm","Student Deleted Successfully")
                        a.set("")
                    except:
                        messagebox.showerror("Error","Something goes wrong")
        Label(self, text = "Enter Student Id: ",bg='gray',fg='black',font=('Courier new', 15, 'bold')).place(x = 5,y = 40)
        Entry(self,textvariable = a,width = 20).place(x = 210,y = 44)
        Button(self, text='Delete', width=15, font=('arial', 10),command = ent).place(x=200, y = 90)



Rem().mainloop()

To start executing a CRUD Operations In Python With Source Code, make sure that you have installed Python on your computer.

Steps On How To Run The Project

Download the Source Code below

Summary

This article is a way to enhance and develop our skills and logic ideas which is important in practicing the Python programming language which is the most well-known and most usable programming language in many companies.