SQLite Databases With Python - Full Course

alright welcome to the course my name is John elder from Cody Macau and I'll be your instructor today in this video I want to spend just a couple of minutes sort of touching base talking a little bit about what to expect from the course talking about some of the tools that we're gonna need and letting you know how to get in contact with me if you have any questions along the way but before we get started if you like this video want to see more like if you sure to smash the like button below subscribe to that channel give me a thumbs up for the YouTube algorithm and check out my website Cody me comm where I've dozens of courses with hundreds of videos that teach you to code use coupon code camp to get 65% off membership that's all my courses videos and books for one-time fee it just $27 which is insanely cheap so right off the bat I'm on a Windows computer hopefully you are too if you're on Mac or Linux though no big deal whatsoever you can definitely follow along all the tools and things that we're gonna use in this course have a Mac or Linux equivalent and there shouldn't be any real changes at all between what I'm doing here and what you'll do on your Mac or Linux the code is gonna be the exact same there's no changes that need to be made or anything like that it should work just fine so speaking of the tools that we're gonna need we're gonna need obviously Python now sequel light actually comes with Python so there's nothing else to download besides Python it will also need some sort of text editor to write our code on now I'm going to use something called sublime text if you have some other text editor that you like using pycharm Visual Studio code anything at all notepad plus plus absolutely feel free to use that if you're comfortable using that if you know how to use that if you know if you like using a particular one over another one absolutely feel free to do that no big deal whatsoever we also need some sort of terminal to write our commands on now windows computers come with several terminals there's the command prompt there's PowerShell now those aren't that great so I'm going to download something else something called the git bash terminals completely free sublime text is free too as well as Python so nothing to buy in order to watch this course and but like I said git bash works great so we're gonna use that if you're on Mac or Linux you could just use the terminal that comes with Mac or Linux just got a little search function type in terminal and you should be fine you can't absolutely download the get bash terminal on Mac or Linux if you want to follow along exactly but it's definitely not necessary so that's really all the tools we're going to need in order to take this course like I said not much to see qualité as our tools and stuff since it just comes with Python so that's really cool so finally I want to talk about how to get me contact me if you have questions so I'm in Las Vegas which is Pacific West Coast time in the US so if you're on the other side of the world and it's during the day I'm probably sleeping back in Vegas so just sort of keep that in mind when you sort of factor in a response time but absolutely feel free to ask questions along the way if you don't understand something if your codes not working correctly if you just don't quite get something that I'm saying and you need some clarifications shoot me a message that's what I'm here for I do this full-time this is my job so I'm either creating courses or answering questions for students and so that's cool you can get in contact with me on my website Kota be calm there's a contact form and just click on it it shoots straight to my email so I'll see that as soon as I you know as soon as my email dings and I can see it you can also obviously post a comment below this video if you're watching this on YouTube or wherever and that will work just fine too so there are no stupid questions if you know if you want to ask a question on something else I'm not as receptive but absolutely anything in this course anything in these videos you don't quite understand or like I said if your code isn't working if you're getting errors or something shoot me a message we'll get to the bottom of it and that's cool alright first things first we need Python so chances are you might already have Python on your computer even if you do I recommend you uninstall it reinstall it and download it again and reinstall it and I'll show you why in just a second so to do that we're gonna head over to Python org you can google it if you want or just type in Python org and come to downloads and you can see right here it says Python three point seven point three that's the most current version right now if that version has changed by the time you watch this video as long as it's not version four anything below four go ahead and download whatever version it is perfectly fine so go ahead and click this button and we want to save this anywhere I'm just gonna save it on my desktop and it's not a very big file in fact it's boom it are downloaded and so go ahead and click it to install this thing and we want to run it just clicked it a couple of times there okay so instead Python 3.72 32bit now this is the first thing there is a 64-bit version the 32-bit version is actually the one that you want it's the most common one it works perfectly fine on 64-bit computers we all have a 64-bit computer so don't try and go find the 64-bit you can download the 64-bit version from the website I don't recommend that you do that it's just a hassle there's no real benefit to doing that so this is the one we want now before we click this button this is the most important thing right here and it is so very important and this is why I recommend that you download this and reinstall it even if python is already on your computer and that's this little check box right here add python 3.7 to path and on a Windows computer path means you can run Python in this case anywhere on your computer right you don't have to be in the Python directory in order to run Python if you don't check this box you have to be in the Python directory in order to run Python you can't just run it from anywhere so we want to be able to run it from anywhere right so go ahead and click that box make sure there's a little checkbox the check mark is is selected on there and it's so weird that by default this is not selected we absolutely want this checked and if you install Python some other time chances are you didn't click that little box because it's not the default you're not paying attention whoever reads the stuff on the installation screen you just click Next right so there's a good chance that you don't have Python installed on your path and we want to make sure that it is so go ahead and check that once you do that go ahead and click install now and I'm gonna click no and cancel this thing because I've already installed Python but you'll click OK and just wait for the installation process to complete it'll scroll through some stuff there'll be a little bar status bar and then that'll be that and you're good to go now in order to use Python we don't have to launch Python we don't have to start it we don't have to do anything at all we've added it to our path now we can just use it anytime we want anywhere we want always on our computer so very cool if you're on a Mac I don't have instructions for you to install on a Mac you can just go to youtube and type in Python installation Mac and it'll see a little two-minute video I'll show you if you don't already know yourself chances are you already do so we'll just leave it at that so that's all for this video in the next video we'll jump in and look at installing our text editor and our command prompt terminal okay so we've installed Python now we want to install the text editor that we're gonna use throughout this course and I am just using this it's the sublime text editor we'll take a look at it more detail in just a minute but it's sort of like the standard text editor most coders use it it's super easy to use has all the bells and whistles that you would want and it's completely free now you can actually pay a little box will pop up every once in a while asking for a donation so if you want to throw those guys some money that's cool you absolutely don't have to you can use it forever for free just whenever that box pops up every few weeks or so just click no and and that's that so to use this thing I'm just gonna go to google and type in sublime text and the first thing that popped up is sublime text calm or you could just go directly there and here's the website all you have to do is click download for Windows right now it's sublime text 3 build 3 207 if it's a different number by the time you watch this video no big deal whatsoever just download the latest version now I'm not going to install this because there's nothing to it just like in the last video with the Python the little thing will pop up down here you click it and just walk through the installation wizard I don't think there's anything you actually have to do I just click all the defaults ok and then install it and after you've installed it you can run it by going to your Windows Start menu and just typing in sublime and little icon will pop up and you can click it so that's cool now the last thing we need and the only other thing we need is a terminal something to run commands on and it really doesn't matter what kind of terminal you use if you're on Linux or Mac they come with a terminal that's perfectly fine you can use that on a Mac just go up the little search thing of the type at the top and type in terminal and it'll pop up you know if you're on Windows there's a command prompt or a PowerShell you can probably use either of those I don't like using those I enjoy instead using the Python or using the git bash terminal allows us to do version control we're not going to do any version control in this course because there's not much to it code wise but if you've got a big project and you want to use git or github you need something that'll for that and this gate Bosch terminal does so it's really nice and it's absolutely free so use that I'm just gonna go to Google Google and type and get Bosch that should be it and then here's the website git - SCM com4 slash downloads and so we can click download this and I will walk you through this one because it's a little weird so I'm just going to download this to the desktop and it takes a little bit longer for this to download it's only 44 megabytes and so it should download immediately especially on a high-speed Internet like I have for some reason it takes a bit to download it always has it's something about this website or something it's just it's on a slow server or something and here we go it's downloaded so go ahead and click that and run it let me pull this over and so okay it's just the license click Next and you can leave all of these the defaults now here it's saying what text editor would you like to use now this is weird because we're not going to use this in a text editor you don't use it it's a standalone thing right so we are gonna use sublime text so I'll just leave this but you can just ignore this if you want just click Next and here we want to use git and optionally unix tools from the command prompt we're gonna like I said we're also we're gonna use the git bash terminal on its own so this again doesn't really matter but we'll leave this one clicked here at the bottom and click Next OpenSSH this is for pushing stuff up to like github where you need an SSH key we're not gonna use any of this doesn't matter we just click Next same for this click Next and this is well this is a get thing we're not gonna be using get really in this course so we can click Next again same thing with this min TTY thing next basically were just taking all of the defaults again click Next you get to this final thing and then go ahead and click install now I'm not going to do that because I've already installed it so I'll just click cancel but you can click install am I sure you say em and it takes a couple of minutes to install it has a little you know statusbar thing that'll scroll through and finish when it's finished and yeah that's all there is to it so those are all the tools we need we don't have to download anything else in order to using this so in the next video we'll jump right in and start writing some code and using the database all right so we've got all the tools we need all the things we have to download and install now we can just start using this database so most people don't realize sequel Lite 3 comes with Python it's built in already so there's nothing to download there's nothing to install it's already there all you have to do is just sort of reference it in your code and start using it so in order to use sequel I three all we have to do is import and then name it so it's SQL ite 3 right so this is just a blank file that we've opened in sublime first thing we want to do is save this and we need to save this as a Python file so I'm look at my pair to file and save as and go to our C directory here and we need to create a new directory in order to hold all the files that we're going to be creating throughout this course so I'm just going to right click and click new folder and let's just name this sequel Lite so then we can click on here and we can name this anything we want let's name it sequel Lite PI well no we don't want to name it the same thing that we import here and then the sequel Lite 3 so you go let's just call this a database dot PI and you see as soon as we do that things sort of change color a little bit this becomes red that tells us that sublime now knows that this is a Python program a Python file that we've created right so that's cool so all right now we've imported sequel i3 the next step is to create a connection and in order to do anything we need a connection to our database so we need a way for this file to sort of connect and do stuff so to do that's really easy we just create a variable you can call it anything you want but I'm just going to call this con short for connection and then we want to send set this equal to sequel I'd 3 dot connect and then we want to pass in the name of the database that we want to create now this will connect to whatever we call the database and if it doesn't exist it will create it in this directory in this sequel Lite directory that we just we just create it so we can call this anything we want let's just call it customer dot DB we're gonna pretend we have a company we have customers so we're gonna create a database of those customers so that's pretty much all we need to do and one thing I will note sequel Lite 3 allows you to create a database like we've just done or you could also just use a database in memory and it won't save it you can use it in your program but then as soon as the program ends the database disappears so you can do stuff to it but it's not permanent right so if you want to do that I'm just gonna copy this and paste instead of naming the database oops but instead of naming this database instead we just type in colon go on colon and then memory and then another colon right so this will create a connection to a database in memory now what we're not going to do that in this course we want to actually save our database that we can use it later on if we want to but if you've just got some little thing you need to store some data do some stuff too and then delete it afterwards this memory thing will work so that's kind of cool I'll just comment that out okay so we've got our connection and if we save this I'm gonna hit ctrl s on my keyboard now we can run this it won't actually do much well that's not actually true to run this head over to your Windows Start menu and type in get bash right just search for get bash and that will open up this and if we go PWD we can see we're in the C users flat planet directory now my computer is called flat planet it's just the name of it I don't think the world is flat I just find it hilarious that some people do so that's when I named my computer whatever your computer name is or whatever your user name is you'll likely see that there so what we want to do first off is change directories we want to move into that directory we just created with our sublime text so to do that we use the CD command stands for change directory and then just type in C and then sequel Lite I think that's the name of the directory we just created with sublime and if we type in LS we can see our database PI file that we've just created and that is just this file right here all right database dot PI and there's not much going on in here just yet but we can still run this and to run it as long as we're in this directory we just type in Python and then the name of the file which is database and if we do that it doesn't look like anything's happen but now if we type in LS we see this customer dot DB file it's been created and that is just this guy right here so the database wasn't wasn't there before but when we ran this connection it noticed that there wasn't a there wasn't a database called customary DB and we're trying to connect to customer DB so it just creates it for us so that's a real quick and easy way to create a database and that's all for this video in the next video we'll create a table and then we'll start adding data to that table and should be fun all right so we've created our database we have a connection now we want to build a table in order to you know put data in and if you're not really familiar with databases a database itself doesn't really do much it's the table inside that has all the stuff in it all the data goes into the table whenever you query a database you're querying a table whenever you're putting information into a database you're putting it into a table so it's the table that's the important thing of a database and think of a table as an Excel spreadsheet it's just a big thing with rows and columns right that's pretty much it so if you can visualize a spreadsheet that's what a table is and so to create a table in sequel a3 it's pretty simple first we need to create a cursor and a cursor is it's sort of like I don't know it's it's it's what tells the database what you want to do so whenever you do anything you're going to use your cursor to do it and you'll understand more exactly what that means as we start to use this it's really easy we just have to build it once and then we can use it so to create a cursor we just create a variable and you can call it anything you want you could call it cursor if you wanted I'm just gonna call it C because we're gonna be typing it a lot so just C so C equals and we want to connect to our connection this con and then we want to create a cursor instance and you don't need to know what that means it's just it's the cursor right so we do that and let's make a comment and I'm gonna go create a cursor okay so now we can use this cursor to do all kinds of things and the first thing we want to do is create a table so let's go create a table now so to create a table it's really easy we use our cursor C and whenever we're we want to do things in the database we're almost always executing some command so we want to go C dot execute and then pass in whatever commands we want this case we're gonna create a table and there's a couple of different ways to execute commands using sequel and the first one I'm going to show you is something called a docstring I think this was yeah doc string and it's just six quotation marks right and inside of here you can do multiple things on multiple lines right that's why we want to use this doc string and this is what the Python documentation recommends you do now a lot of times when we execute a command to our database we're just doing one thing right add Bob to the database right so that's one line we won't use doc strings we'll just use regular quotation marks but in this instance when we're creating a whole table there's gonna be a lot of data to push up we want to do it on multiple lines just so it's easier to read and so we'll use these doc strings so what we want to do is create a table and what do we want to call our table or our database is a customer database so let's create a table of customers plural so the customers table is in the customer singular database call it anything you want though it doesn't really matter and now all we have to do now is define the rows and columns that we want so this is gonna be on several lines I'm just gonna tab over here so what do we want well we want to record customers first names their last names and let's say a email so we just sort of put all those things out we've been first and what we want to call it first underscore name and then we have to decide the data type I'll just put the data type for now and we'll talk about data types in a minute so first name last underscore name data type and email or email address let's just do email and then data type okay now we can yes and let's have these over again okay so I'll talk about data types in just a second but let's look at this here we're creating a table and one thing I should note sequel like three is case-sensitive so the things you type capitalized or lowercase they matter so this needs to be capital create table and then lowercase customers okay so let's look at this whole command if I copy this and print it again just to give you an example without the doc strings we could just use regular quotation marks but you see you get these big red angry error messages here in our sublime text that's because this all needs to go on one line if you use single quotation marks and let's see finally boom so okay this will work right this is equally valid this is valid and this is valid but just look at this right here like it's hard to read this right you know you got a scroll you know it's all smooshed together it's just no good that's why I use these doc string triple quotation marks in this case so just a quick little aside so alright we're almost there now we need to talk about datatypes and datatypes if you're familiar with programming a data type is a type of data so like think strings numbers integers boolean's Python has a bunch of other data types like lists and dictionaries and things like that if you're familiar with data types it's the same thing here you have to define the type of data that's going to be in each of these fields so in our case first name last name and email those are all text type things so we need a take that we need to define a text data type now the thing about sequel lights is actually kind of cool I actually like is it only has five data types that you can choose from other databases my sequel Postgres any other database you can think of has dozens of data types and it can get confusing because you only really use a couple right if you think about it you're going to use text you're going to use numbers that's probably about it right so that's kind of cool that sequel I three only has five and the five are null let's see an integer I'll talk about these in just a second here a real text and blob right so Knowle just means does it exist or does it not exist if it doesn't exist it's not right if it does exist it's not an all right an integer is a number a whole number 1 9 106 2 million right as opposed to real which is a decimal 10.5 right 1995 that's real right so if you're using something as a number just use integer if you have decimals use real text is just how it sounds to text and finally blob is it's it's it's extraordinary file music file might be a blob right it's just a blob right so pretty simple in our case we're gonna use text for all of these so we just type in text text and text and that's it all right let's go data types right okay so we're almost there now there's one more step actually two more steps we need to do we've created this cursor right and now we've created a command for the cursor to do but our program hasn't actually executed that cursor command yet right in order to do that we need to commit this thing to the database and if you're familiar with other databases committing something to a database as a common theme here to do it we just commit our connection so Khan dot commit right and that's it this will now execute this guy right here push whatever we're trying to do into the database and do whatever we're trying to do finally the last thing we really need to do and let's go commit our command the last thing we need to do is close our connection so anytime you create a connection like we did up here right you want to close that connection you don't have to when your file stops running it will by default close itself but it's just best practice to do it explicitly and to do that we just type in conn dot close all right okay so if we save this and now run it pull up our terminal clear the screen and I'm just gonna push up on my keyboard and that will scroll to previous commands and we just type in Python database dot pi which is the name of our file and we hit this boom nothing has happened on the screen but we didn't get any error messages so that means that this thing executed correctly we've now created a table called customers inside of that customers table there are three columns first name last name and email and now we can start sending data into the database and we'll look at how to do that in the next video okay so we've got our table created now we want to put data into the table so how do we do that well pretty simple we just create another cursor command right so seed execute anytime we do anything we want to execute our see our cursor right and so now we want to insert data into our table so we use the insert into sequel command and this is all just sequel if you're familiar with sequel sequel these are just sequel commands right very basic so they're always capitalized like that so we're we want to insert into customers the name of our table and then values and then just type in whatever values we want so remember we have first name last name and email so we need three things right so we want let's go John and we separate each of these by comma elder comma and then John at Kota me.com right so you notice I'm using single quotes that's because we started this string off with a double quote so if we used if we try to use a double quote here the whole thing would be wonky because it would think that we're trying to close this double quote instead we just use single quotes and everything works fine so let's see that worked that looks good now again we need to commit this command so we'll just leave that there and we need to close our connection so let's go ahead and save this come back here and run this again and again we get no message now we could create a little message if we wanted to you know we could just print to the screen command executed successfully I don't know you know if we wanted to do that we could we definitely don't have to do that so we've now inserted one record one row into our database we can do more than that we could go Tim M Smith let's go Tim at Kota me calm so if we save this come back here run it again command executed successfully it makes you feel a little better that something is on the screen right whatever let's do one more let's go Mary what's a good last name Mary Brown I don't know and she's at Mary echo to me calm save this run it again okay so now we've got three records in our database pretty simple to sort of add one thing at a time to our database and it's all for this video okay the last video we inserted one value at a time and sometimes you want to do that but sometimes you want to sort of insert lots of things all at once how do you do that well it's a little bit more complicated but not too much more complicated so let's create I don't know many customers a variable and we want this to be a Python list right so inside of here each item as you would with any Python item you can go like this right so each of these things become an item right so let's say like a couple inside of a list right and here you would just go let's go Wes at Brown and let's go Wes at Brown comm right and we can put these on multiple lines if we want to sort of just make it easier to read we can go I don't know staff and staff at and we can go an pass and Dan's email address is Dan at Escom all right I'm going to go ahead and put there at the end of that okay so now we need to do a little bit different down here in our execute instead of executing we're going to execute many right slightly different command there and here instead of typing in all the things we want to create a tuple and we want to put placeholders so the placeholder in sequel i3 is the question mark so - a question mark question mark question mark so this is sort of first name last name email right and then we put a comma and we just pass in the name of our list here right any customer actually this changes many customers makes more sense right okay so what's going to do here is it's going to execute many each of these is one two three and here's the placeholder for each one well pretty simple if we save this and run it again command execute a successful so hopefully that has worked we don't know yet if any of these have worked because we haven't actually looked inside of our database to see what's in there and we'll do that in the next video okay so we've put a bunch of stuff into the database now how do we get anything out and sort of display it see what's in there well to do that we need to query the database and to do that we use our cursor see that executes there you go and then inside of here we want to select and let's just pull out everything at this point so star stands for everything from and we want to pull it out from the customers table all right so that sort of creates the query and to actually get the thing we need to then go see dot fetch and you can do three different things I'll just type them out we can fetch one we can see dot fetch many and then pass in how many ever many we want or we can see dot fetch all and this will return as you would expect everything this will just fetch the left the last item in the table yeah so what we want to do is fetch all so if we run this nothing actually happens we have to actually print to the screen explicitly so we would wrap this whole thing in a print function and then we can go ahead and comment out that so let's save this and run it and see what we got here and boom we get all of this stuff and this is returned as a Python list right so here we have John elder that was the first one Tim Smith Mary Brown and then that time in the last video when we did them all at once we had Wes Steph and Dan and you can see all of them are sort of just sort of spewing up on the screen right so this is just all the data given at once obviously you can do all kinds of stuff to pull out specific stuff to make this look more pretty readable pretty you know useful and I think we'll look at that in the next video okay so we fetched all right right off the bat it spewed back all this stuff we can also let's go ahead and comment this out and we could just fetch one and if we want to print this to the screen we wrap it all on print function we save this and run it just real quick just to show you what this other stuff does boom John elder John Academy comm you can see it looks like a tuple couple however you want to say that and you can access it just as a tupple so you notice this is the very first thing so it's not the last thing it fetch one brings you it's the first thing right likewise you could do let's see fetch mini let's comment this out and we want to wrap this save this and run it we get three one two and three it's just the first second and third one right so kind of interesting but we don't want to do that we want to work with fetch all and now we want to do stuff right so well actually first before we do that let's go back to the fetch one now this is a tupple so we can access the stuff inside of it by referencing the the item number so first off before we do that let's just save this and run it again just to show you real quick I'm gonna clear the screen okay so fetch one brings one record John elder and Johnny code me calm so this is the zero with item in the tupple this is the first item and this is the second item this is just python tuples stuff you're familiar with tuples or lists when you number items in a list or a tuple the first one is 0 the second one is 1 and the third one is 2 right so we can access specific ones of those by referencing those numbers and to do that we just I said slap on the brackets here and let's just call 0 the 0 with item that should be John so if you save this and run it boom we hit John so that is one way to do it right and you can do the same thing with fetch fetch all and fetch many for that matter but that's not really what we want to look at right now we want to look at is fetch all and this is a list right so we could set a new variable we could create a variable called items and set it equal to this then we could either just print items in fact if we do this it'll look the same save this and run it as it did earlier just spews out all of it right or you could create a loop of some sort to loop through each thing let's go ahead and give that a try so let's go for item in items print item okay so if we save this and run it just a basic for loop boom we have each of these printed out one line at a time now we're starting to get somewhere it's starting to be a little bit more readable right so each of these are now a tupple and we can do couple e things to them so instead of printing out the whole item we could print out use our brackets and say print out the zeroeth item inside each tuple so if we save this and run it we get john timm mary wes staff and dan very cool there are three columns so we could if we really wanted to play around with this we could go print this plus we can then concatenate and let's make a space and concatenate again and let's go item 1 concatenate make another space and then concatenate it again and go item - right so if we save this and run it now let's clear the screen we get John elder John at code me calm Tim Smith all right so it's starting to format up a little bit and we can play around with this you know we could do a little formatting like that if we save this and run it yeah that's looking a little bit better we could oh I don't know we could use tabs instead that is backslash T all right there's a forward slash backslash so this will tab everything over so if we run this and now things are starting to look a little bit better this one didn't quite make it over because this did tab but it this name is so short the tab didn't go as far that's kind of funny we could do two tabs I suppose right and here we could go outside of the loop we could go name we need rotation marks name yeah plus two tabs and email like right and here we can do print and one two three four five plus couple of tabs and then what one two three four five six seven eight a couple more oh I don't know save this I'm just playing around at this point cuz it's kind of fun alright formatting has gone a little bit wonky here this is too short but you get the idea right you can sort of play around with all this stuff let's get rid of one of these tabs see how that looks there the screen real quick alright so looking a little better the point is you can use all the sort of pythonic things you want loops things like that to format the stuff that you fetched all in any way you want and you use it just by calling any list item and returned data so a little bit much to swallow in this one but really pretty simple when you get right down to it all this stuff at the end which is playing around here so that's how you sort of format results using Python in sequel I three okay in this video I want to talk about primary keys and a primary key is a database thing if you're familiar with other databases you're probably already aware what a primary key is and basically it's a unique ID number that each record in your database gets so for instance we've got we've select everything from our date of database table customers and we've put it into this items variable now we could print out items right if we save this and run it there you go we see each of these items John elder 10 blah blah blah we can like we did in the last video a couple of videos instead we can go for item in items print item if we save this it looks a little bit nicer right so each of these is a record this is a record this is a record this is a record right and you know each record has a column first name a column last name column email well actually sequel 8-3 creates another column sort of in the background with a specific primary key a unique ID there is unique for each of these records and to see what that is it's called a row ID and we can actually select right up here in our select statement we could just go row ID and everything from customers now if we save this and run it it's gonna print out that ID as well so you can see 1 2 3 4 5 6 John - Tim 3 Mary so you know we can look up Mary Brown or we can look up number 3 right and we'll look at how to call specific things from our database in the next video but in this video I just wanted a really quickly touch base on that primary key because it's an important database concept and in most databases you have to create the primary key to yourself and then create some sort of auto incrementing mechanism so that anytime you add a new record to the database it auto increments a new ID so for instance we have six right six is our last ID if we added a new person and we would need to create something that said okay the last one was six so make the next one seven right we don't have to do any of that with sequel Lite it does it for us automatically unless you specifically tell it not to and I'm not even gonna get into showing you how to do that because you're never not gonna want to do that you're always gonna want this ID it's a good idea to have a unique number for each record that you can reference and we'll see why a little bit later on in the course so I just really quickly wanted to show you that row ID it's just sort of unique sequel like three three thing it does it for you it's very very cool and yeah so that's all for this video and the next video will look at pulling out specific things from our database like searching for specific things and that'll be in the next video okay up until now we've just been pulling everything from the database from the table now we want to look at how to pull off specific things how to search for things right so you maybe don't want everybody you might want just everybody with the name last name elder how would you do that well come up to our query here and so we want to let's get rid of the row ID we don't really need that when I select everything from customers and now we just use the where clause where this is just basic sequel where and we could go last underscore name equals and use single quotation marks elder right so if we save this and run it let's clear the screen here we get boom John elder that's cool now we can also use all of our other sort of comparison operators right if we had numbers we could use less than equal to greater than greater than or equal to you know where let's say if we had an age column we don't but if we had an age column where age is greater than let's say we wanted everybody over the age of 21 right or equal to 21 it would do that right so that's kind of cool one you're gonna use a lot is like so we could go where last underscore name is like and then use quotation marks to sort of search and we've got a bunch of Browns so we could go where the name is sort of like B R and then you put this what is that the percentage sign yeah - sort of like a wild-card where it starts with B R and ends with whatever right so if we save this and run it we get Mary Brown and West Brown right so very very cool we've got a Chuco Tamizh right we could play around with that where we go where email is like and we can start out with this percentage sign and then we can go Kota me calm save this I'm not sure why that turned purple like that yeah but we get three returned results let's look at this more let's go let's get rid of this yeah it's just gonna do that it looks like so okay so there were three now we can test this just to make sure that was correct right and let's save this and just run it again so we have one two three and that's all so that was correct so a pretty simple way to search for different things using your query using the where clause and like I said you're going to use your logical operators those are basically you know like I said equal to less than greater than less than or equal to greater than or equal to like and that's pretty cool so in the next video we'll look at updating records making changes once we've already put something in the database and that'll be in the next video okay in this video we want to update records so let's head over here let's just put this stuff down and let's go update records and it's pretty simple pretty straightforward and we just go see go see dot execute all right now this is gonna actually be kind of long so we're gonna use those doc strings again one two three four five six all right and what we want to do is update and we want to update our customers table right now we want to set what do we want to set well let's say we want to change the first name of John elder to Bob elder right so we want to set first-name equal to Bob and then we're and let's go last name equals elder right and actually we want to wrap these in single quotes there we go comeback that time there we go alright so we could go to this all online but easier to break it apart like that so this will work but it's probably not the way you want to do it we'll talk about that in just a second so let's go ahead and we want to commit this so we'll go see commit our conduct commit right then we want to select everything from our database and print it all under the screen just to make sure that the change got made right so let's save this head back over here and run it again and boom we get Bob elder Tim Smith everything else is the same and it looks like it's worked now this isn't a good way to do it I mean it works and this is the correct structure and everything but you're not going to want to make a change to a record based on a last name because we could have a bunch of elders in there and if we did that this would change them all and we don't want to necessarily do that right so instead we want to come down here and let's call our row ID again let's save this and run it and now we see our row IDs what you really want to do is use those row IDs for this exact thing right we want we don't want to update all the records that have a last name elder we just want to update record number one so instead we want to do it that way so let's let's go ahead and try that again and up here we want to set the first name equal let's change it back to John we're and then we go row ID equals one all right so if we save this and run this again boom now it's John elder right so like down here Brown we want to change Mary Brown - Marty Brown right so she's number three if instead we did Brown that would throw off some problems right so let's try that and just see what happens so we want to go Marty we're a last underscore name equals brown right so if we save this and run it boom this one is Marty Brown and this one is Marty Brown see that's no good we can't have that right so instead let's change it back to Mary and we can do that like this save this and run it that gets us part of the way now they're both Mary Brown but we want number four to be West Brown right instead of Mary so we would come up here where row I D equals four all right Wes Wes Wes yep four alright so then we want to change it to Wes so we save this let's clear the screen and run this one more time we get number three meri Brown number four West Brown now everything is right in the world so important that you use ID the row ID the primary key in order to update things also to delete things we'll look at that next you know just very important otherwise you can get into all kinds of problems like we just saw and that's no good so that's all for this video in the next video we'll look at how to delete a record okay in the last video we looked at updating records in this video we want to look at deleting and deleting is very similar to updating is pretty simple we just come over here and go seed execute and then inside here we want to delete from our customers table and just use our where clause where and then what well our data is gone but the last row was that Dan guy he was number six right and let's get rid of him we had trouble with the formatting because his name was too small way back at the beginning of the course so let's get rid of him so where we go row ID equals six now you'll notice I'm not wrapping this in quotation marks because that would make this a string and the row ID is a number it's an integer so you can't use a string when it's a number so I just sort of keep that in mind and I think that's pretty much it so if we save this I head over here and run it again boom number six Dan Pass is gone so that's delete once you delete a thing it's gone dan has been dropped from the table and we delete things we call it dropping from a table so he's been dropped he's deleted he's gone and that's that he can't come back so keep that in mind when you delete something it's sort of permanent so uh yeah just keep that in mind though so that's all there is to delete pretty simple and in the next video we'll look at ordering okay in this video I want to look at ordering and ordering allows us to return the results however we want all right now we're just returning them by default which is just in order one two three four five right so we could order by just about anything and well we'll just go through and do some and you'll see so let's see let's change this to order by well in fact we can get rid of this instead of query the database let's go order by alright so same sort of thing right we want to select row ID and everything from customers and we just go order by and then let's go row ID all right so if we save this and run it we're not really going to see anything different here right one two three four five because default that's how things go a sending from small to high that's a sending or a a SC a sending short for ace and ding right you can also do descending de SC short for descending alright and descending is high to low so we can do that just by changing this to de SC all right so if we save this and run it now see the first one listed is 5 4 3 2 1 right pretty cool you know you could type in ASC here but the default is a sending so you don't have to be explicit right we could ordered by the last name now that's going to be alphabetical from low to high or from A to Z right so if we save this and run it let's clear the screen we get three four one five two but you'll notice Brown B so a b c d e f g h i j k l m n o p q r s and it's like that so let's see that was a sending we could do the opposite descending from z down to a right let me save this and run it we now have the s ke b and b and let's see know how they determine which of the Browns go first since they're both the same I would guess it's numerically but we're descending so I don't know yeah interesting so that's order by relatively simple and yeah not much to it in the next video we'll look at and and or okay in this video I want to look at and and or and and and or allow us to sort of extend the functionality of your where clause so you know let's just come right in here let's go where so and and or allow you to add more conditions to your where Clause basically just search for more than one thing so let's look at our data real quick so we've got two of these Browns right so let's search for we're last underscore name is like and then let's go BR and then that remember that percentage sign and write and we're row ID well actually first let's just run this by itself just to make sure this works right so here when we get Mary and Wes Brown now let's say we only want Mary but we're not sure so we have to search also so we could go we're last name is like bra and row ID equals three right so notice we have two two conditions this condition and this condition and both of those have to be true in order for it to return something right so if we save this and run it we see welcome just Mary Brown number three right we can also use or instead you go or and in this case it's gonna return two different results Mary and Wes why well because this is a different logical type of thing before we just wanted we wanted and and means both or means either or or right do this or do that so this has to be true or this has to be true and in our case up here the last name has to start with B R or the number is three well this one starts with B R it's number is not three but it doesn't both have to be only one has to be true in order for it to return and in this case BR is true for this one so it returns Wes and Mary so a pretty simple concept but very useful there are times when you need to search for several different things and I've just done two things one two you can do as many as you want just keep slapping on oars or or you know you get it or you know email is like something or you know and you can just keep going and going going I don't know why you would want to but there are certain circumstances where you might have a few things you want to search for you could do that with the work if you need all of them to be true use and and simple in the next video we'll look at limit okay in the last video we looked at and in or and this video I will want to look at limiting so our little database only has five records so you know we likely would never want to limit the results but in reality you might have a thousand records ten thousand a hundred thousand a million records two million records you know you might only want to return a certain number of results right so to do that you use limit and it's very simple you just type in limit and then the number limit to right so here if we save this we're selecting the row ID and everything from customers limit - I can't imagine you would guess what the result will be for this but it limits it to two results right so we have one in two let's see if we can string along some let's go descending this is gonna throw an error I think I'm not sure so I'm gonna run it yeah so we gotta go you know order by what row ID descending right that might work we actually have to put our limit at the end in this case so from customers order by row ID descending limit - I think this will work now get sloppy so now we have 5 & 4 the last 2 right yeah I should mention all of these things we're learning you can usually just string along just by adding them to your executing statement here all right so sometimes they have to be in specific order like we just saw just now order by has to go first and then limit it at the end if it doesn't work when you try something try changing the order and it probably will so you know pretty simple limit three limit however many you want and very cool so that's limit just very simple not a whole lot to it but I thought I would mention it just in case you ever need the limit and yeah so in the next video I'll show you how to delete a table okay so we've already looked at deleting a record that's pretty straightforward in this video I want to show you how to delete an entire table so like I said a couple videos ago when we talked about deleting when you remove stuff from a table you're dropping it from the table from the database same thing with a table you drop the table from the database right so to do that we want to see dot execute all right and we're gonna need to commit that I'll just go ahead and put that right there now inside of here first you take a deep breath and then you just dive in drop table and then the name of the table customers alright so if we save this it will drop the table and then try and I'll put everything from the table which there shouldn't be anything in the table so see how that goes and we get an error no such table customers so that tells us that the the table has been dropped it has been deleted and like everything else when you delete a thing it's gone that table is gone it no longer exists and that's it so drop table pretty simple and yeah that's how that works so I'm gonna rebuild the table after this video and we're gonna play around with this stuff some more and sort of build a very basic database app that we can use and we'll start to look at that in the next video okay in the last video we deleted our table we dropped it I've gone ahead and recreated it I put all the data back in exactly as it was so we can continue to use it in this video I want to start to build out a little app and it takes sort all the stuff we've learned up until now and sort of uses it so we've at this point we've learned all the basics for sequel Lite 3 with Python and you know we went through it really quickly but there's not a whole lot - it's a very simple database to use and it's one of the nice things about it so from here on out we're not going to do anything revolutionary we're just going to build a simple app that sort of makes all the stuff we've done up until now a little easier sort of splits it apart into a couple of different files so it's easier to read and should be a lot of fun it'll help us reinforce the stuff we've already learned and I don't know just be kind of fun so I'm gonna start right off the bat I'm gonna create a new file and let's save this as I don't know our app dot hi and the first thing we want to do is import database and that's just this file right here that we've been working on throughout the whole course database app hi we can import that into another file so what we want to do here is take all of this stuff and build it into little functions Python functions that we can then call from the other file so we'll sort of abstract all the database stuff away and just make everything a lot cleaner so I'm gonna start out right here I'm just gonna push all this stuff down let's create a function and let's go define and let's call let's call it show all right and what we want to do here is just show all the stuff in our database right in our table so I'm going to take all of this stuff and I'm just gonna paste it inside of this function and paste and same thing with this and let's add these guys as well indenting is important for functions okay and so we get rid of that now these two guys I think we need to put these in each of our functions that we create okay so I think that looks good right so let's create a comment and let's call queries go query West query the database and return all records right so connecting to the database creating a cursor executing a command to show all the stuff in there putting it into this variable and then looping through that variable and printing out each item onto the screen and enclosing making our commit in this case we don't actually need to commit any things we're not we're just querying we're not committing so we'll probably take that out eventually and then at the end we want to close our connection so if we save this and head back over here now if we want to actually use that we just call database dot show underscore all like you would call any function we need to put it in this database because the name of the file here is database dot show all which is the name of that function right there and we're not passing anything in because it doesn't require us to all right so if we save this what do we call this our app dot pi and if we head back over here we can go python our app dot pi if we run it boom it prints out everything onto the screen very very cool now likewise if we commented this out and save this in ran it again nothing's gonna happen so there's nothing else going on in our our app pi so and our database dot pi only has one function now we're gonna spend the rest of the course building out more functions that we can think call but that's a good way to get started I think and the next video will go on and do the next thing okay so we've created a function to show everything now let's create a function to add a record to our databases so let's call this define and let's call add one right and here let's make a comment that says add a new record to the table all right so we need to pass in the stuff that we want to add to the table so we want first let's call it last and email right so we already know how to add stuff to our database what we do is well first we need these two guys we need to create a connection and a cursor and since these are inside functions you need to do it every time connect to the database and create cursor just get rid of this there we go right next we go see execute as we already know how to do and we want to insert into and our table is customers and the values are we want to put placeholders learn how to do that already alright now to add the variables that we passed first last and email we just put them right here first last email okay and then we need to commit this and close our connection right underneath there boom commit our command close our connection all right that looks pretty good so if we save this and head back over to our app now to run this guy we just called database dot one we call it add one let's make sure here add one yep and we need to pass it three things a first name a last name and an email so let's go oops wrong one here let's go well first name last name and email we might go who me name here Laura Smith Laura at smith.com right so this will run that and then if we want to show it all we'll keep this one here just to to make sure so let's go add a record to the database here let's go show all the records all right so if you save this and run it boom we see Laura Smith Laura Smith calm has been added very cool very easy and that's all there is to it in the next video we will create a function to delete a record from our database okay the last video we added a new record to the table in this video we want to create a function that deletes a record from the table and this was a little bit trickier than you might expect and I'll show you what I'm talking about in just a second so let's head over here to our database table pi file and let's create a delete one function and we want to pass in ID now inside of here again we're gonna make our connections here and we want to close our connections here and inside of here we want to see execute alright and we know how to do this already we want to delete delete' from customers where and we want to let's do row ID equals and here we want to put a placeholder alright and then we want to pass in this ID which is this ID right so that looks pretty good okay so that'll work but now hit it back over to our app to use this thing we call database dot what do we call this remove delete one copy this delete one and now we want to pass in the number of the ID we want to delete in this case we want to delete six it's not up anymore but six was the one we just added in the last video now here's the weird thing the IDs are numbers right integers we know they're integers the database knows they're integers so you would think we would pass an integer but if we do we save this and run it we get an error it says module database has no attribute delete one wait that's the wrong error database delete one did we not save let's backup delete one must not have saved it alright so let's clear the screen run this again alright this is the different this is the the error I thought we would get value error parameters are of unsupported type for this ID we're passing a number an integer for some reason we can't do that we need to pass this six as a string so let's go delete record use row ID as string right so now if we save this in quotation marks it'll pass this six over to here ID become six a string it gets put into there that then somehow magically becomes a number again and it should work it's the weirdest thing if we run it boom now it works six has been deleted and just that simple so that's how you delete stuff in the next video I think we already looked at how to add one record let's create one to add a bunch of records all at once and we'll look at that in the next video okay so in this video we want to create a function to add many records to our table we've already done one where we've added one record so make sense that we would do for many so let's go to find add many and let's pass in a list we'll just call it list and you can I'm just gonna copy all of this stuff so is execute many and then inserting a customer's values but pop up then here we want pass in list which is this guy right here all right so look good I think so so let's save this and head back over here and we don't want to delete anything so here let's go add many records all right so we want to call database dot add many and we want to pass in our stuff no so here we need to define stuff we need to create a list right and inside of this let's add let's add two records all right so here we want Bom Bom well and I'm just gonna copy this and here let's go Brenda Smith Orton no and she is Brenda at Smith third ton calm and here we'll call Joshua Raintree I don't get these names Josh at Raintree calm all right so we're gonna call add many which is this guy we're gonna pass in stuff which is all of this right so stuff which is this list becomes list and then here we just pass that in and since we have execute many it should work I think fingers crossed okay and then at the end we'll show all so all right let's see if this work let's clear the screen boom so we have Brenda Smith Orton and Joshua Raintree number 6 and number 7 and it worked just that easy alright so that was pretty cool a little bit more complicated but still not too bad now we sort of done all this stuff in this program here and which I think you would probably want to do as opposed to adding it here somehow that makes sense right yeah ok so that's all for this video in the next video we'll look at where and yeah that's about to the end of the stuff but we need to add into as functions so yeah look at that the next video okay so let's crank out one more of these functions let's look at the where clause now to create a function to deal with where is kind of silly I mean there's just so many options you could look up you know pull all the records for database where first-name is X or where last name is X or where email address is X or where you know row ID is X or you know any of us zillion different permutations of that so it doesn't really make a lot of sense to create a where function but we're not really building a real app here we're just sort of playing around with this to reinforce some of the concepts so might as well just knock one out and let's just say we want to look up email addresses it's pretty simple so let's just call define email lookup and we want to pass email I guess all right so I'm just gonna copy all of this paste it in and for our execute what do we want to do we want to select everything from customers right where and we want where email equals and we want to pass in email right now this is a tough ball passing one thing and so we put a little comma afterwards just a little quirk and sort of how that goes now we also we're not committing anything here we're querying and we want to put it up on the screen so we need to come up here and let's see like a with a query we need to fetch all and let's loop through and print those out so I'm just gonna copy that and that looks good I think yeah okay so I think that looks good right let's save this and head back over to our app and look up email address so we want to call database dot email look up and we want to pass something so let's pass John at Kota me.com we know that's an address that exists in our system so we mm head back over here run this again whoo first of all let's comment out this show all one we don't need to show all we just want to show the one that we're looking up so save this run it Oh Oh Dan data nice why didn't you tell me I misspelled that database all right let's clear the screen and run this again in boom John elder John echo to me comm if we want the ID as well so we would go just we could add it like this row ID and everything come back here and now we get the row ID so you know you can do this for anything you want if you want to let you create an email look up one you could do that if you wanted to create a specific row ID you know primary key ID lookup you could do that like I said this app we're building here we have built I suppose is you know this is not supposed to be some professional app we're building this is just a little toy thing that we can sort of play around with just to reinforce all of these different things and it's kind of nice to have this you know examples of how to for instance query you know right there how to add something to a table you know right there how to add many things to a table right there how to delete something from a table it's just kind of nice to have them all in one file and you know for a reference going forward into the future if you need to refer back to this if you've built this with with me along with me you'll have this to sort of you know reference back so it's kind of cool so I think that's all for this video in the next video we'll say a few more things and wrap this all up okay so that's all for this video and for the course if you liked it be sure to smash the like button below subscribe to the channel give me a thumbs up for the YouTube algorithm I check out my website coding me comm where you can use coupon code camp you get 65% off membership so you pay just $27 to access all my courses over 40 courses hundreds of videos and the PDFs of OMON best-selling coding books join over 100,000 students learning to code just like you my name is John elder from Kodu be calm and we'll see in the next video

Channel: freeCodeCamp.org

Views: 251,314

Rating: 4.9530182 out of 5

Keywords: sqlite, sqlite3, python, python database, sql database, sql, sqlite database

Id: byHcYRpMgI4

Length: 89min 37sec (5377 seconds)

Published: Tue May 12 2020