Are you a beginner and want to learn how to connect MySQL and perform CRUD operations in Python? Today we are going to learn MySQL CRUD Operations in Python using GUI Tkinter App. We’ll see how to connect with the MySQL database and perform insert, update, delete, and select operations using the python program with GUI Tkinter application.
MySQL Server Installation
So first, you need to download and install the MySQL server. If you don’t have it installed, you can watch this video on how to download the MySQL server for Ubuntu 20.04 LTS.
For Windows, you can check other videos on youtube or visit MySQL’s official website
Install Python MySQL Connector & Tkinter Packages
Before we begin, we’ll need to install two packages in python to implement in our program:
- Install MySql Connector
To establish a connection with the MySQL database, we’ll need to install the MySQL connector package for python.
Use the command “pip install mysql-connector-python” to install the MySQL connector package.
- Install Tkinter
We will use Tkinter to create a GUI-based window application in python.
Use this command “sudo apt-get install python3-tk” or you can watch this complete video on how to install Tkinter.
Create Tkinter GUI Window
Once you complete the setup for the MySQL server and other packages installation, It’s time to import packages in our python program.
First, we’ll design our GUI application, you can follow below source code to use in your python script:
from tkinter import *
import tkinter.messagebox as MessageBox
import mysql.connector as mysql
root = Tk()
root.geometry("500x300")
root.title("MySQL CRUD Operations")
id = Label(root, text="Enter ID:", font=("verdana 15"))
id.place(x=50, y=30)
id_entry = Entry(root, font=("verdana 15"))
id_entry.place(x=150, y=30)
name = Label(root, text="Name:", font=("verdana 15"))
name.place(x=50, y=80)
name_entry = Entry(root, font=("verdana 15"))
name_entry.place(x=150, y=80)
phone = Label(root, text="Phone:", font=("verdana 15"))
phone.place(x=50, y=130)
phone_entry= Entry(root, font=("verdana 15"))
phone_entry.place(x=150, y=130)
btnInsert = Button(root, text="Insert", command=Insert, font=("verdana 15")).place(x=100, y=190)
btnDelete = Button(root, text="Delete", command=Del, font=("verdana 15")).place(x=200, y=190)
btnUpdate = Button(root, text="Update", command=Update, font=("verdana 15")).place(x=320, y=190)
btnSelect= Button(root, text="Select", command=Select, font=("verdana 15")).place(x=200, y=240)
root.mainloop()
MySQL CRUD Operations
Now that we have created our GUI App, we’ll write a code for MySQL connection and perform CRUD operations. I’ve provided the source code for Insert, Update, Delete, and Select functions.
- INSERT Function
def Insert():
id = id_entry.get()
name = name_entry.get()
phone = phone_entry.get()
if(id == "" or name == "" or phone == ""):
MessageBox.showinfo("ALERT", "Please enter all fields")
else:
con = mysql.connect(host="localhost", user="Database Username", password="Your Database Password", database="Your Database Name")
cursor = con.cursor()
cursor.execute("insert into Person values('" + id +"', '"+ name +"', '" + phone +"')")
cursor.execute("commit")
MessageBox.showinfo("Status", "Successfully Inserted")
con.close();
- UPDATE Function
def Update():
id = id_entry.get()
name = name_entry.get()
phone = phone_entry.get()
if(name == "" or phone == ""):
MessageBox.showinfo("ALERT", "Please enter fiels you want to update!")
else:
con = mysql.connect(host="localhost", user="Database Username", password="Your Database Password", database="Your Database Name")
cursor = con.cursor()
cursor.execute("update Person set name = '"+ name +"', phone='"+ phone +"' where id ='"+ id +"'")
cursor.execute("commit");
MessageBox.showinfo("Status", "Successfully Updated")
con.close();
- DELETE Function
def Del():
if(id_entry.get() == ""):
MessageBox.showinfo("ALERT", "Please enter ID to delete row")
else:
con = mysql.connect(host="localhost", user="Database Username", password="Your Database Password", database="Your Database Name")
cursor = con.cursor()
cursor.execute("delete from Person where id='"+ id_entry.get() +"'")
cursor.execute("commit");
id_entry.delete(0, 'end')
name_entry.delete(0, 'end')
phone_entry.delete(0, 'end')
MessageBox.showinfo("Status", "Successfully Deleted")
con.close();
- SELECT Function
def Select():
if(id_entry.get() == ""):
MessageBox.showinfo("ALERT","ID is required to select row!")
else:
con = mysql.connect(host="localhost", user="Database Username", password="Your Database Password", database="Your Database Name")
cursor = con.cursor()
cursor.execute("select * from Person where id= '" + id_entry.get() +"'")
rows = cursor.fetchall()
for row in rows:
name_entry.insert(0, row[1])
phone_entry.insert(0, row[2])
con.close();
For step by step guide, follow the above video and try it in your system, and for full source code download here.
Conclusion
So today we learn how to install and download MySQL server, MySQL connector, and Tkinter. After that, we learn how to establish a connection with the MySQL server using the python program. Finally, we perform MySQL CRUD operation with the help of good looking GUI Application.
This is just a simple example however, you can try more things with the MySQL server and I’ll come up with more examples like this, so stay tuned. For more python examples, check out our Python programming series or visit my 100 Python Exercises for Beginners.
