Software Department
Database Management Lab. / 2nd Class
AY:2023-2024
LAB#05-LAB#06 - Create, read, update and delete (CRUD) operations.
1. Creating Database Using Connector/Python
All DDL (Data Definition Language) statements are executed using a handle structure known as a
cursor. The following examples show how to create the Database called students:
mydb = mysql.connector.connect(
host="127.0.0.1",
user="root",
password="MySQL8.0.34",
)
myCursor = mydb.cursor()
myCursor.execute("CREATE DATABASE IF NOT EXISTS stud")
myCursor.close()
mydb.close()
After we successfully create or change to the target database, we create the tables by iterating
over the items of the TABLES dictionary:
2. Creating Tables Using Connector/Python
To handle the error when the table already exists, we notify the user that it was already there.
import tkinter as tk
from tkinter import simpledialog
from tkinter import messagebox
import mysql.connector
# connect to MySQL Server
conn = mysql.connector.MySQLConnection(host='127.0.0.1', username='root',
password='MySQL8.0.34', database='students')
if not conn.is_connected():
exit()
else:
print("Connection has been established successfully")
studentCursor = conn.cursor(buffered=True)
studentCursor.execute('CREATE TABLE IF NOT EXISTS personalInfo (id
INTEGER, name VARCHAR(20), gender INTEGER)')
# create a window
win = tk.Tk()
win.title("Student information")
win.minsize(400, 250)
# labels
lblID = tk.Label(win, text="Student ID: ")
lblID.grid(column=0, row=0, padx=25, pady=10, sticky=tk.W)
1
Software Department
Database Management Lab. / 2nd Class
AY:2023-2024
lblName = tk.Label(win, text="Student Name: ")
lblName.grid(column=0, row=1, padx=25, pady=10, sticky=tk.W)
lblGender = tk.Label(win, text="Student Gender: ")
lblGender.grid(column=0, row=2, padx=25, pady=10, sticky=tk.W)
lblnote = tk.Label(win, justify=tk.LEFT, wraplength=320, text="Before clicking
on update or delete, please, click on search to determine the student ID you
want to update or delete.")
lblnote.grid(columnspan=2, row=3, padx=25, pady=10, )
# variables from user
sID = tk.IntVar()
sName = tk.StringVar()
sGender = tk.IntVar()
# Entries
entID = tk.Entry(win, textvariable=sID)
entID.grid(column=1, row=0, pady=10, sticky=tk.W)
ntName = tk.Entry(win, textvariable=sName)
entName.grid(column=1, row=1, pady=10, sticky=tk.W)
lblfGender = tk.LabelFrame(win)
lblfGender.grid(column=1, row=2, pady=10, sticky=tk.W)
# define gender
gender = {
'Male': 0,
'Female': 1
}
# define thr radiobutton
for i, j in gender.items():
rdbGender = tk.Radiobutton(lblfGender, text=i, variable=sGender, value=j)
rdbGender.pack(side=tk.LEFT)
def insertion():
query = 'INSERT INTO personalInfo VALUES (%s,%s,%s)'
data = (sID.get(), sName.get(), sGender.get())
studentCursor.execute(query, data)
conn.commit()
def search():
idToSearch = simpledialog.askinteger('Search', 'Please, enter the ID you
want to search')
query ='SELECT * FROM personalInfo WHERE id= %s'
studentCursor.execute(query, (idToSearch,))
2
Software Department
Database Management Lab. / 2nd Class
AY:2023-2024
resultSet = studentCursor.fetchone()
sID.set(resultSet[0])
sName.set(resultSet[1])
sGender.set(resultSet[2])
def update():
query = 'UPDATE personalInfo SET name = %s,gender = %s WHERE id = %s'
data = (sName.get(), sGender.get(), sID.get())
studentCursor.execute(query, data)
conn.commit()
def delete():
query = 'DELETE FROM personalInfo WHERE id = %s'
data = (sID.get(),)
response = messagebox.askyesnocancel("Delete Record", "Are you sure you
want to delete student of ID " + str(sID.get())+ " ?", icon='warning')
if response:
studentCursor.execute(query, data)
conn.commit()
# Insert button
btnInsert = tk.Button(win, text="Insert", width=10, command=insertion)
btnInsert.grid(column=2, row=0, padx=25, pady=10, sticky=tk.W)
# Update button
btnUpdate = tk.Button(win, text="Update", width=10, command=update)
btnUpdate.grid(column=2, row=1, padx=25, pady=10, sticky=tk.W)
# Delete button
btnDelete = tk.Button(win, text="Delete", width=10, command=delete)
btnDelete.grid(column=2, row=2, padx=25, pady=10, sticky=tk.W)
# Search button
btnSearch = tk.Button(win, text="Search", width=10, command=search)
btnSearch.grid(column=2, row=3, padx=25, pady=10, sticky=tk.W)
# Close button
btnClose = tk.Button(win, text="Close", width=10, command=quit)
btnClose.grid(row=4, columnspan=4, padx=25, pady=10, sticky=tk.EW)
win.mainloop()
1. We first open a connection to the MySQL server and connect to the database students.
2. We then create a new cursor, by default a studentCursor object, using the connection's cursor()
method.
3. create a window as follows
3
Software Department
Database Management Lab. / 2nd Class
AY:2023-2024
3. The button insert is dedicated to append the information that the user input into the table
personalInfo
4. The button Update is dedicated to update the information that the user modify.
5. The button Delete is dedicated to delete the information that the user select from the table
personalInfo
6. The user is informed to click Search button before he/she wants to update/delete any record from
the table personalInfo, since the button Search asks the user to enter the student ID to be
updated/deleted. This is done using Tkinter simpledialog which is module used to prompt for user
input. In Tkinter, the simpledialog.askstring() method (there are askinteger, askfloat) is used to prompt
user for input, in a simple dialog box. The user can enter a value in the entry field, and click on the OK
button. Then the entered value is returned by the simpledialog.askstring() method. if the user clicks on
Cancel, the simpledialog.askstring() method returns None.
