WEBVTT 1 00:00:00.000 --> 00:00:02.210 Hi guys, and welcome back. 2 00:00:02.210 --> 00:00:04.770 In this video we're going to be looking at the first 3 00:00:04.770 --> 00:00:06.490 database queries that we'll need 4 00:00:06.490 --> 00:00:09.610 for stage one of our project. 5 00:00:09.610 --> 00:00:11.530 The stage one of a project should replicate 6 00:00:11.530 --> 00:00:12.363 what you would do 7 00:00:12.363 --> 00:00:14.680 when you're starting a much larger project. 8 00:00:14.680 --> 00:00:17.850 And that is picking one feature that will be useful 9 00:00:17.850 --> 00:00:20.330 to users and going and implementing it 10 00:00:20.330 --> 00:00:22.260 in the simplest way possible. 11 00:00:22.260 --> 00:00:25.340 A lot of people, many developers, start designing 12 00:00:25.340 --> 00:00:26.776 and planning a lot of things, 13 00:00:26.776 --> 00:00:28.070 and that can lead to 14 00:00:28.070 --> 00:00:31.770 over complicating and over developing your programmes. 15 00:00:31.770 --> 00:00:33.140 It's good to keep things simple 16 00:00:33.140 --> 00:00:34.660 that's really one of the most important things 17 00:00:34.660 --> 00:00:35.493 in programming, 18 00:00:35.493 --> 00:00:38.340 so that's what we're gonna start with stage one. 19 00:00:38.340 --> 00:00:39.753 All right, let's get to it. 20 00:00:41.170 --> 00:00:44.760 I've gone ahead and created the database.py file, 21 00:00:44.760 --> 00:00:47.540 so I'm gonna go and open that. 22 00:00:47.540 --> 00:00:51.010 And now we're going to close the file explorer there, 23 00:00:51.010 --> 00:00:52.340 so we can focus on the code. 24 00:00:52.340 --> 00:00:54.340 And by the way, for this stage, the feature is 25 00:00:54.340 --> 00:00:57.200 to store and retrieve watched movies. 26 00:00:57.200 --> 00:00:58.230 And you may think that we should start 27 00:00:58.230 --> 00:00:59.170 with a different feature, 28 00:00:59.170 --> 00:01:01.670 but that's fine, everybody does things differently. 29 00:01:01.670 --> 00:01:03.710 The important thing is that we try to simplify things 30 00:01:03.710 --> 00:01:05.480 before we work on them. 31 00:01:05.480 --> 00:01:06.460 So like we've discussed, 32 00:01:06.460 --> 00:01:08.224 we're going to have single table 33 00:01:08.224 --> 00:01:11.660 with title, release date, 34 00:01:11.660 --> 00:01:13.763 and a watched property. 35 00:01:14.770 --> 00:01:17.240 If we add movies to a table with this structure, 36 00:01:17.240 --> 00:01:19.134 we'll know the title and release date, 37 00:01:19.134 --> 00:01:22.070 and then we're going to have a one watched 38 00:01:22.070 --> 00:01:24.300 and zero for not watched. 39 00:01:24.300 --> 00:01:26.550 In release date, we're going to have a timestamp, 40 00:01:26.550 --> 00:01:29.773 or the number of seconds since first of January 1970. 41 00:01:31.280 --> 00:01:33.490 I'm gonna leave a resource in the resources section 42 00:01:33.490 --> 00:01:36.110 of this lecture with information as to why 43 00:01:36.110 --> 00:01:39.300 the first of January 1970, and not any other date. 44 00:01:39.300 --> 00:01:40.470 So for a table like this one, 45 00:01:40.470 --> 00:01:43.480 we're going to need queries to create the table, 46 00:01:43.480 --> 00:01:45.950 insert a movie, select all movies, 47 00:01:45.950 --> 00:01:49.310 select upcoming movies, select watched movies, 48 00:01:49.310 --> 00:01:52.170 and also to set a movie to watched. 49 00:01:52.170 --> 00:01:54.540 We're going to code some of those right now. 50 00:01:54.540 --> 00:01:57.860 For the create movies table query, 51 00:01:57.860 --> 00:01:59.990 we're going to have this multi-line query, 52 00:01:59.990 --> 00:02:01.490 because it's a bit longer. 53 00:02:01.490 --> 00:02:03.890 And here we're going to put our create table, 54 00:02:03.890 --> 00:02:06.350 so we'll do create table, 55 00:02:06.350 --> 00:02:09.150 we're gonna add here an if not exists, 56 00:02:09.150 --> 00:02:11.280 just in case we wanna run this multiple times, 57 00:02:11.280 --> 00:02:13.790 and we're gonna create the table movies. 58 00:02:13.790 --> 00:02:16.230 Now I'm opening the brackets here for the columns, 59 00:02:16.230 --> 00:02:18.040 and then I'm going into a new line just to give me 60 00:02:18.040 --> 00:02:19.160 a bit more room. 61 00:02:19.160 --> 00:02:20.570 And usually when you do that, 62 00:02:20.570 --> 00:02:23.020 you indent with four spaces 63 00:02:23.020 --> 00:02:25.260 when you're going to this new line. 64 00:02:25.260 --> 00:02:26.970 And so here we're going to put the columns 65 00:02:26.970 --> 00:02:27.803 that we wanna create, 66 00:02:27.803 --> 00:02:29.726 which is title, which is a text column. 67 00:02:29.726 --> 00:02:32.170 The release timestamp, 68 00:02:32.170 --> 00:02:33.003 which is gonna be 69 00:02:33.003 --> 00:02:35.590 a real column for a number. 70 00:02:35.590 --> 00:02:38.023 And watched, which is going to be an integer. 71 00:02:39.030 --> 00:02:41.630 Then we put the semicolon at the end and we're gonna 72 00:02:41.630 --> 00:02:42.920 close the string right there. 73 00:02:42.920 --> 00:02:45.550 So this is the create table query. 74 00:02:45.550 --> 00:02:47.280 Create table if not exists, 75 00:02:47.280 --> 00:02:48.760 Movies is the table name, 76 00:02:48.760 --> 00:02:50.520 and it's got three columns at the moment. 77 00:02:50.520 --> 00:02:52.933 Next we're going to insert movies. 78 00:02:53.790 --> 00:02:56.280 So we're going to do insert into movies, 79 00:02:56.280 --> 00:02:58.690 and then the columns that we're going to insert into, 80 00:02:58.690 --> 00:03:02.960 so that's title, release timestamp, and watched. 81 00:03:02.960 --> 00:03:06.530 The values of question mark, question mark, zero. 82 00:03:06.530 --> 00:03:09.500 So here we're going to put two arguments 83 00:03:09.500 --> 00:03:11.550 that are gonna be passed into the query. 84 00:03:11.550 --> 00:03:14.260 Zero is just going to be the initial value 85 00:03:14.260 --> 00:03:15.800 of the watched column. 86 00:03:15.800 --> 00:03:18.250 We're going to assume that users are inserting movies 87 00:03:18.250 --> 00:03:19.620 they haven't watched yet. 88 00:03:19.620 --> 00:03:21.630 This here, by the way, is optional, 89 00:03:21.630 --> 00:03:24.310 because we're passing in all of the columns, 90 00:03:24.310 --> 00:03:26.700 so we could just remove it and keep the values, 91 00:03:26.700 --> 00:03:27.750 but I do like putting it there. 92 00:03:27.750 --> 00:03:30.463 I think it helps with readability a little bit. 93 00:03:32.210 --> 00:03:34.240 Then we're gonna select all movies. 94 00:03:34.240 --> 00:03:37.320 Here we're gonna do select star from movies, 95 00:03:37.320 --> 00:03:39.180 and we know how to do that. 96 00:03:39.180 --> 00:03:41.240 To select the upcoming movies, 97 00:03:41.240 --> 00:03:43.660 we are going to use a where clause 98 00:03:43.660 --> 00:03:46.200 to filter my release timestamp. 99 00:03:46.200 --> 00:03:50.010 That's one of the benefits of the real timestamp there. 100 00:03:50.010 --> 00:03:52.030 We can do something like select upcoming movies 101 00:03:52.030 --> 00:03:56.800 is gonna be equal to select star from movies, 102 00:03:56.800 --> 00:04:01.450 where the release timestamp is greater than question mark. 103 00:04:01.450 --> 00:04:03.410 Because this is going to be a number, 104 00:04:03.410 --> 00:04:06.110 the number of seconds since the first of January 1970, 105 00:04:07.150 --> 00:04:08.500 we can pass in another number, 106 00:04:08.500 --> 00:04:10.830 and then this is just number to number comparison. 107 00:04:10.830 --> 00:04:11.870 It's really straightforward. 108 00:04:11.870 --> 00:04:13.095 So when we do the where clause there, 109 00:04:13.095 --> 00:04:14.720 that's going to be really easy to do. 110 00:04:14.720 --> 00:04:17.010 And the argument we're going to pass in here 111 00:04:17.010 --> 00:04:19.260 is gonna be the number of seconds since 112 00:04:19.260 --> 00:04:22.410 the first of January 1970 right now. 113 00:04:22.410 --> 00:04:24.970 And that's going to mean that the release timestamp must be 114 00:04:24.970 --> 00:04:27.530 greater than the time right now, 115 00:04:27.530 --> 00:04:30.290 that's gonna give us upcoming movies. 116 00:04:30.290 --> 00:04:33.520 Finally, for the select watched movies, 117 00:04:33.520 --> 00:04:35.370 we're gonna have something identical. 118 00:04:35.370 --> 00:04:39.910 Select star from movies where watched equal one. 119 00:04:39.910 --> 00:04:42.080 Notice that we're not gonna have any arguments here 120 00:04:42.080 --> 00:04:44.600 because watched can either be zero or one, 121 00:04:44.600 --> 00:04:46.010 we're defining that here. 122 00:04:46.010 --> 00:04:47.560 The watched property must be one, 123 00:04:47.560 --> 00:04:50.240 that tells us the movie has been watched. 124 00:04:50.240 --> 00:04:52.500 All right, so up to here there's nothing new. 125 00:04:52.500 --> 00:04:54.700 We've learned how to do all of this before, 126 00:04:54.700 --> 00:04:56.150 we've written our queries, 127 00:04:56.150 --> 00:04:58.440 now the next step is to write the functions 128 00:04:58.440 --> 00:05:01.750 that app.py will use to interact with the database 129 00:05:01.750 --> 00:05:03.240 by using these queries. 130 00:05:03.240 --> 00:05:05.850 Let's go and write those functions in the next video. 131 00:05:05.850 --> 00:05:06.800 I'll see you there.