Validate the user inputs before executing MySQL insert command.
Insert data to MySQL table and get the confirmation by reading number of rows affected by the insert query.
Add the parent ( node ) to Treeview using the same user entered input data
In the first part of the code below the layout and the Treeview is declared. Using the Treeview and button we will insert the user entered data to the MySQL table and Treeview.
add_data(): the main function
The function add_data() is executed once the button Add record is clicked.
flag
We have set one flag first and keep it true, if any of the validation fails then we will make this flag to False. Based on the final value of the flag we will insert data to MySQL table.
flag_validation=True # set the flag
In our validation we are checking for these issues.
Name should not be less than 2 char length ( Can't be blank )
Class should not be less than 2 char length ( Option must be selected)
Gender should not be less than 2 char length ( One choice must be there )
The mark must be an integer
if(len(my_name) < 2 or len(my_class)<2 or len(my_gender) < 2 ):
flag_validation=False
try:
val = int(my_mark) # checking mark as integer
except:
flag_validation=False
If validation is cleared then data is inserted to MySQL database using insert query. After inserting the row to MySQL table we will get confirmation from MySQL by reading the lastrowid which gives us the value of the auto-incremented id field value. This id value is given by MySQL and can be treated as success of data insert operation. Based on this id value we will insert the data to MySQL table.
if(id.lastrowid>=1): # data is inserted successfully
trv.insert("",'end',
values=(id.lastrowid,my_name,my_class,my_mark,my_gender))
Full code is here
from tkinter import ttk
import tkinter as tk
from tkinter import *
my_w=tk.Tk()
my_w.geometry('400x500')
my_w.title("www.plus2net.com")
trv=ttk.Treeview(my_w,selectmode='browse')
trv.grid(row=1,column=1,columnspan=4,padx=20,pady=20)
trv["columns"]=("1","2","3","4","5")
trv['show']='headings'
trv.column("1",width=30,anchor='c')
trv.column("2",width=80,anchor='c')
trv.column("3",width=80,anchor='c')
trv.column("4",width=80,anchor='c')
trv.column("5",width=80,anchor='c')
trv.heading("1",text="id")
trv.heading("2",text="Name")
trv.heading("3",text="Class")
trv.heading("4",text="Mark")
trv.heading("5",text="Gender")
i=1
trv.insert("",'end',iid=1,
values=(i,'Alex','Four',78,'Male'))
l0 = tk.Label(my_w, text='Add Student',
font=('Helvetica', 16), width=30,anchor="c" )
l0.grid(row=2,column=1,columnspan=4)
l1 = tk.Label(my_w, text='Name: ', width=10,anchor="c" )
l1.grid(row=3,column=1)
# add one text box
t1 = tk.Text(my_w, height=1, width=10,bg='white')
t1.grid(row=3,column=2)
l2 = tk.Label(my_w, text='Class: ', width=10 )
l2.grid(row=3,column=3)
# add list box for selection of class
options = StringVar(my_w)
options.set("") # default value
opt1 = OptionMenu(my_w, options, "Three", "Four", "Five")
opt1.grid(row=3,column=4)
l3 = tk.Label(my_w, text='Mark: ', width=10 )
l3.grid(row=5,column=1)
# add one text box
t3 = tk.Text(my_w, height=1, width=4,bg='white')
t3.grid(row=5,column=2)
radio_v = tk.StringVar()
radio_v.set('Female')
r1 = tk.Radiobutton(my_w, text='Male', variable=radio_v, value='Male')
r1.grid(row=5,column=3)
r2 = tk.Radiobutton(my_w, text='Female', variable=radio_v, value='Female')
r2.grid(row=5,column=4)
b1 = tk.Button(my_w, text='Add Record', width=10,
command=lambda: add_data())
b1.grid(row=6,column=2)
my_str = tk.StringVar()
l5 = tk.Label(my_w, textvariable=my_str, width=10 )
l5.grid(row=8,column=1)
#database connection if not added before
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
# add your mysql userid, password and db name here ##
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
def add_data(): # on click of button
flag_validation=True # set the flag for input validation
my_name=t1.get("1.0",END) # read name
my_class=options.get() # read class
my_mark=t3.get("1.0",END) # read mark
my_gender=radio_v.get() # read gender
# length of my_name , my_class and my_gender more than 2
if(len(my_name) < 2 or len(my_class)<2 or len(my_gender) < 2 ):
flag_validation=False
try:
val = int(my_mark) # checking mark as integer
except:
flag_validation=False
if(flag_validation):
my_str.set("Adding data...")
try:
query="INSERT INTO `student`(`name`,`class`,`mark`,`gender`)\
VALUES(%s,%s,%s,%s)"
my_data=(my_name,my_class,my_mark,my_gender)
id=my_conn.execute(query,my_data) # insert data
t1.delete('1.0',END) # reset the text entry box
t3.delete('1.0',END) # reset the text entry box
l5.grid()
l5.config(fg='green') # foreground color
l5.config(bg='white') # background color
if(id.lastrowid>=1):# data is inserted successfully
trv.insert("",'end',
values=(id.lastrowid,my_name,my_class,my_mark,my_gender))
my_str.set("ID:" + str(id.lastrowid))
l5.after(3000,lambda:l5.config(fg='white',bg='white',text=''))
except SQLAlchemyError as e:
error = str(e.__dict__['orig'])
l5.grid()
#return error
l5.config(fg='red') # foreground color
l5.config(bg='yellow') # background color
print(error)
my_str.set(error)
else:
l5.grid()
l5.config(fg='red') # foreground color
l5.config(bg='yellow') # background color
my_str.set("check inputs.")
l5.after(3000,lambda:l5.config(fg='white',bg='white',text=''))
my_w.mainloop()