WEBVTT 00:00:00.420 --> 00:00:01.840 Hi guys, and welcome back. 00:00:01.840 --> 00:00:04.120 In this video, we're going to be completing 00:00:04.120 --> 00:00:07.160 our database.py file, so let's get started. 00:00:07.160 --> 00:00:08.940 We've got most of the code already, 00:00:08.940 --> 00:00:10.890 so what we're going to change is mostly 00:00:10.890 --> 00:00:15.480 the create tables and watch movie functions. 00:00:15.480 --> 00:00:18.070 Create tables now has to create the two tables that we need 00:00:18.070 --> 00:00:21.580 instead of just one, and watched movie now has to delete 00:00:21.580 --> 00:00:24.490 from one table and insert into the other. 00:00:24.490 --> 00:00:26.270 Some of your other queries are also gonna have to change, 00:00:26.270 --> 00:00:29.070 but most of our Python code is going to remain the same. 00:00:29.070 --> 00:00:31.340 So here we've got the queries that we had 00:00:31.340 --> 00:00:33.220 at the end of stage one. 00:00:33.220 --> 00:00:34.670 We're creating our movies table, 00:00:34.670 --> 00:00:36.950 then we can insert movies, select movies, upcoming 00:00:36.950 --> 00:00:40.530 and watched, set movies to be watched by updating the table, 00:00:40.530 --> 00:00:42.180 and finally, we've got this delete movie 00:00:42.180 --> 00:00:44.680 that we added just recently. 00:00:44.680 --> 00:00:46.270 Let's modify our existing queries 00:00:46.270 --> 00:00:47.820 to match the new data model. 00:00:47.820 --> 00:00:50.930 The first thing we wanna do is remove the watched property 00:00:50.930 --> 00:00:51.990 from the movies table. 00:00:51.990 --> 00:00:54.240 We're not going to need that anymore. 00:00:54.240 --> 00:00:56.210 Similarly, when we go to insert movies, 00:00:56.210 --> 00:00:58.300 we no longer need the watched property, 00:00:58.300 --> 00:00:59.860 so we can remove that column, 00:00:59.860 --> 00:01:02.660 as well as the value we're inserting. 00:01:02.660 --> 00:01:05.150 When we go to select watched movies, 00:01:05.150 --> 00:01:08.490 now we're no longer going to select star from movies, 00:01:08.490 --> 00:01:11.390 we're going to select star from the watched table 00:01:11.390 --> 00:01:13.260 that we're gonna create, 00:01:13.260 --> 00:01:15.180 and we're going to set the where 00:01:15.180 --> 00:01:19.390 to where watcher name equals the argument, 00:01:19.390 --> 00:01:21.230 so we're going to change that, 00:01:21.230 --> 00:01:22.830 and then we're going to get everything 00:01:22.830 --> 00:01:25.860 that a certain person has watched. 00:01:25.860 --> 00:01:28.330 We also need a few new queries as well, of course. 00:01:28.330 --> 00:01:32.420 We have to create the watch list table, 00:01:32.420 --> 00:01:35.850 and that is going to be another of these multi-line queries 00:01:35.850 --> 00:01:37.370 in here, and I'm just gonna copy it in 00:01:37.370 --> 00:01:39.460 to save you some time. 00:01:39.460 --> 00:01:42.600 What we've got is create table if not exists, 00:01:42.600 --> 00:01:45.330 the watched table, and it has the watcher name, 00:01:45.330 --> 00:01:48.880 or which user watched a thing, 00:01:48.880 --> 00:01:50.560 and the thing they watched is in here, 00:01:50.560 --> 00:01:52.310 the title of the movie. 00:01:52.310 --> 00:01:55.520 Okay, I'm just gonna move the delete movie up here 00:01:55.520 --> 00:01:56.360 below insert. 00:01:56.360 --> 00:01:58.610 I think that's a slightly better place for it, 00:01:58.610 --> 00:02:02.150 and then we've got the insert watched movie, as well. 00:02:02.150 --> 00:02:04.600 So we need insert watched movie, 00:02:04.600 --> 00:02:07.810 and that is gonna be insert into the watched table, 00:02:07.810 --> 00:02:10.890 and we have the watcher name and the title, 00:02:10.890 --> 00:02:13.840 and the values are the two arguments. 00:02:13.840 --> 00:02:15.480 All right, this is everything we need. 00:02:15.480 --> 00:02:18.120 I'm sure that with this data, 00:02:18.120 --> 00:02:21.020 you can go and fill in the rest of the blanks 00:02:21.020 --> 00:02:22.880 in our database.py file, 00:02:22.880 --> 00:02:25.210 so I would recommend that you do that first of all, 00:02:25.210 --> 00:02:27.330 because as you know, coding is the best way 00:02:27.330 --> 00:02:28.980 to practise coding, 00:02:28.980 --> 00:02:30.900 but we're gonna do that just now, as well. 00:02:30.900 --> 00:02:32.310 So in order to create the tables, 00:02:32.310 --> 00:02:34.000 the first thing that we have to do is, of course, 00:02:34.000 --> 00:02:38.303 to duplicate this and make sure to create the watched table. 00:02:39.158 --> 00:02:41.670 We'll create the watch list table in there as well 00:02:41.670 --> 00:02:44.380 in order to have everything that we need. 00:02:44.380 --> 00:02:47.230 Something to remember though is that we should delete 00:02:47.230 --> 00:02:51.960 data.db in order to make sure that we recreate the table, 00:02:51.960 --> 00:02:53.910 because at the moment, the movies table exists, 00:02:53.910 --> 00:02:55.550 but it has the old columns. 00:02:55.550 --> 00:02:58.080 We have to get rid of that in order to be able 00:02:58.080 --> 00:03:01.990 to recreate it, so I'm gonna go ahead and delete that 00:03:01.990 --> 00:03:04.310 and also delete the DB journal. 00:03:04.310 --> 00:03:07.040 This is created by the DB Browser to keep track 00:03:07.040 --> 00:03:10.490 of the old changes before we actually save the file. 00:03:10.490 --> 00:03:12.340 To mark a movie as watched, 00:03:12.340 --> 00:03:14.930 we're now going to have to delete the movie 00:03:14.930 --> 00:03:16.260 from the movies table 00:03:16.260 --> 00:03:19.570 and insert it into the watched movies table. 00:03:19.570 --> 00:03:23.410 So we're gonna do connection.execute delete movie, 00:03:23.410 --> 00:03:25.750 and that takes in the movie title, 00:03:25.750 --> 00:03:28.820 and we also have to do connection.execute, 00:03:28.820 --> 00:03:32.870 and we insert a watched movie, passing in the username 00:03:32.870 --> 00:03:37.130 of the person that watched the movie, as well as the title. 00:03:37.130 --> 00:03:39.540 Of course, we do need the username to be passed 00:03:39.540 --> 00:03:42.520 into this function in order to have access to it here. 00:03:42.520 --> 00:03:45.730 Because we need the username, let's go back to app.py 00:03:45.730 --> 00:03:49.120 and make sure to pass that in when we watch a movie. 00:03:49.120 --> 00:03:50.727 Here we can see in prompt watch movie. 00:03:50.727 --> 00:03:52.490 We get the movie title. 00:03:52.490 --> 00:03:54.570 We're now going to get the username as well, 00:03:54.570 --> 00:03:56.330 or who watched the movie. 00:03:56.330 --> 00:03:59.690 So it's a username, it's that, and pass it in here 00:03:59.690 --> 00:04:01.120 just like that. 00:04:01.120 --> 00:04:03.940 To get the watched movies of a person, 00:04:03.940 --> 00:04:06.340 we're also going to need that person's username. 00:04:06.340 --> 00:04:08.740 Otherwise, we won't be able to find specifically 00:04:08.740 --> 00:04:10.720 that person's watched movies, 00:04:10.720 --> 00:04:13.920 and so we have to go ahead and, in database.py, 00:04:13.920 --> 00:04:17.700 make sure that get watched movies accepts a username, 00:04:17.700 --> 00:04:19.990 and then when we execute select watched movies, 00:04:19.990 --> 00:04:21.790 pass that in as the argument. 00:04:21.790 --> 00:04:23.860 Make sure to make it a tuple. 00:04:23.860 --> 00:04:24.960 That's because when we changed 00:04:24.960 --> 00:04:26.880 select watched movies earlier, 00:04:26.880 --> 00:04:30.130 we now have the watcher name that we need. 00:04:30.130 --> 00:04:31.410 Of course, just like before, 00:04:31.410 --> 00:04:33.070 because we have the username here, 00:04:33.070 --> 00:04:34.587 we now need to go back into app.py 00:04:34.587 --> 00:04:36.380 and make sure to pass that in. 00:04:36.380 --> 00:04:39.340 So right here, before calling get watched movies, 00:04:39.340 --> 00:04:42.740 we'll do username equal input of username, 00:04:42.740 --> 00:04:46.108 just as we have up here, and we're gonna pass it in there. 00:04:46.108 --> 00:04:49.007 You may start to believe that this could be a function, 00:04:49.007 --> 00:04:50.300 and I would probably agree with you. 00:04:50.300 --> 00:04:52.330 You can make this a function if you want, 00:04:52.330 --> 00:04:55.630 but I think it's just short enough and just clear enough 00:04:55.630 --> 00:04:58.700 and just similar enough to previous pieces of code up here 00:04:58.700 --> 00:05:00.560 that if I make this a function, 00:05:00.560 --> 00:05:01.970 I would start to feel like I should probably 00:05:01.970 --> 00:05:04.300 make this a function too, and this, 00:05:04.300 --> 00:05:06.610 so for now, I'm just gonna leave it as that, 00:05:06.610 --> 00:05:09.160 but again, it's totally your choice. 00:05:09.160 --> 00:05:12.320 Something important to note is that if we go over to, 00:05:12.320 --> 00:05:16.580 for example, database.get_movies, which is here, 00:05:16.580 --> 00:05:20.410 that is selecting movies from the movies table, 00:05:20.410 --> 00:05:23.300 which has a title and a release timestamp. 00:05:23.300 --> 00:05:24.780 Something to take into account is that 00:05:24.780 --> 00:05:28.770 database.get_watched_movies, if we go over to database.py, 00:05:28.770 --> 00:05:32.850 is actually retrieving data from the watched table 00:05:32.850 --> 00:05:34.980 instead of from the movies table, 00:05:34.980 --> 00:05:37.510 so therefore, getting data from the watched table 00:05:37.510 --> 00:05:40.160 is gonna give us watcher name and title 00:05:40.160 --> 00:05:43.810 and not title and timestamp as it did before. 00:05:43.810 --> 00:05:47.610 So when we get that, then pass it to print movie list, 00:05:47.610 --> 00:05:50.910 what we're gonna end up with is trying to print out 00:05:50.910 --> 00:05:52.740 the movie title as a timestamp, 00:05:52.740 --> 00:05:54.150 which is not gonna make any sense, 00:05:54.150 --> 00:05:56.560 as well as the person's name. 00:05:56.560 --> 00:05:59.710 So clearly, we can't get data from get watched movies 00:05:59.710 --> 00:06:02.280 and pass it directly to print movie list. 00:06:02.280 --> 00:06:04.220 So instead, we're gonna create a new function 00:06:04.220 --> 00:06:05.200 similar to this one, 00:06:05.200 --> 00:06:07.960 just for printing the watched movie data. 00:06:07.960 --> 00:06:11.850 We're gonna do print watched movie list, 00:06:11.850 --> 00:06:14.540 and that is gonna take the username and the movies 00:06:14.540 --> 00:06:16.000 of the person, 00:06:16.000 --> 00:06:18.340 and then we're gonna print out something like, for example, 00:06:18.340 --> 00:06:20.050 usernames 00:06:20.050 --> 00:06:21.653 watched movies, 00:06:22.870 --> 00:06:24.630 like that, and then at the end, 00:06:24.630 --> 00:06:27.390 we're gonna print, again, the four dashes 00:06:27.390 --> 00:06:31.640 with the backslash, and then for movie in movies, 00:06:31.640 --> 00:06:34.360 we're going to print the movie name that they have watched, 00:06:34.360 --> 00:06:35.823 so movie one. 00:06:37.610 --> 00:06:39.030 Just like that. 00:06:39.030 --> 00:06:41.520 Now, what this is gonna do is it's going to 00:06:41.520 --> 00:06:42.780 print out the person's name, 00:06:42.780 --> 00:06:44.320 and then going through the movies, 00:06:44.320 --> 00:06:48.070 it's gonna get the movie title for each movie. 00:06:48.070 --> 00:06:50.860 Notice that movie zero for each movie watched 00:06:50.860 --> 00:06:54.430 is the watcher name, so we don't really want to print that 00:06:54.430 --> 00:06:56.853 because that should be the same as the username. 00:06:57.950 --> 00:07:00.300 Now, down here, instead of print movie list, 00:07:00.300 --> 00:07:03.860 we wanna do, of course, print watched movie list, 00:07:03.860 --> 00:07:05.560 passing in the username that they gave us 00:07:05.560 --> 00:07:08.410 as well as the movies that they have watched. 00:07:08.410 --> 00:07:09.510 I hope all of this makes sense. 00:07:09.510 --> 00:07:12.480 Remember, you've got this all written down over in the ebook 00:07:12.480 --> 00:07:14.911 if you prefer reading it or if you want another way 00:07:14.911 --> 00:07:18.330 of explaining things, you can read that and also see 00:07:18.330 --> 00:07:20.630 all the code changes that we've made 00:07:20.630 --> 00:07:23.410 written down there, as well. 00:07:23.410 --> 00:07:25.140 Although this has been a bit of a long video, 00:07:25.140 --> 00:07:29.683 the actual code changes that we've made are not that many. 00:07:30.600 --> 00:07:31.433 Just think about it. 00:07:31.433 --> 00:07:34.810 We've changed a couple of things here when we prompt users 00:07:34.810 --> 00:07:37.500 and print the watched movie list, and then in database.py, 00:07:37.500 --> 00:07:38.850 we've added a couple of queries 00:07:38.850 --> 00:07:40.230 or changed a couple of queries, 00:07:40.230 --> 00:07:43.690 but most of the other stuff has remained the same. 00:07:43.690 --> 00:07:46.280 That's because we are separating our replication 00:07:46.280 --> 00:07:49.520 into different parts, and changing one part does not mean 00:07:49.520 --> 00:07:51.010 that we have to change everything else. 00:07:51.110 --> 00:07:52.020 Thank you for watching, guys. 00:07:52.020 --> 00:07:55.050 I hope you've enjoyed this video. I'll see you in the next one.