Python And MySQL - Insert Update Delete Search And Display Data On Tkinter TreeView

How To Insert Update Delete And Show Records In TreeView Using Python Tkinter

In this Python Tkinter CRUD Tutorial With MySQL Database we will see How To:

- insert data in mysql database.

- remove selected record from mysql database.

- edit selected record from mysql database.

- search for a specific record by id.

- display mysql database records in a tkinter treeview.

Project Source Code:

import tkinter as tk

from tkinter import *

from tkinter import ttk

from tkinter import messagebox

import mysql.connector

connection = mysql.connector.connect(host='localhost', user='root', port='3306',

password='', database='test_py')

c = connection.cursor()

root = Tk()

frame = tk.Frame(root)

frame_btns = tk.Frame(frame)

label_id = tk.Label(frame, text="ID:", font=('verdana',14))

entry_id = tk.Entry(frame, font=('verdana',14))

label_fname = tk.Label(frame, text="First Name:", font=('verdana',14))

entry_fname = tk.Entry(frame, font=('verdana',14))

label_lname = tk.Label(frame, text="Last Name:", font=('verdana',14))

entry_lname = tk.Entry(frame, font=('verdana',14))

label_email = tk.Label(frame, text="Email:", font=('verdana',14))

entry_email = tk.Entry(frame, font=('verdana',14))

label_age = tk.Label(frame, text="Age:", font=('verdana',14))

entry_age = tk.Entry(frame, font=('verdana',14))

button_add = tk.Button(frame_btns, text="Add", font=('verdana',14), bg='green',

fg='#ffffff')

button_edit = tk.Button(frame_btns, text="Edit", font=('verdana',14), bg='blue',

fg='#ffffff')

button_remove = tk.Button(frame_btns, text="Remove", font=('verdana',14), bg='red',

fg='#ffffff')

button_search = tk.Button(frame_btns, text="Search", font=('verdana',14), bg='orange',

fg='#ffffff')

trv = ttk.Treeview(frame, columns=(1,2,3,4,5), height=15, show="headings")

trv.column(1, anchor=CENTER, stretch=NO, width=100)

trv.column(2, anchor=CENTER, stretch=NO, width=100)

trv.column(3, anchor=CENTER, stretch=NO, width=100)

trv.column(4, anchor=CENTER, stretch=NO, width=100)

trv.column(5, anchor=CENTER, stretch=NO, width=100)

trv.heading(1, text="ID")

trv.heading(2, text="First Name")

trv.heading(3, text="Last Name")

trv.heading(4, text="Email")

trv.heading(5, text="Age")

def add():

fname = entry_fname.get().strip() # remove white space

lname = entry_lname.get().strip()

email = entry_email.get().strip()

age = entry_age.get().strip()

vals = (fname,lname,email,age)

if(len(fname) > 0 and len(lname) > 0 and len(email) > 0 and int(age) > 10):

c.execute("INSERT INTO `users_2`(`firstname`, `lastname`, `email`, `age`)

VALUES (%s,%s,%s,%s)", vals)

connection.commit()

messagebox.showinfo('Add','User Info Has Been Added')

displayUsers()

else:

messagebox.showwarning('Add','Incorrrect Data')

def edit():

user_id = entry_id.get().strip()

fname = entry_fname.get().strip()

lname = entry_lname.get().strip()

email = entry_email.get().strip()

age = entry_age.get().strip()

if(len(fname) > 0 and len(lname) > 0 and len(email) > 0 and int(age) > 10):

vals = (fname,lname,email,age,user_id)

c.execute("UPDATE `users_2` SET `firstname`=%s,`lastname`=%s,`email`=%s,

`age`=%s WHERE `id`=%s", vals)

connection.commit()

messagebox.showinfo('Edit','User Info Has Been Edited')

displayUsers()

else:

messagebox.showwarning('Edit','Incorrrect Data')

def remove():

user_id = entry_id.get().strip()

c.execute("DELETE FROM `users_2` WHERE `id` = " + user_id)

connection.commit()

messagebox.showinfo('Delete','User Info Has Been Deleted')

displayUsers()

def search():

user_id = entry_id.get().strip()

c.execute("SELECT * FROM `users_2` WHERE `id` = " + user_id)

user = c.fetchone()

# clear fields

entry_fname.delete(0, END)

entry_lname.delete(0, END)

entry_email.delete(0, END)

entry_age.delete(0, END)

if user:

# display data

entry_fname.insert(0, user[1])

entry_lname.insert(0, user[2])

entry_email.insert(0, user[3])

entry_age.insert(0, user[4])

else:

messagebox.showwarning('User','No User Found')

def displayUsers():

# clear treeview

for row in trv.get_children():

trv.delete(row)

# populate treeview

c.execute("SELECT * FROM `users_2`")

users = c.fetchall()

for user in users:

trv.insert('', END, values=user)

button_add['command'] = add

button_edit['command'] = edit

button_remove['command'] = remove

button_search['command'] = search

displayUsers()

label_id.grid(row=0, column=0, sticky='e')

entry_id.grid(row=0, column=1)

label_fname.grid(row=1, column=0, sticky='e')

entry_fname.grid(row=1, column=1)

label_lname.grid(row=2, column=0, sticky='e')

entry_lname.grid(row=2, column=1)

label_email.grid(row=3, column=0, sticky='e')

entry_email.grid(row=3, column=1)

label_age.grid(row=4, column=0, sticky='e')

entry_age.grid(row=4, column=1)

frame_btns.grid(row=5, column=0, columnspan=2)

button_add.grid(row=0, column=0, padx=10, pady=10)

button_edit.grid(row=0, column=1, padx=10, pady=10)

button_remove.grid(row=0, column=2, padx=10, pady=10)

button_search.grid(row=0, column=3, padx=10, pady=10)

trv.grid(row=0, column=3, rowspan=5, padx=10, pady=10)

frame.grid(row=0, column=0)

root.mainloop()

///////////////OUTPUT:

Python Tkinter Insert Button To MySQL

Python Insert Button To MySQL 2

if you want the source code click on the download button below

---

Need Projects + Source Code ?

➜ Java Projects

➜ PHP Projects

➜ Python Projects

PYTHON And MySQL - How To Delete Records In MySQL Database Using Python Tkinter

PYTHON - How To Delete Data In MySQL Database In Python Tkinter

In this Python Tutorial, we will learn how to delete data from a MySQL database using Python. We will cover the process of removing records from the database to effectively manage and update your data using Python programming language.

Project Source Code:

import tkinter as tk
from tkinter import *
from tkinter import ttk
import mysql.connector
root = Tk()
root.title("Delete Data")
connection = mysql.connector.connect(host='localhost', user='root', password='',
                                     port='3306', database='test_py')
c = connection.cursor()
bkg = "#badc58"

frame = tk.Frame(root, bg=bkg)
label_id = tk.Label(frame, text="ID: ", font=('verdana',12), bg=bkg)
entry_id = tk.Entry(frame, font=('verdana',12))
def deleteData():
    user_id = entry_id.get()
    delete_query = "DELETE FROM `users_2` WHERE `id` = " + user_id
    #vals = (user_id)
    c.execute(delete_query)
    connection.commit()

button_delete = tk.Button(frame, text="Delete", font=('verdana',14),fg='#ffffff',
                          bg='red', command = deleteData)
label_id.grid(row=0, column=0)
entry_id.grid(row=0, column=1, pady=10, padx=10)
button_delete.grid(row=1,column=0, columnspan=2, pady=10, padx=10, sticky='nsew')
frame.grid(row=0, column=0)

root.mainloop()

OUTPUT:      

---

▶ Download Python Projects Source Code

Python Delete Data From MySQL Database

How To Delete Record From MySQL Database Using Python

import mysql.connector

connection = mysql.connector.connect(

host='localhost',

user='root', password='',

port='3306',

database='test_py'

)

c = connection.cursor()

def deleteData(user_id):

delete_query = "DELETE FROM `users_2` WHERE `id` = " + user_id

c.execute(delete_query)

connection.commit()

deleteData("8")

OUTPUT:

Python MySQL Delete Record - Before

Python MySQL Delete Record - After

---

▶ Download Python Projects Source Code