1 00:00:00,362 --> 00:00:01,940 - [Instructor] Hi and welcome back. 2 00:00:01,940 --> 00:00:02,991 In this video we're going to look 3 00:00:02,991 --> 00:00:05,409 at SQLite and how it works. 4 00:00:05,409 --> 00:00:08,980 Now, I've opened here the folder in ATOM 5 00:00:08,980 --> 00:00:12,275 and we've got the code folder and venv folder. 6 00:00:12,275 --> 00:00:15,151 Remember, venv is just a Python installation. 7 00:00:15,151 --> 00:00:18,553 In the code folder, I've copied section four's code 8 00:00:18,553 --> 00:00:21,395 so it's got app, security and user just like we had 9 00:00:21,395 --> 00:00:23,645 in section four at the end. 10 00:00:24,925 --> 00:00:26,827 Outside either of these folders 11 00:00:26,827 --> 00:00:30,994 I'm going to create new file, I'm going to call it test.py. 12 00:00:32,433 --> 00:00:34,632 Now, I'm just calling it that because I'm going to go 13 00:00:34,632 --> 00:00:37,014 over with you how SQLite works, 14 00:00:37,014 --> 00:00:40,279 how we can interact with a SQLite database from Python 15 00:00:40,279 --> 00:00:43,446 and that's not really part of our app. 16 00:00:44,985 --> 00:00:46,972 So I'm gonna create a separate file 17 00:00:46,972 --> 00:00:51,055 just to show you how we can interact with SQLite. 18 00:00:52,760 --> 00:00:54,410 When we interact with SQLite the first 19 00:00:54,410 --> 00:00:57,410 thing we wanna do is import SQLite3. 20 00:00:59,728 --> 00:01:03,577 This library, this set of code, is already in Python 21 00:01:03,577 --> 00:01:05,801 we don't have to instal it, but it includes 22 00:01:05,801 --> 00:01:08,241 a bunch of things such as allowing us to connect 23 00:01:08,241 --> 00:01:12,452 to a SQLite database, and allowing us to run SQL queries 24 00:01:12,452 --> 00:01:14,979 and things like that. 25 00:01:14,979 --> 00:01:17,988 And also we're not going to go too in depth 26 00:01:17,988 --> 00:01:19,911 into how to build SQL queries, 27 00:01:19,911 --> 00:01:23,828 but we are going to cover some of that as well. 28 00:01:26,147 --> 00:01:30,380 Just importing SQLite3 gives us the ability 29 00:01:30,380 --> 00:01:33,666 to connect and run queries and things like that, 30 00:01:33,666 --> 00:01:36,527 but it doesn't actually connect us to a database. 31 00:01:36,527 --> 00:01:38,622 So the first thing we have to do is 32 00:01:38,622 --> 00:01:41,891 we have to initialise the connection. 33 00:01:41,891 --> 00:01:43,954 I'm going to create a variable connection 34 00:01:43,954 --> 00:01:47,204 and this is going to be sqlite3.connect 35 00:01:48,156 --> 00:01:50,919 and then here we can put inside a string 36 00:01:50,919 --> 00:01:53,502 the connection string, the URI, 37 00:01:54,955 --> 00:01:59,531 the Unified Resource Identifier for our database. 38 00:01:59,531 --> 00:02:03,698 Now, SQLite3 works by storing all of its data in a file 39 00:02:04,993 --> 00:02:08,125 and the file can be called whatever you want, 40 00:02:08,125 --> 00:02:11,458 but in my case I'm gonna call it data.db 41 00:02:13,499 --> 00:02:16,750 So we're going to be creating a file data.db 42 00:02:16,750 --> 00:02:19,083 inside our current directory 43 00:02:20,051 --> 00:02:23,634 and that's going to be our SQLite database. 44 00:02:25,420 --> 00:02:28,429 If you use for example, something like PostgreSQL, 45 00:02:28,429 --> 00:02:32,159 MySQL, or other SQL databases, you'll know 46 00:02:32,159 --> 00:02:36,994 that these SQL databases normally are large and cumbersome. 47 00:02:36,994 --> 00:02:41,161 Also the data files occupy a folders worth of stuff. 48 00:02:42,006 --> 00:02:45,598 With SQLite it is light, so the only thing 49 00:02:45,598 --> 00:02:47,542 it occupies is a single file. 50 00:02:47,542 --> 00:02:49,720 And in that file it contains all data 51 00:02:49,720 --> 00:02:51,791 and all the things it needs. 52 00:02:51,791 --> 00:02:55,463 That doesn't mean that SQLite is substantially slower 53 00:02:55,463 --> 00:02:58,480 particularly at writing the database 54 00:02:58,480 --> 00:03:01,813 than its more professional counterparts. 55 00:03:02,648 --> 00:03:04,731 Now that we've connected, 56 00:03:05,843 --> 00:03:09,010 we can do something like this cursor. 57 00:03:12,040 --> 00:03:15,829 The cursor essentially is like the cursor on the computer 58 00:03:15,829 --> 00:03:17,789 that you're seeing move right now. 59 00:03:17,789 --> 00:03:21,386 It allows you to select things and start things. 60 00:03:21,386 --> 00:03:25,386 So, for example, we can tell the cursor to start 61 00:03:26,252 --> 00:03:28,692 at the top of the database and tell us 62 00:03:28,692 --> 00:03:30,541 the data in that database. 63 00:03:30,541 --> 00:03:33,458 That would be retrieving data from a database. 64 00:03:33,458 --> 00:03:37,625 So the cursor is response for actually executing the queries 65 00:03:38,913 --> 00:03:41,026 such as the selection from a database 66 00:03:41,026 --> 00:03:43,716 or inserting into database and things like that. 67 00:03:43,716 --> 00:03:46,729 Also, storing the result, so the cursor is going 68 00:03:46,729 --> 00:03:49,114 to run a query and then store the result 69 00:03:49,114 --> 00:03:51,864 so that we can access the result. 70 00:03:53,968 --> 00:03:57,551 So let's look at the first SQL query. 71 00:03:57,551 --> 00:04:01,205 To create a table in a SQL database. 72 00:04:01,205 --> 00:04:04,652 Well I'm going to call the variable create_table. 73 00:04:04,652 --> 00:04:06,508 And this variable is going to be a string, 74 00:04:06,508 --> 00:04:10,960 and in this string we are going to put our SQL command. 75 00:04:10,960 --> 00:04:15,127 To create a table in SQL we start with CREATE TABLE. 76 00:04:16,030 --> 00:04:20,744 No surprise there, and then we give the table name, 77 00:04:20,744 --> 00:04:24,911 users for example, and then inside brackets, we specify 78 00:04:26,265 --> 00:04:28,698 the columns of our table. 79 00:04:28,698 --> 00:04:30,846 Remember a table is just a set of columns 80 00:04:30,846 --> 00:04:33,788 and a set of rows and we'll have some data 81 00:04:33,788 --> 00:04:37,955 in each row for the columns that the table is made up of. 82 00:04:39,370 --> 00:04:42,370 So our users maybe made up of an ID, 83 00:04:44,713 --> 00:04:47,200 which is going to be an integer, 84 00:04:47,200 --> 00:04:50,420 a username which is going to be text, 85 00:04:50,420 --> 00:04:54,391 and a password which is going to be text. 86 00:04:54,391 --> 00:04:57,602 So what this means is that the users table 87 00:04:57,602 --> 00:05:00,237 is going to have three columns, an ID column, 88 00:05:00,237 --> 00:05:03,201 a username column and password column. 89 00:05:03,201 --> 00:05:06,037 Whenever we create a new user and store it 90 00:05:06,037 --> 00:05:09,844 in that table, we're going to specify the ID, 91 00:05:09,844 --> 00:05:12,630 the username and the password for that user. 92 00:05:12,630 --> 00:05:14,013 And then we create another one, 93 00:05:14,013 --> 00:05:15,617 we would also have to specify ID, 94 00:05:15,617 --> 00:05:17,930 username and password and so on. 95 00:05:17,930 --> 00:05:20,702 So essentially this defines what is called a schema. 96 00:05:20,702 --> 00:05:23,000 How the data is going to look, 97 00:05:23,000 --> 00:05:25,669 what the data is gonna look like. 98 00:05:25,669 --> 00:05:29,836 Now that we've created the query we have to run the query 99 00:05:31,462 --> 00:05:34,169 The way we do that is using the cursor. 100 00:05:34,169 --> 00:05:38,086 So it's a cursor.execute and then create_table. 101 00:05:43,311 --> 00:05:47,478 So let's run our programme here, and see what happens. 102 00:05:48,364 --> 00:05:51,823 So I'm going to go back to the terminal, 103 00:05:51,823 --> 00:05:53,614 I am running the virtual environment 104 00:05:53,614 --> 00:05:55,391 the way you don't have to. 105 00:05:55,391 --> 00:05:59,782 And then I'm just going to run python test.py. 106 00:05:59,782 --> 00:06:03,040 Okay, that's run, it doesn't tell us anything. 107 00:06:03,040 --> 00:06:04,960 But as you can see back in ATOM we've 108 00:06:04,960 --> 00:06:07,460 got now a file called data.db. 109 00:06:09,339 --> 00:06:12,854 So if we open this data.db we can see that 110 00:06:12,854 --> 00:06:16,145 it looks very weird because it's not a text file 111 00:06:16,145 --> 00:06:19,856 it is essentially a binary file, but it contains 112 00:06:19,856 --> 00:06:22,383 some things we can read. 113 00:06:22,383 --> 00:06:26,550 For example, we've got tableuser CREATE TABLE users 114 00:06:28,101 --> 00:06:31,963 id int, username text, password text. 115 00:06:31,963 --> 00:06:34,918 So we know that we've done something. 116 00:06:34,918 --> 00:06:39,307 But we've not yet stored any data in this database 117 00:06:39,307 --> 00:06:42,674 or indeed retrieve any data when we run our programme 118 00:06:42,674 --> 00:06:45,004 it doesn't tell us anything. 119 00:06:45,004 --> 00:06:47,150 So that's what we're gonna do, let's store 120 00:06:47,150 --> 00:06:49,461 some data in this database, for example, 121 00:06:49,461 --> 00:06:53,861 we can create out first user, which is gonna be 122 00:06:53,861 --> 00:06:57,836 this one here, and each of the elements corresponds 123 00:06:57,836 --> 00:07:01,078 to one of the columns, so one is gonna be the ID, 124 00:07:01,078 --> 00:07:03,022 Jose is gonna be the username 125 00:07:03,022 --> 00:07:06,387 and asdf is gonna be the password. 126 00:07:06,387 --> 00:07:09,387 Remember, this user is just a tuple, 127 00:07:10,599 --> 00:07:13,954 a Python data structure, but we have to insert 128 00:07:13,954 --> 00:07:16,505 the user into the database. 129 00:07:16,505 --> 00:07:19,082 So the insert_query is going to 130 00:07:19,082 --> 00:07:22,415 be INSERT INTO the table and the VALUES. 131 00:07:28,648 --> 00:07:32,191 So here's what's happening, this is a SQL query once again 132 00:07:32,191 --> 00:07:35,655 and the way we build it is we say INSERT INTO 133 00:07:35,655 --> 00:07:39,822 the table that we wanna insert values into, the word VALUES, 134 00:07:41,101 --> 00:07:44,460 and then inside brackets each of 135 00:07:44,460 --> 00:07:46,826 the values that we want to insert. 136 00:07:46,826 --> 00:07:49,784 So this one is gonna be the ID, this one is gonna be 137 00:07:49,784 --> 00:07:53,951 the username and this one is gonna be the password. 138 00:07:56,052 --> 00:07:59,156 Fortunately, we can leave it as question mark, 139 00:07:59,156 --> 00:08:01,835 question mark, question mark, and then when we run 140 00:08:01,835 --> 00:08:06,002 the query with the cursor we just say insert_query 141 00:08:08,141 --> 00:08:09,308 and then user. 142 00:08:10,784 --> 00:08:13,667 And the cursor is smart enough to replace 143 00:08:13,667 --> 00:08:17,447 each of the values in the user tuple for the question marks 144 00:08:17,447 --> 00:08:19,264 and we don't have to do anything else. 145 00:08:19,264 --> 00:08:23,431 So that's going to insert the user in the table there. 146 00:08:25,581 --> 00:08:29,640 Whenever we insert data, we have to tell the connection 147 00:08:29,640 --> 00:08:32,540 to actually save all of our changes 148 00:08:32,540 --> 00:08:35,677 into the disc, into the data.db file. 149 00:08:35,677 --> 00:08:39,760 The way we do that is by doing connection.commit. 150 00:08:40,724 --> 00:08:45,356 At the end it's also good practise to do connection.close. 151 00:08:45,356 --> 00:08:46,814 To make sure that the connection's closed 152 00:08:46,814 --> 00:08:50,110 and it's not gonna receive anymore data 153 00:08:50,110 --> 00:08:55,021 or consuming resources while it waits for more data. 154 00:08:55,021 --> 00:08:59,225 So I saved that now let's delete our data.db, 155 00:08:59,225 --> 00:09:02,694 an important step, because we're going to create a table 156 00:09:02,694 --> 00:09:05,043 and if that already exists, then we're 157 00:09:05,043 --> 00:09:07,075 gonna have a wee problem. 158 00:09:07,075 --> 00:09:11,242 Let's go back to our terminal and run python test.py again 159 00:09:12,379 --> 00:09:14,720 and now surely we expect something slightly different 160 00:09:14,720 --> 00:09:17,470 to be inside data.db, and indeed. 161 00:09:19,552 --> 00:09:22,430 As you can see we've got the same table users 162 00:09:22,430 --> 00:09:26,089 blah blah blah, but now, we've got out first value, 163 00:09:26,089 --> 00:09:27,589 which is juseasdf. 164 00:09:28,839 --> 00:09:32,032 Notice how the ID is nowhere to be seen 165 00:09:32,032 --> 00:09:34,559 I wonder where it is, but don't worry, 166 00:09:34,559 --> 00:09:37,059 I'm sure it I there somewhere. 167 00:09:38,766 --> 00:09:42,933 Okay, so now we are able to insert a user into the database, 168 00:09:44,159 --> 00:09:47,409 let's see how we can insert many users. 169 00:09:49,471 --> 00:09:51,977 I'm going to create a variable called users, 170 00:09:51,977 --> 00:09:55,598 and it's gonna be a list of tuples, so I'm gonna copy 171 00:09:55,598 --> 00:09:59,181 this one there, change the values slightly, 172 00:10:01,433 --> 00:10:03,733 just to show you how you can insert multiple rows 173 00:10:03,733 --> 00:10:08,250 in one go it's quite a useful thing to be able to do. 174 00:10:08,250 --> 00:10:11,080 Okay, now we've got two users here, and what we're gonna do 175 00:10:11,080 --> 00:10:15,247 is exactly the same insert_query, but now we're gonna say 176 00:10:17,599 --> 00:10:20,849 cursor.executemany insert_query, users. 177 00:10:23,822 --> 00:10:28,461 And all that's gonna do is cursor.execute but once 178 00:10:28,461 --> 00:10:31,878 for each user in our list, fairly simply. 179 00:10:35,553 --> 00:10:37,380 Okay, I'm not gonna run that because you know 180 00:10:37,380 --> 00:10:38,561 that's gonna work, or at least 181 00:10:38,561 --> 00:10:40,606 you should trust me that's gonna work. 182 00:10:40,606 --> 00:10:43,530 The last thing I wanna show you is how to retrieve data out 183 00:10:43,530 --> 00:10:47,697 so that our programme can actually tell us something. 184 00:10:48,554 --> 00:10:51,590 So select_query is gonna be another variable 185 00:10:51,590 --> 00:10:54,757 which is gonna be another SQL command. 186 00:10:55,895 --> 00:10:58,735 And to retrieve data from a SQL database is really simple 187 00:10:58,735 --> 00:11:02,652 all you do is SELECT * FROM and then the table, 188 00:11:04,097 --> 00:11:08,264 users in this case, SELECT * is gonna go to the users table 189 00:11:09,580 --> 00:11:13,302 and is going to find every row and then 190 00:11:13,302 --> 00:11:16,321 it's just going to SELECT all of data in each row. 191 00:11:16,321 --> 00:11:20,029 Here for example, we will SELECT id 192 00:11:20,029 --> 00:11:24,377 and then it will only return the ID values for each row. 193 00:11:24,377 --> 00:11:27,303 We SELECT * it returns all of the column values. 194 00:11:27,303 --> 00:11:30,619 You can play around with that if you wish. 195 00:11:30,619 --> 00:11:34,536 Then we're gonna say for row in cursor.execute, 196 00:11:35,737 --> 00:11:37,737 select_query, print row. 197 00:11:41,039 --> 00:11:42,111 So really what we are doing is 198 00:11:42,111 --> 00:11:45,344 we are running the SELECT statement 199 00:11:45,344 --> 00:11:47,982 and we're storing the results of that 200 00:11:47,982 --> 00:11:51,928 in the result of this command and then we're 201 00:11:51,928 --> 00:11:54,172 gonna iterate over that just as if 202 00:11:54,172 --> 00:11:57,695 it were a list which it is essentially. 203 00:11:57,695 --> 00:11:59,396 Then we're gonna say for each row in 204 00:11:59,396 --> 00:12:01,595 that we're gonna print that out. 205 00:12:01,595 --> 00:12:05,762 Once again, delete data.db and we're gonna run this again 206 00:12:08,989 --> 00:12:13,472 and now something should come up, which indeed it does. 207 00:12:13,472 --> 00:12:16,196 The three users that we inserted come up. 208 00:12:16,196 --> 00:12:20,363 So, jose, rolf and anne with their respective passwords. 209 00:12:21,340 --> 00:12:25,522 OK, so now we've looked at how to insert single items 210 00:12:25,522 --> 00:12:27,697 into database and to insert multiple items 211 00:12:27,697 --> 00:12:29,753 and have to retrieve them. 212 00:12:29,753 --> 00:12:33,116 Over the next few videos, we're going to extend our API 213 00:12:33,116 --> 00:12:35,487 to make use of this new knowledge 214 00:12:35,487 --> 00:12:37,754 and we're going to be allowing users to sign up 215 00:12:37,754 --> 00:12:39,841 and then after that we are going to be writing 216 00:12:39,841 --> 00:12:42,987 and reading our items from the database. 217 00:12:42,987 --> 00:12:45,372 Now, this is going to make our code substantially longer 218 00:12:45,372 --> 00:12:49,560 but there's gonna be a lot of duplicated code, for example, 219 00:12:49,560 --> 00:12:52,975 you always need a connection and a cursor. 220 00:12:52,975 --> 00:12:55,545 You always need to commit and close a connection. 221 00:12:55,545 --> 00:12:58,941 So, as you can see it's not really very complicated. 222 00:12:58,941 --> 00:13:02,247 All you need is a query and to execute it, 223 00:13:02,247 --> 00:13:06,614 and then make sure to commit so that it's saved to the disc. 224 00:13:06,614 --> 00:13:10,136 And that's it, so we're going to be extending our API 225 00:13:10,136 --> 00:13:12,146 over the next few videos, I'm excited to be guiding you 226 00:13:12,146 --> 00:13:15,356 through that, so I'll see you in the next video.