This Python crud will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE. using Mysql Database. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.
The tutorial you learn Python mysql crud operation step by step. Crud operations are must when you developing a Python mini projects. learn how learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the registation table in the database named payroll. registation table consist of following columns id, empname,mobile,salary.
we will do the Python best practice for understand java programming clearly and easily.
Feature of projects
- The system shall be able to record employee details : id,empname,mobile,salary.
- The system shall be able to retrieve the employee details : id,empname,mobile,salary.
- Then system shall be able to Edit and Delete the employee details : id,empname,mobile,salary.
Learn how to make this System Step by step
Install Mysql Connector
Step 1: go to Python path : to C:\Users\your-pc-name\AppData\Local\Programs\Python\Python38 Python
copy the path and paste on cmd and inorder to install type on python -m pip install mysql-connector-python
after installed.
Open the Pycharm IDE and Create a new project.
Paste the following code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | import tkinter as tk from tkinter import ttk, messagebox import mysql.connector from tkinter import * def GetValue(event): e1.delete(0, END) e2.delete(0, END) e3.delete(0, END) e4.delete(0, END) row_id = listBox.selection()[0] select = listBox.set(row_id) e1.insert(0,select['id']) e2.insert(0,select['empname']) e3.insert(0,select['mobile']) e4.insert(0,select['salary']) def Add(): studid = e1.get() studname = e2.get() coursename = e3.get() feee = e4.get() mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="payroll") mycursor=mysqldb.cursor() try: sql = "INSERT INTO registation (id,empname,mobile,salary) VALUES (%s, %s, %s, %s)" val = (studid,studname,coursename,feee) mycursor.execute(sql, val) mysqldb.commit() lastid = mycursor.lastrowid messagebox.showinfo("information", "Employee inserted successfully...") e1.delete(0, END) e2.delete(0, END) e3.delete(0, END) e4.delete(0, END) e1.focus_set() except Exception as e: print(e) mysqldb.rollback() mysqldb.close() def update(): studid = e1.get() studname = e2.get() coursename = e3.get() feee = e4.get() mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="payroll") mycursor=mysqldb.cursor() try: sql = "Update registation set empname= %s,mobile= %s,salary= %s where id= %s" val = (studname,coursename,feee,studid) mycursor.execute(sql, val) mysqldb.commit() lastid = mycursor.lastrowid messagebox.showinfo("information", "Record Updateddddd successfully...") e1.delete(0, END) e2.delete(0, END) e3.delete(0, END) e4.delete(0, END) e1.focus_set() except Exception as e: print(e) mysqldb.rollback() mysqldb.close() def delete(): studid = e1.get() mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="payroll") mycursor=mysqldb.cursor() try: sql = "delete from registation where id = %s" val = (studid,) mycursor.execute(sql, val) mysqldb.commit() lastid = mycursor.lastrowid messagebox.showinfo("information", "Record Deleteeeee successfully...") e1.delete(0, END) e2.delete(0, END) e3.delete(0, END) e4.delete(0, END) e1.focus_set() except Exception as e: print(e) mysqldb.rollback() mysqldb.close() def show(): mysqldb = mysql.connector.connect(host="localhost", user="root", password="", database="payroll") mycursor = mysqldb.cursor() mycursor.execute("SELECT id,empname,mobile,salary FROM registation") records = mycursor.fetchall() print(records) for i, (id,stname, course,fee) in enumerate(records, start=1): listBox.insert("", "end", values=(id, stname, course, fee)) mysqldb.close() root = Tk() root.geometry("800x500") global e1 global e2 global e3 global e4 tk.Label(root, text="Employee Registation", fg="red", font=(None, 30)).place(x=300, y=5) tk.Label(root, text="Employee ID").place(x=10, y=10) Label(root, text="Employee Name").place(x=10, y=40) Label(root, text="Mobile").place(x=10, y=70) Label(root, text="Salary").place(x=10, y=100) e1 = Entry(root) e1.place(x=140, y=10) e2 = Entry(root) e2.place(x=140, y=40) e3 = Entry(root) e3.place(x=140, y=70) e4 = Entry(root) e4.place(x=140, y=100) Button(root, text="Add",command = Add,height=3, width= 13).place(x=30, y=130) Button(root, text="update",command = update,height=3, width= 13).place(x=140, y=130) Button(root, text="Delete",command = delete,height=3, width= 13).place(x=250, y=130) cols = ('id', 'empname', 'mobile','salary') listBox = ttk.Treeview(root, columns=cols, show='headings' ) for col in cols: listBox.heading(col, text=col) listBox.grid(row=1, column=0, columnspan=2) listBox.place(x=10, y=200) show() listBox.bind('<Double-Button-1>',GetValue) root.mainloop() |
i have attached the video link below. which will do this tutorials step by step.
