WEBVTT 1 00:00:00.200 --> 00:00:01.850 Hi guys and welcome back. 2 00:00:01.850 --> 00:00:03.810 In this video we're going to be implementing 3 00:00:03.810 --> 00:00:06.910 the stage three database.py. 4 00:00:06.910 --> 00:00:08.960 And as you know, the most important change that 5 00:00:08.960 --> 00:00:11.880 we're making is the change to our data model. 6 00:00:11.880 --> 00:00:14.140 So first of all what changes are we making 7 00:00:14.140 --> 00:00:15.180 to our tables? 8 00:00:15.180 --> 00:00:17.120 Well, let's copy in here 9 00:00:17.120 --> 00:00:19.810 the new create table for movies. 10 00:00:19.810 --> 00:00:22.590 Movies is now gonna have an I-D property 11 00:00:22.590 --> 00:00:24.670 which is gonna be the primary key 12 00:00:24.670 --> 00:00:29.050 a number used to identify uniquely each movie. 13 00:00:29.050 --> 00:00:30.780 Every movie is then gonna have their title 14 00:00:30.780 --> 00:00:33.673 and the release timestamp as it had before. 15 00:00:34.640 --> 00:00:37.260 Then we're gonna have a user's table. 16 00:00:37.260 --> 00:00:39.640 And I'm gonna bring it in here as well. 17 00:00:39.640 --> 00:00:41.230 The user's table 18 00:00:41.230 --> 00:00:43.090 is just gonna have a user's name for now. 19 00:00:43.090 --> 00:00:44.760 Which is gonna be the primary key. 20 00:00:44.760 --> 00:00:47.580 This is use to uniquely identify people 21 00:00:47.580 --> 00:00:48.970 using our application. 22 00:00:48.970 --> 00:00:51.670 And remember you can use more columns here 23 00:00:51.670 --> 00:00:54.010 to add more information about specific users 24 00:00:54.010 --> 00:00:55.390 if you wanted to. 25 00:00:55.390 --> 00:00:58.560 Finally, we're gonna have the watched table. 26 00:00:58.560 --> 00:01:01.133 And the watched table that I'm gonna chain here. 27 00:01:02.010 --> 00:01:03.220 Create watched table. 28 00:01:03.220 --> 00:01:06.080 Is gonna be a little more complicated. 29 00:01:06.080 --> 00:01:09.260 Firstly, we have the create table if not exists 30 00:01:09.260 --> 00:01:12.000 as normal, and it's gonna be called, watched. 31 00:01:12.000 --> 00:01:14.800 But, then, inside it we have the user_username 32 00:01:14.800 --> 00:01:15.770 which is text, 33 00:01:15.770 --> 00:01:18.703 and that is gonna reference this column here. 34 00:01:20.990 --> 00:01:23.760 We've got the movie ID which is gonna be an integer 35 00:01:23.760 --> 00:01:26.070 referencing this column up here. 36 00:01:26.070 --> 00:01:29.770 So, this table is simply references to other columns. 37 00:01:29.770 --> 00:01:32.993 And how do we tell SQlite or PostgreSQL later on 38 00:01:32.993 --> 00:01:36.750 that these columns here are referencing other columns 39 00:01:36.750 --> 00:01:38.890 with a foreign key constraint. 40 00:01:38.890 --> 00:01:42.385 So, we'll do foreign key user_username 41 00:01:42.385 --> 00:01:46.713 references the user stable on specifically the username. 42 00:01:47.880 --> 00:01:50.040 Similarly, we will have another foreign key constraint 43 00:01:50.040 --> 00:01:53.760 for movie ID that references the movie stable 44 00:01:53.760 --> 00:01:55.663 specifically the ID column. 45 00:01:56.610 --> 00:01:59.790 Then, at the end, we have our semicolon as usual, 46 00:01:59.790 --> 00:02:02.020 and that's our table created. 47 00:02:02.020 --> 00:02:03.840 So, again, when we add the new record 48 00:02:03.840 --> 00:02:06.550 to the watched table that is going to mean 49 00:02:06.550 --> 00:02:08.778 that the user that we added watched 50 00:02:08.778 --> 00:02:11.100 the movie that we added. 51 00:02:11.100 --> 00:02:12.850 Then with some smart querying, 52 00:02:12.850 --> 00:02:14.510 We can go through and find more information 53 00:02:14.510 --> 00:02:15.860 about either of those. 54 00:02:15.860 --> 00:02:19.010 Remember to use these new tables and variables 55 00:02:19.010 --> 00:02:23.270 in here, in order to create the tables themselves. 56 00:02:23.270 --> 00:02:26.670 And it's important that we create them in order. 57 00:02:26.670 --> 00:02:29.400 So, the foreign key must appear 58 00:02:29.400 --> 00:02:31.620 after the other tables have been created. 59 00:02:31.620 --> 00:02:33.500 So, here we're referencing the user's table. 60 00:02:33.500 --> 00:02:36.020 Therefore the user's table must be created first. 61 00:02:36.020 --> 00:02:37.760 Here, we're referencing the movie's table. 62 00:02:37.760 --> 00:02:39.520 So, therefore that must be created first. 63 00:02:39.520 --> 00:02:40.780 So, the order that I'm gonna go for it, 64 00:02:40.780 --> 00:02:42.793 is movies, users, and then watched. 65 00:02:44.330 --> 00:02:46.260 Also, remember to change the name of this variable 66 00:02:46.260 --> 00:02:48.950 like I just did, if you are gonna change that. 67 00:02:48.950 --> 00:02:51.870 Next up to insert a new movie. 68 00:02:51.870 --> 00:02:53.960 Is there anything we have to change? 69 00:02:53.960 --> 00:02:56.880 Well, not really. We've got insert into movies. 70 00:02:56.880 --> 00:02:58.670 We're putting in here the title and the timestamp, 71 00:02:58.670 --> 00:03:01.401 so we're not adding a value to the ID column. 72 00:03:01.401 --> 00:03:05.100 Therefore, SQlite is gonna automatically add that for us 73 00:03:05.100 --> 00:03:08.060 using that primary key. Which is, as we know, 74 00:03:08.060 --> 00:03:10.670 an alias for the row ID column. 75 00:03:10.670 --> 00:03:12.210 So, nothing to change here at all. 76 00:03:12.210 --> 00:03:13.043 That's great. 77 00:03:13.043 --> 00:03:16.140 When we get and display movies from the movies table, 78 00:03:16.140 --> 00:03:19.086 we do have to make a small change in app.py. 79 00:03:19.086 --> 00:03:21.210 That's because we've added a new column. 80 00:03:21.210 --> 00:03:23.270 So, when we go to app.py, 81 00:03:23.270 --> 00:03:27.010 we no longer want to access movie one and movies zero. 82 00:03:27.010 --> 00:03:30.440 We really want to access movie two and movie one. 83 00:03:30.440 --> 00:03:32.130 However, it's gonna be useful 84 00:03:32.130 --> 00:03:34.380 to print out the movie ID as well, 85 00:03:34.380 --> 00:03:36.150 since we might need it for other things. 86 00:03:36.150 --> 00:03:39.790 So, we're gonna do movie zero as well in here. 87 00:03:39.790 --> 00:03:42.290 So, it'll be something like one, The Matrix, 88 00:03:42.290 --> 00:03:45.980 on this date, two, Gone Girl, on that date. 89 00:03:45.980 --> 00:03:47.650 This might be later on for users 90 00:03:47.650 --> 00:03:51.270 to be able to refer to the movies by their IDs. 91 00:03:51.270 --> 00:03:52.880 Something interesting to note in here, 92 00:03:52.880 --> 00:03:55.000 is that we could use tuple destructing. 93 00:03:55.000 --> 00:03:56.560 I'm gonna leave a reference to that 94 00:03:56.560 --> 00:03:58.660 in the resources section of this lecture. 95 00:03:58.660 --> 00:04:00.710 To simplify this code a little bit. 96 00:04:00.710 --> 00:04:05.710 We can do for ID title and release date in movies 97 00:04:05.940 --> 00:04:08.220 and now we can refer to these things separately 98 00:04:08.220 --> 00:04:10.570 instead of movie two, zero, and one. 99 00:04:10.570 --> 00:04:14.010 So, we can refer here to release date. 100 00:04:14.010 --> 00:04:16.640 Here, we can refer to underscore ID. 101 00:04:16.640 --> 00:04:18.920 And here, we can refer title. 102 00:04:18.920 --> 00:04:21.370 And notice that I'm using underscore ID here 103 00:04:21.370 --> 00:04:22.740 instead of just ID, 104 00:04:22.740 --> 00:04:25.480 because ID is a built in function in Python, 105 00:04:25.480 --> 00:04:29.700 and it's generally a bad idea to shadow it or override it. 106 00:04:29.700 --> 00:04:32.300 When we come to marking movies as watched, 107 00:04:32.300 --> 00:04:35.600 that's when the differences start popping up. 108 00:04:35.600 --> 00:04:36.810 To mark a movie as watched, 109 00:04:36.810 --> 00:04:39.880 we need to insert a row into the watched table. 110 00:04:39.880 --> 00:04:43.600 And, that requires username and movie ID. 111 00:04:43.600 --> 00:04:47.290 So, we're going to go over to that watch movie 112 00:04:47.290 --> 00:04:50.170 in database.py down here and we now 113 00:04:50.170 --> 00:04:53.690 instead of title, need to take in movie ID. 114 00:04:53.690 --> 00:04:55.170 We no longer need to delete anything. 115 00:04:55.170 --> 00:04:57.810 That was really the purpose of the stage three really. 116 00:04:57.810 --> 00:05:00.770 To not have to delete movies when we insert them. 117 00:05:00.770 --> 00:05:02.490 So, here we're gonna insert watch movie, 118 00:05:02.490 --> 00:05:06.710 but we're gonna pass in the user name and the movie ID. 119 00:05:06.710 --> 00:05:07.543 What that's gonna do, 120 00:05:07.543 --> 00:05:09.530 when we go to insert watched movie, 121 00:05:09.530 --> 00:05:14.010 is we're now going to insert data into this watched table. 122 00:05:14.010 --> 00:05:16.200 Notice that watched, has kept the same name, 123 00:05:16.200 --> 00:05:18.910 but we do now need to insert into watcher name, 124 00:05:18.910 --> 00:05:23.570 and movie ID, but of course this is user_username. 125 00:05:25.190 --> 00:05:27.520 You can see that the watch movie function is now 126 00:05:27.520 --> 00:05:29.840 simpler because we've only got a single insert 127 00:05:29.840 --> 00:05:32.780 statement that tells us that his happened. 128 00:05:32.780 --> 00:05:34.360 This person watched this movie. 129 00:05:34.360 --> 00:05:37.040 That's now a row in our table. 130 00:05:37.040 --> 00:05:39.700 If we go over to app.py, we do also have to 131 00:05:39.700 --> 00:05:41.530 make a couple of changes in here. 132 00:05:41.530 --> 00:05:43.890 In prompt watch movie, we're asking for the movie title, 133 00:05:43.890 --> 00:05:46.590 now we need to ask for the movie ID. 134 00:05:46.590 --> 00:05:49.450 And, I'm gonna add here movie ID. 135 00:05:49.450 --> 00:05:52.790 This is the reason why we were printing _id up here. 136 00:05:52.790 --> 00:05:54.300 We need that of course to be able to 137 00:05:54.300 --> 00:05:56.623 use it down here when we ask. 138 00:05:57.770 --> 00:06:00.240 In order to be able to watch a movie, 139 00:06:00.240 --> 00:06:03.730 we need to have a user be created first. 140 00:06:03.730 --> 00:06:07.530 Otherwise the foreign key here the username 141 00:06:07.530 --> 00:06:09.710 won't be able to reference any specific value. 142 00:06:09.710 --> 00:06:10.720 That's gonna give us an error. 143 00:06:10.720 --> 00:06:12.370 So we need to have a query 144 00:06:12.370 --> 00:06:15.060 for inserting a new user into the user's table. 145 00:06:15.060 --> 00:06:18.010 So, here we're gonna do insert, user, 146 00:06:18.010 --> 00:06:21.270 and that's gonna be insert into users username 147 00:06:21.270 --> 00:06:24.650 the values is gonna be just a question make there, 148 00:06:24.650 --> 00:06:28.160 and we also need of course the add user function 149 00:06:28.160 --> 00:06:32.070 that we are going to make for example down here. 150 00:06:32.070 --> 00:06:34.670 And def add user and it's gonna take in a username, 151 00:06:34.670 --> 00:06:37.250 and all it's gonna do is use the connection 152 00:06:38.450 --> 00:06:41.490 to execute the insert user. So, execute 153 00:06:41.490 --> 00:06:44.610 insert user with the username. 154 00:06:44.610 --> 00:06:47.510 Now we're gonna wanna use that in app.py 155 00:06:47.510 --> 00:06:50.270 with a new menu option to let the user 156 00:06:50.270 --> 00:06:53.040 create a new user in the table. 157 00:06:53.040 --> 00:06:55.541 So going up to the menu, we're going to change 158 00:06:55.541 --> 00:07:00.380 exit to seven, six to add user to the app. 159 00:07:00.380 --> 00:07:02.410 Then, going back to the while loop, 160 00:07:02.410 --> 00:07:05.590 we're gonna change the exit condition to seven. 161 00:07:05.590 --> 00:07:07.910 And we're going to add and new elif branch 162 00:07:07.910 --> 00:07:11.500 at the bottom for user input equal six. 163 00:07:11.500 --> 00:07:13.960 And that is gonna do prompt add user. 164 00:07:13.960 --> 00:07:16.030 A function which doesn't exist yet, 165 00:07:16.030 --> 00:07:17.520 that we're gonna add. 166 00:07:17.520 --> 00:07:22.520 So, going up here, we'll do prompt, add, user, 167 00:07:22.870 --> 00:07:25.180 and that's just gonna take in a username. 168 00:07:25.180 --> 00:07:28.680 Input a username, and do database.add 169 00:07:28.680 --> 00:07:31.160 user with that username. 170 00:07:31.160 --> 00:07:33.130 So, that's all the changes that 171 00:07:33.130 --> 00:07:35.570 we're gonna make in this video. 172 00:07:35.570 --> 00:07:38.050 But, the more keen eyed amongst you 173 00:07:38.050 --> 00:07:40.660 will have noticed that there is a problem. 174 00:07:40.660 --> 00:07:43.610 We go over to database.py; you can see that 175 00:07:43.610 --> 00:07:45.970 when we select watched movies we're selecting 176 00:07:45.970 --> 00:07:48.970 star from the watched table. 177 00:07:48.970 --> 00:07:53.300 Which has given us the username and the movie ID. 178 00:07:53.300 --> 00:07:56.260 But, it's not giving us the movie title. 179 00:07:56.260 --> 00:07:57.530 So, how do we do this? 180 00:07:57.530 --> 00:08:00.280 Well, we're selecting watched movies in here, 181 00:08:00.280 --> 00:08:01.640 when we ask them for the username. 182 00:08:01.640 --> 00:08:02.800 Then, we get the watched movies, 183 00:08:02.800 --> 00:08:04.300 and then we're printing them out. 184 00:08:04.300 --> 00:08:07.237 Which takes us to here, and then, we're printing 185 00:08:07.237 --> 00:08:09.560 the username, and for each movie 186 00:08:09.560 --> 00:08:14.560 we're printing movie one. Movie one is the movie ID. 187 00:08:15.440 --> 00:08:16.960 So, how do we get the title in there, 188 00:08:16.960 --> 00:08:20.230 instead of the ID. Well, in order to do that, 189 00:08:20.230 --> 00:08:23.120 we need to use a new sequel construct. 190 00:08:23.120 --> 00:08:24.530 Which is the join. 191 00:08:24.530 --> 00:08:26.500 Joins are a little bit more complicated. 192 00:08:26.500 --> 00:08:29.200 They involve selecting data from multiple tables 193 00:08:29.200 --> 00:08:31.910 at the same time. And, they're really cool. 194 00:08:31.910 --> 00:08:33.320 They're really useful. They're really 195 00:08:33.320 --> 00:08:34.820 one of the main things in SQL 196 00:08:34.820 --> 00:08:36.170 that you really need to know about. 197 00:08:36.170 --> 00:08:37.880 So, we're going to learn about them 198 00:08:37.880 --> 00:08:39.140 in the next couple of videos. 199 00:08:39.140 --> 00:08:41.100 Then we will come back and be able to add them 200 00:08:41.100 --> 00:08:42.710 to out application to actually display 201 00:08:42.710 --> 00:08:44.830 the movie title in here. 202 00:08:44.830 --> 00:08:46.520 Thank you guys for watching! Thanks for joining me 203 00:08:46.520 --> 00:08:48.913 in this video and I'll see you in the next one!