SQLite Databases With Python - Full Course

All right, welcome to the course. My name is John Elder from codingme.com 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 going to 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 and want to see more like it, be sure to smash the like button below, subscribe to the channel, give me a thumbs up for the YouTube algorithm, and check out my website codingme.com where I have dozens of courses with hundreds of videos that teach you to code. Use coupon codecamp to get 65% off membership. That's all my courses, videos, and books for a one-time fee of 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 going to use in this course have a Mac or Linux equivalent, and uh 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. Uh the code is going to 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 going to need, we're going to need, obviously, Python. Now, SQLite actually comes with Python, so there's nothing else to download besides Python. It's We'll 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++, 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 terminal. It's completely free. Uh Sublime Text is free, too, as well as Python. So nothing to buy in order to to watch this course. Uh but like I said, the Git Bash works great, so we're going to use that. If you're on Mac or Linux, you could just use the terminal that comes with Mac or Linux. Just go to the little search function, type in terminal, and you should be fine. Uh if you can absolutely download the Git 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 that we're going to need in order to take this course. Like I said, not much to SQLite as far as tools and stuff since it just comes with Python, so that's really cool. So, finally, I want to talk about how to get in 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 response time, but absolutely feel free to ask questions along the way if you don't understand something, if your code's 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 codemy.com. There's a contact form, you 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. Uh you can also, obviously, post a comment below this video if you're watching this on YouTube or wherever, and uh 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. All right. 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 going to 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 3.7.3. 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 4, anything below 4, go ahead and download whatever version it is. Perfectly fine. So, I'm going to go ahead and click this button. And we want to save this anywhere. I'm just going to save it on my desktop. And it's not a very big file. In fact, it's boom, it already downloaded. And so, go ahead and click it to install this thing. And we want to run it. Oops, clicked it a couple of times there. Okay, so install Python 3.7.2 32-bit. 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. Uh it works perfectly fine on 64-bit computers. [clears throat] We all have a 64-bit computer. So, uh 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 checkbox right here. Add Python 3.7 to path. And on a Windows computer, path means uh 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 and click that box. Make sure there's a little checkbox uh 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 uh 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 going to click no and cancel this thing cuz I've already installed Python, but you'll click okay 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 show you a little 2-minute video that'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 going to 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 going to go to Google and type in Sublime Text. And the first thing that popped up is sublimetext.com. Or you can 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 3207. 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. Uh 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. Uh just click all the defaults okay, and then install it. And uh after you've installed it, you can run it by going to your Windows start menu and just typing in Sublime, and the little icon will pop up, and you can click it. So, that's cool. Now, the last thing we need and 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 a Linux or Mac, they come with a terminal that's perfectly fine. You can use that. On a Mac, just go up to the little search thing at the type at 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. It allows us to do version control. We're not going to do any version control in this course cuz there's not much to it um code-wise. But if you've got a big project and you want to use Git or GitHub, you need something that allows for that, and this Git Bash terminal does, so it's really nice. And it's absolutely free. So, to use that, I'm just going to go to Google Google and type in Git Bash. That should be it. And then here's the website git-scm.com/downloads. And so, we can click download this, and I will walk you through this one cuz it's a little weird. So, I'm just going to download this to uh the desktop. And it takes a little bit longer for this to download. It's only 44 megabytes, 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. Uh 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. And 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. You use it it's a standalone thing, right? So um we are going to use Sublime Text, so I'll just leave this, but you can just ignore this if you want. Uh just click next. And here we want to use Git and optional unit Unix tools from the command prompt. We're Like I said, we're also we're going to 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. Open SSH. This is for uh pushing stuff up to like GitHub where you need an SSH key. We're not going to use any of this. Doesn't matter. We just click next. Same for this. Click next. And this is well, this is a Git thing. We're not going to be using Git really in this course. Uh so we can click next again. Same thing with this MinTTY thing. Click next. Basically we're just taking all of the defaults. Again, click next. And 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? Yes, I am. And it takes a couple of minutes to install. It has a little, you know, status bar thing that'll scroll through and finish when it's finished. And uh so 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 start using this. So, in the next video, we'll jump right in and start writing some code and uh 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 that SQLite3 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 SQLite3, all we have to do is import and then name it. So, it's s q l i t e 3, right? So, this is just a 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 going to come up here 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 light. So, then we can click on here and we can name this anything we want. Uh let's name it sequel light dot py. Well, no. We don't want to name it the same thing that we import here. Uh SQLite3. So, you got Let's just call this a database dot py. 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 SQLite3. 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 this really easy we just create a variable and you can call it anything you want but I'm just going to call this con short for connection and then we want to set set this equal to SQLite3 .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 SQLite directory that we just we just created. So, we can call this anything we want. Let's just call it customer.db. We're going to pretend we have a company, we have customers so we're going to create a database of those customers. So, that's pretty much all we need to do and one thing I will note SQLite3 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 going to copy this and paste instead of naming the database oops go back. Instead of naming this database instead we just type in colon colon colon and then memory and then another colon. Right? So, this will create a connection to a database in memory. Now, we're not going to do that in this course. We want to actually save our database so we can use it later on if we want to but if you've you got some little thing you need to store some data, do some stuff to, 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 going to hit control S on my keyboard. Now, we can run this. It won't actually do much. Well, that's not actually true. Uh 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 what I name my computer. Whatever your computer name is or whatever your username 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 SQLite. 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.py file that we've just created. And that is just this file right here, right? database.py. 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.py. And if we do that, it doesn't look like anything's happened. But now, if we type in LS, we see this customer.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 customer.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 uh 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 uh SQL i3, 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. Uh we just have to build it once, and then we can use it. So, to 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 going to call it C, cuz we're going to 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 going to go create a cursor. Right? 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.execute and then pass in whatever commands we want. In this case, we're going to create a table, and there's a couple of different ways to execute commands using SQL. And the first one I'm going to show you is something called a a docstring. I think this is what Yeah, docstring. 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 docstring. 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? Uh add Bob to the database, right? So, that's one line. We won't use docstrings. We'll just use regular quotation marks. But in this instance, when we're creating a whole table, there's going to 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 docstrings. So, what we want to do is create a table. And what do we want to call our table? Well, 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 going to be on several lines. I'm just going to tab over here. So, what do we want? Well, we want to record customers' first names, their last names, and let's say email. So, we just sort of put all those things out. We We type in first, and what do we want to call it? First underscore name, and then we have to decide the um data type. I'll just put 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 do this, and let's tab 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, SQLite 3 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 docstrings, we could just use regular quotation marks. But, you can see you got these big red angry uh 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 to scroll. You know, it's all smooshed together. It's just no good. That's why I use these docstring triple quotation marks in this case. So, just a a quick little aside. So, all right, we're almost there. Now we need to talk about data types. And data types, if you're familiar with programming, a data type is a type of data. So, like think strings, numbers, integers, uh booleans. Um Python has a bunch of other data types like lists and uh dictionaries and all the 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 we need to define a text data type. Now, the thing about SQLite that's actually kind of cool, I actually like, is it only has five data types that you can choose from. Other databases, MySQL, Postgres, any other database you can think of, has dozens of data types and it can get confusing cuz 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 uh SQLite 3 only has five. And the five are null, uh let's see, and integer. I'll talk about these in just a sec here. Uh real, text, and blob. Right? So, null just means doesn't exist or does it not exist. If it doesn't exist, it's null, right? If it does exist, it's not null, 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, text. And finally, blob is it's it's it's extor- it's stored exactly as it is. So, think of like images. An image might be a blob. Um an MP3 file, a music file, might be a blob, right? It's just a blob, right? So, pretty simple. In our case, we're going to use uh text for all of these. So, we just type in text. text and text. And that's it, right? Uh let's go data types, right? All right, 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 is a common theme. Here, to do it, we just commit our connection. So, con. 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 uh, commit our command. 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 con.close. Right? All right. So, if we save this and now run it, pull up our terminal, clear the screen, and I'm just going to push up on my keyboard, and that will scroll to previous commands, and we just type in database.py, which is the name of our file, and we hit this, boom, nothing has happened on the screen, but uh, 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, c.execute. Anytime we do anything, we want to execute our c, our cursor, right? And so, now we want to insert data into our table. So, we use the insert into SQL command, and this is all just SQL. If you're familiar with SQL, SQL, these are just SQL commands, right? Very basic. So, uh, 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, uh we want Let's go uh John. And we separate each of these by a comma. Alder, comma, and then john@codeme.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 tried to use a uh 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, uh 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. Uh you know, we could just print to the screen uh 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 could do more than that. We could go Tim. Tim uh Smith. Let's go tim@codeme.com. So, if we save this, come back here, run it again. Command executed successfully. That makes you feel a little better that something is on the screen, right? Whatever. Uh let's do one more. Let's go uh Mary What's a good last name? Mary Brown? I [laughter] don't know. And she's at mary@gotome.com. 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 uh yeah. That'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 um 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, it's sort of like a a tuple inside of a list, right? And here, you would just go uh let's go West at brown uh and uh let's go West at brown.com, right? And we can put these on multiple lines if we want. To sort of just make it easier to read, uh we can go um, I don't know. Steph [snorts] Uh, uh, Steph at And finally, we can go Dan Pass And Dan's email address is Dan at pass.com. All right. I'm going to go ahead and put the comma 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 SQLite 3 is the question mark. So, we go 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? Many customer. Actually, let's change this to 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 1 2 3. And here's the placeholder for each one. Boom. Pretty simple. If we save this and run it again, command executed successfully. 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 c.execute. There we 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 uh table. All right? So, that sort of creates the query and to actually get the thing, we need to then go c. fetch and you can do three different things. I'll just type them out. We can fetch one. We can c.fetch many and then pass in how many however many we want, or we can c.fetch all. And this will return, as you would expect, everything. This will just fetch the la- the last item in the table. Yeah. Uh 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 uh 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 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 it spewed back all this stuff. We could 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 in print function. We save this and run it just real quick just to show you what this other stuff does. Boom, John Dollar, john@codemy.com. You can see it's it looks like a tuple, a tuple, however you want to say that. And you can access it just as a tuple. So, you notice this is the very first thing. So, it's not the last thing that fetch one brings you, it's the first thing, right? Likewise, you could do let's see, fetch many. 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, it's 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 tuple. 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. And I'm going to clear the screen. Okay, so fetch one brings one record, John Elder and john@codingme.com. So, this is the zeroth item in the tuple. This is the first item and this is the second item. This is Python tuple stuff. You're familiar with tuples or lists. When you number items in a list or a tuple, the first one is zero, the second one is one, and the third one is two, right? So, we can access specific ones of those by referencing those numbers. And to do that, we just, like I said, slap on the brackets here. And let's just call zero, the zeroth item. That should be John. So, if we save this and run it, boom, we get 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, it's not really what we want to look at right now. What 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 um uh 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. It just spews out all of it, right? Or, you could create uh a loop of some sort to loop through each thing. Let's go ahead and give that a try. So, let's go um for item in items, uh print item. Right? 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 tuple, and we can do tuple-y things to them. So, instead of printing out the whole item, we could print out we use our brackets and say print out the zeroeth item inside each tuple. All right, so if we save this and run it we get John, Tim, Mary, Wes, Steph, and Dan. Very cool. Um 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 one concatenate make another space and then concatenate again and go item uh two. Right? So, if we save this and run it and let's clear the screen. We get John Elder, John at codeme.com, Tim Smith. Right? So, it's starting to format up a little bit. And we can play around with this, you know, we could uh do a little formatting. Like that. If we save this and run it uh that's looking a little bit better. We could um oh, I don't know. We could use tabs instead. That is backslash t. Is that right? Or is it forward slash? Backslash. So, this will tab everything over. So, if we run this, then now things are starting to look a little bit better. This one didn't quite make it over because this it did tab, but it this name is so short, the tab didn't go as far. That's kind of funny. Uh we could do two tabs, I suppose. Right? And here we could go outside of the loop. We could go uh name Well, we need quotation marks. Name Yeah. Plus two tabs and email. That look right? And here we could do print and 1 2 3 4 5 uh plus couple of tabs and then what? 1 2 3 4 5 6 7 8 I don't know. Couple more. Boom. I don't know. Save this. I'm just playing around at this point cuz it's kind of fun. All right, formatting has gone a little bit wonky here cuz this is too short. But you get the idea, right? You can sort of um play around with all this stuff. Let's get rid of one of these tabs and see how that looks. Clear the screen real quick. All right, 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 in the returned data. So, a little bit much to to swallow in this one, but uh really pretty simple when you get right down to it. All this stuff at the end we're just playing around here. So, uh that's how you sort of format results using uh Python and SQLite3. 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 of 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 data 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, oops. There we go. We see each of these items, John Elder, Tim, blah blah blah. We can, like we did in the last video, couple of videos, instead we can go um for item in items, uh 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, and a column email. Well, actually, SQLite3 creates another column sort of in the background with a specific primary key, a unique ID that 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 going to print out that ID as well. So, you can see 1 2 3 4 5 6 John, two Tim, three Mary. So, you know, we can look up Mary Brown or we can look up number three, 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 to really quickly touch base on that primary key cuz it's an important database concept and in most databases you have to create the primary key 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 add in a new person in, 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 SQLite. It does it for us automatically unless you specifically tell it not to. And I'm not even going to going to get into showing you how to do that because you're never not going to want to do that. You're always going to want this ID. It It's a good idea to have a unique um 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. Uh it's just sort of a unique SQLite three three thing. It does it for you. It's very very cool. And uh yeah, so that's all for this video. In the next video we'll look at pulling out specific things from our database uh 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 out specific things. How to search for things, right? So, you maybe don't want everybody. You might want uh 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. We want to select everything from customers. And now we just use the where clause. Where This is just basic SQL. Where uh we could go last _ name equals and use single quotation marks uh Elder, right? So, if we save this and run it, uh 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 uh 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. We would do that, right? So, that's kind of cool. Uh one you're going to use a lot is like. So, we could go where last _ 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 BR and then you put this uh What is that? The percentage sign? Yeah. To It's sort of like a wild card where it starts with BR and ends with whatever, right? So, if we save this and run it, we get Mary Brown and Wes Brown, right? So, uh very very cool. Uh we've got a bunch of codemies, right? We could play around with that where we could go where email is like and we can start out with this percentage sign and then we can go code to me. com. Save this. I'm not sure why that turned purple like that. Yeah, but we get three returned results. Uh let's look at this more. Let's go let's get rid of this. Yeah, it's just going to 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 1 2 3 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, uh 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 Here we go. c.execute right? Now, this is going to actually be kind of long so we're going to use those docstrings again. 1 2 3 4 5 6 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 where And let's go last name equals Elder. Right? And actually, we want to wrap these in single quotes. There we go. Ah, come back. I cannot type. There we go. All right, so we could have put this all on one line, but easier to break it apart like that. So, uh 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 c.commit. Or con.commit, right? And then we want to select everything from our database and print it all onto 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 cuz 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 first name equal Let's change it back to John where and then we go row ID equals one. 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 to 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 where uh last_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 Wes Brown. Right, instead of Mary. So, we would come up here where row ID equals four. Is that right? Which was Wes? Wes was Wes, yeah. Four. All right, so then we want to change it to Wes. So, we save this. Let's clear this screen and run this one more time. We get number three, Mary Brown. Number four, Wes Brown. And now everything is right in the world. So, important that you use ID, the row ID, the primary key in order to uh update things. Also to delete things. We'll look at that next. Uh you know, it's 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. It's pretty simple. We just come over here and go c.execute. And then inside of 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 uh let's get rid of him. We had trouble with the formatting cuz his name was too small way back at the beginning of the course. So, let's just get rid of him. So, where we go row ID equals six. Now, you'll notice I'm I'm 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, uh just sort of keep that in mind. And I think that's pretty much it. So, if we save this and 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. Uh Dan has been dropped from the table and when you delete things, we call it dropping from a table. Uh so, he's been dropped. He's deleted. He's gone. And uh 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. Well, 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. Right now, we're just returning them by default, which is just in order, 1 2 3 4 5, right? So, we could order by just about anything. And um well, we'll just go through and do some and and you'll see. So, let's see. Let's change this to order by. Well, in fact, we can get rid of all of this. Instead of query the database, let's go order by. All right. 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 um row ID. Right? So, if we save this and run it, we're not really going to see anything different here. Right? 1 2 3 4 5. Cuz default, that's how things go, ascending from small to high. That's ascending. Or a a s c ascending, short for ascending. Right? You can also do descending, d e s c. Short for descending, right? And descending is high to low. So, we can do that just by changing this to d e s c. Right? So, if we save this and run it. Boom, now see the first one listed is 5 4 3 2 1, right? Pretty cool. You know, you could type in a s c here, but the default is ascending, so you don't have to be explicit, right? We could order by 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 3 4 1 5 2, 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, uh let's see. That was ascending. We could do the the opposite, descending from z down to a, right? If we save this and run it. We now have the s k e b and b. And let's see. I don't know how they determine which of the browns go first since they're both the same. I would guess it's uh numerically, but we're descending, so I don't know. Huh. Interesting. So, that's order by relatively simple. and uh yeah, not much to it. In the next video we'll look at and and or. Okay, 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 and let's go where. So and and or allow you to add more conditions to your where clause basically to 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 where last underscore name is like and then let's go BR and then that remember that percentage sign and right? And where row ID Well, actually first let's just run this by itself just to make sure this works, right? So here boom 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 where 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 boom Jess Mary Brown number three, right? We can also use or instead we could go or and in this case it's going to 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 BR or the number is three. Well, this one starts with BR. Its 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, uh 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 uh or's 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 and 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 can do that with a war. Uh if you need all of them to be true, use and. And uh yeah, pretty simple. In the next video, we'll look at limit. Okay, in the last video, we looked at and and or. In this video, I want to want to look at limiting. So, our little database only has five records. So, you know, we we likely would never want to limit the results. But, in reality, you might have 1,000 records, 10,000, 100,000, a million records, 2 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 two, right? So, here, if we save this, we're selecting the row ID and everything from customers, limit two. I can't imagine you would guess what the result will be for this, but it limits to to two results, right? So, we have one and two. Uh let's see if we can string along some Let's go uh descending. This is going to throw an error, I think. I'm not sure, so I'm going to run it. Yeah, so we got to go, you know, uh order by um what? Row ID descending, right? That might work. No. We actually have to put our limit at the end in this case. So, from customers order by row ID descending limit two. I think this will work now. It's sloppy. So, now we have five and four, the last two, right? Um 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, right? So, uh 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, um that's limit. Very simple. Not a whole lot to it, but I thought I would mention it just in case you ever need to learn it. [laughter] And uh 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 was 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 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 c.execute. All right? And we're going to need to commit that. I'll just go ahead and put that right there. Now, inside of here First you take a a deep a deep breath [laughter] and then you just type in drop table and then the name of the table, customers. All right? So, if we save this, it will drop the table and then try and output 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 uh that's it. So, drop table, pretty simple. And [laughter] uh yeah, that's how that works. So, I'm going to rebuild the table after this video and we're going to play around with this stuff some more and sort of build a a very basic uh database app that we can use. And uh 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. Uh in this video, I want to start to build out a little app uh that takes sort of 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 SQLite3 with Python. And, you know, we went through it really quickly, but there's not a whole lot to it. It's a very simple database to use, and uh 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 a simple app that sort of makes all the stuff we've done up until now a little easier, sort of uh uh 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 uh I don't know, just be kind of fun. So, I'm going to start right off the bat. I'm going to create a new file, and let's save this as I don't know, our app. dot py 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.py. 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 uh just make everything a lot cleaner. So, I'm going to start out right here. Just going to 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 going to paste it inside of this function. And ba ba paste and same thing with this. And let's add these guys as well. Indenting is important for functions, so. Okay. And so, we can 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 uh query Let's go query quest query the database and return all records. Right? So, here we're 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 then closing making our commit. In this case, we don't actually need to commit anything cuz we're not We're just querying. We're not committing. Uh 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. show_all show_all like you would call any function. We need to put it in this database cuz the name of the file here is database. 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.py. And if we head back over here, we can go python our app.py. If we run it, boom, it uh prints out everything onto the screen. Very, very cool. Now, likewise, if we commented this out and save this and ran it again, nothing's going to happen. So, there's nothing else going on in our our app.py. So, and our database.py only has one function. Now, we're going to spend the rest of the course building out more functions that we can then call, uh but that's a good way to get started, I think. And the next video, we'll 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 database. And so, let's call this define and let's call add one. All 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, uh we want first, let's call it last and email. All right? So, we already know how to uh add uh 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 c.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. Right? Now, to add the variables that we pass, 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 call database. add one. Did we call it add one? Let's make sure here. Add one. Yep. Now, we need to pass it three things. A first name, a last name, and an email. So, let's go um Oops, wrong one. Here, let's go Well, first name, last name, and email. We might go who? Um Give me a name here. Uh Laura Smith. laura@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 uh 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 we save this and run it, boom, we see Laura Smith, laura@smith.com has been added. Very cool, very easy. And uh that's all there is to it. In the next video, we will create a function to delete a record from our database. Okay, in 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.py file, and let's create a uh delete one function, and we want to pass in ID. Now, inside of here, again, we're going to make our connections here. And we want to close our connections here. And inside of here, we want to c.execute. All right? 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. Right? 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 head it back over to our app to use this thing. We call database. What do we call this? Remove Oh, delete one. Copy this. That 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. Uh 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. Uh database delete one. Did we not save? Let's pull this back up. Delete one. Must not have saved it. All right, so now let's clear the screen and run this again. All right, this is the different This is the the error I thought we would get. Value error. Parameters are of unsupported type for this ID. Right? 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 becomes 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. But 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 a 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 add one record. So makes sense that we would do for many. So let's go define add many. And let's pass in a list. Let's call it list. And can I'm just going to copy all of this stuff. So what we want to do is execute many. And then insert into customers values blah blah blah blah blah. But then here, we want to pass in list, which is this guy right here. Right? Does that 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. add many. And we want to pass in our stuff. I don't know. 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. Right? So here we want bomb. Bomb. Bomb. And I'm just going to copy this. And here let's go Brenda. Um Smitherton. I don't [laughter] know. And she is Brenda at Smith erton.com. And here we'll call Joshua Raintree. I don't know where I get these names. Josh at raintree.com. All right. So we're going to call add many which is this guy. We're going to 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 worked. Let's clear the screen. Boom. So we have Brenda Smitherton and Joshua Raintree number six and number seven. And it worked. Just that easy. All right. 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. Okay. So, that's all for this video. In the next video, we'll look at where and uh yeah, I think that's about into the end of the stuff that we need to add into here as function. So, yeah. Look at that in 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 from 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 a zillion different permutations of that. So, it doesn't really make a lot of sense to create a where function. Uh 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, we might as well just knock one out. And let's just say we want to look up email addresses. It's pretty simple. So, uh let's just call uh define email lookup. And we want to pass email, I guess. All right, so I'm just going to 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 tuple. We're passing one thing. And so, we put a little comma afterwards. It's just a little quirk. And uh just 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 going to copy that. And that looks good, I think. Yeah. Okay. So, I think that looks good, right? So, let's save this and head back over to our app and look up email address. So, we want to call database .email_lookup. And we want to pass something. So, let's pass john@codemy.com. We know that's an address that exists in our system. So, we can head back over here. Run this again. Oh, 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. Uh-oh. Oh, dan- datanace. [laughter] Why didn't you tell me I misspelled that? database. All right. Let's clear the screen and run this again. And boom, John Elder. Um john@codemy.com. 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 create an an email lookup one, you You 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 uh uh you know, for 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, that'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. And check out my website codemy.com where you can use coupon code camp to get 65% off membership, so you pay just $27 to access all my courses, over 40 courses, hundreds of videos, and the PDFs of all my best-selling coding books. Join over 100,000 students learning to code just like you. My name is John Elder from codemy.com and we'll see you in the next video.