 and welcome everybody to SQLite3 Database with Tokyo EdTech, that is me. Today, I'm gonna teach you how to use an SQLite3 database. Basically, we're gonna show you how to create a simple database, how to access it, and how to update the information in that database. Okay, so first I'd like to give a big shout out to my channel members. You can see the snakes on the left, invaders on the right. Thank you so much for supporting the channel. If you'd like to support the channel, click the join button down below. So let's get started. First, what we're gonna look at here is what's called SQLite3. And SQLite3 is a module that comes with Python, so you should already have it installed. And it provides a very simple database interface for you to store information on your computer in a file. So I think the first question you have to ask yourself, if you're not familiar with databases, is what really is a database? And so what I've done is I've created a little spreadsheet here to kind of give you an idea. So your database is a file. It's basically a big file. And in that file, you have multiple tables. And in those tables, you can store information. So we're making a very simple database today called demo.db, I know, very original. And it's going to have three columns, name, phone, and city. Each of these pieces of data is gonna have a type, and they are all three going to be text. And here's the information that I'm going to store into this database, or into this table, I should say. And I'm gonna name this table contacts. Which is, you know, so we're gonna make a very simple contact type app. So the first thing we need to do is to actually just create the basic database structure. Now I'm not an expert on this subject. I did just take a database course. But SQL Lite 3 doesn't do everything that a full-fledged database like MySQL would do. But it does enough, I think, for a simple app for a lot of different games or different types of things you might wanna do. So you can see here, I'm just gonna be following along in the documents. And I'll put a link to that down below. So let's go ahead and start writing some code. And you can see over here, the first thing we need to do is to import SQLite 3. So that will import the module into Python, then we can use it. And then you'll see, the next thing it says, c-o-n-n equals SQLite 3.connect and example.db. So I'm gonna go ahead and try that. I'm gonna say con equals SQLite 3, SQLite 3.connect. And basically what we're doing is we're connecting to a database. Now they have single quotes, it could be double quotes. I usually use double quotes. And if I'm gonna go ahead and use demo.db. So I'm gonna say demo.db. And I'm gonna hit save. And what this will do is this will create, well actually it will look for a file called demo.db in the same folder. So here is what my folder looks like right now. It says SQLite 3.db, underscore demo.py. And I'm using Linux. You may, I don't know how Windows works. You may have to put the full path into that. I don't really know. I know on Linux I don't have to do that. Okay, so it's gonna look for that file. And I think if it doesn't exist, it will create it. Let's go ahead and try it. Let's just go ahead and run it and see what happens. Okay, and oh, it spelled SQLite wrong. Okay, lovely. So let's go ahead and run that again. And you'll see here, okay the program exited, no errors. Let's see what happened in our folder. Okay, so you can see it has created that database file for us. Okay, so the next step we need to do is to create the table. And to do that, we need to create something called a cursor. And we're just gonna use C to represent the cursor. So it equals con.cursor. And this is what we're gonna actually be using to send commands to our database. So I'm gonna just put a little hashtag here. So connect to database. In this case, we're actually opening that file, create a cursor object. And then in here is where we're gonna actually start writing and sending commands to the database. So I'm just gonna go ahead and create the table just like they have here. So going back to my demo database, I've got three columns. Okay, again, name, which is a text field. Phone, which is gonna be a text field. The reason is because I have a dash in there so I can't use an integer, for example. And city, which is also text. So I'm gonna go ahead and type the file. I'm gonna say c.execute. So execute, execute, we'll execute some SQL. So let's do it this way. So make it a little bit more clear. SQL equals, and I'm gonna basically kind of copy this format. So it says create table. And if you recall, I created a table over here called contacts. Note the capitalization. And I wanna create three columns. The first is name and it's text. The second is, I think phone is also text. And the third is, what was it, city. And it is also text. And I'm gonna go ahead and put that in parentheses. Okay, so that is my full SQL statement. Create table contacts. It's gonna have three columns. Name, which is a text field. Phone, which is a text field. And city, which is also a text field. And then I'm gonna go ahead and execute SQL. Now, here's something that if you've never done SQL stuff before, SQL stuff, whatever you wanna call it. Executing doesn't really do anything. You actually need to commit changes and that will save it. So I'm gonna go ahead and do connection.commit. So don't get confused here between the cursor and the connection. The cursor is kind of what you're sending information to the database and then you have to actually commit it. So I'm gonna go ahead and run that. See what happens. Okay, so we didn't get any errors. So I'm going to assume, oh, I forgot to close it. So I'll put that down here at the end. Now, if you don't close it, it closes automatically, but it's probably, it's good practice to do that. So close connection. And you only wanna close the connection at the end of the program. So kind of close or when you're done with that particular connection. So now we are assuming that that has worked. So let's go ahead and try and test it a little bit. So what I wanna do is I'm gonna go ahead and just comment this section of code out. Since we've already created the table, we don't need to create it again. Now there's probably a way to check that. I don't, again, I just started using this myself. So I'm just kind of playing around with it a little bit. But what we wanna do next is we wanna insert those values into the database. So I'm gonna go ahead and insert, we'll go ahead and insert Jenny, all right? So just to kind of get an idea how things work. So insert some values. So I'm gonna do the SQL again, equals. I'm gonna use insert into contacts, the table name. And let's go look at the syntax of it. Okay, I gotta put values and parentheses and quotation marks. So the values that we wanna put in there, they have to be in this order. So I'm gonna go ahead and now because I use double quotation marks here, I have to use single quotation marks here. I'm gonna put Jenny, oops. Her phone number is 8675-5309. And we'll say she lives in Los Angeles. I don't really know where the original Jenny lives, or lived, Angelus, Angelus, okay? So I'm gonna insert. So again, same thing, c.execute SQL. And then we commit the changes and close the connections. Let's go ahead and run that and see if we get any errors, okay? Attribute object, did I spell execute wrong? Yeah, I have been making so many stupid spelling mistakes. So let's go ahead and try that again. And okay, so no errors. So at this point, we're assuming the values are in there. So let's go ahead and see if we can retrieve some values. So let's go ahead and retrieve values. So again, same thing, we're gonna be doing some SQL. And again, all this stuff is here. You just gotta kind of figure out how to use it. And in SQL, we use the select statement. So I'm just gonna keep this really simple. I'm gonna say select, the asterisk is a wildcard. It means select everything. And from, and it's context, context. So for now, we're just gonna do it that way. And then we're gonna say c.execute SQL. Now, here's how I personally do this. We are going to get, this is going to return some objects. So results equal, actually that's not what I'm gonna do that way. I'm gonna do c.execute SQL. I'll do it this way instead. And then what I gotta do is actually fetch. Okay, so what I'm gonna do is I'm gonna say results equal. I'm gonna put this c.fetch all. So this will pool all the different results from the cursor object that we just did. Again, you can see there's a slightly different way they do it over here, but I think this is probably easiest. And then at this point, we can print results. Now, I could have put c.fetch all here and it would have done the same thing. But I think this is probably easier for later. Okay, so I'm gonna go ahead and execute that and see what happens. And you can see we got the value that we had put in there. And again, it's the same order, Jenny, number, and city. So what I could have done is I could say print results zero, or results zero, and then zero is the name. Oops, so this is just, this is a regular list, I believe. Or it could be some kind of weird object, but anyway, we'll just call it a list for now. It functions like a list. And one, and results zero, two. And this zero is because it is a tuple inside of a list. So let's go ahead and just run that again. And you'll see what happened there. So results zero, zero is Jenny, results zero, one is the number, and results zero, two is that. Now, we can also select specific columns. So let's say, for example, for whatever reason, we just wanna know the names of the people in our list. So instead of an asterisk, we just type name. And then when we do that, results is gonna be just a little bit different. So I'm gonna say print results. So I'm gonna go ahead and hit F5 to run it. And you can see how it gave us Jenny and a comma. That's kind of interesting. So the results zero, see, let's try results zero here. And that gives us a tuple of all the names. Now right now there's only one name. So that's why we only see, we still see this tuple, but we see Jenny and then a comma. So if there were more names in there, we would get more results. So you just gotta be real careful using this index so that you get what you wanted out of that. Now there is also a fetch one, where I think it fetches them one at a time, but I think it's probably just for this purpose of this exercise, we'll just fetch them all and do it that way. Okay, so you can see how we have selected all of the contexts. We can also do the filing. I'll show you this as well. Select name from context. Or actually I could do this. If select phone from context, where name equals Jenny. So let's say we had a thousand names in there and we're looking for Jenny's phone number. And this should give us that name. Or that, oops, number, I did it, I did extra space in there. Okay, so that should give us the phone number. Okay, so you can see we got the phone number of Jenny. Again, this assumes that there's only one Jenny. I'm skipping a lot of SQL stuff, but just to kind of give you an idea of how this works. So you can see how we can retrieve values, we can insert values. Assume that we can update values. I haven't tried it on this because this isn't a full SQL, but let's get a shot here, let's try and update, see what happens. I should tell you what, we'll leave that there and I'll go ahead and put update values. So I'm gonna do SQL equals update. Now, is it, there's an insert. Insert into context. Actually I should have tried this ahead of time but I didn't see, hmm, should I have to Google that one? So SQL update, so I forgot the syntax for that one. Ah, yeah, okay. So it is actually update and some update. The table name is contacts, set, let's just, let's just, let's say Jenny moved from LA to Dallas. So set city equals Dallas, where name equals Jenny, oops, Jenny. Okay, and then again we'll go ahead and do c.execute. And that's the cursor, we'll execute that SQL. And then down here we'll go ahead and select all from contacts where name equals Jenny. And then we'll see if the city name has changed. Okay, and that did do it. So that is the syntax for updating something that already exists. So those are the basic things that you need to do to use a database. So you need to connect to the database, create a cursor. The first time through you're gonna need to create that table so that it has the structure that you want. And then you can start inserting values into it. You can retrieve the values using the wildcard or you can just, you can set conditions like where. And I think normally in SQL you would add a semicolon but it's pretty lax here because we're only doing one statement at a time. Again, databases is a very, very large subject. Databases, modern databases are very impressive with what they can do. But those are the basics of creating a table inside of a database. Now a database can have more than one table. You don't have to have just one. You can insert values as you saw here. You can update values. So using set and where. You can retrieve values by selecting. And again the asterisk is the wildcard or you can just choose a column. So let's say if you wanna know the city. So what's Jenny city? This would give you Jenny city. And then you can use the fetch all. You can also use fetch one. Gives you a slightly different style of result. Let's see what happens there. I'm kinda curious. It's been a while. So I've done this stuff. Okay, so if you do fetch one, it doesn't give you the, it doesn't put it into a list because it's probably just fetching the very first results. So depending on how many results you have, you might wanna use fetch one. You might wanna use fetch all. So something to play around with at least. So yeah, that's about it. I will put a link to this code down below. I'll put it into my GitHub and you can kinda just download it and play around with it. And yeah, and kinda go from there. So that's it. Thanks for watching. Also back to just real quick, this SQLite. There are only four types of data in SQLite as opposed to a regular database. And you have text integer, which are real numbers, or integers, which are like one, two, three, four, five. Real, which is like decimals, like 2.5, 8.6, 3.0. Blob, which is stores, I think, binary data. I never tried that on here, so not 100% sure. And null, meaning that there is no value. The value hasn't been put in yet. So those are the only four or five values that come with SQLite. So SQLite isn't particularly powerful, but it's easy to get started and to learn the basics of databases with that. Okay, so again, thanks to my channel members. Consider subscribing, consider joining, and yeah, clicking a thumbs up, I do appreciate those things. So take care and keep on coding.