Adding user input data to records of MySQL table from tkinter GUI window

Connect to MySQL database Add record using sqlalchemy.
Add records to MySQL Student table by taking user entered inputs from a Tkinter window.

Window for user input

There are three parts in this script.

Part 1: Create the GUI window using tkinter.
Part 2: Validate the user entered data.
Part 3: Add data to MySQL table.

Tkinter window to take user inputs& add data to MySQL table after validation & displaying messages

The same script can be used to store data in SQLite database and Excel workbook. The GUI and data validation parts are same.
tutorial on inserting data using Tkinter and SQLite
Inserting data to Excel workbook
Tutorial on inserting data to webpage & MySQL using Tkinter.

Part 1 : Tkinter window to accept user inputs

User will enter four input data using our Tkinter window. Main components are here

Name : String of name through a text box t1
Class : Select one option from the dropdown OptionMenu
Mark : Integer value ( number ) through text box t3
gender : Selection through a pair of radio buttons ( Male , Female or other )
Submit : Button to trigger a click event to handle all the inputs.

There are Labels to show message about the input data ( l1,l2,l3 ) and one final Label ( l5 ) to display the output or return id of the record after inserting the data in table. One OptionMenu ( opt1) is used for selection of class.

l0 = tk.Label(my_w,  text='Add Student',
              font=('Helvetica', 16), width=30,anchor="c" )  
l0.grid(row=1,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=4,column=1) 

# 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=4,column=2)

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=6,column=2)

r2 = tk.Radiobutton(my_w, text='Female', variable=radio_v, value='Female')
r2.grid(row=6,column=3)

b1 = tk.Button(my_w,  text='Add Record', width=10, 
               command=lambda: add_data())  
b1.grid(row=7,column=2) 
my_str = tk.StringVar()
l5 = tk.Label(my_w,  textvariable=my_str, width=10 )  
l5.grid(row=3,column=3) 
my_str.set("Output")

The Button b1 is used to execute the function add_data() by using the command or click event of the button.

b1 = tk.Button(my_w,  text='Add Record', width=10, 
               command=lambda: add_data())
b1.grid(row=7,column=2)

Inside the function add_data() we have two sections. In first section we will check each input data. It is expected that no input is left blank by user. Without filling the details if user has clicked the button ( b1 ) then we will display a message through our Label ( l5 ) asking to check the inputs ( Read the else part of the if-else).

def add_data():
     flag_validation=True # set the flag 
     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):
	  #  Part 3  database insert part 
     else:
        l5.config(fg='red')   # foreground color
        l5.config(bg='yellow') # background color
        my_str.set("check inputs.")

In this code we first set one flag validation ( flag_validation ) to True, if any where the validation of the inputs fails then we will change this status of validation to False. ( this is part of the above code )

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

After all the checks ( validation of inputs ) if the flag ( flag_validation ) is True , then we can start adding the data to MySQL table. This completes the Part 2 and in next section we will add data to our table in Part 3 .

Database connection

Connect to database by using the sqlalchemy driver.

from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

In above code update your userid and password with your MySQL login details and database name.

We used Parameterized query in sqlalchemy to add one record to table. The INSERT Query is used to add the record to MySQL table.

query="INSERT INTO  `student` (`name` ,`class` ,`mark` ,`gender`) \
            VALUES(%s,%s,%s,%s)"
my_data=(my_name,my_class,my_mark,my_gender)
id=engine.execute(query,my_data) # insert data

Our student table has one auto incremented id column. So after adding the record we can collect the unique id of the auto increment field and display it to user to show successful completion of adding record. Here id.lastrowid stores the value of this new ID.

my_str.set("ID:" + str(id.lastrowid))

We have used the Label l5 to display the id of the record added. This display of Id ensures that the record is inserted successfully.

	l5.config(fg='green') # foreground color 
	l5.config(bg='white') # background color 
	my_str.set("ID:" + str(id.lastrowid))

Adding time delay

We want to show the ID ( after adding the record ) or the error message for some time ( say 3 seconds ) and it should vanish after the time delay.

l5.after(3000,lambda:l5.config(fg='white',bg='white',text=''))

Remove the user entered text after adding of records

t1.delete('1.0',END)  # reset the text entry box
t3.delete('1.0',END)  # reset the text entry box

l5.config(fg='green') # foreground color 
l5.config(bg='white') # background color 
my_str.set("ID:" + str(id.lastrowid))

import tkinter as tk
from tkinter import *

my_w = tk.Tk()
my_w.geometry("400x250")
my_w.title("www.plus2net.com")

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://id:pw@localhost/my_db")
# add one Label
l0 = tk.Label(my_w, text="Add Student", font=("Helvetica", 16), width=30, anchor="c")
l0.grid(row=1, 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=4, column=1)

# 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=4, column=2)

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=6, column=1)

r2 = tk.Radiobutton(my_w, text="Female", variable=radio_v, value="Female")
r2.grid(row=6, column=2)

r3 = tk.Radiobutton(my_w, text="Others", variable=radio_v, value="Others")
r3.grid(row=6, column=3)

b1 = tk.Button(my_w, text="Add Record", width=10, command=lambda: add_data())
b1.grid(row=7, column=2)
my_str = tk.StringVar()
l5 = tk.Label(my_w, textvariable=my_str, width=10)
l5.grid(row=3, column=3)
my_str.set("Output")


def add_data():
    flag_validation = True  # set the flag
    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
            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()

View and Download tkinter_16-insert-data.ipynb file

We can display 10 records from student table without any user input.

Displaying records from student table
Search and display record based on input ID
Detail sqlalchemy Database Error message from MySQL

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Inserting Data to MySQL table

Part 1 : Tkinter window to accept user inputs

Database connection

Adding time delay

Subscribe