Python SQLite Data Entry Form with Tkinter tutorial for beginners - Python GUI project

hello everyone and welcome back to my channel so today's video is a follow-up to a previous video of mine in which i showed you how to create this data entry form using python and tkinter so in that video i showed you how to create this interface that you see right here and then capture all the data that the user inputs through all these different fields save it and print it out one thing we didn't do in that video is actually save the data permanently in a persistent database i followed it up also with another video in which i actually inserted the data into an excel sheet and we saved all the user entries there however in this video we're going to save everything inside an sqlite database please do note however you can watch this video independently from the other videos if you're only interested in the sqlite part in this part i'm not going to touch on any of the tkinter parts we're not really here to learn about the ui we already went through this in a very long video previously so you can refer to that if you're interested to see how i made the ui but in this part we're only going to go through the sqlite part alright so without further ado let's get started and let me first show you how this app should work so again we have this data entry form the user would want to enter their name so let's say jane doe let's give her a title so let's go with miss some age nationality we previously just put the continents because too many countries would have been a lot of typing and then we say let's accept the terms and let's enter the data so what should happen in this case is that if i go to an sqlite database and i refresh my table you should see i have a new entry for jane doe which contains all the data that we entered inside the data entry form so this is essentially our goal this is what we want to achieve within this video all right so let's actually start coding and before i do that let me first talk a bit about sqlite so this interface that you see right here if you're not familiar with sqlite this is called db browser so you can see here this is db browser for sqlite this is sort of a very nice and simple interface you can use to interact with your database you can see i can go here to database structure i have one table this is student data and inside this table i can browse the data and you can see all the different columns as well as all the different information inside it so it's very nice very visual you can just plainly explore all of your data visually which is really nice so to download this i want you to go to sqlitebrowser.org and then go to download and then you just are going to go through some very simple steps to download db browser now if you already had it perfect let's actually get started so let me go back to vs code and let me actually delete this database so i'm going to close it here and i'm going to come back here and delete this database so that we can actually start from scratch so let me run the application again you can see this is what we have if i actually do try to enter data so let's go with jane doe and then i'm going to accept what happens is the output here what gets printed is all the information that we collected so if you go through the code you can see that here we collected the first name we collected the last name title age nationality and so on we captured all of this information from the tkinter interface and what we simply did was just print it out for the user and we just looked at it here we didn't save it anywhere now this is why i said that tkinter is not really related in this video we're simply going to use these variables that you see right here and we're going to just take this data and put it in the database so how can we do that first things first you're going to want to actually import sqlite3 so sqlite3 comes by default with python3 so you don't really need to install anything no pip install no packages needed just directly import sqli3 obviously you have to install db browser if you want to follow along and actually take a look at the sqlite database all right now that you've imported sqlite3 what we want to do is actually create a database using the code so before what i showed you there actually was a database that i was taking a look at and going through now let's say for example your database doesn't exist there's no db file there's nothing how do i create that programmatically through the python code well let me show you so first things first we need to create a connection with sqlite3 so i'm going to create this variable called con and it's going to be sqli3.connect and i'm just going to name my database data.db here this name is definitely up to you as long as it ends with this dot db extension so now that we have this we want to also close the connection make sure you close it at the end of using it so you create the connection and you close it now how can i actually create the table well after actually creating the file here i have a table create query allow me to note one thing however if this data.dbrd exists the connection will be made automatically if it doesn't exist the data.db will be created and then the connection will occur as well so from your end you do not need to check whether the db already exists now for the table you do need to check if it already exists this is a type of special syntax this is sql itself so the query language itself this is how it looks like so we have to follow the proper syntax this is not just any python string so as you can see we say create table if not exist we're saying if the table student data doesn't exist please create it and if it does exist then just don't do anything this is the point of this query and then we specify the different fields that you see right here so you can see we say first name it's a text so this is the data type so here you have to specify each column and then the data type then column then data type so each of these guys is going to be a different column inside the table inside the database so this is how this whole thing will look like now this query will allow me to actually create my database so obviously i created the query i wrote it down inside a string in python and i saved it in a variable called table create query what's the next step the next step is in fact to actually execute this query and to do so you simply say connection dot execute and i will just say table create query and after doing so we obviously close the connection and we should have a new table inside our database inside a new database with these different column names so let's actually try it out let's go and run the program and obviously for now no data is going to get inserted because we haven't written any code related to inserting the data just yet so for now we're only creating the table with the columns so let's go ahead with john smith and then let's give him a title and just say he accepts the terms so we enter the data and as you can see this data.db here was just created so this is a database file obviously i can't open it up in vs code i'm going to go to db browser and i'm going to open database so i go here and i select data.db you can see the type is a database file let me open it and as you can see there is one table called studentdata just how we specified and it has all the columns that we told it to have so it has first name last name title and everything else so obviously here you can use new record to enter some records manually but what we want to do is we want to enter it through the data entry form so now we have the table we just want to fill in the information and as you saw with the the actual data.db file didn't exist so it was created using this sqlite3.connect line of code so next what we want to do is we want to actually insert the data that we have in the form how are we going to do so well first things first we're going to need two things we're going to need a data insert query and a data insert tuple and what's the difference between these two i'm going to show you as we discuss each one step by step so the query is very similar to the way we wrote the table create query it's going to be a line of sql code in which we're specifying we're telling it insert some data into this database and this is how it's going to look like it's going to be insert into student data which is the name of our table then you specify which columns you're inserting into so you have first name last name title age nationality and so on these are the same columns as this one so here i'm saying insert this data insert these values into the following fields into the following columns when you do so you're basically telling sqlite to place the first name inside the first named column the last name inside the last name column and so on but as you see here the values we didn't really give any value we just put a bunch of question marks a total of eight question marks for eight different columns so if you want you can see here we have eight different columns that doesn't really make sense why is that the reason is we don't have the values just yet we want to fill them in programmatically using the code so to do so what we're going to do is we're going to use these variables in which we captured this information so the first name last name title age where we got this information from tkinter we're going to fill that in here and we do so using the data insert tuple so the tuple is going to look like this it's basically a python tuple of length eight and it contains all eight of these variables and then what's going to happen is this tuple will replace this part right here the question marks so first name is going to go here last name is going to go here title is going to go here and so on and then each one will be inserted respectively into the correct column inside the student data now why didn't i just write first name here well the reason is this is a string and we would not be able to access the value of the first name variable so this is why we put it in here to replace this question mark all right now that we have the query and the tuple we have everything we need what we want to do next is we want to use the cursor so the cursor is sort of the mid-way between our sqlite connection and the actual database so it will sort of execute all the queries and then it will decide where everything is being inserted so at what point in the database so this is very useful when you're using queries and you're inserting data into a database other than the cursor i want to actually execute so i will say cursor.execute then i will insert the data insert query so i'm saying execute this query and put this data so the data insert tuple use this data in your execution finally the very very last step is this one so connection dot commit you need to use a commit whenever you're inserting data into an sqlite database the reason is that if you don't commit these changes they will not be made to the database and you will finish your program without having any commit so this is definitely necessary you need it in order to save the data in the database all right with these few lines of code now we should be able to insert our data into the database and let's actually try it out so i'm going to stop the previous execution and read on the program i'm going to come here and i'm going to say john smith okay my bad so this should be here smith and then um title i'm just going to put like any data here and then just a bunch of numbers just so we can have a bit of information and we say we accept and we enter the data after entering the data i'm going to go to db browser i'm going to press this little refresh icon and as you can see the data related to john smith has been inserted into this database now i can also choose to maybe go with jane smith and change her data and enter the data as well then refresh and you can see we have a new entry to the database all right so that is really it for the video so i was able to show you how you can actually use the data entry form that we created previously and add this sqlite element to persist your data and save it inside a database and how you can use sql to actually create this table and insert these values that we captured using tkinter so i really hope you enjoyed this video and i hope you found it useful do let me know what other videos you would like to see on this channel and thank you so much for watching bye bye

Channel: Code First with Hala

Views: 48,219

Keywords:

Id: gdDI_GhIRGo

Length: 13min 36sec (816 seconds)

Published: Fri Sep 09 2022