Adding rows to MySQL table and using insert() in Tkinter Treeview


Adding row to MySQL and Treeview

Adding row to Treeview only after inserting to MySQL table

Read the first part about adding user entered data to Treeview.
We will extend this script to include following points.
  1. Connect to MySQL database using SQLAlchemy
  2. Validate the user inputs before executing MySQL insert command.
  3. Insert data to MySQL table and get the confirmation by reading number of rows affected by the insert query.
  4. 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.
  1. Name should not be less than 2 char length ( Can't be blank )
  2. Class should not be less than 2 char length ( Option must be selected)
  3. Gender should not be less than 2 char length ( One choice must be there )
  4. 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()
Read the first part about adding user entered data to Treeview.
Displaying MySQL records using Treeview
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer