Python Tkinter SQLite Example

hello and welcome back to the python beginners plus course in this tutorial we take a look at progressing our knowledge from the previous tutorial where we looked at building a dickinson gui interface and we're going to connect it to an sql-like database in this tutorial so that we're super clear what's going to happen here we're going to build a simple interface with some inputs and then go ahead and in our code create a new database here we're using sqlite database so there's no installs everything should just work and then we'll go ahead and build a simple interface so that we can add items to the database from our graphical gui interface so this is the final product here we've got our nice interface here and we're going to add in some in this case new users you can change this to whatever you like so let's add a new user username password we've added a new user if we happen to try and enter a user that already exists then it's going to say user name already exists all right so let's get started right so let's start off by building our interface with kinser so from or tkinter from tk enter s import this time we're just going to import all so previously we used as tk so let's just show the other way that you might see other people utilizing so we're just going to import everything and the first thing if you remember if you have seen the previous tutorial is we're going to make a window so that's going to be the window interface that we're going to specify so we can place other elements onto it so that we can put some input forms for example so we can type some text and then eventually that would go into the database so let's go ahead and activate that so once that's done um let's now go ahead and set out the geometry geometry of our window um so we want to set the size of this so let's go for for example 450 by 180 so that's going to be the size of our window that we're going to make now we can quickly test that of course if you remember let's go ahead and run that window.main loop okay so let's just give this a go here we go just bring this in there we go so that's what we've just built here we've built um we brought bring brought in the tk into tools we've initiated a a new instance so that we can then specify our window geometry 450 by 180 and that's what we're looking at here and then we've started the application utilizing window.mainloop so that fires everything off and now we have an interface so next up let's have a look at now building our database so that we can actually store some data in our database so here in visual studio code if we go down into the extensions i do have which will make it easier for you sqlite installed and that's just going to allow us to inspect the database and all the data inside of it so go ahead and install that and let's just go back to the code here right so next up then we're going to need our database so let's import sqlite so this is a lightweight database which we can utilize uh to store data um right so that imports our database so we now have our database we can work with so what we're going to need to do here is well let's we're going to need to make a database right so let's go ahead and try this now we don't need to actually physically or we don't need to actually initiate a database here on the right hand side we can do that with the code right so let's go ahead and use with so with uh sq our light or sqlite 3 let's go ahead and try and connect to our database which doesn't exist yet so this is going to build a new database so let's just call this for example details or whatever you want to call it db so that's going to be our database file and now we're going to connect or we're going to be able to access that as db okay so that's going to provide here a connection or it's going to build a database initially and we're going to then utilize this whenever we need to connect to a database and store data of course or collect data from the database so we're going to utilize or reference that as db whenever we want to connect to the database or send data to the database right so let's bring in the cursor so a way to explain this what we're doing here is db.cursor so i'm accessing our database here through db and i'm getting what is known as the cursor right so this is basically going to allow me access to the database and to save data and to collect data from the database so basically i'm going to use this so db cursor so basically what i've done here is i've created a database or that will create a database and i'm going to access that database through db and i go into db which is kind of storing all the database and then i'll go inside of there and get the cursor and that cursor is going to allow me like i said to access or execute queries on the database so we can store data so i'm saving that as cursor so now i'm going to use this word or this variable we've set up here cursor whenever i want to access the database for example save things to the database right so now we've got that in place let's just go ahead and run and you can see straight away we do run our application still but we now have a database so if you do have the extension sqlite installed what we can do now is right click and then we can go to open database here and you can see that we have a database there's nothing inside of it at the moment we're going to need to build some tables of course in a minute but we do have our database now available and we can now go ahead and i said let's make some tables all right so i appreciate that you may not know anything about databases at all so we're going to need to use some sql code here to create some tables so in our database there'll be a table that table will have some fields where we'll be able to store some data so we're going to need cursor in order to perform this action and then what we're going to do is we're going to execute an sql statement a command on our database to build some tables so let's do that execute and now we're going to send or use a sql statement we're going to wrap it here in the triple quotes here so we're going to create a new table so create table um if not exists okay so i don't want to keep creating a table if it were to exist um then we just simply won't run this command uh yeah exists i spell this right exists okay so create table apologies create table if not exist and the table we want to make in this case is users so i'm going to make a table called users we're going to have for example two fields in our table so let's go ahead now and build that up so inside my table i've got an id field and that's going to be an integer so a whole number and it's going to be a primary key so it's a unique field so you can't have duplicate items in here and what we're going to do is we're going to make that primary key and then it's going to be auto incrementing okay so that means that anytime i add anything to the database a new field this is just going to auto increment um for example by by one so we'll see that in action um so increment right so we've got that so we've got our first field it's called id it's going to be an integer type so a whole number it's going to be a primary key so it's going to be unique field and it's going to auto increment so every time i add something to the database it's just going to add one to it so for example the first record here is going to be 1 the second record is going to be 2 and so on right so now i use comma because i want to add a new field so this is going to be username it's going to be that's just text for example um and then it's not null so the user has to type in something um else that won't work uh so i've just used text there that's probably not not the best type but that's gonna work in this instance so password same again text it just makes sense if you're not ever utilized sql before text makes sense to you right it's just text so we're now going to save a password as text and then not no so again we're saying not no because we don't want a user to be able to make a username and password without entering one or the other so not null right so once we're done with that let's finish off with the semicolon i'm going to press o and z so we can just wrap that a soft wrap here so we can see the whole thing and there we go that's our sql statement so create a table if it doesn't exist users and we're going to create three fields id username and password right so if we fire this off now we would like to expected it to had worked um we've got a problem here potentially attribute error bulletin function or method object has no attribute execute so after much soul searching i found the issue okay so we're going to need the parentheses there so cursor right so now with that in place let's go ahead and run that you can see our database has been built and our application has started but let's go into this so right click now open the database remember you're going to need the extension extored install the mysql extension and now i can see i've got a table excellent so inside of my table i have three fields where i'm going to store some data id username and password right so our database is now set up for us to now add some information to it so let's go ahead and finish our interface here so we're going to need two inputs so we start off with a label of course label one it's going to be a label so we're going to borrow this from the tk into the package and it's going to be text enter username so we built a simple label that's going to be a piece of text that's going to appear above our input and then we're just going to place that onto our window here x and y so x from the left to the right y from the top to bottom that's the business positioning there of it and then we're going to have a background of light green and i'm just going to remove any padding on the left and right padding x so next up let's actually add the input so i'm going to call this username this is going to be an entry so where i can add some text in this case the username and then i'm going to place that onto my window so x and y again i'm going to give it a width of 200 height of 25 and there we go so let's just go ahead and run this have a look see what it looks like and there we go so now we can do the same thing for the password so let's call this label two so these variable names have to be different of course so this is label two it's going to be enter password and now we'll just position this again like we did the first label and i'm going to give it a light green background for no particular reason and just remove any padding on the left and right and then same again password is going to be an entry uh text by default there's nothing there and i'm going to place it onto our window x y width and height and let's just give this a go again so we'll run this again you can now see we have username and password so the final step here then we're going to need a button so let's create a new variable button we're going to use the button from the tk into package and then text add command and we're going to connect this to a function shortly called add new user so that doesn't exist at the moment but let's just go ahead and place this button onto our window and let's just go ahead and build this function in preparation so i'm going to build this just above here a window so this is going to be a function i'm just going to a placeholder in place pass just so it can still work for now okay so we've added a command here so when we press the button it's going to fire off this function here called add user add new user right so let's just give this a go and there we go right so let's build out some of this functionality that we're going to need to actually collect the data from our form prepare it and then send it to the database so it saves to the database right so we know here that when we press this button here it's going to fire off this command or this function so this function is going to start so what we're going to need to do is we're going to need to collect the text from the inputs from the two inputs in this case the uh the two entries so the username and password so we need to collect that first so let's go ahead and do that so let's go for a new uh new username equals username dot get okay so the username is a reference to the this variable here username so we're just storing data in a variable that the users typed in so we're just going to collect that and then we're going to need to do the same thing again with the password ecos password dot get right so now we've collected that data now we need to prepare that data and send it to the database right so we're going to need our cursor again and execute so we can execute fire off a we can fire off an sql statement right so let's do that so we're going to select um first of all we're going to check to see if for example there is a user already in the database so we're going to use a select so we're going to select something from the database but we're going to select and count all okay so what we're doing here is we're setting up a command we're going to select everything in the database and then we're going to count it so what are we going to count so we're going to count from users okay so that's the name of the table remember so basically selecting everything and counting everything from the users table at this point but we don't want to select and count everything we only want to count where where the username is equal to for example the username that was typed in okay so that's going to be important so let's go ahead and do that so we need to from this point we need to grab the username okay so plus a new username this is where it gets a little bit tricky because of the syntax is just need to be careful so here we're just trying to utilize the data from this variable so just be careful with this syntax uh plus and then double single okay so we've we've opened up with a single and then a double a plus and then the actual data plus okay so that's that done and then we want to get the result so that sounded looked a little bit tricky let's get the result uh which is going to be cursor dot fetch in this case we just want to fetch one so we're going to be returning in this case one item so there should only be one if we make a match with a user there should only be one item that's returned so here we're going to use cursor batch one now if there was multiple items we would probably not use fetch one you can see there's a few options here factual and that allows us then to iterate over multiple items if we did return them but here we're just going to use fetch one right okay so what we've done here then is we selected everything from a database where from the user's table sorry where the username equals whatever the users typed in so here we're just going to check to make sure that there's no user with the same username if there is then we can do something about it right so this result is stored in this variable called result so what we've done now is we've executed this query and now we're returning it here into this result variable so if we did make a match we'll be able to work that out how we can work that out in actual fact is if there is a match if for example we typed in bob and there was already a username called bob we would return one item from the database so that means that if there wasn't a username called bob in the database we would return zero so therefore we can then turn this into an if statement so be an integer if result and the first instance of that result is for example uh less more than zero so if we return result if we return from a database a result and there's something commit that's going to be one so result is going to be one so if it's more than zero it means well we've got an error here because it means that there's an item in the database that matches whatever the user has typed in so what we need to do now is create an error right so we're going to need a an error message let's just pass there for now so let's just go back into our interface um maybe at the top here let's just go ahead and add a section where we're going to display an error okay so let's create a new variable called error it's going to be a message that's going to allow me to place text in the window it's going to have a width of 160. so the original text is going to be nothing there's nothing inside of it and then we're going to place it onto our window and then just give it some padding left and right of zero so that's going to display nothing by default but what we're going to do is if we do receive an error if there is a match with a username we're going to fill this text up with something and that's going to appear in our interface so let's go ahead now and just finish off this if statement so we're going to grab that error section as or that variable called error error and then what we need to do here is go ahead and in the text we just need to update it so that's going to equal error username already exists okay so if we do match if we do make a match here we're going to return one in which case it is more than zero so therefore we're going to run this statement here and we're going to update the error text to display error username or already exists cool now of course if that doesn't match we're going to run something else and that's obvious obviously to save that data into the database so we'll use the error for now so let's just update the error text maybe we should call that info instead of error and then let's type in added new user so if the users don't match if the username user typed in doesn't match something that's already in the database then we're going to say added new user and then we just need to add the new user so again let's bring cursor execute same type of situation here and this time we're going to insert so we're going to run an insert sql statement insert into users that's a table and then we just need to set up the data we want to insert so we want to insert data into username and password those are the two fields where we want to store data so that's what we specified there and now we can specify the values that we want to insert so values and then here we're going to use some placeholders so i won't fully explain this let's just call these placeholders okay and then i can go ahead and now put the data inside of it so let's go ahead and do that so new username do you remember we're storing the data originally the user types in in new username and new password so now we're going to pass this data into the values here which is going to then pass into these fields and it's going to then be inserted into the database there's kind of a whole flow here the format i'm using here is for security reasons okay right so there we go so now we've done that we just need to now commit this so db commit so we're gonna go ahead and run this happy days and that query should now run and insert the username and password into our database right so there we go that's um i think the whole application now built so let's give this a go so we have username password so test test add added new user so let's go ahead and just try this let's just show this table now we might need to do a refresh here so let's show the table you can now see in our database we have a new username so the id remember this was auto incrementing so if i were to add a new user here for example test2 that is going to auto increment because we're not actually adding anything into this field it's just going to auto increment for us so i need to just refresh right click show table again and there we go we now have our second user excellent right so let's go ahead and try out our see if we can capture the error so let's just type in or just let's just keep that the same so let's just try and add in a user with the same username that already exists into our database press add and now it says error username already exists there we go so that worked too so we've covered quite a lot there in actual fact although we've just inserted into the database of course we would want to potentially update data in the database so the crud situation create data return data update data delete data this is just an example a simple example to help you get started and start thinking about using uh with python gui interface and we're working with an sql-lite database and there's a lot there of course because you need to know a little bit about databases the graphical interface and python at the same time so there's a lot of skills that you're building upon here in order to get this working and this was just meant to be a a nice example for you to start thinking about potentially utilizing these tools so i do hope that this was useful if it was just let me know give me a thumbs up and any suggestions just let me know and hopefully i'll see you in another tutorial thank you very much

Channel: Very Academy

Views: 2,593

Keywords: tkinter, python tkinter, python gui, python tk, tkinter tutorials, tkinter python 3, building a gui in python, python tkinter tutorials, python gui programming, tkinter example, tkinter sql, tkinter sqlite, tkinter database

Id: geDjQUaTKAY

Length: 25min 20sec (1520 seconds)

Published: Sat May 29 2021