The GUI , data handling part of this tutorial is same as MySQL data insert tutorial. The difference in code between MySQL database and SQLite part is highlighted here. For easy understanding the full code is repeated here. Inserting data to Excel workbook
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 Sex : 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.
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 sqlite database student table. This completes the Part 2 and in next section we will add data to our table in Part 3 .
my_data=(None,my_name,my_class,my_mark,my_gender)
my_query="INSERT INTO student values(?,?,?,?,?)"
my_conn.execute(my_query,my_data)
my_conn.commit()
The difference in code between SQLite and MySQL is in two parts.
1. Connection string
2. Format of Query to add parameters ( SQLite uses ? , MySQL it is % )
Our student table has one Primary Key as ID column. So after adding the record we can collect the unique id of this primary key and display it to user to show successful completion of adding record. Here last_insert_rowid() stores the value of this new ID.
x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
l5.grid()
l5.config(fg='green') # foreground color
l5.config(bg='white') # background color
my_str.set("ID:" + str(id[0]))
l5.after(3000, lambda: l5.grid_remove() )
We have used the Labell5 to display the id of the record added. This display of Id ensures that the record is inserted successfully.