1 00:00:00,000 --> 00:00:04,419 [MUSIC PLAYING] 2 00:00:04,419 --> 00:00:17,720 3 00:00:17,720 --> 00:00:19,470 BRIAN YU: All right, welcome back everyone 4 00:00:19,470 --> 00:00:21,750 to Web Programming with Python and JavaScript. 5 00:00:21,750 --> 00:00:24,630 So last time we took a look at a brand new web framework 6 00:00:24,630 --> 00:00:26,640 written in Python called Django. 7 00:00:26,640 --> 00:00:30,270 And what Django allowed us to do was to build dynamic web applications. 8 00:00:30,270 --> 00:00:32,580 Applications where we now had the ability 9 00:00:32,580 --> 00:00:36,540 to not just display the same HTML and CSS to the user every single time, 10 00:00:36,540 --> 00:00:38,910 but to dynamically generate HTML in order 11 00:00:38,910 --> 00:00:42,270 to allow users to interact with a page that was a little bit more dynamic. 12 00:00:42,270 --> 00:00:45,720 Doing things like rendering things on the page only if a certain condition 13 00:00:45,720 --> 00:00:46,500 was true. 14 00:00:46,500 --> 00:00:50,670 Or looping over a list of values and displaying one HTML element 15 00:00:50,670 --> 00:00:52,500 for each of those possible values. 16 00:00:52,500 --> 00:00:54,428 But where Django gets especially powerful 17 00:00:54,428 --> 00:00:56,970 and we're web application certainly get much more interesting 18 00:00:56,970 --> 00:00:59,640 is as we start to delve into the world of data 19 00:00:59,640 --> 00:01:03,472 and trying to have web applications that store data inside of a database. 20 00:01:03,472 --> 00:01:06,180 And to do that we're going to introduce a couple of topics today. 21 00:01:06,180 --> 00:01:09,420 In particular, we're going to introduce the idea of SQL, models, 22 00:01:09,420 --> 00:01:10,590 and migrations. 23 00:01:10,590 --> 00:01:14,640 SQL is a database language that we can use to interact with databases 24 00:01:14,640 --> 00:01:18,990 and Django is going to allow us to have an abstraction layer on top of SQL. 25 00:01:18,990 --> 00:01:21,630 To interact not by writing direct SQL queries, 26 00:01:21,630 --> 00:01:24,480 but by interacting with Python classes and objects 27 00:01:24,480 --> 00:01:26,482 that we're going to refer to as models. 28 00:01:26,482 --> 00:01:28,440 And migrations are going to be a technique that 29 00:01:28,440 --> 00:01:32,250 are going to allow us to update our database in response to changes 30 00:01:32,250 --> 00:01:34,990 that we make to our underlying models. 31 00:01:34,990 --> 00:01:37,560 So before we get into things that are more Django specific, 32 00:01:37,560 --> 00:01:40,020 let's begin with a discussion of SQL more generally. 33 00:01:40,020 --> 00:01:43,050 And more broadly, just a discussion of data and the types of data 34 00:01:43,050 --> 00:01:44,610 that we're going to want to store. 35 00:01:44,610 --> 00:01:47,910 And now there are a number of ways that we could try to store data inside 36 00:01:47,910 --> 00:01:50,730 of a computer system but oftentimes in databases, 37 00:01:50,730 --> 00:01:54,330 in particular, in a type of database known as a relational database, 38 00:01:54,330 --> 00:01:57,690 we're going to store data inside of a table where each table is 39 00:01:57,690 --> 00:02:00,040 going to have rows and columns. 40 00:02:00,040 --> 00:02:02,040 So for the sake of today, we're going to imagine 41 00:02:02,040 --> 00:02:04,770 starting to construct a database and a web application 42 00:02:04,770 --> 00:02:05,912 that an airline might use. 43 00:02:05,912 --> 00:02:08,370 For example, for keeping track of various different flights 44 00:02:08,370 --> 00:02:12,510 on that airline and keeping track of which passengers are on those flights. 45 00:02:12,510 --> 00:02:15,660 And so what we have here is a sample table, for example, 46 00:02:15,660 --> 00:02:19,260 of how you might represent a whole bunch of flight related data. 47 00:02:19,260 --> 00:02:22,410 Here I have three columns for three pieces of information 48 00:02:22,410 --> 00:02:25,140 that I might want to keep track of for any particular flight. 49 00:02:25,140 --> 00:02:27,432 For any given flight I might want to know that flight's 50 00:02:27,432 --> 00:02:29,010 origin, what city it's weaving from. 51 00:02:29,010 --> 00:02:31,380 Its destination, what city it's arriving at. 52 00:02:31,380 --> 00:02:34,380 And its duration, some value in minutes of how long 53 00:02:34,380 --> 00:02:38,170 it's going to take the flight to get from the origin to the destination. 54 00:02:38,170 --> 00:02:40,350 So each of those columns represents one field 55 00:02:40,350 --> 00:02:42,690 that I might want to keep track of about my data. 56 00:02:42,690 --> 00:02:46,500 And each row, meanwhile, is going to represent an individual flight. 57 00:02:46,500 --> 00:02:50,280 So here, for example, is one row representing one flight from New York 58 00:02:50,280 --> 00:02:55,290 to London and that flight just so happens to take 415 minutes. 59 00:02:55,290 --> 00:02:57,330 And so what SQL is going to allow us to do 60 00:02:57,330 --> 00:02:59,820 is it is going to be a database of language that 61 00:02:59,820 --> 00:03:03,660 is designed to interact with relational database management systems. 62 00:03:03,660 --> 00:03:08,610 Databases that organize data into tables where every table has rows and columns. 63 00:03:08,610 --> 00:03:11,820 And it turns out, there are a number of different database management systems 64 00:03:11,820 --> 00:03:15,180 that implement parts of the SQL standard of this type of language. 65 00:03:15,180 --> 00:03:19,020 Some of the more popular are MySQL, PostgreSQL, and SQLite 66 00:03:19,020 --> 00:03:20,687 but there are definitely others as well. 67 00:03:20,687 --> 00:03:23,687 And each of these has various different features and different use cases 68 00:03:23,687 --> 00:03:25,340 where some might be more appropriate. 69 00:03:25,340 --> 00:03:29,220 MySQL and PostgreSQL, otherwise known as just Postgres, 70 00:03:29,220 --> 00:03:32,850 are more heavier database management systems, so to speak. 71 00:03:32,850 --> 00:03:36,960 They're generally running on servers elsewhere where a lot of big companies 72 00:03:36,960 --> 00:03:39,573 will use servers like this in order to store data 73 00:03:39,573 --> 00:03:42,240 and represent their data in a sort of a separate database where. 74 00:03:42,240 --> 00:03:45,120 The idea of that is that you might want your web application running 75 00:03:45,120 --> 00:03:47,053 on one server but you might want your database 76 00:03:47,053 --> 00:03:48,720 running as an entirely separate process. 77 00:03:48,720 --> 00:03:51,690 Such that they can behave independently, you can debug and test them 78 00:03:51,690 --> 00:03:56,010 independently, and if one goes down, the other doesn't go down as well. 79 00:03:56,010 --> 00:03:58,440 SQLite is a bit of a simpler lighter weight 80 00:03:58,440 --> 00:04:00,420 implementation of the SQL standard. 81 00:04:00,420 --> 00:04:03,830 And what SQLite is going to do is rather than be an entire server that 82 00:04:03,830 --> 00:04:06,600 is going to be listening for requests and making responses, 83 00:04:06,600 --> 00:04:10,150 SQLite is just going to store all of its data as a single file. 84 00:04:10,150 --> 00:04:12,630 So we're going to store SQL data inside of a SQLite file 85 00:04:12,630 --> 00:04:14,880 and that's going to make it a little bit easier for us 86 00:04:14,880 --> 00:04:18,180 as we're just getting started to begin to write queries and add things 87 00:04:18,180 --> 00:04:20,610 to our database and retrieve things from our database. 88 00:04:20,610 --> 00:04:24,150 But know that many of the same types of SQL syntax that we'll see with SQLite, 89 00:04:24,150 --> 00:04:26,730 which is Django's default, can also be applied 90 00:04:26,730 --> 00:04:31,050 to other languages as well and other database management systems too. 91 00:04:31,050 --> 00:04:34,170 So as we begin to store data inside of a SQL database, 92 00:04:34,170 --> 00:04:37,167 ultimately each of those pieces of data has a type. 93 00:04:37,167 --> 00:04:39,000 In the same way that in Python we have types 94 00:04:39,000 --> 00:04:42,330 for various different types of data, we have integers and we have strings 95 00:04:42,330 --> 00:04:44,370 and we have lists and tuples and so forth. 96 00:04:44,370 --> 00:04:49,170 SQL too has types that represent various different categories of information 97 00:04:49,170 --> 00:04:51,370 that you might want to store inside of a database. 98 00:04:51,370 --> 00:04:54,660 And each database management system has its own different set of types. 99 00:04:54,660 --> 00:04:59,580 SQLite for example, has a fairly short list of basic types that it supports. 100 00:04:59,580 --> 00:05:03,240 It supports text for just like strings of text, for example. 101 00:05:03,240 --> 00:05:06,360 Something like a city name, for instance, might be stored as text. 102 00:05:06,360 --> 00:05:09,660 We have support for integers, which are just 0, 1, 2, 3 4 103 00:05:09,660 --> 00:05:14,110 plus the negative numbers, negative 1, negative 2, negative 3, and so forth. 104 00:05:14,110 --> 00:05:16,590 We have support for real numbers where real numbers don't 105 00:05:16,590 --> 00:05:19,880 have to just be integers, they can have something after the decimal point. 106 00:05:19,880 --> 00:05:23,390 Something like 2.8 or something else that might have a decimal in it. 107 00:05:23,390 --> 00:05:26,960 And then numeric is another category for data that's just more generally 108 00:05:26,960 --> 00:05:29,370 some sort of number that might number like data, 109 00:05:29,370 --> 00:05:33,350 so something like a Boolean value or something like a date, for example, 110 00:05:33,350 --> 00:05:35,030 might be stored using this numeric type. 111 00:05:35,030 --> 00:05:37,113 Where it's not really an integer or a real number, 112 00:05:37,113 --> 00:05:41,100 though you could represented at such, it's other numeric type of data. 113 00:05:41,100 --> 00:05:44,510 And then finally blob, which stands for binary large object, 114 00:05:44,510 --> 00:05:46,370 is any type of other binary data. 115 00:05:46,370 --> 00:05:49,980 Just zeros and ones that you might want to store inside of your database. 116 00:05:49,980 --> 00:05:52,250 So for example, if you're storing files, maybe they're 117 00:05:52,250 --> 00:05:54,680 audio files or images or other types of files 118 00:05:54,680 --> 00:05:57,300 that you want to keep track of inside of your database, 119 00:05:57,300 --> 00:06:02,010 you can store just pure binary data inside of a SQL database as well. 120 00:06:02,010 --> 00:06:05,030 So these now are the basic types of SQLite supports 121 00:06:05,030 --> 00:06:06,860 but other database management systems have 122 00:06:06,860 --> 00:06:08,450 other types that they support as well. 123 00:06:08,450 --> 00:06:11,690 MySQL, you all for example, has a much longer list of other types 124 00:06:11,690 --> 00:06:12,770 that it supports. 125 00:06:12,770 --> 00:06:16,340 In addition to just supporting a text type for arbitrary length text, 126 00:06:16,340 --> 00:06:20,750 MySQL also has a type called char that takes an argument of size. 127 00:06:20,750 --> 00:06:23,870 For saying I want like size characters worth of data 128 00:06:23,870 --> 00:06:25,790 that we're going to store inside the database. 129 00:06:25,790 --> 00:06:28,850 And you might imagine that this can be advantageous in situations 130 00:06:28,850 --> 00:06:30,770 where you know that your data is going to be 131 00:06:30,770 --> 00:06:32,450 within a certain number of characters. 132 00:06:32,450 --> 00:06:35,690 So if you're storing a zip code for example, in the United States. 133 00:06:35,690 --> 00:06:38,660 A zip code in the United States might just be five characters 134 00:06:38,660 --> 00:06:40,430 and so you can allocate a character length 135 00:06:40,430 --> 00:06:44,240 of five characters worth of space for storing every single zip code 136 00:06:44,240 --> 00:06:45,625 inside of your database table. 137 00:06:45,625 --> 00:06:48,380 So for efficiency's sake, if you know the maximum length, 138 00:06:48,380 --> 00:06:50,990 and you can fix that length inside your database, 139 00:06:50,990 --> 00:06:52,580 that can be an efficient choice. 140 00:06:52,580 --> 00:06:54,650 VARCHAR of a size is similar. 141 00:06:54,650 --> 00:06:56,720 And this is a variable length character. 142 00:06:56,720 --> 00:06:59,720 If something isn't going to be exactly a certain number of characters, 143 00:06:59,720 --> 00:07:02,060 but maybe up to a certain number of characters, 144 00:07:02,060 --> 00:07:04,160 you can use VARCHAR to say maybe sometimes it's 145 00:07:04,160 --> 00:07:05,630 going to be fewer characters. 146 00:07:05,630 --> 00:07:08,840 But it could be up to size, number of characters. 147 00:07:08,840 --> 00:07:11,015 And there are tradeoffs there, too. 148 00:07:11,015 --> 00:07:13,140 Then there are various different types of integers. 149 00:07:13,140 --> 00:07:15,060 There's not just a single integer type. 150 00:07:15,060 --> 00:07:18,710 But SMALLINT and INT and BIGINT, each of which uses a different number of 151 00:07:18,710 --> 00:07:21,000 bytes in order to store an integer data. 152 00:07:21,000 --> 00:07:24,170 So if you want to be able to store much larger or much smaller numbers, 153 00:07:24,170 --> 00:07:26,448 you might need a bigger type, like an INT or a BIGINT. 154 00:07:26,448 --> 00:07:28,490 But if your numbers are going to be pretty small, 155 00:07:28,490 --> 00:07:30,490 maybe you're OK with just a SMALLINT. 156 00:07:30,490 --> 00:07:33,740 And likewise there are similar types of trade-offs for floating point numbers. 157 00:07:33,740 --> 00:07:36,532 Where, much as in programming languages like C, if you're familiar, 158 00:07:36,532 --> 00:07:38,420 have both a float and a double type. 159 00:07:38,420 --> 00:07:42,080 Where a double uses more bytes of memory in order to store information. 160 00:07:42,080 --> 00:07:44,780 Here, too, we have FLOAT and DOUBLE, which allow 161 00:07:44,780 --> 00:07:46,410 us to store floating point numbers. 162 00:07:46,410 --> 00:07:49,400 Numbers that might be real numbered values, where a double just 163 00:07:49,400 --> 00:07:52,460 allows us to express a number to a little bit more precision 164 00:07:52,460 --> 00:07:54,530 than a floating point number might be able to. 165 00:07:54,530 --> 00:07:57,030 And there are many other types as well, but the general idea 166 00:07:57,030 --> 00:07:59,330 is that here even in SQL, much as in a language 167 00:07:59,330 --> 00:08:03,990 like Python, we have types for each of these various different kinds of data. 168 00:08:03,990 --> 00:08:07,640 So now that we understand that each type of data has types, what we'd like to do 169 00:08:07,640 --> 00:08:11,417 is to be able to, inside of a database, actually create a table. 170 00:08:11,417 --> 00:08:13,250 And there are various different SQL commands 171 00:08:13,250 --> 00:08:15,830 that we can run on our database in order to perform 172 00:08:15,830 --> 00:08:17,420 various different operations. 173 00:08:17,420 --> 00:08:19,250 And the first we might want to execute is 174 00:08:19,250 --> 00:08:22,530 a command that is just going to create a table, for example. 175 00:08:22,530 --> 00:08:24,150 So how might we go about doing that? 176 00:08:24,150 --> 00:08:25,900 Well, it turns out the syntax for creating 177 00:08:25,900 --> 00:08:28,460 a table is going to look a little something like this. 178 00:08:28,460 --> 00:08:32,630 This here is the syntax we would use to create a table in SQLite 179 00:08:32,630 --> 00:08:35,419 for representing flights, for example. 180 00:08:35,419 --> 00:08:38,299 So we begin with the keyword Create Table, 181 00:08:38,299 --> 00:08:41,929 to say I would like to create a new table inside of this database. 182 00:08:41,929 --> 00:08:43,850 Next I give the name of the table. 183 00:08:43,850 --> 00:08:46,280 Every table has a name just for ease of reference. 184 00:08:46,280 --> 00:08:49,880 And here the name and the table is just the word "flights." 185 00:08:49,880 --> 00:08:53,750 Then what follows in parentheses are a comma-separated list 186 00:08:53,750 --> 00:08:56,840 of all of the columns that should be present in the table. 187 00:08:56,840 --> 00:08:59,060 I'm not yet adding any data to the table. 188 00:08:59,060 --> 00:09:02,850 The Create Table query is just going to decide the structure of the table. 189 00:09:02,850 --> 00:09:05,950 What are the columns and what are the columns' types. 190 00:09:05,950 --> 00:09:08,270 And so here we see that in the beginning of each 191 00:09:08,270 --> 00:09:13,890 of these clauses that are separated by commas, we have the name of the column. 192 00:09:13,890 --> 00:09:18,020 So we have an id column because, though we didn't see this before, often in SQL 193 00:09:18,020 --> 00:09:19,910 it's going to be helpful to be able to have 194 00:09:19,910 --> 00:09:23,812 some way of uniquely referencing a particular element inside of the table. 195 00:09:23,812 --> 00:09:25,520 Maybe there are multiple flights that are 196 00:09:25,520 --> 00:09:28,970 going from New York to London, for example, 197 00:09:28,970 --> 00:09:31,280 and I want some way of distinguishing between them. 198 00:09:31,280 --> 00:09:35,210 And one easy way is to give every single entry inside of a table 199 00:09:35,210 --> 00:09:40,037 a unique identifier, or a unique id, represented here by this id column. 200 00:09:40,037 --> 00:09:42,620 And that's going to be a way of making sure that we can always 201 00:09:42,620 --> 00:09:45,560 access a particular flight uniquely. 202 00:09:45,560 --> 00:09:48,830 So we have an id column, an origin column, a destination column, 203 00:09:48,830 --> 00:09:50,480 and a duration column. 204 00:09:50,480 --> 00:09:53,270 Each of those columns has a type. 205 00:09:53,270 --> 00:09:55,280 So the types here, id is an integer. 206 00:09:55,280 --> 00:09:58,280 It's just going to be some number representing the flight, counting one, 207 00:09:58,280 --> 00:10:00,500 two, three, four, and so on and so forth. 208 00:10:00,500 --> 00:10:03,370 Origin and destination we'll label here as text. 209 00:10:03,370 --> 00:10:06,020 Though you'll see that if we were using my SQL, 210 00:10:06,020 --> 00:10:08,325 I might make these a variable length character that 211 00:10:08,325 --> 00:10:10,200 could be up to a certain number of characters 212 00:10:10,200 --> 00:10:11,950 if I know there's probably not going to be 213 00:10:11,950 --> 00:10:15,350 a city that's longer than a certain number of characters, for instance. 214 00:10:15,350 --> 00:10:18,070 Then I have a duration, which here is an integer. 215 00:10:18,070 --> 00:10:19,900 Some number of minutes that is going to be 216 00:10:19,900 --> 00:10:24,720 how long it takes for this flight to go from the origin to the destination. 217 00:10:24,720 --> 00:10:26,470 After the types for each of the columns, I 218 00:10:26,470 --> 00:10:29,950 have additional constraints that I might add to the columns as well. 219 00:10:29,950 --> 00:10:31,930 So integer Primary Key. 220 00:10:31,930 --> 00:10:34,300 The Primary Key here means that the id is 221 00:10:34,300 --> 00:10:38,320 going to be the primary way via which I uniquely identify a flight. 222 00:10:38,320 --> 00:10:40,120 There's going to be some optimizations here 223 00:10:40,120 --> 00:10:44,380 to make it very quick to be able to look up a flight by its id. 224 00:10:44,380 --> 00:10:48,290 Null is a constraint that I can place on columns to be able to say, 225 00:10:48,290 --> 00:10:51,680 I don't want to allow for this particular column to ever be empty. 226 00:10:51,680 --> 00:10:54,430 I don't want there to ever be a flight that doesn't have an origin 227 00:10:54,430 --> 00:10:56,810 or doesn't have a destination or a duration. 228 00:10:56,810 --> 00:11:00,460 So I can add constraints to a particular column to say, 229 00:11:00,460 --> 00:11:02,950 the origin, destination, duration, columns 230 00:11:02,950 --> 00:11:04,690 should not be allowed to be null. 231 00:11:04,690 --> 00:11:09,220 I want to always have an origin and a destination and a duration. 232 00:11:09,220 --> 00:11:12,100 Finally this Autoincrement after the Primary Key 233 00:11:12,100 --> 00:11:15,970 is a cue into SQL to automatically update the id every time 234 00:11:15,970 --> 00:11:17,582 I add a new row into the table. 235 00:11:17,582 --> 00:11:19,540 So if I add a new flight, I can give the flight 236 00:11:19,540 --> 00:11:21,550 an origin, destination, and duration. 237 00:11:21,550 --> 00:11:23,888 But I don't have to worry about giving it an id. 238 00:11:23,888 --> 00:11:25,930 SQL is going to handle that for me automatically. 239 00:11:25,930 --> 00:11:32,680 Automatically giving that new row in that table the next available id 240 00:11:32,680 --> 00:11:33,730 number. 241 00:11:33,730 --> 00:11:37,030 Here, then, is the way we create a table by giving the name of the table, 242 00:11:37,030 --> 00:11:39,940 each of the columns, the types for each of those columns, 243 00:11:39,940 --> 00:11:44,390 and then any constraints we would want to place on the columns. 244 00:11:44,390 --> 00:11:46,690 There are a number of different types of constraints 245 00:11:46,690 --> 00:11:48,370 that I can add to a particular column. 246 00:11:48,370 --> 00:11:50,140 Not null is one we've seen before. 247 00:11:50,140 --> 00:11:54,790 You can add a default, which just adds a default value to a particular column. 248 00:11:54,790 --> 00:11:56,050 Primary Key we've seen. 249 00:11:56,050 --> 00:11:59,230 Unique guarantees that every value is going to be unique. 250 00:11:59,230 --> 00:12:01,840 So if you don't want to allow for the same value 251 00:12:01,840 --> 00:12:04,257 to appear twice for a particular column, you 252 00:12:04,257 --> 00:12:06,340 can add a unique constraint to a particular column 253 00:12:06,340 --> 00:12:08,200 to enforce that as well. 254 00:12:08,200 --> 00:12:12,160 Check can be used to make sure that a value obeys a certain condition. 255 00:12:12,160 --> 00:12:14,898 Like, a number falls within a certain range, for example. 256 00:12:14,898 --> 00:12:18,190 If you have movie ratings, for example, and you're storing movie ratings inside 257 00:12:18,190 --> 00:12:19,990 of a database, you might care to make sure 258 00:12:19,990 --> 00:12:23,890 that those ratings are within the 1 to 5 range or the 1 to 10 range 259 00:12:23,890 --> 00:12:27,190 or whatever range you deem valid for the database. 260 00:12:27,190 --> 00:12:29,500 So via constraints you can begin to ensure 261 00:12:29,500 --> 00:12:33,460 that as you add data to the table, that data is going to be valid in some way. 262 00:12:33,460 --> 00:12:36,130 That it needs to obey and certain constraints, 263 00:12:36,130 --> 00:12:38,240 otherwise, if you try to add it to the table, 264 00:12:38,240 --> 00:12:40,900 it's not going to be accepted at all. 265 00:12:40,900 --> 00:12:43,300 And so that leads into the natural next question, which 266 00:12:43,300 --> 00:12:45,940 is, I now have all of these various different tables 267 00:12:45,940 --> 00:12:48,430 that I can create using these create table commands, 268 00:12:48,430 --> 00:12:51,670 but how do I actually add data into those tables? 269 00:12:51,670 --> 00:12:55,030 The way we're going to do that is via an INSERT command, where we're 270 00:12:55,030 --> 00:12:58,390 going to insert data into a SQL table. 271 00:12:58,390 --> 00:13:00,010 So what might that command look like? 272 00:13:00,010 --> 00:13:02,650 The command is generally going to look like this. 273 00:13:02,650 --> 00:13:05,140 We're going to say, INSERT Into, to say I 274 00:13:05,140 --> 00:13:08,170 would like to add a new row into a table. 275 00:13:08,170 --> 00:13:10,240 Following that, we provide the name of the table. 276 00:13:10,240 --> 00:13:12,073 Something like "flight" to say, I would like 277 00:13:12,073 --> 00:13:14,650 to add a new row to the flights table. 278 00:13:14,650 --> 00:13:16,660 And following the name of the table, we need 279 00:13:16,660 --> 00:13:19,900 to provide in parentheses a comma-separated list 280 00:13:19,900 --> 00:13:24,830 of all of the column names for which we are going to provide values. 281 00:13:24,830 --> 00:13:28,670 So here I said I want to provide values for the origin, destination, 282 00:13:28,670 --> 00:13:30,730 and duration of this flight. 283 00:13:30,730 --> 00:13:32,530 Note that I'm leaving out the ID column. 284 00:13:32,530 --> 00:13:35,118 I don't have to worry about adding a value for the ID, 285 00:13:35,118 --> 00:13:36,910 because I said the id should autoincrement. 286 00:13:36,910 --> 00:13:40,450 So it's going to automatically give the next available ID. 287 00:13:40,450 --> 00:13:42,340 And what values am I going to provide? 288 00:13:42,340 --> 00:13:44,900 Well, after the word "values", I provide, again, 289 00:13:44,900 --> 00:13:48,670 in parentheses, a comma-separated list of all of the values 290 00:13:48,670 --> 00:13:51,970 that are to be associated with those columns in the same order. 291 00:13:51,970 --> 00:13:53,870 So origin was the first one here. 292 00:13:53,870 --> 00:13:55,810 So origin will correspond to New York. 293 00:13:55,810 --> 00:13:57,850 Destination will correspond to London. 294 00:13:57,850 --> 00:14:00,910 Duration will correspond to 415. 295 00:14:00,910 --> 00:14:04,750 So this query let's me take the flights table that I've already created 296 00:14:04,750 --> 00:14:09,010 and add some data into that table. 297 00:14:09,010 --> 00:14:11,290 So you can use these INSERT queries once you 298 00:14:11,290 --> 00:14:14,470 have a table to being able to add new rows of data to that table. 299 00:14:14,470 --> 00:14:16,780 Every time a new flight comes up for an airline, 300 00:14:16,780 --> 00:14:19,570 the airline might, behind the scenes, be running an INSERT 301 00:14:19,570 --> 00:14:22,810 into Flights command that takes data about that flight 302 00:14:22,810 --> 00:14:24,340 and adds it to the table. 303 00:14:24,340 --> 00:14:26,920 But, of course, once we've added data to a table, 304 00:14:26,920 --> 00:14:30,780 we'd ideally like some way to get data out of that table, as well. 305 00:14:30,780 --> 00:14:33,220 That once we've stored data inside of our database 306 00:14:33,220 --> 00:14:35,350 we want to retrieve that data, too. 307 00:14:35,350 --> 00:14:38,290 To do that, we're going to use a particular type of query 308 00:14:38,290 --> 00:14:39,760 called a SELECT query. 309 00:14:39,760 --> 00:14:43,690 What SELECT is going to do is take a table that already exists 310 00:14:43,690 --> 00:14:46,682 and allow us to get data out of that table. 311 00:14:46,682 --> 00:14:48,640 It's not going to modify the data that's there, 312 00:14:48,640 --> 00:14:52,342 it's just going to retrieve data that might be inside a particular table. 313 00:14:52,342 --> 00:14:53,800 So what do those queries look like? 314 00:14:53,800 --> 00:14:56,508 Well, the simplest query might look a little something like this. 315 00:14:56,508 --> 00:14:59,140 SELECT * from flights. 316 00:14:59,140 --> 00:15:03,580 SELECT from flights means I want to select data from the flights table. 317 00:15:03,580 --> 00:15:07,000 And this * is a wild card that here just means 318 00:15:07,000 --> 00:15:09,560 select all of the possible columns that you can. 319 00:15:09,560 --> 00:15:10,060 320 00:15:10,060 --> 00:15:11,920 So if this is my table, my table of flights 321 00:15:11,920 --> 00:15:16,970 where each flight has an id, an origin, a destination, and a duration, 322 00:15:16,970 --> 00:15:20,090 then select * from flights will select all of that data. 323 00:15:20,090 --> 00:15:22,310 All of the rows and all of the columns are 324 00:15:22,310 --> 00:15:25,948 going to come back to me when I run a query like this. 325 00:15:25,948 --> 00:15:29,240 Now it's possible that when I'm running these queries, when I'm accessing data, 326 00:15:29,240 --> 00:15:31,638 that I don't actually care about all of these columns, 327 00:15:31,638 --> 00:15:34,430 especially if I have a table with many more columns than just this. 328 00:15:34,430 --> 00:15:37,160 Maybe I only care about particular columns. 329 00:15:37,160 --> 00:15:39,170 And it would be wasteful to query the database 330 00:15:39,170 --> 00:15:43,230 asking it to return to me a whole lot more data than I actually need. 331 00:15:43,230 --> 00:15:45,890 So instead I could run query like this. 332 00:15:45,890 --> 00:15:52,310 SELECT-- instead of * I'm saying select origin comma destination from flights. 333 00:15:52,310 --> 00:15:54,240 So I'm again selecting from the flights table. 334 00:15:54,240 --> 00:15:57,500 Selecting all of the rows still, but the difference 335 00:15:57,500 --> 00:16:01,190 is that instead of select *, which means select all of the columns that 336 00:16:01,190 --> 00:16:05,390 are present in the table, select origin comma destination means I'm only 337 00:16:05,390 --> 00:16:07,370 going to select these two columns-- 338 00:16:07,370 --> 00:16:09,890 the origin column and the destination column. 339 00:16:09,890 --> 00:16:12,170 So highlighted in blue here is the data that 340 00:16:12,170 --> 00:16:16,820 would be returned if someone were to run this particular SQL query. 341 00:16:16,820 --> 00:16:20,270 Now especially as tables begin to get larger and begin to have more and more 342 00:16:20,270 --> 00:16:23,390 rows-- here I just have six rows, but you might imagine a table with 343 00:16:23,390 --> 00:16:25,820 thousands or even millions of rows within it-- 344 00:16:25,820 --> 00:16:29,910 you probably don't want to be returning every single row every single time. 345 00:16:29,910 --> 00:16:32,700 So just as you can constrain which columns come back to you, 346 00:16:32,700 --> 00:16:35,240 likewise, you too can constrain which rows come back 347 00:16:35,240 --> 00:16:37,310 to you when you perform a query. 348 00:16:37,310 --> 00:16:41,620 So I could say something like select * from flights where id= "3." 349 00:16:41,620 --> 00:16:43,910 Again, this reads as very English like. 350 00:16:43,910 --> 00:16:47,690 I'm saying select all of the columns from the flights table, 351 00:16:47,690 --> 00:16:49,010 but not all of the rows. 352 00:16:49,010 --> 00:16:51,770 I only want to select the rows where-- 353 00:16:51,770 --> 00:16:53,150 this is a SQL keyword-- 354 00:16:53,150 --> 00:16:54,950 where id= "3." 355 00:16:54,950 --> 00:16:57,620 ID is the name of a column, 3 is the value, 356 00:16:57,620 --> 00:17:00,470 and it's going to look through this table, only find me the flights 357 00:17:00,470 --> 00:17:05,240 where the id is equal to 3, and I'll just get back that one row as a result. 358 00:17:05,240 --> 00:17:10,369 It finds the row with an ID of 3, and it gives that back to me. 359 00:17:10,369 --> 00:17:14,270 If, instead, I said select * from flights where origin= "New York," 360 00:17:14,270 --> 00:17:17,060 I can see that I don't just need to filter on the primary key-- 361 00:17:17,060 --> 00:17:19,670 the id-- I can also filter on any other column. 362 00:17:19,670 --> 00:17:22,920 I can say, give me all the flights where New York is the origin. 363 00:17:22,920 --> 00:17:24,974 So all the flights leaving from New York. 364 00:17:24,974 --> 00:17:26,849 You might imagine that in New York's airport, 365 00:17:26,849 --> 00:17:29,570 for example they likely want some sort of query that's 366 00:17:29,570 --> 00:17:32,000 going to find all the flights leaving from New York such 367 00:17:32,000 --> 00:17:35,480 that they can display on a screen of some sort all of the departures 368 00:17:35,480 --> 00:17:36,770 from that particular airport. 369 00:17:36,770 --> 00:17:39,050 So you could run a query like this and get back 370 00:17:39,050 --> 00:17:43,130 only the rows in this database, inside of this table, that happened to have 371 00:17:43,130 --> 00:17:46,320 an origin of New York, for example. 372 00:17:46,320 --> 00:17:49,100 So let's go ahead and take a look at how we might actually 373 00:17:49,100 --> 00:17:53,310 run some of these queries inside of a SQL database. 374 00:17:53,310 --> 00:17:57,060 So in order to create a SQLite database, which is really just a file, 375 00:17:57,060 --> 00:17:59,240 I can start just by creating the file. 376 00:17:59,240 --> 00:18:02,420 It turns out that in the terminal, there is a command called Touch that 377 00:18:02,420 --> 00:18:04,610 will create a brand new file for me. 378 00:18:04,610 --> 00:18:08,650 So I'm going to create a file called flights.sql. 379 00:18:08,650 --> 00:18:12,680 It's going to be a SQLite database file that by default has nothing in it. 380 00:18:12,680 --> 00:18:15,140 But then, assuming you have SQLite installed, 381 00:18:15,140 --> 00:18:20,150 I can run SQLite 3 followed by flights.sql, 382 00:18:20,150 --> 00:18:22,700 and now I'm inside of the SQLite prompt. 383 00:18:22,700 --> 00:18:28,230 I can now begin to write commands that will be executed on this SQL database. 384 00:18:28,230 --> 00:18:32,370 So one thing I might do is create a table called "flights." 385 00:18:32,370 --> 00:18:36,660 In this table, I want to have an id column, which is an integer. 386 00:18:36,660 --> 00:18:40,250 It should be the primary key, the main way via which I identify a flight. 387 00:18:40,250 --> 00:18:42,893 I would like to autoincrement it. 388 00:18:42,893 --> 00:18:45,560 I would also like for the flights table to have an origin, which 389 00:18:45,560 --> 00:18:48,190 will be a text field that is not null. 390 00:18:48,190 --> 00:18:52,490 I'll add a destination field, also a text field that is not null. 391 00:18:52,490 --> 00:18:54,650 And then finally I'll add a duration field, 392 00:18:54,650 --> 00:18:57,410 which is an integer that also shouldn't be null. 393 00:18:57,410 --> 00:19:00,830 I'll end with an end parentheses and a semicolon, press Return, 394 00:19:00,830 --> 00:19:03,440 and that executes that command on my database. 395 00:19:03,440 --> 00:19:06,260 I have now created a table called "flights." 396 00:19:06,260 --> 00:19:10,150 To verify this in SQLite, in the prompt I can type .tables, 397 00:19:10,150 --> 00:19:14,210 and that will show me all of the tables that currently exist in my database. 398 00:19:14,210 --> 00:19:17,335 It just so happens that I have a table called "flights." 399 00:19:17,335 --> 00:19:19,460 Of course, there's nothing inside this table, which 400 00:19:19,460 --> 00:19:22,480 I can verify by running SELECT * from flights, 401 00:19:22,480 --> 00:19:25,130 meaning get all the data from the flights table. 402 00:19:25,130 --> 00:19:27,140 If I press Return nothing happens. 403 00:19:27,140 --> 00:19:28,190 I don't see anything. 404 00:19:28,190 --> 00:19:30,740 There is no data that happens to be here. 405 00:19:30,740 --> 00:19:34,353 But now I could say something like INSERT into Flights. 406 00:19:34,353 --> 00:19:36,770 Then I'll provide in parentheses the names of the columns. 407 00:19:36,770 --> 00:19:39,388 And on the slide a moment ago I showed this on multiple lines. 408 00:19:39,388 --> 00:19:40,680 That was just for visuals sake. 409 00:19:40,680 --> 00:19:43,660 These commands can be entirely on one line if we would like it to. 410 00:19:43,660 --> 00:19:48,530 So I can say origin, destination, and duration and then provide values. 411 00:19:48,530 --> 00:19:52,540 I can say let's add New York to London, and I'd 412 00:19:52,540 --> 00:19:56,570 like for this flight to be duration of 415 minutes. 413 00:19:56,570 --> 00:19:58,600 So I type in the command, press Return. 414 00:19:58,600 --> 00:20:01,630 That adds a new row to my flights table. 415 00:20:01,630 --> 00:20:06,250 I can verify this by running SELECT * from flights. 416 00:20:06,250 --> 00:20:09,340 And I see that now I have this table whose id is one-- 417 00:20:09,340 --> 00:20:12,850 that's the id column from New York to London-- 418 00:20:12,850 --> 00:20:15,310 with a duration of 415 minutes. 419 00:20:15,310 --> 00:20:16,770 I could do this again and again. 420 00:20:16,770 --> 00:20:18,520 I, in advance, prepared a couple of INSERT 421 00:20:18,520 --> 00:20:20,860 queries just to give us a little bit more data. 422 00:20:20,860 --> 00:20:23,600 Each of this is just a different INSERT query 423 00:20:23,600 --> 00:20:26,540 that's going to add one new flight into our database. 424 00:20:26,540 --> 00:20:29,260 So I'll go ahead and copy those and then into the database 425 00:20:29,260 --> 00:20:31,570 I'll go ahead and paste in all these INSERT commands 426 00:20:31,570 --> 00:20:34,930 to insert a whole bunch of flights into this database. 427 00:20:34,930 --> 00:20:40,420 Now if I run SELECT * from flights what I get is all of the flights. 428 00:20:40,420 --> 00:20:42,360 This is not formatted particularly nicely. 429 00:20:42,360 --> 00:20:45,590 SQLite has some additional tricks if you'd like to format it a little nicer. 430 00:20:45,590 --> 00:20:48,910 So I can say, go ahead and put this into columns mode, 431 00:20:48,910 --> 00:20:51,190 and it gives me some headers. 432 00:20:51,190 --> 00:20:54,220 So .headers yes. 433 00:20:54,220 --> 00:20:56,960 And now if I SELECT * from flights, the data 434 00:20:56,960 --> 00:20:59,890 is organized a little more nicely where things are really into columns 435 00:20:59,890 --> 00:21:01,682 and there are headers for each of the rows. 436 00:21:01,682 --> 00:21:05,430 Where I can see that I kind have a text-based representation of what 437 00:21:05,430 --> 00:21:07,180 I was displaying graphically a moment ago. 438 00:21:07,180 --> 00:21:11,420 That inside of my flights table I have four columns-- an id column, origin, 439 00:21:11,420 --> 00:21:13,090 destination, and duration. 440 00:21:13,090 --> 00:21:18,310 Each flight is just one of the rows that's inside of this table. 441 00:21:18,310 --> 00:21:20,930 Now I can begin to run additional SELECT queries. 442 00:21:20,930 --> 00:21:28,120 If I want to do something like SELECT * from flights where origin= "New York," 443 00:21:28,120 --> 00:21:30,730 well then instead of getting back all of the flights, 444 00:21:30,730 --> 00:21:33,700 I'm only going to get back the flights where the origin is New York. 445 00:21:33,700 --> 00:21:36,340 So I can very quickly filter down on this data, 446 00:21:36,340 --> 00:21:39,790 only getting the information that I actually care about taking a look at. 447 00:21:39,790 --> 00:21:42,220 There are other ways of interacting with SQLite as well, 448 00:21:42,220 --> 00:21:44,440 but this SQLite prompt is a very direct way 449 00:21:44,440 --> 00:21:46,630 of just being able to write a query very quickly 450 00:21:46,630 --> 00:21:49,465 and see the results of that query. 451 00:21:49,465 --> 00:21:52,340 So it turns out, there are other types of queries we can run as well. 452 00:21:52,340 --> 00:21:53,840 So we'll take a look at some others. 453 00:21:53,840 --> 00:21:57,130 We don't just need to say where something equals something else. 454 00:21:57,130 --> 00:21:59,890 Other types of Boolean expressions are allowed as well. 455 00:21:59,890 --> 00:22:02,800 So I could say something like SELECT * from flights 456 00:22:02,800 --> 00:22:05,210 where duration is greater than 500. 457 00:22:05,210 --> 00:22:08,020 Where the idea here is let's look at the duration column. 458 00:22:08,020 --> 00:22:12,130 It's an integer, so we can do arithmetic expressions on these values. 459 00:22:12,130 --> 00:22:15,630 I can take a value and see if it's greater than 500 or not. 460 00:22:15,630 --> 00:22:18,250 When I do that, what I get back is all of the rows that have 461 00:22:18,250 --> 00:22:20,590 a duration that's greater than 500. 462 00:22:20,590 --> 00:22:23,560 I can begin to query not just on whether one value equals 463 00:22:23,560 --> 00:22:27,520 another value, but also other types of comparisons as well. 464 00:22:27,520 --> 00:22:30,640 Much as in Python, as when we had Boolean expressions in Python, 465 00:22:30,640 --> 00:22:33,310 I could join multiple Boolean expressions together 466 00:22:33,310 --> 00:22:35,710 using words like AND and OR. 467 00:22:35,710 --> 00:22:39,310 SQL has the same type of thing in its own query syntax, too. 468 00:22:39,310 --> 00:22:43,300 I could say something like SELECT * from flights where duration is greater than 469 00:22:43,300 --> 00:22:46,360 500 AND destination= "Paris." 470 00:22:46,360 --> 00:22:48,460 As you might logically intuit this means, 471 00:22:48,460 --> 00:22:51,430 this means get me all the flights that are going to Paris 472 00:22:51,430 --> 00:22:53,590 and that take longer than 500 minutes. 473 00:22:53,590 --> 00:22:56,860 It turns out there's only one such row inside this table that 474 00:22:56,860 --> 00:22:59,830 satisfies both of those constraints. 475 00:22:59,830 --> 00:23:01,960 Of course, instead of using AND I could've also 476 00:23:01,960 --> 00:23:08,080 used OR to get a different query where now I'm looking for all of the flights 477 00:23:08,080 --> 00:23:11,890 longer than 500 minutes or the destination is Paris. 478 00:23:11,890 --> 00:23:15,820 So as long as either of those conditions are met, I'm going to get results back. 479 00:23:15,820 --> 00:23:19,180 So what I get back are some of the rows where the destination is Paris, 480 00:23:19,180 --> 00:23:21,190 some rows where the destination isn't Paris, 481 00:23:21,190 --> 00:23:23,930 but the duration is more than 500 minutes, 482 00:23:23,930 --> 00:23:27,160 and of course any row that satisfies both of these constraints. 483 00:23:27,160 --> 00:23:29,860 In particular, flight id number 2. 484 00:23:29,860 --> 00:23:35,888 That gets resulted as well when I run this particular query on this table. 485 00:23:35,888 --> 00:23:38,680 There are other types of queries I can perform in addition to that. 486 00:23:38,680 --> 00:23:41,050 So I can say something like SELECT * from flights 487 00:23:41,050 --> 00:23:44,020 where origin is in-- and then in parentheses-- in "New York" 488 00:23:44,020 --> 00:23:48,250 comma "Lima" to say, check if the origin is in this sequence of possible values. 489 00:23:48,250 --> 00:23:52,300 The sequence of New York and Lima, as long as it is one of those two, then 490 00:23:52,300 --> 00:23:54,040 I want for the results to come back. 491 00:23:54,040 --> 00:23:57,400 So then you'll get back rows that have an origin of New York, rows 492 00:23:57,400 --> 00:23:59,080 that have an origin of Lima, as well. 493 00:23:59,080 --> 00:24:02,410 So you can begin to construct more sophisticated queries that 494 00:24:02,410 --> 00:24:05,770 are able to check for whether an origin is in a list of possible values 495 00:24:05,770 --> 00:24:08,740 or even if it matches a particular pattern. 496 00:24:08,740 --> 00:24:11,440 That maybe you know that one of the columns looks a certain way 497 00:24:11,440 --> 00:24:13,607 or is formatted according to a particular structure, 498 00:24:13,607 --> 00:24:16,550 but you don't know exactly what the value is. 499 00:24:16,550 --> 00:24:20,530 I could run a query like SELECT * from flights where origin-- 500 00:24:20,530 --> 00:24:23,890 not equals-- but where origin is like-- 501 00:24:23,890 --> 00:24:29,380 and then in quotation marks, I have a bit of a strange expression "%a%" 502 00:24:29,380 --> 00:24:32,450 This percent stands for a wild card. 503 00:24:32,450 --> 00:24:35,930 And this time the wild card is looking at the values of a particular column. 504 00:24:35,930 --> 00:24:39,130 In this case, the value of the origin column, meaning the percent 505 00:24:39,130 --> 00:24:42,940 stands for zero or more characters, no matter what those characters are. 506 00:24:42,940 --> 00:24:46,030 We're looking for some number of characters-- maybe zero maybe more-- 507 00:24:46,030 --> 00:24:49,090 followed by an A, followed by some other number of characters-- 508 00:24:49,090 --> 00:24:51,260 maybe zero maybe more. 509 00:24:51,260 --> 00:24:53,650 And ultimately what this query is going to do 510 00:24:53,650 --> 00:24:56,620 is look inside of the flights table and get back to me 511 00:24:56,620 --> 00:25:00,160 all of the results where there is an A the origin. 512 00:25:00,160 --> 00:25:02,290 As long as there is an A in the origin column, 513 00:25:02,290 --> 00:25:05,290 doesn't matter if there are characters before it or characters after it, 514 00:25:05,290 --> 00:25:08,110 all of the rows that come back are going to be the rows that 515 00:25:08,110 --> 00:25:10,990 just so happened to have an A in it. 516 00:25:10,990 --> 00:25:13,360 So lots of ways we can begin to run SELECT queries. 517 00:25:13,360 --> 00:25:16,443 And there are even additional functions you can add to your select queries 518 00:25:16,443 --> 00:25:17,200 as well. 519 00:25:17,200 --> 00:25:19,660 If instead of just selecting the values of columns, 520 00:25:19,660 --> 00:25:23,488 I want to compute the average duration of a particular category of flights, 521 00:25:23,488 --> 00:25:26,530 or I want to count how many flights that are coming out of New York, or I 522 00:25:26,530 --> 00:25:28,720 want to find the longest flight that goes to London, 523 00:25:28,720 --> 00:25:30,580 or the shortest flight that goes to London, 524 00:25:30,580 --> 00:25:33,497 or I want to add up all of the durations for a whole bunch of flights. 525 00:25:33,497 --> 00:25:35,830 Maybe there are flights that are connected to each other 526 00:25:35,830 --> 00:25:38,440 and I want to add up the total travel time that might take. 527 00:25:38,440 --> 00:25:40,750 SQL has a number of built-in functions that 528 00:25:40,750 --> 00:25:44,413 allow us to perform these sorts of calculations, as well. 529 00:25:44,413 --> 00:25:47,080 So a couple of different SQL commands we've now taken a look at. 530 00:25:47,080 --> 00:25:50,950 We've taken a look at Create Table that allows us to create a new table. 531 00:25:50,950 --> 00:25:53,770 INSERT that lets us add data to a table. 532 00:25:53,770 --> 00:25:57,497 SELECT, which lets us take data inside of the table and retrieve it. 533 00:25:57,497 --> 00:25:59,830 But, of course, often when we're dealing with databases, 534 00:25:59,830 --> 00:26:01,270 the data doesn't just get added. 535 00:26:01,270 --> 00:26:03,170 The data changes in some way. 536 00:26:03,170 --> 00:26:06,700 So we also need some way of being able to update data after it's already 537 00:26:06,700 --> 00:26:08,840 inside of our database. 538 00:26:08,840 --> 00:26:11,740 There's another query for that, which is an Update command that we 539 00:26:11,740 --> 00:26:13,660 can execute on our database. 540 00:26:13,660 --> 00:26:15,520 And that command looks something like this. 541 00:26:15,520 --> 00:26:19,050 Again displayed on multiple lines, but you could put this entirely just on one 542 00:26:19,050 --> 00:26:19,550 line. 543 00:26:19,550 --> 00:26:22,053 SQL doesn't really care if you add line breaks. 544 00:26:22,053 --> 00:26:24,970 It just knows that when it sees a semicolon at the end of the command, 545 00:26:24,970 --> 00:26:28,060 that is the end of this particular command. 546 00:26:28,060 --> 00:26:30,910 But here what I've said is used the Update keyword 547 00:26:30,910 --> 00:26:33,153 to say I would like to update a table. 548 00:26:33,153 --> 00:26:34,570 What table would I like to update? 549 00:26:34,570 --> 00:26:36,910 I'd like to update the flights table. 550 00:26:36,910 --> 00:26:38,380 What would I like to do? 551 00:26:38,380 --> 00:26:42,670 Well I'd like to set the duration equal to 430. 552 00:26:42,670 --> 00:26:45,370 So whatever the value of duration happens to be right now, 553 00:26:45,370 --> 00:26:47,410 change it to 430. 554 00:26:47,410 --> 00:26:51,160 But of course I don't want to change it to 430 for every single flight. 555 00:26:51,160 --> 00:26:54,890 Just as in a SELECT clause, I could say WHERE something = something or WHERE 556 00:26:54,890 --> 00:26:57,460 and then some other clause to specify where 557 00:26:57,460 --> 00:26:59,600 I want the rows to be selected from. 558 00:26:59,600 --> 00:27:03,820 Likewise, to an Update I can say set duration equal to 430 559 00:27:03,820 --> 00:27:06,110 WHERE a particular condition is true. 560 00:27:06,110 --> 00:27:08,560 So here I'm going to look through the flights table, 561 00:27:08,560 --> 00:27:12,160 find myself all of the flights where the origin is New York 562 00:27:12,160 --> 00:27:14,500 and the destination is London. 563 00:27:14,500 --> 00:27:18,010 For those rows I'm going to update the value of the duration column 564 00:27:18,010 --> 00:27:20,920 setting the duration column equal to 430. 565 00:27:20,920 --> 00:27:23,530 So using that syntax I can take data and update it, 566 00:27:23,530 --> 00:27:27,850 changing one value to another value by pinpointing which row or rows I 567 00:27:27,850 --> 00:27:28,840 would like to change. 568 00:27:28,840 --> 00:27:33,610 If I only want to change one row, I might do Update flights SET duration= 569 00:27:33,610 --> 00:27:36,940 something WHERE id= a particular id. 570 00:27:36,940 --> 00:27:40,300 To be able to pinpoint one id and then take that row 571 00:27:40,300 --> 00:27:42,760 and make some modification to it. 572 00:27:42,760 --> 00:27:46,070 In addition to inserting data, selecting data, and updating data, 573 00:27:46,070 --> 00:27:48,310 the last main command we'll concern ourselves with 574 00:27:48,310 --> 00:27:49,848 is the ability to delete data. 575 00:27:49,848 --> 00:27:52,390 The ability to take a row and say, I'd like to get rid of it. 576 00:27:52,390 --> 00:27:54,700 Or take multiple rows and get rid of them. 577 00:27:54,700 --> 00:27:59,110 And so a command like delete from flights, where destination= "Tokyo" 578 00:27:59,110 --> 00:28:02,650 as you might imagine deletes from the flights table all of the rows that 579 00:28:02,650 --> 00:28:07,132 satisfy this condition where the destination is equal to Tokyo. 580 00:28:07,132 --> 00:28:10,090 So a number of different operations now that we have the ability to do. 581 00:28:10,090 --> 00:28:13,690 The ability to delete from a particular table where a condition is true, 582 00:28:13,690 --> 00:28:17,140 the ability to update a table based on particular conditions, the ability 583 00:28:17,140 --> 00:28:21,760 to select data from a table, and insert data into a table as well. 584 00:28:21,760 --> 00:28:25,570 There are a couple other clauses that can be used to add SQL queries as well. 585 00:28:25,570 --> 00:28:27,850 Just to add additional functionality. 586 00:28:27,850 --> 00:28:31,450 If I don't want all of the rows to come back from a particular SQL query, 587 00:28:31,450 --> 00:28:33,490 I can limit the results that come back. 588 00:28:33,490 --> 00:28:36,160 So normally SELECT * from flights would get me 589 00:28:36,160 --> 00:28:38,320 all of the flights inside of the table. 590 00:28:38,320 --> 00:28:42,220 But I could say SELECT * from flights, LIMIT 5 to just say, 591 00:28:42,220 --> 00:28:46,120 I only want five results to come back from this table. 592 00:28:46,120 --> 00:28:50,140 ORDER BY allows me to decide how the results are ordered inside the results 593 00:28:50,140 --> 00:28:50,980 to come back. 594 00:28:50,980 --> 00:28:54,970 So I can say SELECT * from flights, ORDER BY destination, or ORDER BY 595 00:28:54,970 --> 00:28:58,870 duration to get all of the flights in order by how long they are. 596 00:28:58,870 --> 00:29:02,840 GROUP BY allows me to group a whole bunch of rows together. 597 00:29:02,840 --> 00:29:05,753 So if I wanted to group all of the flights by their origin 598 00:29:05,753 --> 00:29:08,920 so I can get all of the flights leaving New York and all the flights leaving 599 00:29:08,920 --> 00:29:11,650 London and so forth, I could do something like SELECT * 600 00:29:11,650 --> 00:29:16,480 from flights GROUP BY origin to group flights by their origin as well. 601 00:29:16,480 --> 00:29:19,210 HAVING is a constraint I can place on GROUP BY 602 00:29:19,210 --> 00:29:23,830 to say that I would like to select all of the flights grouping them 603 00:29:23,830 --> 00:29:27,100 by their origin, but they need to have a count of at least three. 604 00:29:27,100 --> 00:29:29,650 Meaning there needs to be at least three flights that 605 00:29:29,650 --> 00:29:31,390 are leaving from that particular city. 606 00:29:31,390 --> 00:29:32,230 607 00:29:32,230 --> 00:29:34,060 For all of these particular clauses, these 608 00:29:34,060 --> 00:29:36,700 are helpful to know if you're going to be directly writing SQL. 609 00:29:36,700 --> 00:29:39,010 We won't worry about them too much here, in particular 610 00:29:39,010 --> 00:29:42,100 because fairly shortly we're not going to be writing SQL ourselves. 611 00:29:42,100 --> 00:29:44,230 We're going to just be writing Python code 612 00:29:44,230 --> 00:29:47,500 and Django is going to, under the hood, be manipulating the database 613 00:29:47,500 --> 00:29:49,930 and creating the SQL commands that it is going 614 00:29:49,930 --> 00:29:52,020 to run on the underlying database. 615 00:29:52,020 --> 00:29:54,310 So we will see how we don't actually need to worry 616 00:29:54,310 --> 00:29:56,110 about writing the specific syntax. 617 00:29:56,110 --> 00:30:00,080 But Django is going to handle much of that for us. 618 00:30:00,080 --> 00:30:02,500 So here now we have a flights table, a table 619 00:30:02,500 --> 00:30:04,960 that keeps track of all of the flights that we have, 620 00:30:04,960 --> 00:30:08,500 organizing them by their id and their origin and their destination 621 00:30:08,500 --> 00:30:09,700 and their duration. 622 00:30:09,700 --> 00:30:13,310 But oftentimes when we're dealing with data, especially in a larger database, 623 00:30:13,310 --> 00:30:15,460 we don't just have one table of data. 624 00:30:15,460 --> 00:30:17,330 We have multiple tables of data. 625 00:30:17,330 --> 00:30:21,528 And those multiple tables might relate to each other in some way. 626 00:30:21,528 --> 00:30:24,070 Let's take a look at an example of how that might come about. 627 00:30:24,070 --> 00:30:26,770 We're going to introduce a concept that will call foreign keys, 628 00:30:26,770 --> 00:30:29,540 and we'll see what that means in just a moment. 629 00:30:29,540 --> 00:30:31,690 So here again is our flights table. 630 00:30:31,690 --> 00:30:37,270 The flights table has four columns-- an id, origin, destination, and duration. 631 00:30:37,270 --> 00:30:41,215 But of course in New York, there are multiple airports. 632 00:30:41,215 --> 00:30:43,090 And so it might not make sense for me to just 633 00:30:43,090 --> 00:30:46,840 label each origin or each destination just by the name of the city. 634 00:30:46,840 --> 00:30:49,540 Maybe I also want to give the three letter airport 635 00:30:49,540 --> 00:30:54,070 code that corresponds to the airport to which I'm referring in this case. 636 00:30:54,070 --> 00:30:57,730 So how would I encode into this table, not only the origin, but also 637 00:30:57,730 --> 00:30:59,170 that city's airport code? 638 00:30:59,170 --> 00:31:01,430 And not only for the destination the name of the city, 639 00:31:01,430 --> 00:31:04,450 but also the airport code for that airport as well? 640 00:31:04,450 --> 00:31:06,450 Well I could just add more columns. 641 00:31:06,450 --> 00:31:08,200 I could say something like, all right, now 642 00:31:08,200 --> 00:31:14,530 we have this table that has an id, an origin, an origin code, a destination, 643 00:31:14,530 --> 00:31:17,410 a destination code, and a duration. 644 00:31:17,410 --> 00:31:21,050 But here now, the table is starting to get fairly wide. 645 00:31:21,050 --> 00:31:23,730 There are a lot of columns here and in particular, there 646 00:31:23,730 --> 00:31:27,100 is some duplicate data. 647 00:31:27,100 --> 00:31:30,010 Paris is associated with this particular three letter 648 00:31:30,010 --> 00:31:33,680 code, and the same thing for New York and other airports as well. 649 00:31:33,680 --> 00:31:37,330 There is some messiness in the structure of this data. 650 00:31:37,330 --> 00:31:39,340 Often what we'll want to do as we begin to deal 651 00:31:39,340 --> 00:31:42,520 with data in larger and larger sets with more and more columns 652 00:31:42,520 --> 00:31:44,410 is we'll want to normalize this data. 653 00:31:44,410 --> 00:31:47,710 Separating things out into multiple different tables that 654 00:31:47,710 --> 00:31:50,850 just reference one another in some way. 655 00:31:50,850 --> 00:31:54,130 So instead of just having a single flights table what we might consider 656 00:31:54,130 --> 00:31:57,610 doing is saying that flights are one type of object 657 00:31:57,610 --> 00:32:01,360 but another type of object that I care about is an airport. 658 00:32:01,360 --> 00:32:04,270 So I might just have a separate table just for airports 659 00:32:04,270 --> 00:32:06,480 where this table has three columns. 660 00:32:06,480 --> 00:32:09,220 A column for the ID of the airport, just some unique number 661 00:32:09,220 --> 00:32:11,440 that can identify a particular airport. 662 00:32:11,440 --> 00:32:14,290 One column for the three letter code for that airport, 663 00:32:14,290 --> 00:32:16,360 and one letter for the city, or one column 664 00:32:16,360 --> 00:32:19,060 for the city where that airport is in. 665 00:32:19,060 --> 00:32:22,210 Now this is a much more straightforward, simpler representation 666 00:32:22,210 --> 00:32:23,920 of all of the airports. 667 00:32:23,920 --> 00:32:27,100 The question becomes what happens to my flights table? 668 00:32:27,100 --> 00:32:32,410 My flights table that here had an id, an origin, destination, and duration where 669 00:32:32,410 --> 00:32:36,610 the type of origin and destination were in this case just text. 670 00:32:36,610 --> 00:32:39,700 Text-based data representing the name of the city 671 00:32:39,700 --> 00:32:43,740 from which the flight is departing or to which the flight is arriving. 672 00:32:43,740 --> 00:32:45,490 Well now that I have the separate airports 673 00:32:45,490 --> 00:32:47,950 table where every row in the airports table 674 00:32:47,950 --> 00:32:51,440 has its own unique id, then what I can do in this case is, 675 00:32:51,440 --> 00:32:54,700 instead of storing an origin and a destination as text, 676 00:32:54,700 --> 00:32:56,950 I can store what we'll call a foreign key. 677 00:32:56,950 --> 00:33:00,730 A reference to a key in another table and rename these columns 678 00:33:00,730 --> 00:33:04,120 to origin id and destination id. 679 00:33:04,120 --> 00:33:08,500 That instead of storing text are going to store a number where 680 00:33:08,500 --> 00:33:11,860 origin_id 1 means the origin of flight one 681 00:33:11,860 --> 00:33:16,200 is whatever airport number 1 happens to be. 682 00:33:16,200 --> 00:33:21,160 I could go to the airports table, look up which airport has an id of 1, 683 00:33:21,160 --> 00:33:23,390 and that would tell me the origin of this flight. 684 00:33:23,390 --> 00:33:26,080 And if I went to the airports table and looked up which airport 685 00:33:26,080 --> 00:33:30,410 had an ID of 4 that would tell me the destination of this flight as well. 686 00:33:30,410 --> 00:33:32,980 So by combining two different tables. 687 00:33:32,980 --> 00:33:37,150 One table for representing airports, and one table for representing flights. 688 00:33:37,150 --> 00:33:40,510 I'm able to connect these two different tables together 689 00:33:40,510 --> 00:33:41,710 by way of a foreign key. 690 00:33:41,710 --> 00:33:45,700 Some columns inside of my flights table, namely the origin id column 691 00:33:45,700 --> 00:33:48,220 and the destination id column that together 692 00:33:48,220 --> 00:33:52,840 allow me to reference information stored inside of another table as well. 693 00:33:52,840 --> 00:33:55,330 As you imagine this sort of airlines database 694 00:33:55,330 --> 00:33:57,610 growing and storing more different kinds of data, 695 00:33:57,610 --> 00:34:00,090 the ability to relate tables to each other 696 00:34:00,090 --> 00:34:02,187 is going to become incredibly powerful. 697 00:34:02,187 --> 00:34:04,270 So one thing you might imagine is that in addition 698 00:34:04,270 --> 00:34:07,630 to storing airports and storing flights, an airline probably 699 00:34:07,630 --> 00:34:10,330 also needs to store information about its passengers, 700 00:34:10,330 --> 00:34:12,420 like who on which flight. 701 00:34:12,420 --> 00:34:15,400 So you could imagine constructing a passenger's table 702 00:34:15,400 --> 00:34:19,360 that has an id column to uniquely identify every passenger, a first name 703 00:34:19,360 --> 00:34:21,600 column that stores every passenger's first name, 704 00:34:21,600 --> 00:34:23,710 a last name column for storing their last name, 705 00:34:23,710 --> 00:34:28,790 and a flight id column for storing what flight that passenger happens to be on. 706 00:34:28,790 --> 00:34:32,560 So in this case, I could say that Harry Potter is on flight number one. 707 00:34:32,560 --> 00:34:34,449 I could look that up in the flights table 708 00:34:34,449 --> 00:34:36,760 to find out inside the flights table, here 709 00:34:36,760 --> 00:34:39,370 is where the flight is leaving from, where it's going to 710 00:34:39,370 --> 00:34:42,310 and what its duration happens to be. 711 00:34:42,310 --> 00:34:44,679 Now as we begin to design these tables, we 712 00:34:44,679 --> 00:34:47,770 have to think about what the implications of that design 713 00:34:47,770 --> 00:34:48,699 happen to be. 714 00:34:48,699 --> 00:34:50,679 In the case of this passenger's table, it 715 00:34:50,679 --> 00:34:55,270 does seem that there is a limitation on the table design that I have created. 716 00:34:55,270 --> 00:34:58,540 Namely, if you think about it you'll see the limitation of this table design 717 00:34:58,540 --> 00:35:04,480 is that any particular row can only have one flight id associated with it. 718 00:35:04,480 --> 00:35:07,600 That Harry Potter has a single flight id column 719 00:35:07,600 --> 00:35:10,780 that can only have one value stored inside of it. 720 00:35:10,780 --> 00:35:13,330 This would seem to make it impossible to allow 721 00:35:13,330 --> 00:35:15,580 for us to be able to represent a person that 722 00:35:15,580 --> 00:35:18,580 could be on multiple different flights. 723 00:35:18,580 --> 00:35:22,360 So this starts to get at the idea of different types of relationships 724 00:35:22,360 --> 00:35:25,330 that rows in a table can have to one another. 725 00:35:25,330 --> 00:35:28,000 One type of relationship is a many to one 726 00:35:28,000 --> 00:35:30,290 relationship, or a one to many relationship, 727 00:35:30,290 --> 00:35:33,070 where I express the idea that one flight can 728 00:35:33,070 --> 00:35:36,940 be associated with many different passengers, for instance. 729 00:35:36,940 --> 00:35:40,640 But we might also want is a many to many relationship, 730 00:35:40,640 --> 00:35:43,910 where many different passengers can be associated with many different flights. 731 00:35:43,910 --> 00:35:46,330 A passenger might have more than one flight. 732 00:35:46,330 --> 00:35:48,820 A flight might have more than one passenger. 733 00:35:48,820 --> 00:35:52,090 To do that we're going to need a slightly different structure 734 00:35:52,090 --> 00:35:54,280 for this particular type of table. 735 00:35:54,280 --> 00:35:57,700 One way we could approach it is by creating a separate table 736 00:35:57,700 --> 00:35:59,050 for storing people. 737 00:35:59,050 --> 00:36:03,220 I can have a people table where every person has an id, has a first name, 738 00:36:03,220 --> 00:36:05,170 and has a last name same as before. 739 00:36:05,170 --> 00:36:08,830 But I'm no longer storing flight information inside of the table. 740 00:36:08,830 --> 00:36:10,210 I've cleaned my setup up. 741 00:36:10,210 --> 00:36:13,570 I'm only storing people in this table and nothing about their flight 742 00:36:13,570 --> 00:36:14,880 information. 743 00:36:14,880 --> 00:36:18,190 I'll have a separate table for dealing with passengers on the flight 744 00:36:18,190 --> 00:36:20,633 and mapping people to their flights. 745 00:36:20,633 --> 00:36:23,050 We can think about what does that table need to look like? 746 00:36:23,050 --> 00:36:27,400 Well I need some sort of table that is going to relate people to what flights 747 00:36:27,400 --> 00:36:28,690 they happen to be on. 748 00:36:28,690 --> 00:36:31,780 So odds are we're going to need one column that is a foreign key that 749 00:36:31,780 --> 00:36:35,170 references this people table, and we'll need another column that 750 00:36:35,170 --> 00:36:38,140 is a foreign key that references the flights table, such 751 00:36:38,140 --> 00:36:40,690 that I can relate those two tables together. 752 00:36:40,690 --> 00:36:43,360 So that table could look like this. 753 00:36:43,360 --> 00:36:46,940 This now is a simplified passengers table that only has two columns. 754 00:36:46,940 --> 00:36:50,500 It has a person id column and a flight id column. 755 00:36:50,500 --> 00:36:53,560 The idea of this table now is it's known as an association 756 00:36:53,560 --> 00:36:57,790 table, or a joined table that just associates one value from one table 757 00:36:57,790 --> 00:37:00,130 with another value from another table. 758 00:37:00,130 --> 00:37:04,390 This row here, one and one, means the person with an id of one 759 00:37:04,390 --> 00:37:06,610 is on flight number one. 760 00:37:06,610 --> 00:37:09,670 I could look up that person inside of the people table, 761 00:37:09,670 --> 00:37:11,860 look up that flight inside of the flights table, 762 00:37:11,860 --> 00:37:14,780 and figure out who the person is and what flight they're on. 763 00:37:14,780 --> 00:37:18,670 Down here, two and four, means whoever the person with an ID of 2 764 00:37:18,670 --> 00:37:24,370 is on whichever flight happens to have an ID of 4. 765 00:37:24,370 --> 00:37:27,190 So this now has allowed us to be able to represent 766 00:37:27,190 --> 00:37:28,930 the types of relationships we want. 767 00:37:28,930 --> 00:37:33,250 We have a table for airports and a table for flights and any flight 768 00:37:33,250 --> 00:37:37,390 is going to map to two different airports, one destination one origin. 769 00:37:37,390 --> 00:37:40,130 And any airport might appear on multiple different flights. 770 00:37:40,130 --> 00:37:42,730 It's sort of a one to many relationship. 771 00:37:42,730 --> 00:37:45,070 Then over here, when it comes to passengers, 772 00:37:45,070 --> 00:37:47,590 we've stored people inside of a separate table, 773 00:37:47,590 --> 00:37:50,980 and then had a many to many mapping between people and flights 774 00:37:50,980 --> 00:37:53,800 so that any person could be on multiple different flights. 775 00:37:53,800 --> 00:37:58,420 Like here, for example, person number two is on both flights one and four. 776 00:37:58,420 --> 00:38:00,890 Likewise, a flight could have multiple people. 777 00:38:00,890 --> 00:38:04,720 So in this case flight number six has passengers five and six 778 00:38:04,720 --> 00:38:06,400 that are on that flight as well. 779 00:38:06,400 --> 00:38:09,740 We've been able to represent those relationships. 780 00:38:09,740 --> 00:38:13,750 Of course a byproduct of doing this is that now our tables are a little bit 781 00:38:13,750 --> 00:38:15,130 messier to look at. 782 00:38:15,130 --> 00:38:17,840 Messy in the sense that it's not immediately obvious to me, 783 00:38:17,840 --> 00:38:20,320 when I look at this table, what data I'm looking at. 784 00:38:20,320 --> 00:38:23,350 I see these numbers, but I don't know what these numbers mean. 785 00:38:23,350 --> 00:38:26,175 I've separated all these tables into different places. 786 00:38:26,175 --> 00:38:29,050 Now it's a little harder for me to figure out who is on which flight. 787 00:38:29,050 --> 00:38:31,960 I have to look at this data, look up people in the people table, 788 00:38:31,960 --> 00:38:34,180 look up flights in the flights table, and somehow 789 00:38:34,180 --> 00:38:37,810 associate all of that information back together in order 790 00:38:37,810 --> 00:38:39,910 to draw any sort of conclusion. 791 00:38:39,910 --> 00:38:42,100 But luckily, SQL makes it pretty easy for us 792 00:38:42,100 --> 00:38:45,430 to be able to take data across multiple different tables 793 00:38:45,430 --> 00:38:47,620 and join them all back together. 794 00:38:47,620 --> 00:38:51,670 We can do this using a JOIN query that takes multiple tables 795 00:38:51,670 --> 00:38:52,960 and joins them together. 796 00:38:52,960 --> 00:38:56,540 So the syntax for a JOIN query might look something like this. 797 00:38:56,540 --> 00:38:59,020 And here we'll go back to just the two-table setup where 798 00:38:59,020 --> 00:39:02,200 I have flights and passengers, where every passenger is 799 00:39:02,200 --> 00:39:03,610 associated with one flight. 800 00:39:03,610 --> 00:39:05,800 But you could extend this and join multiple tables 801 00:39:05,800 --> 00:39:08,150 to deal with our more complex example as well. 802 00:39:08,150 --> 00:39:11,350 But here, I'd like to select every person's first name 803 00:39:11,350 --> 00:39:14,170 and their origin and their destination. 804 00:39:14,170 --> 00:39:16,390 I'm going to select that from the flights table, 805 00:39:16,390 --> 00:39:19,300 but I need to join it with the passengers table. 806 00:39:19,300 --> 00:39:21,970 Then I say ON to indicate how it is these two 807 00:39:21,970 --> 00:39:23,992 tables are related to one another. 808 00:39:23,992 --> 00:39:26,200 In this case, I'm saying the way these two tables are 809 00:39:26,200 --> 00:39:30,250 related to one another is that the flight id column of the passengers 810 00:39:30,250 --> 00:39:34,330 table is associated with the id column of the flights table. 811 00:39:34,330 --> 00:39:37,960 The flights table has an id that uniquely identifies every flight, 812 00:39:37,960 --> 00:39:40,780 and the passengers table has a flight id column 813 00:39:40,780 --> 00:39:43,900 that uniquely identifies the flight that we're referring to 814 00:39:43,900 --> 00:39:45,640 for this particular passenger. 815 00:39:45,640 --> 00:39:48,340 And so the result I might get is a table that looks like this. 816 00:39:48,340 --> 00:39:50,730 That gives me everyone's first name, but also 817 00:39:50,730 --> 00:39:52,500 their origin and their destination. 818 00:39:52,500 --> 00:39:58,560 Our origin and destination are going to be drawn from that table of flights 819 00:39:58,560 --> 00:40:01,770 and the first name is going to be drawn from the table of passengers. 820 00:40:01,770 --> 00:40:04,410 But by using a JOIN query, I've been able to take data 821 00:40:04,410 --> 00:40:08,490 from two separate tables and join them both back together. 822 00:40:08,490 --> 00:40:11,850 And there are a number of different types of JOIN queries that I can run. 823 00:40:11,850 --> 00:40:14,160 What we saw here was just the default JOIN, which 824 00:40:14,160 --> 00:40:16,950 is otherwise known as an INNER JOIN. 825 00:40:16,950 --> 00:40:19,830 Effectively, an INNER JOIN will take the two tables, 826 00:40:19,830 --> 00:40:22,770 it will cross compare them based on the condition that I specified 827 00:40:22,770 --> 00:40:26,760 and only return back to me the results where there's a match on both sides. 828 00:40:26,760 --> 00:40:31,430 Where we match a passenger's flight id with an id in the flights table. 829 00:40:31,430 --> 00:40:33,180 There are various different kinds of outer 830 00:40:33,180 --> 00:40:35,790 joins if I want to be OK with the idea that maybe 831 00:40:35,790 --> 00:40:37,800 something on the left table that I'm joining 832 00:40:37,800 --> 00:40:39,508 doesn't match with anything on the right, 833 00:40:39,508 --> 00:40:42,258 or maybe something on the right table doesn't match with something 834 00:40:42,258 --> 00:40:42,900 on the left. 835 00:40:42,900 --> 00:40:47,640 But just know there are other types of JOIN queries that I can run as well. 836 00:40:47,640 --> 00:40:50,680 Other strategies that can be helpful when dealing with SQL tables 837 00:40:50,680 --> 00:40:54,120 are optimizations we can make to make queries more efficient. 838 00:40:54,120 --> 00:40:57,690 One thing we can do with our tables is to create an index 839 00:40:57,690 --> 00:40:59,130 on a particular table. 840 00:40:59,130 --> 00:41:02,830 You can think of an index as kind of like the index in the back of a book, 841 00:41:02,830 --> 00:41:06,260 for example, where if you wanted to be able to search for a topic in a text 842 00:41:06,260 --> 00:41:10,055 book, you could open the textbook and just page by page look for every topic 843 00:41:10,055 --> 00:41:12,180 and just try and find the topic you're looking for. 844 00:41:12,180 --> 00:41:14,670 But often what you'll be able to do if the table has 845 00:41:14,670 --> 00:41:19,450 an index is go to the index of the book, find the topic you're looking for, 846 00:41:19,450 --> 00:41:21,600 and that will quickly give you a reference for how 847 00:41:21,600 --> 00:41:23,730 to get to the right page in question. 848 00:41:23,730 --> 00:41:26,370 An index on a table operates in much the same way. 849 00:41:26,370 --> 00:41:29,640 It is an additional data structure that can be constructed, 850 00:41:29,640 --> 00:41:32,820 and it does take time and memory to be able to construct this data structure 851 00:41:32,820 --> 00:41:36,550 and to maintain it anytime you update the data inside the table. 852 00:41:36,550 --> 00:41:40,920 But once it exists it makes querying on a particular column much more 853 00:41:40,920 --> 00:41:41,580 efficient. 854 00:41:41,580 --> 00:41:44,340 You can very quickly look something up in the index 855 00:41:44,340 --> 00:41:47,410 and find the corresponding rows that go along with it. 856 00:41:47,410 --> 00:41:50,480 So here we can have a command like create an index that we're 857 00:41:50,480 --> 00:41:53,910 going to call name index on the passengers table, 858 00:41:53,910 --> 00:41:57,630 and in particular on the last name column. 859 00:41:57,630 --> 00:42:00,960 I expect that as I query this table, I'm pretty frequently 860 00:42:00,960 --> 00:42:03,820 going to be looking up passengers by their last name. 861 00:42:03,820 --> 00:42:06,360 So I would like to create an index on that table 862 00:42:06,360 --> 00:42:09,390 to be able to more efficiently search for a passenger 863 00:42:09,390 --> 00:42:12,280 based on their last name as well. 864 00:42:12,280 --> 00:42:15,710 So that's just a general overview of what SQL syntax is all about. 865 00:42:15,710 --> 00:42:19,830 A syntax that we can use to be able to create data tables inside of which 866 00:42:19,830 --> 00:42:24,090 are storing rows of data where every row consists of some number of columns, 867 00:42:24,090 --> 00:42:25,760 and every column has a type. 868 00:42:25,760 --> 00:42:28,650 We have the ability to create tables, add data to them, 869 00:42:28,650 --> 00:42:31,950 update, delete, and get data out of those tables as well. 870 00:42:31,950 --> 00:42:34,320 But as we begin to introduce these new technologies, 871 00:42:34,320 --> 00:42:36,510 there are always risks and potential threats 872 00:42:36,510 --> 00:42:39,360 that are associated with those technologies as well. 873 00:42:39,360 --> 00:42:44,370 In SQL, the key one to be aware of is what's known as a SQL injection attack. 874 00:42:44,370 --> 00:42:46,800 A security vulnerability that can happen if you're not 875 00:42:46,800 --> 00:42:50,878 careful about how it is you actually execute your SQL commands. 876 00:42:50,878 --> 00:42:52,170 So where might this come about? 877 00:42:52,170 --> 00:42:56,020 You might imagine for instance, that if a database has some number of users, 878 00:42:56,020 --> 00:42:59,490 you might be storing those users inside of a database. 879 00:42:59,490 --> 00:43:02,130 For instance, in a users table where there's a user name 880 00:43:02,130 --> 00:43:03,830 column and a password column. 881 00:43:03,830 --> 00:43:04,950 Though, in practice you probably wouldn't 882 00:43:04,950 --> 00:43:06,870 want to store passwords and clear text, let's 883 00:43:06,870 --> 00:43:10,740 imagine here for example that you are storing usernames and passwords inside 884 00:43:10,740 --> 00:43:12,090 of a table. 885 00:43:12,090 --> 00:43:14,250 We have a log in form for a website that looks 886 00:43:14,250 --> 00:43:17,395 like this, where you get to type in your username and your password. 887 00:43:17,395 --> 00:43:19,520 So if someone types in their username and password, 888 00:43:19,520 --> 00:43:23,550 what might happen is that the web application might look SELECT * FROM 889 00:43:23,550 --> 00:43:28,080 users, where username= this particular user name highlighted here. 890 00:43:28,080 --> 00:43:30,570 We're just going to substitute the user name right there. 891 00:43:30,570 --> 00:43:34,442 And password= and we'll substitute the password over there. 892 00:43:34,442 --> 00:43:36,150 So if someone tries to log into our site, 893 00:43:36,150 --> 00:43:40,440 like Harry logs in with a password of 12345, what we might do 894 00:43:40,440 --> 00:43:42,090 is run this SELECT query. 895 00:43:42,090 --> 00:43:45,540 Say SELECT * FROM users where username is "Harry" 896 00:43:45,540 --> 00:43:48,660 and where the password is 12345. 897 00:43:48,660 --> 00:43:50,970 Our logic might be if we get results back, 898 00:43:50,970 --> 00:43:55,890 then that means there is a user whose username is Harry and password is 12345 899 00:43:55,890 --> 00:43:59,100 and we can go ahead and sign that user in. 900 00:43:59,100 --> 00:44:02,460 But imagine now what might happen if instead, 901 00:44:02,460 --> 00:44:07,950 the user who typed in a username of hacker"--. 902 00:44:07,950 --> 00:44:10,202 Seems like a bit of a strange username to type in. 903 00:44:10,202 --> 00:44:12,660 It doesn't matter what they put in their password here now. 904 00:44:12,660 --> 00:44:16,110 The result might be that what they would plug into the username is where 905 00:44:16,110 --> 00:44:19,290 username="hacker" and then the - -. 906 00:44:19,290 --> 00:44:20,520 It turns out -- 907 00:44:20,520 --> 00:44:22,680 in SQL stands for a comment in SQL. 908 00:44:22,680 --> 00:44:25,680 It just means ignore everything that comes after it in the same way that 909 00:44:25,680 --> 00:44:29,340 in Python you can use the hashtag symbol to mean the rest of this line is 910 00:44:29,340 --> 00:44:31,980 a comment, and the compiler-- whoever is running the program-- 911 00:44:31,980 --> 00:44:33,100 should just ignore it. 912 00:44:33,100 --> 00:44:36,510 So everything after the - - kind of gets ignored. 913 00:44:36,510 --> 00:44:39,390 We've effectively been able to bypass the password check. 914 00:44:39,390 --> 00:44:43,080 Someone could bypass a password check and log into an account 915 00:44:43,080 --> 00:44:45,240 even if they were unauthorized to do so. 916 00:44:45,240 --> 00:44:48,720 So here is a vulnerability within the SQL syntax. 917 00:44:48,720 --> 00:44:51,490 If we're not careful about when we're running SQL syntax, 918 00:44:51,490 --> 00:44:54,350 we could be running untrusted SQL commands 919 00:44:54,350 --> 00:44:58,900 that some hacker or some adversary has been able to plug-in to our program. 920 00:44:58,900 --> 00:45:00,930 So how do we solve this sort of problem? 921 00:45:00,930 --> 00:45:03,750 One strategy is to escape these characters. 922 00:45:03,750 --> 00:45:06,270 Escaping just meaning add some back slashes 923 00:45:06,270 --> 00:45:09,840 that just makes sure that SQL knows to treat these as a literal quotation 924 00:45:09,840 --> 00:45:13,980 mark and a literal dash, and not special SQL syntax of any sort. 925 00:45:13,980 --> 00:45:17,550 Another strategy is to use an abstraction layer on top of SQL 926 00:45:17,550 --> 00:45:19,800 so that we don't have to write the SQL queries at all. 927 00:45:19,800 --> 00:45:23,640 That's in fact what we're about to do as we transition to the world of Django 928 00:45:23,640 --> 00:45:27,000 to take a look at how when we begin to use a web framework like Django, 929 00:45:27,000 --> 00:45:31,530 we now have the ability to not worry about the nuances of the syntax of SQL 930 00:45:31,530 --> 00:45:34,415 and just deal a little more high level with what our models are. 931 00:45:34,415 --> 00:45:37,290 What the types of objects are that we're dealing with and interacting 932 00:45:37,290 --> 00:45:40,320 with inside of this application. 933 00:45:40,320 --> 00:45:43,260 One other concern worth noting about with regards to SQL 934 00:45:43,260 --> 00:45:45,510 is the possibility of race conditions. 935 00:45:45,510 --> 00:45:47,970 A race condition is something that might happen 936 00:45:47,970 --> 00:45:51,780 anytime you have multiple events that are happening in parallel threads, 937 00:45:51,780 --> 00:45:52,320 so to speak. 938 00:45:52,320 --> 00:45:56,070 That you have one thing happening and another thing happening simultaneously. 939 00:45:56,070 --> 00:45:59,190 You might imagine that in the case of social media sites 940 00:45:59,190 --> 00:46:02,590 where you can like a post, like an image on Instagram or a tweet on Twitter, 941 00:46:02,590 --> 00:46:03,580 for example. 942 00:46:03,580 --> 00:46:08,610 What would happen if two people tried to like the same post at the same time? 943 00:46:08,610 --> 00:46:11,880 If we're not careful about how we run those particular SQL queries 944 00:46:11,880 --> 00:46:16,530 there's a potential for us to be able to get race condition problems, 945 00:46:16,530 --> 00:46:20,100 where we end up trying to query for the number of likes that a post has 946 00:46:20,100 --> 00:46:22,378 and then another person tries to do the same thing 947 00:46:22,378 --> 00:46:24,420 and there are conflicts when we try and update it 948 00:46:24,420 --> 00:46:27,350 where the result might not be what we would expect it to be. 949 00:46:27,350 --> 00:46:29,310 There are a number of unexpected results that 950 00:46:29,310 --> 00:46:32,610 can happen when we deal with problems related to race conditions 951 00:46:32,610 --> 00:46:35,440 where multiple things are happening simultaneously. 952 00:46:35,440 --> 00:46:36,930 How do we solve those problems? 953 00:46:36,930 --> 00:46:40,230 Well, one strategy is to sort of place a lock on the database. 954 00:46:40,230 --> 00:46:43,110 To say, while I'm working on this database nobody else 955 00:46:43,110 --> 00:46:44,290 can touch this data. 956 00:46:44,290 --> 00:46:46,580 Let me finish this transaction, so to speak. 957 00:46:46,580 --> 00:46:49,890 Finish working on this particular transaction and making all the changes 958 00:46:49,890 --> 00:46:51,360 I need to make to the database. 959 00:46:51,360 --> 00:46:55,620 Only after I'm done I can release the lock and let someone else go ahead 960 00:46:55,620 --> 00:46:57,720 and modify the database as well. 961 00:46:57,720 --> 00:47:00,090 So a number of concerns to be aware of as we 962 00:47:00,090 --> 00:47:05,520 begin dealing in this world of SQL and trying to work with databases. 963 00:47:05,520 --> 00:47:07,800 So now that we've taken a look at the syntax of SQL, 964 00:47:07,800 --> 00:47:10,830 understanding how these tables work, how they're structured, 965 00:47:10,830 --> 00:47:13,060 and what it is that we can add to those tables, 966 00:47:13,060 --> 00:47:16,110 let's go ahead and turn our attention in particular to Django 967 00:47:16,110 --> 00:47:20,208 models, which are a way of representing data inside of a Django application. 968 00:47:20,208 --> 00:47:23,250 Because where Django is really going to get powerful in designing our web 969 00:47:23,250 --> 00:47:27,718 applications is the ability to represent data in terms of these models. 970 00:47:27,718 --> 00:47:30,510 So we're going to go ahead and try to create a web application that 971 00:47:30,510 --> 00:47:34,410 is going to represent what an airline might want to store inside 972 00:47:34,410 --> 00:47:37,110 of its own web application. 973 00:47:37,110 --> 00:47:37,610 All right. 974 00:47:37,610 --> 00:47:40,770 So the first thing I want to do is create a Django project. 975 00:47:40,770 --> 00:47:43,725 So I'll go ahead and type Django-admin START PROJECT 976 00:47:43,725 --> 00:47:46,100 and the name of my project will just be called "airline." 977 00:47:46,100 --> 00:47:49,470 I'm creating a project for an airline's website for example. 978 00:47:49,470 --> 00:47:51,530 I'll go ahead and go into the airline directory, 979 00:47:51,530 --> 00:47:53,310 open that up in my code editor. 980 00:47:53,310 --> 00:47:55,680 Before I actually begin editing any code, 981 00:47:55,680 --> 00:48:00,000 remember that every Django project needs to have one or more apps within it. 982 00:48:00,000 --> 00:48:02,610 So the first app that I'll create for this airline 983 00:48:02,610 --> 00:48:05,760 is an app for keeping track of flights. 984 00:48:05,760 --> 00:48:08,460 So keeping track of flight related information, 985 00:48:08,460 --> 00:48:10,770 like origins and destinations and durations 986 00:48:10,770 --> 00:48:13,680 and what passengers are on those flights. 987 00:48:13,680 --> 00:48:16,650 When I create a new app, first thing I'll need to do 988 00:48:16,650 --> 00:48:19,530 is go into settings.py inside of airline, 989 00:48:19,530 --> 00:48:22,890 and go ahead and add this app as an installed app. 990 00:48:22,890 --> 00:48:26,040 So "flights" is now an app that I have installed. 991 00:48:26,040 --> 00:48:32,702 Then what I'll want to do is say, go ahead and go into urls.py, which is, 992 00:48:32,702 --> 00:48:34,410 again, that table of contents for all the 993 00:48:34,410 --> 00:48:36,870 URLs I can get to for this particular web application. 994 00:48:36,870 --> 00:48:41,160 I'll IMPORT include, because I want to do is when someone visits the path 995 00:48:41,160 --> 00:48:46,720 of flights/ something, I want to take them to flights.urls, 996 00:48:46,720 --> 00:48:51,540 mapping them to the urls.py file that will be inside of my "flights" 997 00:48:51,540 --> 00:48:52,950 application. 998 00:48:52,950 --> 00:48:56,960 Of course, now I need a urls.py file inside of my "flights" application. 999 00:48:56,960 --> 00:49:01,050 So I go into "flights" and create a new file that I'll call urls.py. 1000 00:49:01,050 --> 00:49:03,870 1001 00:49:03,870 --> 00:49:09,210 We can do from FROM.django.urls IMPORT path FROM .IMPORT views. 1002 00:49:09,210 --> 00:49:14,110 And then my URL patterns are going to go inside of this list right here. 1003 00:49:14,110 --> 00:49:16,260 But before I begin dealing with actual URLs, 1004 00:49:16,260 --> 00:49:19,540 the first thing I'm going to want to do is create some models. 1005 00:49:19,540 --> 00:49:22,470 Models are going to be a way of creating a Python class that 1006 00:49:22,470 --> 00:49:27,090 is going to represent data that I want Django to store inside of a database. 1007 00:49:27,090 --> 00:49:28,860 So when I create a model, Django is going 1008 00:49:28,860 --> 00:49:32,940 to figure out what SQL syntax it needs to use it to A-- create that table 1009 00:49:32,940 --> 00:49:34,740 but then B-- manipulate that table. 1010 00:49:34,740 --> 00:49:39,450 Selecting and updating and inserting anytime I make changes to those models. 1011 00:49:39,450 --> 00:49:42,990 So here what I can do is inside of every app that gets created-- 1012 00:49:42,990 --> 00:49:46,340 in this case called "flights"-- there is a models.py file. 1013 00:49:46,340 --> 00:49:48,090 We haven't looked at this before, but this 1014 00:49:48,090 --> 00:49:51,070 is going to be the place where we get to define what models 1015 00:49:51,070 --> 00:49:53,860 are going to exist for our application. 1016 00:49:53,860 --> 00:49:56,170 Every model is going to be a Python class. 1017 00:49:56,170 --> 00:49:59,560 You can think of this as having one model for each of the main tables 1018 00:49:59,560 --> 00:50:02,180 we care about storing information about. 1019 00:50:02,180 --> 00:50:04,600 So let me define a new class called "flight" 1020 00:50:04,600 --> 00:50:08,080 that is going to inherit from models dot model some creating a new class 1021 00:50:08,080 --> 00:50:11,830 called "flight" that is going to be a model. 1022 00:50:11,830 --> 00:50:15,520 Then I need to provide inside of this class all of the parameters 1023 00:50:15,520 --> 00:50:16,290 that a flight has. 1024 00:50:16,290 --> 00:50:20,050 What properties does a flight have that I might want to keep track of? 1025 00:50:20,050 --> 00:50:22,180 Well, a flight has an origin. 1026 00:50:22,180 --> 00:50:25,690 And the origin is going to be a models.charfield. 1027 00:50:25,690 --> 00:50:27,520 This is all documented on Django's website 1028 00:50:27,520 --> 00:50:30,820 in terms of the various different types of fields that exist that I 1029 00:50:30,820 --> 00:50:33,070 can include inside of a Django model. 1030 00:50:33,070 --> 00:50:35,780 Where here I'm saying here is a character field 1031 00:50:35,780 --> 00:50:39,120 whose max length is going to be 64. 1032 00:50:39,120 --> 00:50:42,460 I assume that most city names are not going to go longer than 64 characters. 1033 00:50:42,460 --> 00:50:46,180 That seems like a reasonable maximum length for the origin of the flight, 1034 00:50:46,180 --> 00:50:47,500 for example. 1035 00:50:47,500 --> 00:50:49,480 Every flight will also have a destination, 1036 00:50:49,480 --> 00:50:53,890 which will be a character field whose max length is also 64. 1037 00:50:53,890 --> 00:50:58,570 Every flight will have a duration, which will just be an integer field. 1038 00:50:58,570 --> 00:51:01,600 So now this is my very first Django model. 1039 00:51:01,600 --> 00:51:05,470 It is a class called "flight" where I've defined all of the properties 1040 00:51:05,470 --> 00:51:08,320 that a flight has, and then using Django syntax to find 1041 00:51:08,320 --> 00:51:09,880 what type they should have as well. 1042 00:51:09,880 --> 00:51:15,190 Every flight has an origin, has a destination, and has a duration. 1043 00:51:15,190 --> 00:51:17,260 But of course nothing here is actually modified 1044 00:51:17,260 --> 00:51:21,190 the database the Django is using in order to store information 1045 00:51:21,190 --> 00:51:22,390 about my web application. 1046 00:51:22,390 --> 00:51:24,710 And we can see if we, in fact go back to airline, 1047 00:51:24,710 --> 00:51:30,320 and I type LS, what you see here is that there isn't yet a database that exists. 1048 00:51:30,320 --> 00:51:33,010 I just have an airline directory, a flights directory, 1049 00:51:33,010 --> 00:51:35,530 and a manage.py file. 1050 00:51:35,530 --> 00:51:38,590 So what I'd like to do is somehow tell Django 1051 00:51:38,590 --> 00:51:42,760 that you should update the database to include information about the models 1052 00:51:42,760 --> 00:51:44,260 that I have just created. 1053 00:51:44,260 --> 00:51:47,410 This is a process that we refer to in Django and more generally as 1054 00:51:47,410 --> 00:51:48,650 migrations. 1055 00:51:48,650 --> 00:51:51,700 I create a migration to say, here are some changes 1056 00:51:51,700 --> 00:51:54,070 that I would like to apply to the database. 1057 00:51:54,070 --> 00:51:57,670 Then I migrate them to tell Django to take those changes 1058 00:51:57,670 --> 00:51:59,850 and actually apply them to the database. 1059 00:51:59,850 --> 00:52:01,180 So it's a 2-step process. 1060 00:52:01,180 --> 00:52:05,110 One is creating the migration, the instructions for how to actually go 1061 00:52:05,110 --> 00:52:06,610 about manipulating the database. 1062 00:52:06,610 --> 00:52:10,900 And then one to take that migration step of saying now take those instructions 1063 00:52:10,900 --> 00:52:14,800 and actually apply them to the underlying database. 1064 00:52:14,800 --> 00:52:17,320 We can make the migrations via command. 1065 00:52:17,320 --> 00:52:20,980 Again we'll use the manage.py script that has a number of different commands 1066 00:52:20,980 --> 00:52:23,830 that allow us to control various parts of the application. 1067 00:52:23,830 --> 00:52:29,290 I'll use python manage .py and then MAKE migrations. 1068 00:52:29,290 --> 00:52:35,590 Now what we see is we've created a migration inside of 0001_initial.py 1069 00:52:35,590 --> 00:52:40,650 where in this migration it's created a model called "flight." 1070 00:52:40,650 --> 00:52:43,870 So if I go ahead and look at the migrations directory, 1071 00:52:43,870 --> 00:52:46,760 I see this file has been created for me. 1072 00:52:46,760 --> 00:52:48,610 I didn't have to create it myself. 1073 00:52:48,610 --> 00:52:52,330 This file has instructions to Django for how 1074 00:52:52,330 --> 00:52:56,800 to manipulate the database to reflect the changes I have made to the model. 1075 00:52:56,800 --> 00:53:00,550 Here is an instruction to Django to create a new model called 1076 00:53:00,550 --> 00:53:04,300 "flight" that has these particular fields inside of it. 1077 00:53:04,300 --> 00:53:07,690 It's basing this off of the changes that I made to models.py. 1078 00:53:07,690 --> 00:53:12,280 The model that I added is now reflected in this migration. 1079 00:53:12,280 --> 00:53:16,750 Now if I want to apply the migration, actually apply it to Django's database, 1080 00:53:16,750 --> 00:53:22,292 I can run python manage.py MIGRATE to go ahead and apply these migrations. 1081 00:53:22,292 --> 00:53:25,000 There are a bunch of default migrations that get applied as well, 1082 00:53:25,000 --> 00:53:28,870 but notice that one of the migrations that gets applied is this one here. 1083 00:53:28,870 --> 00:53:33,970 Applying flights.0001_initial to say let's go ahead and apply that 1084 00:53:33,970 --> 00:53:38,500 migration, create that table that is going to represent flights. 1085 00:53:38,500 --> 00:53:43,870 If I type LS now, you'll see that now I have a db.sqlite3 file. 1086 00:53:43,870 --> 00:53:47,590 A SQLite database that is going to contain a table that 1087 00:53:47,590 --> 00:53:50,417 is going to store all of my flights. 1088 00:53:50,417 --> 00:53:52,750 And so how can I actually begin to manipulate this data? 1089 00:53:52,750 --> 00:53:56,470 How can I interact with these sorts of models? 1090 00:53:56,470 --> 00:53:59,980 I could use direct SQL syntax by opening up this database file 1091 00:53:59,980 --> 00:54:03,490 and running commands, but Django provides some nice abstraction layers 1092 00:54:03,490 --> 00:54:07,120 on top of it so that I don't actually need to execute those commands myself. 1093 00:54:07,120 --> 00:54:11,530 I can begin to work more generally with Python classes and variables and things 1094 00:54:11,530 --> 00:54:14,410 that I'm used to inside the Python language. 1095 00:54:14,410 --> 00:54:18,167 So I can enter Django's shell where I can just run Python commands 1096 00:54:18,167 --> 00:54:19,584 by running python manage.py shell. 1097 00:54:19,584 --> 00:54:22,750 1098 00:54:22,750 --> 00:54:24,670 What this does is open up a shell or a console 1099 00:54:24,670 --> 00:54:27,700 where I can begin to write Python commands that 1100 00:54:27,700 --> 00:54:30,370 get executed on this web application. 1101 00:54:30,370 --> 00:54:34,360 The first thing I'd like to do is from flights.models 1102 00:54:34,360 --> 00:54:37,020 let me just import flight. 1103 00:54:37,020 --> 00:54:38,500 So "flights" is the name of my app. 1104 00:54:38,500 --> 00:54:40,240 "Models" is the name of that file. 1105 00:54:40,240 --> 00:54:44,770 I'm importing the flight class from that models file that I've just created. 1106 00:54:44,770 --> 00:54:48,310 Now what I can do is I can create a new flight. 1107 00:54:48,310 --> 00:54:54,190 I can say something like f= a flight whose origin is "New York" 1108 00:54:54,190 --> 00:55:00,820 and whose destination is "London" and whose duration= 415 minutes. 1109 00:55:00,820 --> 00:55:05,262 And then I can say f.save to save that new flight that I have created. 1110 00:55:05,262 --> 00:55:08,470 This syntax-- I'll go ahead and make it a little bit bigger so you can see it 1111 00:55:08,470 --> 00:55:09,460 a little easier-- 1112 00:55:09,460 --> 00:55:13,130 is my way of inserting data into this table. 1113 00:55:13,130 --> 00:55:15,520 I don't need to use an INSERT query in SQL. 1114 00:55:15,520 --> 00:55:18,610 I just have to write a Python command, and Django knows that when 1115 00:55:18,610 --> 00:55:22,750 I create a new flight and save it, that it should run an instant command 1116 00:55:22,750 --> 00:55:24,340 on the underlying SQL tables. 1117 00:55:24,340 --> 00:55:27,910 Where here I've created a new flight with this particular origin 1118 00:55:27,910 --> 00:55:32,020 and destination and duration and I've gone ahead and saved that flight as 1119 00:55:32,020 --> 00:55:33,060 well. 1120 00:55:33,060 --> 00:55:36,160 If I want to query that flight, get information about that flight, 1121 00:55:36,160 --> 00:55:40,240 I can say something like, flight.objects.all 1122 00:55:40,240 --> 00:55:42,010 is the equivalent of a Select-All. 1123 00:55:42,010 --> 00:55:46,390 Get me all of the flights that exist inside of my database. 1124 00:55:46,390 --> 00:55:50,740 Here I see I get back a query set, which is just a set of results. 1125 00:55:50,740 --> 00:55:54,070 Here I have one flight that came back, flight object 1. 1126 00:55:54,070 --> 00:55:56,830 So a flight has been created for me with ID 1. 1127 00:55:56,830 --> 00:55:59,950 Now "flight object 1" probably not all that helpful of a name. 1128 00:55:59,950 --> 00:56:02,110 It'd be nicer if this model had a cleaner 1129 00:56:02,110 --> 00:56:06,340 way of seeing the name of a particular flight, for example. 1130 00:56:06,340 --> 00:56:08,140 And it turns out we can do that. 1131 00:56:08,140 --> 00:56:11,680 Any model-- I'll go back to the code inside of models.py-- 1132 00:56:11,680 --> 00:56:15,520 any model can implement double underscore str 1133 00:56:15,520 --> 00:56:19,720 function, which returns a string representation 1134 00:56:19,720 --> 00:56:21,610 of that particular object. 1135 00:56:21,610 --> 00:56:25,090 This applies not just to Django models but to Python classes more generally. 1136 00:56:25,090 --> 00:56:28,400 That if this function returns a string representation of the object, 1137 00:56:28,400 --> 00:56:33,100 let's go ahead and return a formatted string that is self.id 1138 00:56:33,100 --> 00:56:37,420 I'll say self.origin to self.destination. 1139 00:56:37,420 --> 00:56:41,530 So here what I've said is that the string representation of any flight 1140 00:56:41,530 --> 00:56:46,030 is going to be a string that gives its ID and then says origin to destination. 1141 00:56:46,030 --> 00:56:50,800 Just a nice clean name that is going to represent this particular flight. 1142 00:56:50,800 --> 00:56:57,150 So now if I go back to the shell by running python manage.py shell, 1143 00:56:57,150 --> 00:57:00,658 I can say from flights.models import Flight. 1144 00:57:00,658 --> 00:57:02,700 I can say, all right, let's let a variable called 1145 00:57:02,700 --> 00:57:06,360 flights be equal to flight.objects.all. 1146 00:57:06,360 --> 00:57:11,760 And now flights is going to be this flight, flight 1, New York to London. 1147 00:57:11,760 --> 00:57:13,980 It now has a much nicer, string representation 1148 00:57:13,980 --> 00:57:17,670 of the name, which just makes it a little bit easier to interact with. 1149 00:57:17,670 --> 00:57:23,010 If I wanted to get just that one flight, I can say flight equals flights.first. 1150 00:57:23,010 --> 00:57:24,480 Flights is a query set. 1151 00:57:24,480 --> 00:57:26,920 First gets me that first flight. 1152 00:57:26,920 --> 00:57:30,420 And so now, I have this flight from New York to London. 1153 00:57:30,420 --> 00:57:32,550 And just as in any Python object, I can begin 1154 00:57:32,550 --> 00:57:34,730 to access properties of that object. 1155 00:57:34,730 --> 00:57:36,990 I can say, all right, flight, what is your ID? 1156 00:57:36,990 --> 00:57:38,760 Flight, what is your origin? 1157 00:57:38,760 --> 00:57:40,200 Flight, what is your destination? 1158 00:57:40,200 --> 00:57:41,830 Flight, what is your duration? 1159 00:57:41,830 --> 00:57:45,370 And I can access, as values, all of the properties of this flight 1160 00:57:45,370 --> 00:57:46,590 that I ultimately care about. 1161 00:57:46,590 --> 00:57:51,720 And if I want to delete the flight, I can say something like flight.delete. 1162 00:57:51,720 --> 00:57:54,510 Now, ultimately though, this is not the model 1163 00:57:54,510 --> 00:57:56,610 that I actually want to represent my flight. 1164 00:57:56,610 --> 00:58:00,090 Because here again, I'm using a character field, a char field, 1165 00:58:00,090 --> 00:58:02,010 for things like origin and destination. 1166 00:58:02,010 --> 00:58:06,390 When in reality, I'd probably like to use something like another table 1167 00:58:06,390 --> 00:58:09,750 for representing airports, and then some relationship 1168 00:58:09,750 --> 00:58:12,090 between every flight and an airport. 1169 00:58:12,090 --> 00:58:14,550 So let's go ahead and try and implement that idea now, 1170 00:58:14,550 --> 00:58:18,840 that I can go back into models.py and create a new class. 1171 00:58:18,840 --> 00:58:21,240 I'll create a class called airport. 1172 00:58:21,240 --> 00:58:23,010 That is also a model. 1173 00:58:23,010 --> 00:58:25,530 And I'd like for this airport class to have 1174 00:58:25,530 --> 00:58:30,960 a code, which is a character field with a max length of 3, 1175 00:58:30,960 --> 00:58:32,430 for the airports code. 1176 00:58:32,430 --> 00:58:34,920 As well as a city, which would be a character field 1177 00:58:34,920 --> 00:58:37,710 with a max length of 64. 1178 00:58:37,710 --> 00:58:41,500 And let's also give this airport a string representation. 1179 00:58:41,500 --> 00:58:43,890 We'll say that the string representation of an airport 1180 00:58:43,890 --> 00:58:48,180 will just be the city of the airport, and then in parentheses, 1181 00:58:48,180 --> 00:58:49,280 the code of the airport. 1182 00:58:49,280 --> 00:58:51,863 So it will be something like New York, and then in parentheses 1183 00:58:51,863 --> 00:58:56,010 JFK to represent a particular airport. 1184 00:58:56,010 --> 00:58:59,280 And now, our flight model needs to change a little bit. 1185 00:58:59,280 --> 00:59:02,550 No longer will origin and destination be character fields 1186 00:59:02,550 --> 00:59:04,080 that are just storing text. 1187 00:59:04,080 --> 00:59:08,910 But instead, origin is going to be a foreign key. 1188 00:59:08,910 --> 00:59:14,067 A foreign key that references another table, like the airport table. 1189 00:59:14,067 --> 00:59:16,150 And then, I can provide some additional arguments. 1190 00:59:16,150 --> 00:59:17,500 So this alone would be enough. 1191 00:59:17,500 --> 00:59:23,070 But I can add some additional arguments like on delete equals models.cascade. 1192 00:59:23,070 --> 00:59:24,280 So what does this mean? 1193 00:59:24,280 --> 00:59:27,332 Well, when I have tables that are related to each other, 1194 00:59:27,332 --> 00:59:29,040 SQL needs some way of knowing what should 1195 00:59:29,040 --> 00:59:30,870 happen if you ever delete something. 1196 00:59:30,870 --> 00:59:35,940 If I have a flight from JFK to London, and later in time 1197 00:59:35,940 --> 00:59:38,730 decide to delete JFK airport from my database, 1198 00:59:38,730 --> 00:59:40,380 what should happen to that flight? 1199 00:59:40,380 --> 00:59:44,820 What happens to flights when the thing that it is referencing gets deleted? 1200 00:59:44,820 --> 00:59:47,760 What models.cascade means is if I were to ever delete 1201 00:59:47,760 --> 00:59:51,300 an airport from the airports table, it's going to also delete 1202 00:59:51,300 --> 00:59:53,037 any of the corresponding flights. 1203 00:59:53,037 --> 00:59:54,870 And there are other on delete parameters you 1204 00:59:54,870 --> 00:59:57,440 can set for saying like, don't even let me delete an airport 1205 00:59:57,440 --> 00:59:59,190 if there are flights that are leaving from 1206 00:59:59,190 --> 01:00:02,250 or going to that airport, that's called models.protect. 1207 01:00:02,250 --> 01:00:06,660 But there are other ways of implementing similar types of constraints. 1208 01:00:06,660 --> 01:00:08,700 And the other argument that I'm going to provide 1209 01:00:08,700 --> 01:00:11,190 is what's called a related name. 1210 01:00:11,190 --> 01:00:13,530 And a related name, as we'll see in just a moment, 1211 01:00:13,530 --> 01:00:18,000 is going to be a way of me accessing a relationship in the reverse order. 1212 01:00:18,000 --> 01:00:22,170 That from a flight, I can take a flight and say .origin to get 1213 01:00:22,170 --> 01:00:23,818 the flight's origin in airport. 1214 01:00:23,818 --> 01:00:26,610 But the other question I might want to ask is in the reverse order. 1215 01:00:26,610 --> 01:00:30,210 If I have an airport, how do I get all of the flights that 1216 01:00:30,210 --> 01:00:32,040 have that airport as an origin? 1217 01:00:32,040 --> 01:00:35,280 And so here, if I give a related name to this foreign key, 1218 01:00:35,280 --> 01:00:38,010 Django will automatically set up the relationship 1219 01:00:38,010 --> 01:00:40,000 going in that opposite direction. 1220 01:00:40,000 --> 01:00:42,390 And so here, well, if we have an airport, 1221 01:00:42,390 --> 01:00:45,750 and I want to know all of the flights that have that airport as their origin, 1222 01:00:45,750 --> 01:00:50,430 the reasonable name for a related name here is something like departures. 1223 01:00:50,430 --> 01:00:53,040 So if I have an airport, I can access all 1224 01:00:53,040 --> 01:00:55,950 of the departures, which gets me all of the flights that 1225 01:00:55,950 --> 01:00:58,350 are leaving from that airport. 1226 01:00:58,350 --> 01:01:00,960 And I'll likewise do the same thing here for destination. 1227 01:01:00,960 --> 01:01:04,080 Instead of a character field it's going to be a foreign key. 1228 01:01:04,080 --> 01:01:06,000 It's going to reference airport. 1229 01:01:06,000 --> 01:01:08,310 When we delete it, we'll go ahead and cascade it. 1230 01:01:08,310 --> 01:01:11,160 And the related name will be arrivals. 1231 01:01:11,160 --> 01:01:13,050 Because if I have an airport, I might want 1232 01:01:13,050 --> 01:01:15,810 to access all of the arrivals, all of the flights 1233 01:01:15,810 --> 01:01:18,720 that correspond to flights that are arriving 1234 01:01:18,720 --> 01:01:21,395 at that particular destination. 1235 01:01:21,395 --> 01:01:22,770 And so now, I've done two things. 1236 01:01:22,770 --> 01:01:25,620 I've added a new class called airport, and I've 1237 01:01:25,620 --> 01:01:28,380 modified my existing flight model. 1238 01:01:28,380 --> 01:01:31,110 So this has changed in my Python code, but it hasn't yet 1239 01:01:31,110 --> 01:01:32,890 changed in my database. 1240 01:01:32,890 --> 01:01:36,670 So in order to make the change in the database, again, it's a 2-step process. 1241 01:01:36,670 --> 01:01:40,500 Step one, python manage.py, make migrations, 1242 01:01:40,500 --> 01:01:45,370 to say look for any new changes that have been made to models.py, 1243 01:01:45,370 --> 01:01:48,330 and go ahead and create a migration instruction 1244 01:01:48,330 --> 01:01:50,950 for how to make those changes to the database. 1245 01:01:50,950 --> 01:01:55,440 And here, we see that we've created a new migration file. 1246 01:01:55,440 --> 01:01:58,530 And this migration is going to create a model called airport. 1247 01:01:58,530 --> 01:02:01,380 And it's also going to alter the destination field 1248 01:02:01,380 --> 01:02:04,290 and alter the origin field on my flight model. 1249 01:02:04,290 --> 01:02:07,350 Because as we know, we've changed destination and origin 1250 01:02:07,350 --> 01:02:09,780 to no longer be character fields, but to instead 1251 01:02:09,780 --> 01:02:11,880 be references to a particular airport. 1252 01:02:11,880 --> 01:02:15,030 So that's something that's going to need to change in the database. 1253 01:02:15,030 --> 01:02:19,890 And to make that change, I can run something like python manage.py 1254 01:02:19,890 --> 01:02:22,470 migrate to go ahead and apply those changes. 1255 01:02:22,470 --> 01:02:25,560 We've now applied this migration that we just created, 1256 01:02:25,560 --> 01:02:28,950 and our database is now up to date. 1257 01:02:28,950 --> 01:02:30,190 So what can we do? 1258 01:02:30,190 --> 01:02:33,150 Well, now I can go ahead and go back into the shell. 1259 01:02:33,150 --> 01:02:36,420 And I'll just go ahead and import from flights.models import 1260 01:02:36,420 --> 01:02:38,650 star, import everything. 1261 01:02:38,650 --> 01:02:40,320 And I can now create an airport. 1262 01:02:40,320 --> 01:02:46,260 I can say something like JFK equals an airport whose code is JFK 1263 01:02:46,260 --> 01:02:49,440 and whose city is New York, for example. 1264 01:02:49,440 --> 01:02:50,880 And then save that. 1265 01:02:50,880 --> 01:02:52,050 I can create a London one. 1266 01:02:52,050 --> 01:02:57,650 So LHR is an airport whose code is LHR and whose city is London. 1267 01:02:57,650 --> 01:02:59,063 And I can save that. 1268 01:02:59,063 --> 01:02:59,980 You could create more. 1269 01:02:59,980 --> 01:03:06,270 I could say CDG equals an airport whose code is CDG and city is Paris. 1270 01:03:06,270 --> 01:03:07,670 And maybe we'll do one more. 1271 01:03:07,670 --> 01:03:13,110 We'll say NRT is the airport whose code is NRT and whose city is Tokyo, 1272 01:03:13,110 --> 01:03:14,200 for example. 1273 01:03:14,200 --> 01:03:18,480 So I've created and saved four airports that get added to my airport table. 1274 01:03:18,480 --> 01:03:20,730 And now, I can add a flight. 1275 01:03:20,730 --> 01:03:26,700 F equals flight whose origin equals JFK whose destination equals 1276 01:03:26,700 --> 01:03:31,040 London Heathrow and whose duration equals 415 minutes. 1277 01:03:31,040 --> 01:03:35,123 And I'll go ahead and save that as well. 1278 01:03:35,123 --> 01:03:36,540 So I've now created four airports. 1279 01:03:36,540 --> 01:03:38,670 I've created a flight and saved it. 1280 01:03:38,670 --> 01:03:42,510 If I type F just for my flight, I see that, all right, 1281 01:03:42,510 --> 01:03:45,240 this is a flight from New York to London. 1282 01:03:45,240 --> 01:03:50,730 But I can also say what is f.origin, and to write f.origin, 1283 01:03:50,730 --> 01:03:52,650 that is now an airport object. 1284 01:03:52,650 --> 01:03:53,910 It's JFK, in particular. 1285 01:03:53,910 --> 01:03:58,490 And I can do f.origin.city to get the city of the origin, which is New York. 1286 01:03:58,490 --> 01:04:02,820 f.origin.code to get the code of that airport, which is JFK. 1287 01:04:02,820 --> 01:04:06,720 And if I start with an origin, something like JFK or London Heathrow, 1288 01:04:06,720 --> 01:04:13,200 I can say LHR.arrivals.all to get all of the arrivals, all 1289 01:04:13,200 --> 01:04:15,238 of the flights arriving in London Heathrow. 1290 01:04:15,238 --> 01:04:17,280 And it looks like there's just one of them, which 1291 01:04:17,280 --> 01:04:20,430 is this flight that I've just created from New York that 1292 01:04:20,430 --> 01:04:23,010 is going to London as well. 1293 01:04:23,010 --> 01:04:26,310 And so this now gives us the ability to manipulate SQL 1294 01:04:26,310 --> 01:04:28,130 just by using these Python models. 1295 01:04:28,130 --> 01:04:30,720 And I now have Python classes that represent 1296 01:04:30,720 --> 01:04:32,910 all of these various different types of data. 1297 01:04:32,910 --> 01:04:35,520 And now, instead of running SQL queries like select star 1298 01:04:35,520 --> 01:04:37,680 from flights or from airports, I can just 1299 01:04:37,680 --> 01:04:41,580 interact with these classes and these properties on the classes, 1300 01:04:41,580 --> 01:04:43,860 and Django takes care of the process for me 1301 01:04:43,860 --> 01:04:46,890 of figuring out what the underlying SQL queries should be, 1302 01:04:46,890 --> 01:04:50,670 executing those queries, and just giving those results back to me. 1303 01:04:50,670 --> 01:04:54,570 And we can begin now to design a web application around this idea. 1304 01:04:54,570 --> 01:04:58,530 That I can go into urls.py, and lets add a url pattern that says, 1305 01:04:58,530 --> 01:04:59,970 the default route. 1306 01:04:59,970 --> 01:05:02,160 We'll go ahead and load the index view. 1307 01:05:02,160 --> 01:05:03,360 Give it a name of index. 1308 01:05:03,360 --> 01:05:06,480 Same as similar things we've seen from last time. 1309 01:05:06,480 --> 01:05:09,540 And now, what should we do in the index view? 1310 01:05:09,540 --> 01:05:13,710 Well, the index view, let's go ahead and say, what I would like to do 1311 01:05:13,710 --> 01:05:16,650 is just display a list of all the flights. 1312 01:05:16,650 --> 01:05:22,410 So I might from.models import flight and airport. 1313 01:05:22,410 --> 01:05:23,707 Or maybe I just need flight. 1314 01:05:23,707 --> 01:05:25,290 I just want a list of all the flights. 1315 01:05:25,290 --> 01:05:28,180 So I'm going to import flight from all of my models. 1316 01:05:28,180 --> 01:05:30,540 And now, what I'd like to do is return-- 1317 01:05:30,540 --> 01:05:35,340 let's go ahead and render a template called flight/index.html, 1318 01:05:35,340 --> 01:05:39,960 and give index.html access to a variable called flights. 1319 01:05:39,960 --> 01:05:42,230 And what is that variable going to be equal to? 1320 01:05:42,230 --> 01:05:45,480 It's going to be equal to flight.objects.all 1321 01:05:45,480 --> 01:05:50,370 to get me all of the flights that I would like to put right here. 1322 01:05:50,370 --> 01:05:52,030 All right, so what can I do from now? 1323 01:05:52,030 --> 01:05:55,410 Now, what I need to do is actually create those individual templates. 1324 01:05:55,410 --> 01:06:00,420 So inside of flights, I'll create a new folder called templates. 1325 01:06:00,420 --> 01:06:03,300 Inside of which I'll create a new folder called flights. 1326 01:06:03,300 --> 01:06:06,030 Inside of which I'll go ahead and create a layout.html, 1327 01:06:06,030 --> 01:06:08,250 much as we've done before, where that layout 1328 01:06:08,250 --> 01:06:12,810 is going to contain the basic structure of our HTML page. 1329 01:06:12,810 --> 01:06:18,910 So a head section whose title is flights, and a body section that 1330 01:06:18,910 --> 01:06:24,220 is going to have a block body, and the end of the block. 1331 01:06:24,220 --> 01:06:28,420 Much as before, this is the default layout for this particular page. 1332 01:06:28,420 --> 01:06:33,730 And then, I'll add a new template called index.html that is going to extend 1333 01:06:33,730 --> 01:06:36,400 flight/layout.html. 1334 01:06:36,400 --> 01:06:40,630 And then, inside the body of the page, I'm 1335 01:06:40,630 --> 01:06:43,840 going to display an H1 that just says flights. 1336 01:06:43,840 --> 01:06:46,510 And let's now create an unordered list where I can now 1337 01:06:46,510 --> 01:06:50,020 loop over for flight in flights. 1338 01:06:50,020 --> 01:06:51,550 endfor to end the loop. 1339 01:06:51,550 --> 01:06:54,200 But inside the loop, let me create a list item 1340 01:06:54,200 --> 01:06:57,640 where I just print out a flight-- 1341 01:06:57,640 --> 01:07:01,540 maybe I'll print out the flight and then flight.id 1342 01:07:01,540 --> 01:07:03,940 to print out flight 1, flight 2, flight 3. 1343 01:07:03,940 --> 01:07:10,040 And then, I'll print flight.origin to flight.destination. 1344 01:07:10,040 --> 01:07:13,360 So what I've done here is create a template 1345 01:07:13,360 --> 01:07:16,930 that I'm going to give access to a variable called flights, where flights 1346 01:07:16,930 --> 01:07:20,320 is going to be a variable that represents all of the flights 1347 01:07:20,320 --> 01:07:24,040 that I queried by running flight.objects.all, that is my way 1348 01:07:24,040 --> 01:07:27,520 using Django's API, using the functions that it has given me access to, 1349 01:07:27,520 --> 01:07:32,080 to say, take the flight and get all of the flights that are stored inside 1350 01:07:32,080 --> 01:07:33,520 of Django's database. 1351 01:07:33,520 --> 01:07:36,820 Then here in the template, I'm looping over each one of those flights. 1352 01:07:36,820 --> 01:07:39,730 For each one, printing out a list item where 1353 01:07:39,730 --> 01:07:41,600 I can access properties of that flight. 1354 01:07:41,600 --> 01:07:48,670 Say flight this ID from origin to a particular destination. 1355 01:07:48,670 --> 01:07:51,390 So now, I'll go ahead and go into my terminal. 1356 01:07:51,390 --> 01:07:54,540 Run python manage.py run server, which again, 1357 01:07:54,540 --> 01:07:56,730 is how we run a Django web application. 1358 01:07:56,730 --> 01:07:59,940 And now, if I go to that URL, flash flights this time, 1359 01:07:59,940 --> 01:08:01,140 because that's the URL. 1360 01:08:01,140 --> 01:08:03,330 What I see is exactly what I'd expect to see. 1361 01:08:03,330 --> 01:08:08,910 An unordered list that just so happens to have flight one New York to London 1362 01:08:08,910 --> 01:08:09,710 displayed there. 1363 01:08:09,710 --> 01:08:13,200 It is taking data from my database, and now displaying it inside 1364 01:08:13,200 --> 01:08:14,150 of this template. 1365 01:08:14,150 --> 01:08:18,399 And if I were to add new flights, it would also update on this page as well. 1366 01:08:18,399 --> 01:08:22,620 So if I go ahead and go back, go into the shell, python manage.py shell. 1367 01:08:22,620 --> 01:08:26,189 I'll go from flights.models import star. 1368 01:08:26,189 --> 01:08:28,529 Let's go ahead and-- 1369 01:08:28,529 --> 01:08:31,569 well, all right, let's add a flight from Shanghai to Paris, for example. 1370 01:08:31,569 --> 01:08:34,620 Well, how do I get the airports for Shanghai and Paris? 1371 01:08:34,620 --> 01:08:37,330 Well, it turns out that if I want to get Shanghai, 1372 01:08:37,330 --> 01:08:42,359 I can say Shanghai equals airport.objects. 1373 01:08:42,359 --> 01:08:43,560 and then I can say-- 1374 01:08:43,560 --> 01:08:48,819 if I do airport.objects.all, that gets me all of the airports, for example. 1375 01:08:48,819 --> 01:08:51,330 Oh, and it seems I don't actually have a Shanghai one, 1376 01:08:51,330 --> 01:08:53,140 but I can add one if I wanted to. 1377 01:08:53,140 --> 01:08:57,130 But if I do airport.objects.all, that, again, gives me all of them. 1378 01:08:57,130 --> 01:09:00,300 But if I want to filter my airports list, not get all of the airports 1379 01:09:00,300 --> 01:09:06,120 but just get some of them, I can say airport.objects.filter, and I can say, 1380 01:09:06,120 --> 01:09:12,529 get me all the airports where the city is New York, for example. 1381 01:09:12,529 --> 01:09:15,450 And that is going to go ahead and give me a query set that only 1382 01:09:15,450 --> 01:09:17,520 contains the results that I care about. 1383 01:09:17,520 --> 01:09:22,080 So again, airport.objects.filter lets me constrain the results that come back. 1384 01:09:22,080 --> 01:09:24,540 Not get me all of the airports, but only get me 1385 01:09:24,540 --> 01:09:27,240 airports whose city is New York, for example. 1386 01:09:27,240 --> 01:09:30,540 And it is only giving back one, so I could say .filter city equals New 1387 01:09:30,540 --> 01:09:35,069 York.first, to say, take that query set, and just get me the first and only 1388 01:09:35,069 --> 01:09:36,270 thing in that query set. 1389 01:09:36,270 --> 01:09:38,990 And that gives me airport New York. 1390 01:09:38,990 --> 01:09:40,710 A simplified way of doing the same thing. 1391 01:09:40,710 --> 01:09:43,560 If you know you're only going to get one result back, is I 1392 01:09:43,560 --> 01:09:45,660 can say something like airport.objects.get, 1393 01:09:45,660 --> 01:09:50,010 which will only get one result if it knows that there's only going to be 1394 01:09:50,010 --> 01:09:52,260 one airport with the city of New York. 1395 01:09:52,260 --> 01:09:56,640 That too will return to me New York JFK airport. 1396 01:09:56,640 --> 01:09:59,020 But it will throw an error if ever there's more than one, 1397 01:09:59,020 --> 01:10:00,780 or if there's none, for example. 1398 01:10:00,780 --> 01:10:02,850 So we'll go in and save that inside of JFK, 1399 01:10:02,850 --> 01:10:05,490 and we'll go ahead and create a flight that is going 1400 01:10:05,490 --> 01:10:07,880 from New York to Paris, for example. 1401 01:10:07,880 --> 01:10:11,576 I can do CDG equals airport.objects.get. 1402 01:10:11,576 --> 01:10:13,350 City equals Paris. 1403 01:10:13,350 --> 01:10:17,370 And now, I have this variable CDG, which represents the airport Paris. 1404 01:10:17,370 --> 01:10:21,390 And if I want to create a new flight that goes from New York to Paris, 1405 01:10:21,390 --> 01:10:23,580 I can say F is going to be a flight whose 1406 01:10:23,580 --> 01:10:30,380 origin is JFK whose destination equals CDG and whose duration equals 435. 1407 01:10:30,380 --> 01:10:33,100 And I can save that flight as well. 1408 01:10:33,100 --> 01:10:34,970 And so I've added a new flight. 1409 01:10:34,970 --> 01:10:38,700 And so now, if I run the server, python manage.py, 1410 01:10:38,700 --> 01:10:43,600 run server, refresh the page, I now see that I have two flights. 1411 01:10:43,600 --> 01:10:46,350 One flight that's going from New York to London, one flight that's 1412 01:10:46,350 --> 01:10:48,330 going from New York to Paris. 1413 01:10:48,330 --> 01:10:51,090 But of course, it's going to be pretty annoying if every time I 1414 01:10:51,090 --> 01:10:54,270 want to update the data, adding new data, manipulating the data, 1415 01:10:54,270 --> 01:10:56,820 I need to go into the shell in order to run 1416 01:10:56,820 --> 01:10:58,950 direct commands that are able to add new flights, 1417 01:10:58,950 --> 01:11:00,810 add new airport, so on and so forth. 1418 01:11:00,810 --> 01:11:03,810 What I'd really like to be able to do is just very simply 1419 01:11:03,810 --> 01:11:05,638 to add it via a web interface. 1420 01:11:05,638 --> 01:11:07,430 Via the web, be able to say, all right, let 1421 01:11:07,430 --> 01:11:11,250 me add a new flight that goes from location 1 to location 2. 1422 01:11:11,250 --> 01:11:13,890 And it's possible, using the information we know now, 1423 01:11:13,890 --> 01:11:16,620 to build a web page that does just this. 1424 01:11:16,620 --> 01:11:20,460 But Django is built on this idea that it doesn't want you, the programmer, 1425 01:11:20,460 --> 01:11:23,460 to have to repeat work that other people have already done. 1426 01:11:23,460 --> 01:11:27,090 And this process of trying to define models and very quickly be 1427 01:11:27,090 --> 01:11:29,160 able to create and edit and manipulate models 1428 01:11:29,160 --> 01:11:34,230 is so common that Django has already built for us an entire app that is just 1429 01:11:34,230 --> 01:11:36,960 designed for the manipulation of these models, 1430 01:11:36,960 --> 01:11:40,020 and it's known as the Django admin app. 1431 01:11:40,020 --> 01:11:42,510 And this is an app that we've seen traces of already, 1432 01:11:42,510 --> 01:11:48,660 that if we remember that urls.py file from inside of our application. 1433 01:11:48,660 --> 01:11:51,600 We saw that we added a path for our own app, 1434 01:11:51,600 --> 01:11:55,560 but there was already a path given to us by default, /admin, 1435 01:11:55,560 --> 01:11:58,930 that takes us to the admin app as well. 1436 01:11:58,930 --> 01:12:01,470 And so in order to use the admin app, we need 1437 01:12:01,470 --> 01:12:05,760 to create an administrative account inside of our Django web application. 1438 01:12:05,760 --> 01:12:08,100 And the way to do that is via the command line. 1439 01:12:08,100 --> 01:12:12,875 I can run python manage.py create super user. 1440 01:12:12,875 --> 01:12:14,250 It's going to ask me for my name. 1441 01:12:14,250 --> 01:12:18,020 I'll go in and type in my user name, my email address, 1442 01:12:18,020 --> 01:12:19,770 and it's also going to ask for a password. 1443 01:12:19,770 --> 01:12:22,560 I can just make up a password that I would like to use. 1444 01:12:22,560 --> 01:12:24,420 Retype it in just to confirm it. 1445 01:12:24,420 --> 01:12:30,030 And now, Django has created a super user account for me in this web application 1446 01:12:30,030 --> 01:12:32,940 so that I, using these credentials, have the ability 1447 01:12:32,940 --> 01:12:35,400 to visit the web interface for the admin app 1448 01:12:35,400 --> 01:12:39,520 and actually manipulate some of these underlying models. 1449 01:12:39,520 --> 01:12:43,350 So in order to do this, the first thing I need to do is take my models 1450 01:12:43,350 --> 01:12:45,780 and add those models to the admin app. 1451 01:12:45,780 --> 01:12:49,500 So inside of models.py, I have a class called airport and a class 1452 01:12:49,500 --> 01:12:51,010 called flight. 1453 01:12:51,010 --> 01:12:52,890 And if we look at the files I have, there's 1454 01:12:52,890 --> 01:12:57,660 another file we haven't really looked at yet called admin.py inside of my app. 1455 01:12:57,660 --> 01:13:03,900 And inside of admin.py, I'll first from my models import flight and airport. 1456 01:13:03,900 --> 01:13:08,310 And now, I'm going to say, admin.site.register airport. 1457 01:13:08,310 --> 01:13:13,190 And admin.site.register flight. 1458 01:13:13,190 --> 01:13:15,300 And what this is going to do is it is going 1459 01:13:15,300 --> 01:13:18,870 to tell Django's admin app that I would like to use the admin 1460 01:13:18,870 --> 01:13:23,460 app to be able to manipulate airports and to be able to manipulate flights 1461 01:13:23,460 --> 01:13:25,240 as well. 1462 01:13:25,240 --> 01:13:28,710 So let's take a look at this admin app and see how it actually works. 1463 01:13:28,710 --> 01:13:33,060 I can run python manage.py run server. 1464 01:13:33,060 --> 01:13:34,980 That will start up the web server. 1465 01:13:34,980 --> 01:13:36,210 I'll now visit this URL. 1466 01:13:36,210 --> 01:13:39,600 Instead of going to /flights I'll go /admin. 1467 01:13:39,600 --> 01:13:43,540 And this opens up this Django administration app 1468 01:13:43,540 --> 01:13:44,670 that is not written by me. 1469 01:13:44,670 --> 01:13:47,490 Django has written this, and it's asking me to log in. 1470 01:13:47,490 --> 01:13:50,850 I'll go ahead and log in using those credentials I used a moment ago, typing 1471 01:13:50,850 --> 01:13:52,440 in my username and password. 1472 01:13:52,440 --> 01:13:56,940 And what I get here is Django's site administration interface. 1473 01:13:56,940 --> 01:14:00,030 Built for me by Django, where I didn't need to design this at all. 1474 01:14:00,030 --> 01:14:02,130 But importantly, if we noticed down here, 1475 01:14:02,130 --> 01:14:04,500 I now have the ability to add and manipulate 1476 01:14:04,500 --> 01:14:08,010 airports and flights via this web interface, this Django 1477 01:14:08,010 --> 01:14:09,160 administrative interface. 1478 01:14:09,160 --> 01:14:11,700 So now, using this interface, I have the ability 1479 01:14:11,700 --> 01:14:13,650 to manipulate the underlying database. 1480 01:14:13,650 --> 01:14:18,390 To manipulate my models to add and modify data that already exists. 1481 01:14:18,390 --> 01:14:21,170 So if I click on airports, for example, I see here, 1482 01:14:21,170 --> 01:14:24,150 here are all of the airports that I've already added to my database. 1483 01:14:24,150 --> 01:14:26,370 Tokyo, Paris, London, and New York. 1484 01:14:26,370 --> 01:14:27,450 And I can add a new one. 1485 01:14:27,450 --> 01:14:31,590 I can say, let's go ahead and add PVG which is Shanghai. 1486 01:14:31,590 --> 01:14:34,910 And I can either save it, save and continue editing, save and add another. 1487 01:14:34,910 --> 01:14:37,785 I'm going to add a couple, so I'll go ahead and save and add another. 1488 01:14:37,785 --> 01:14:40,710 Let's go ahead and add Istanbul airport as well. 1489 01:14:40,710 --> 01:14:47,550 Let's add Moscow as an airport two, and maybe one more, we'll add Lima as well. 1490 01:14:47,550 --> 01:14:49,170 And I'll just go ahead and click save. 1491 01:14:49,170 --> 01:14:52,770 And now, I've added a whole bunch of airports all via this web interface. 1492 01:14:52,770 --> 01:14:55,790 Django was originally created for news organization that 1493 01:14:55,790 --> 01:14:58,440 very quickly wanted to be able to post articles and post 1494 01:14:58,440 --> 01:14:59,850 new posts on their website. 1495 01:14:59,850 --> 01:15:02,280 And it made it very easy via an interface like this 1496 01:15:02,280 --> 01:15:04,800 to very quickly just say, here, add a new article 1497 01:15:04,800 --> 01:15:08,070 and here's the content of the article, to be able to display on a page. 1498 01:15:08,070 --> 01:15:11,880 And now, we've been able to very quickly add new airports to our website 1499 01:15:11,880 --> 01:15:12,910 as well. 1500 01:15:12,910 --> 01:15:18,000 And so if we want to add flights, well, we can go ahead and go back home. 1501 01:15:18,000 --> 01:15:19,380 Click on flights. 1502 01:15:19,380 --> 01:15:21,960 I see that I already have two flights inside of my database. 1503 01:15:21,960 --> 01:15:24,420 I have New York to London and New York to Paris. 1504 01:15:24,420 --> 01:15:25,800 I'll add a new one. 1505 01:15:25,800 --> 01:15:28,680 It's letting me choose an origin, destination, and duration. 1506 01:15:28,680 --> 01:15:31,350 And Django knows that the origin must be an airport, 1507 01:15:31,350 --> 01:15:34,400 so it's going to give me the opportunity to just choose an airport. 1508 01:15:34,400 --> 01:15:36,630 Where I can say, OK, Shanghai is the origin. 1509 01:15:36,630 --> 01:15:39,300 The destination is going to be Paris, and the duration 1510 01:15:39,300 --> 01:15:42,410 is going to be 760 minutes, for example. 1511 01:15:42,410 --> 01:15:44,680 So now, using Django's admin interface, I've 1512 01:15:44,680 --> 01:15:46,780 been able to add a number of different flights 1513 01:15:46,780 --> 01:15:48,430 and a number of different airports. 1514 01:15:48,430 --> 01:15:52,210 And if I go back, not to the admin app, but to my flights app, 1515 01:15:52,210 --> 01:15:54,970 the app that I wrote myself, and go back to /flights. 1516 01:15:54,970 --> 01:15:57,790 Now, I actually see all of the new flights 1517 01:15:57,790 --> 01:16:01,253 that I have added to my database via Django's admin interface. 1518 01:16:01,253 --> 01:16:04,420 I added them to the admin interface, and now I see this flight from Shanghai 1519 01:16:04,420 --> 01:16:05,200 to Paris. 1520 01:16:05,200 --> 01:16:09,770 I see this flight from Paris to New York as well. 1521 01:16:09,770 --> 01:16:11,830 And so now, what I might like to do is begin 1522 01:16:11,830 --> 01:16:14,280 to add some more pages to this web application. 1523 01:16:14,280 --> 01:16:17,140 Make this web application a little more sophisticated 1524 01:16:17,140 --> 01:16:20,860 by maybe giving me the ability to click on a particular flight 1525 01:16:20,860 --> 01:16:22,840 to view details about that flight. 1526 01:16:22,840 --> 01:16:25,660 What I'd like is for every flight to have its own page, 1527 01:16:25,660 --> 01:16:30,520 not just /flights for all the flights, but /flight/one for flight ID one. 1528 01:16:30,520 --> 01:16:34,240 /flight/two for ID two, so on and so forth. 1529 01:16:34,240 --> 01:16:38,650 What I can do in order to do that is go back into urls.py 1530 01:16:38,650 --> 01:16:40,090 and create a new path. 1531 01:16:40,090 --> 01:16:42,760 We'll create a path where I'm going to specify 1532 01:16:42,760 --> 01:16:45,490 a flight ID, which would be an integer. 1533 01:16:45,490 --> 01:16:49,810 When I do, let's go ahead and load the flight view, whose name will be flight. 1534 01:16:49,810 --> 01:16:54,730 And now, I need to just go to views.py and add a function called flight. 1535 01:16:54,730 --> 01:16:57,070 So I go back, go into views.py. 1536 01:16:57,070 --> 01:17:01,210 In addition to an index function, we'll define a flight function that 1537 01:17:01,210 --> 01:17:04,390 accepts as an argument a flight ID. 1538 01:17:04,390 --> 01:17:06,740 So now, what is the flight function going to do? 1539 01:17:06,740 --> 01:17:09,820 Well, the first thing I need to do is actually get that flight. 1540 01:17:09,820 --> 01:17:12,630 I can say flight equals flight.objects.get. 1541 01:17:12,630 --> 01:17:18,970 Get me the flight whose idea is equal to flight ID, for example. 1542 01:17:18,970 --> 01:17:22,690 Or alternatively, Django also let's you say pk instead of ID. 1543 01:17:22,690 --> 01:17:24,490 It's a much more generic way of referencing 1544 01:17:24,490 --> 01:17:27,850 the primary key, for whatever the primary key happens to be called. 1545 01:17:27,850 --> 01:17:30,160 The pk in this case is just the ID. 1546 01:17:30,160 --> 01:17:36,790 But then what I can do is render a template, like flight/flight.html, 1547 01:17:36,790 --> 01:17:40,420 and pass as input to that the flight. 1548 01:17:40,420 --> 01:17:44,290 So we're passing this flight to flight.html. 1549 01:17:44,290 --> 01:17:46,300 And now, I can create a template-- 1550 01:17:46,300 --> 01:17:52,030 create a new file called flight.html which is going to also extend 1551 01:17:52,030 --> 01:17:56,090 flight/layout.html using that same HTML layout. 1552 01:17:56,090 --> 01:17:59,320 And inside the body of the page, let's just 1553 01:17:59,320 --> 01:18:04,870 say something like in big we'll say flight ID. 1554 01:18:04,870 --> 01:18:08,920 And then, maybe an unordered list where I can say something like the origin 1555 01:18:08,920 --> 01:18:11,350 is flight.origin. 1556 01:18:11,350 --> 01:18:15,810 The destination is flight.destination. 1557 01:18:15,810 --> 01:18:21,690 And the duration is flight.duration. 1558 01:18:21,690 --> 01:18:24,570 So now, I have a page that displays flight information 1559 01:18:24,570 --> 01:18:26,430 about any particular flight. 1560 01:18:26,430 --> 01:18:31,530 And if I go ahead and load not /flights in my web browser, but /flights/one, 1561 01:18:31,530 --> 01:18:32,222 for example. 1562 01:18:32,222 --> 01:18:34,430 Well, now I have information about flight number one. 1563 01:18:34,430 --> 01:18:38,040 And /flight/two gets me information about flight number two. 1564 01:18:38,040 --> 01:18:40,440 Querying for that particular flight, then printing out 1565 01:18:40,440 --> 01:18:43,480 its origin, destination, and duration. 1566 01:18:43,480 --> 01:18:46,230 Now, there is some error checking that we probably should do here. 1567 01:18:46,230 --> 01:18:48,230 If I try and access a flight that doesn't exist, 1568 01:18:48,230 --> 01:18:50,770 something like flight 28, for example. 1569 01:18:50,770 --> 01:18:53,550 I'm going to get some sort of error that does not exist error. 1570 01:18:53,550 --> 01:18:55,590 Flight matching query does not exist. 1571 01:18:55,590 --> 01:18:58,873 I might like to control what happens in that situation a little better. 1572 01:18:58,873 --> 01:19:01,290 So you might imagine adding some additional error checking 1573 01:19:01,290 --> 01:19:02,790 to handle those cases as well. 1574 01:19:02,790 --> 01:19:05,310 But we'll leave it at this just for now. 1575 01:19:05,310 --> 01:19:07,800 But now, let's go ahead and add the ability, 1576 01:19:07,800 --> 01:19:11,040 not only to have flights that have airports associated with them, 1577 01:19:11,040 --> 01:19:13,650 but let's also add passengers to our flights 1578 01:19:13,650 --> 01:19:16,800 as well to be able to represent passengers that might actually 1579 01:19:16,800 --> 01:19:18,860 be on these flights, too. 1580 01:19:18,860 --> 01:19:22,690 So go ahead and go back into models.py. 1581 01:19:22,690 --> 01:19:27,120 And in models.py, in addition to an airport class and a flight class, 1582 01:19:27,120 --> 01:19:31,350 let me create a new class called passenger. 1583 01:19:31,350 --> 01:19:32,970 Also going to be a model. 1584 01:19:32,970 --> 01:19:35,190 And what properties does a passenger have? 1585 01:19:35,190 --> 01:19:37,110 Well, a passenger has a first name, which 1586 01:19:37,110 --> 01:19:42,450 we'll go ahead and make a models.CharField whose max length 1587 01:19:42,450 --> 01:19:44,610 we'll put at 64. 1588 01:19:44,610 --> 01:19:47,430 And the last name. 1589 01:19:47,430 --> 01:19:49,380 Max length equals 64. 1590 01:19:49,380 --> 01:19:52,290 And passengers also, as we described before, 1591 01:19:52,290 --> 01:19:55,725 they have a many to many relationship with flights. 1592 01:19:55,725 --> 01:19:57,600 That a flight could have multiple passengers, 1593 01:19:57,600 --> 01:19:59,580 a passenger could be on multiple flights, 1594 01:19:59,580 --> 01:20:02,490 and ultimately, we need an additional table to keep track of this. 1595 01:20:02,490 --> 01:20:05,310 But we can think a little bit more abstractly here in Django 1596 01:20:05,310 --> 01:20:09,930 and just say that every passenger has flights associated with them, which are 1597 01:20:09,930 --> 01:20:14,580 a models.manytomanyfield with flight. 1598 01:20:14,580 --> 01:20:18,220 So every passenger could be associated with many flights. 1599 01:20:18,220 --> 01:20:22,290 We'll say blank equals true to allow the possibility that a passenger has 1600 01:20:22,290 --> 01:20:22,890 no flights. 1601 01:20:22,890 --> 01:20:26,140 Maybe if they're not registered for any flights at all. 1602 01:20:26,140 --> 01:20:30,150 And we'll also give this a related name of passengers, 1603 01:20:30,150 --> 01:20:33,960 meaning if I have a passenger, I can use the flights attribute 1604 01:20:33,960 --> 01:20:35,640 to access all of their flights. 1605 01:20:35,640 --> 01:20:39,870 And likewise, if I have a flight, I can use this passenger's related name 1606 01:20:39,870 --> 01:20:42,930 to access all of the passengers who are on that flight. 1607 01:20:42,930 --> 01:20:45,660 And we'll see how that'll be useful in a moment, too. 1608 01:20:45,660 --> 01:20:48,750 The string representation of a passenger will just 1609 01:20:48,750 --> 01:20:52,800 go ahead and be their first name space their last name, 1610 01:20:52,800 --> 01:20:56,610 which feels like a reasonable way of representing a particular passenger. 1611 01:20:56,610 --> 01:20:58,920 And now, I need to apply these changes. 1612 01:20:58,920 --> 01:21:01,080 I need to say, python manage.py. 1613 01:21:01,080 --> 01:21:04,440 Make migrations because I've made new changes to my model. 1614 01:21:04,440 --> 01:21:07,140 I've created a model passenger, in particular. 1615 01:21:07,140 --> 01:21:10,050 And now, if I do python manage.py migrate, 1616 01:21:10,050 --> 01:21:13,800 now I've applied those changes to my actual database. 1617 01:21:13,800 --> 01:21:18,660 And if I go into admin.py, so we'll go into admin.py, 1618 01:21:18,660 --> 01:21:23,460 and register not only flight and airport but passenger, 1619 01:21:23,460 --> 01:21:28,020 admin.site.register passenger, then now via the admin interface, 1620 01:21:28,020 --> 01:21:30,270 I can manipulate passengers as well. 1621 01:21:30,270 --> 01:21:35,550 I can say python manage.py run server to run my web server. 1622 01:21:35,550 --> 01:21:39,810 Go to my web servers admin view by going to /admin. 1623 01:21:39,810 --> 01:21:44,240 Go down to passengers, and let's go ahead and add a passenger. 1624 01:21:44,240 --> 01:21:46,320 Where I can say, all right, first name Harry, 1625 01:21:46,320 --> 01:21:51,430 last name Potter, and we'll go ahead and put him on flight 1 and flight 3, 1626 01:21:51,430 --> 01:21:51,930 maybe. 1627 01:21:51,930 --> 01:21:53,810 He's on two different flights, for example. 1628 01:21:53,810 --> 01:21:57,360 And you can hold down command or control to be able to select multiple flights. 1629 01:21:57,360 --> 01:21:58,860 And we'll go ahead and save that. 1630 01:21:58,860 --> 01:22:00,720 Harry Potter has been added successfully. 1631 01:22:00,720 --> 01:22:02,512 And let's add a couple of other passengers. 1632 01:22:02,512 --> 01:22:04,530 We'll add Ron Weasley. 1633 01:22:04,530 --> 01:22:05,610 And we'll add another. 1634 01:22:05,610 --> 01:22:08,310 We'll add Hermione Granger. 1635 01:22:08,310 --> 01:22:11,480 And we'll add Ginny Weasley as well. 1636 01:22:11,480 --> 01:22:13,440 So we've added a number of different passengers 1637 01:22:13,440 --> 01:22:16,380 that now all exist in Django's admin interface. 1638 01:22:16,380 --> 01:22:18,900 And now, what I'd like to do is on the flight page, 1639 01:22:18,900 --> 01:22:21,420 display information about which passengers 1640 01:22:21,420 --> 01:22:23,790 happened to be on any given flight. 1641 01:22:23,790 --> 01:22:29,250 So the way I might do that is by going into views.py. 1642 01:22:29,250 --> 01:22:33,240 And on the flight page, in addition to giving access to the flight, 1643 01:22:33,240 --> 01:22:35,730 let me also give it access to passengers. 1644 01:22:35,730 --> 01:22:39,010 So passengers this template is going to get access to. 1645 01:22:39,010 --> 01:22:43,670 And we get passengers by saying flight.passengers.all. 1646 01:22:43,670 --> 01:22:46,740 And the reason we can do this is, again, because passengers 1647 01:22:46,740 --> 01:22:48,030 is that related name. 1648 01:22:48,030 --> 01:22:52,050 It is our way of taking a flight and getting all of the passengers that 1649 01:22:52,050 --> 01:22:54,750 happened to be on that flight. 1650 01:22:54,750 --> 01:23:01,230 And so now, inside of flight.html I can add something like, 1651 01:23:01,230 --> 01:23:04,160 let's add an H2 called passengers. 1652 01:23:04,160 --> 01:23:10,570 Where here, I'm going to loop for passenger in passengers. 1653 01:23:10,570 --> 01:23:14,920 Go ahead and display that passenger. 1654 01:23:14,920 --> 01:23:18,050 Just print out that passenger inside of a list item. 1655 01:23:18,050 --> 01:23:20,470 And in Django, I can say, if the list is empty, 1656 01:23:20,470 --> 01:23:23,470 let's just have a list item that says, no passengers. 1657 01:23:23,470 --> 01:23:27,370 Meaning nobody is currently on this flight. 1658 01:23:27,370 --> 01:23:29,620 So now, my web server is still running. 1659 01:23:29,620 --> 01:23:31,700 I can go back to /flights. 1660 01:23:31,700 --> 01:23:34,050 Here are all of the flights. 1661 01:23:34,050 --> 01:23:37,810 And if I go to /flight/one I now see that I'm flight one. 1662 01:23:37,810 --> 01:23:40,160 Harry Potter is a passenger on that flight. 1663 01:23:40,160 --> 01:23:44,173 But if I go to flight two, all right, no passengers are on that flight either. 1664 01:23:44,173 --> 01:23:46,090 And now, it's been a little annoying that I've 1665 01:23:46,090 --> 01:23:49,360 had to do everything by using the URL here to be able to go 1666 01:23:49,360 --> 01:23:51,010 back and forth between pages. 1667 01:23:51,010 --> 01:23:53,560 I could link to those pages if I want to. 1668 01:23:53,560 --> 01:23:57,040 And the way I might do that is, let's on the flight page, 1669 01:23:57,040 --> 01:24:03,250 add a link that goes to the URL index that says something 1670 01:24:03,250 --> 01:24:06,250 like, back to flight list, maybe. 1671 01:24:06,250 --> 01:24:09,400 So here is now a link that takes me to the index view. 1672 01:24:09,400 --> 01:24:12,760 And likewise, I can go into index.html. 1673 01:24:12,760 --> 01:24:16,270 And for each of these list items, each of these list items 1674 01:24:16,270 --> 01:24:23,770 is really going to be a link that links to it's a url to a particular flight. 1675 01:24:23,770 --> 01:24:27,640 And the flight route takes as a parameter a flight ID. 1676 01:24:27,640 --> 01:24:30,520 And so inside this url a substitution here. 1677 01:24:30,520 --> 01:24:35,260 I can specify use flight.id as the ID of the flight 1678 01:24:35,260 --> 01:24:37,850 that I would like to use here. 1679 01:24:37,850 --> 01:24:40,750 And so now, I've put every single flight inside 1680 01:24:40,750 --> 01:24:43,450 of a link that takes me to the flight route. 1681 01:24:43,450 --> 01:24:46,720 But because the flight route requires as an argument the flight ID, 1682 01:24:46,720 --> 01:24:50,250 I can specify the flight ID here. 1683 01:24:50,250 --> 01:24:54,400 And so now, if I go back to /flights, I now see a list of flights where every 1684 01:24:54,400 --> 01:24:57,770 flight is in fact a link that can take me somewhere else. 1685 01:24:57,770 --> 01:25:02,020 And so now, I can click on any one of those links, like New York to Paris, 1686 01:25:02,020 --> 01:25:03,730 and that takes me to the flight page. 1687 01:25:03,730 --> 01:25:06,730 I can click back to flight lists, that takes me back to the flight list. 1688 01:25:06,730 --> 01:25:09,320 Click on another flight and go to that flight as well. 1689 01:25:09,320 --> 01:25:12,820 So I've now been able to come up with this way of linking 1690 01:25:12,820 --> 01:25:16,390 these pages together by having links in each of the various different pages 1691 01:25:16,390 --> 01:25:20,690 that take me to some other route as well. 1692 01:25:20,690 --> 01:25:22,780 And so now what I might like to do is, in addition 1693 01:25:22,780 --> 01:25:25,510 to displaying all the passengers on any particular flight, 1694 01:25:25,510 --> 01:25:28,750 also give myself the ability to add passengers to a flight 1695 01:25:28,750 --> 01:25:30,760 as well, which feels like a reasonable thing 1696 01:25:30,760 --> 01:25:33,830 that I might want to do inside of this web application. 1697 01:25:33,830 --> 01:25:35,947 And so how can I go about doing that? 1698 01:25:35,947 --> 01:25:37,780 Well, in order to do that, I'm going to need 1699 01:25:37,780 --> 01:25:42,330 some new route that lets me book a flight for a particular passenger. 1700 01:25:42,330 --> 01:25:45,610 And so I'll go ahead and go back to urls.py. 1701 01:25:45,610 --> 01:25:54,430 And inside of urls.py I'll add a new path that will be int flightid/book. 1702 01:25:54,430 --> 01:25:56,680 And int flightid/book is going to let me book 1703 01:25:56,680 --> 01:25:59,680 a flight for this particular flight ID. 1704 01:25:59,680 --> 01:26:03,490 For flight one or flight two or flight three, or so forth. 1705 01:26:03,490 --> 01:26:08,560 When I do, we'll go ahead and go to the book view, and we'll name that book. 1706 01:26:08,560 --> 01:26:12,233 And so now, I need to implement the book view. 1707 01:26:12,233 --> 01:26:13,650 So how is this view going to work? 1708 01:26:13,650 --> 01:26:17,290 I'm going to define a function called book 1709 01:26:17,290 --> 01:26:21,070 that is going to take as its argument, not only the request, 1710 01:26:21,070 --> 01:26:22,960 but also a flight ID. 1711 01:26:22,960 --> 01:26:26,110 The first thing, as with before, is I want to get the flight ID. 1712 01:26:26,110 --> 01:26:28,540 But remember from before, that there are multiple ways 1713 01:26:28,540 --> 01:26:30,610 that I can request a web page. 1714 01:26:30,610 --> 01:26:33,470 I can request a web page via the get request method, 1715 01:26:33,470 --> 01:26:35,950 which means I would just like to get this page. 1716 01:26:35,950 --> 01:26:38,140 Or I can request the method via post, meaning 1717 01:26:38,140 --> 01:26:40,330 I would like to send data to the page. 1718 01:26:40,330 --> 01:26:44,570 And generally speaking, anytime you want to manipulate the state of something, 1719 01:26:44,570 --> 01:26:46,690 especially manipulating our database, that 1720 01:26:46,690 --> 01:26:49,000 should be inside of a post request. 1721 01:26:49,000 --> 01:26:51,160 I'm submitting some form, some data. 1722 01:26:51,160 --> 01:26:53,410 And in response to that post submission, you 1723 01:26:53,410 --> 01:26:57,100 should manipulate what's going on inside of the database. 1724 01:26:57,100 --> 01:27:01,000 So we're going to check when this book route is called upon. 1725 01:27:01,000 --> 01:27:06,310 If request method is post, then we want to perform some sort of action. 1726 01:27:06,310 --> 01:27:11,170 The flight in question is just going to be flight.objects.get. 1727 01:27:11,170 --> 01:27:15,460 Get the flight whose primary key is that flight ID. 1728 01:27:15,460 --> 01:27:18,940 And then, what I'd also like to do is associated with the form. 1729 01:27:18,940 --> 01:27:22,120 When someone submits this form to book a new passenger on the flight, 1730 01:27:22,120 --> 01:27:25,780 they should tell me what the ID is of the passenger. 1731 01:27:25,780 --> 01:27:27,752 What passenger should I book on the flight? 1732 01:27:27,752 --> 01:27:29,710 Because those are the two pieces of information 1733 01:27:29,710 --> 01:27:31,990 you need to know in order to actually book a flight. 1734 01:27:31,990 --> 01:27:35,030 You need the flight and the passenger information. 1735 01:27:35,030 --> 01:27:37,510 So let's assume for now that the information is 1736 01:27:37,510 --> 01:27:42,880 going to be in request.post and then in square brackets passenger. 1737 01:27:42,880 --> 01:27:45,940 What this means is that the data about which passenger 1738 01:27:45,940 --> 01:27:49,570 ID we want to register on this flight is going 1739 01:27:49,570 --> 01:27:54,160 to be passed in via a form with an input field whose name is passenger. 1740 01:27:54,160 --> 01:27:58,400 The name on any particular input field dictates what name we get-- 1741 01:27:58,400 --> 01:28:01,060 is received when a route like this book route 1742 01:28:01,060 --> 01:28:04,220 is able to process the request from the user. 1743 01:28:04,220 --> 01:28:06,120 So we'll go ahead and take that information. 1744 01:28:06,120 --> 01:28:09,550 And because by default this might be a string, let's go ahead 1745 01:28:09,550 --> 01:28:11,950 and convert it into an integer just to make sure 1746 01:28:11,950 --> 01:28:13,600 we're dealing with an integer. 1747 01:28:13,600 --> 01:28:16,210 And let me say that the passenger in question 1748 01:28:16,210 --> 01:28:24,100 is going to be passenger.objects.get pk equals this whole thing. 1749 01:28:24,100 --> 01:28:26,830 So now what I've done is, if the request method 1750 01:28:26,830 --> 01:28:30,880 is post, meaning someone submitted this form via the post request method, 1751 01:28:30,880 --> 01:28:34,690 I'm first thing flights.objects.get. to get a particular flight, 1752 01:28:34,690 --> 01:28:37,240 get me the flight with that flight ID. 1753 01:28:37,240 --> 01:28:39,010 And then, I'm getting a passenger. 1754 01:28:39,010 --> 01:28:40,510 Which passenger am I getting? 1755 01:28:40,510 --> 01:28:43,870 The one who's pk, their primary key, otherwise known as ID, 1756 01:28:43,870 --> 01:28:47,830 is equal to whatever was submitted via this post form 1757 01:28:47,830 --> 01:28:49,720 with a name of passenger. 1758 01:28:49,720 --> 01:28:53,690 And we haven't yet created that form, but we'll do so in just a moment. 1759 01:28:53,690 --> 01:28:56,980 Now ultimately, we'll want to add some more error checking to this as well, 1760 01:28:56,980 --> 01:28:59,800 like what if someone requests a passenger that doesn't exist, 1761 01:28:59,800 --> 01:29:01,500 or a flight that doesn't exist either. 1762 01:29:01,500 --> 01:29:03,250 So there is definitely some error checking 1763 01:29:03,250 --> 01:29:05,030 that we probably should be doing here. 1764 01:29:05,030 --> 01:29:09,040 But for simplicity, let's just assume for now that we're able to get a flight 1765 01:29:09,040 --> 01:29:10,660 and get a passenger. 1766 01:29:10,660 --> 01:29:13,090 Well, how do we access a passenger's flights? 1767 01:29:13,090 --> 01:29:16,540 I can just say passenger.flights. 1768 01:29:16,540 --> 01:29:20,140 And in order to add a new item to some set like flights, 1769 01:29:20,140 --> 01:29:24,132 I can just say passenger.flights.add flight. 1770 01:29:24,132 --> 01:29:25,840 And this will do the equivalent of adding 1771 01:29:25,840 --> 01:29:29,740 a new row into a table of keeping track that the passengers on that flight. 1772 01:29:29,740 --> 01:29:31,930 But the nice thing about Django's abstractions 1773 01:29:31,930 --> 01:29:34,600 is that I don't have to worry about those underlying details. 1774 01:29:34,600 --> 01:29:37,420 I don't have to worry about what the structures of the tables are. 1775 01:29:37,420 --> 01:29:41,860 I can think at a much higher level and just say take this passenger, 1776 01:29:41,860 --> 01:29:45,280 take their set of flights, and go ahead and add a new flight 1777 01:29:45,280 --> 01:29:47,820 to that set of flights. 1778 01:29:47,820 --> 01:29:51,250 And when all that's said and done, what I probably want to do 1779 01:29:51,250 --> 01:29:55,420 is return some sort of redirect that redirects the user back 1780 01:29:55,420 --> 01:29:56,560 to the flight page. 1781 01:29:56,560 --> 01:30:01,210 So we'll go ahead and return an HTTP response redirect. 1782 01:30:01,210 --> 01:30:03,170 What you URL would I like to take them to? 1783 01:30:03,170 --> 01:30:06,700 Well, I'd like to take them to the flight route. 1784 01:30:06,700 --> 01:30:09,880 And reverse, again, takes the name of a particular view, 1785 01:30:09,880 --> 01:30:13,180 and gets me what the URL is, and we saw that last time. 1786 01:30:13,180 --> 01:30:14,930 And the flight route takes an argument. 1787 01:30:14,930 --> 01:30:18,618 So I need to pass as an argument the flight's ID. 1788 01:30:18,618 --> 01:30:20,410 So I need to provide it to the flight route 1789 01:30:20,410 --> 01:30:23,620 what the flight's ID is, structured it as a tuple, 1790 01:30:23,620 --> 01:30:27,220 and that is going to redirect me back to the flight route 1791 01:30:27,220 --> 01:30:30,790 so that I can see that flight page again. 1792 01:30:30,790 --> 01:30:35,740 And what I need to add up at the top here is from django.http. 1793 01:30:35,740 --> 01:30:47,370 Import HttpResponseRedirect in addition to from django.urls import reverse. 1794 01:30:47,370 --> 01:30:49,550 And so those I'll need to add as well so that I 1795 01:30:49,550 --> 01:30:52,220 can redirect the user back to the flight page 1796 01:30:52,220 --> 01:30:53,900 after they're done submitting the form. 1797 01:30:53,900 --> 01:30:57,110 And reverse takes the name of a particular view, 1798 01:30:57,110 --> 01:31:01,220 as defined in urls.py, something like index or flight or book, 1799 01:31:01,220 --> 01:31:04,320 and gets me what the actual URL path should be. 1800 01:31:04,320 --> 01:31:06,320 And as we talked about last time, that's helpful 1801 01:31:06,320 --> 01:31:10,340 so that I don't have to hard code URLs into my Django web application. 1802 01:31:10,340 --> 01:31:12,620 I can just reference URLs by their name. 1803 01:31:12,620 --> 01:31:14,870 And if ever I need to change a URL, I can just 1804 01:31:14,870 --> 01:31:18,590 change it in one place in urls.py, and that change 1805 01:31:18,590 --> 01:31:22,490 is going to reflect everywhere else as well. 1806 01:31:22,490 --> 01:31:26,380 So now, the next thing I need to do is actually create this form. 1807 01:31:26,380 --> 01:31:28,610 That what I have so far is just a function 1808 01:31:28,610 --> 01:31:32,240 called book that is waiting for a post request to be made to it. 1809 01:31:32,240 --> 01:31:34,460 And when a post request is made to it, then we're 1810 01:31:34,460 --> 01:31:37,850 going to go ahead and submit this form and go ahead and add the flight 1811 01:31:37,850 --> 01:31:39,530 for this particular passenger. 1812 01:31:39,530 --> 01:31:42,360 But what I'd like to do now is actually add that form. 1813 01:31:42,360 --> 01:31:46,680 So I'll go back into templates, go into flight.html, 1814 01:31:46,680 --> 01:31:49,250 and what I'd like to add here is a form. 1815 01:31:49,250 --> 01:31:53,180 I'll go ahead and label it with an H2 called add passenger. 1816 01:31:53,180 --> 01:32:00,220 And we'll create a form whose action is going to be URL of book. 1817 01:32:00,220 --> 01:32:01,940 So we're going to go to the book route. 1818 01:32:01,940 --> 01:32:04,710 And again, if we recall the book route in urls.py, 1819 01:32:04,710 --> 01:32:10,250 the route with name book, this view, requires as a parameter some flight ID. 1820 01:32:10,250 --> 01:32:14,060 So I need to provide the flight ID as an argument for what flight 1821 01:32:14,060 --> 01:32:15,920 I'm booking the passenger on. 1822 01:32:15,920 --> 01:32:18,890 And it just so happens to be flight.id because this template has 1823 01:32:18,890 --> 01:32:21,730 access to a variable called flight. 1824 01:32:21,730 --> 01:32:25,400 The method of this submission is, again, going to be post. 1825 01:32:25,400 --> 01:32:28,160 And recall from before that whenever I have a form in Django, 1826 01:32:28,160 --> 01:32:31,610 I need to give it the CSRF token just for security to make sure 1827 01:32:31,610 --> 01:32:34,160 that Django knows it's really this application that 1828 01:32:34,160 --> 01:32:35,720 is submitting this form. 1829 01:32:35,720 --> 01:32:38,210 We'll go ahead and add a dropdown list, which you can 1830 01:32:38,210 --> 01:32:41,270 create in HTML using a select field. 1831 01:32:41,270 --> 01:32:45,410 The name of this select field is going to be passenger. 1832 01:32:45,410 --> 01:32:49,970 And the reason for that is inside of views.py, when I get the passenger, 1833 01:32:49,970 --> 01:32:53,570 I'm looking for inside the post data, inside of request.post, 1834 01:32:53,570 --> 01:32:55,880 for a field whose name is passenger. 1835 01:32:55,880 --> 01:32:59,650 And so that is what I would like the name of this dropdown to be. 1836 01:32:59,650 --> 01:33:03,110 And inside of a select dropdown, we have a whole bunch of options. 1837 01:33:03,110 --> 01:33:04,680 Options that we can choose from. 1838 01:33:04,680 --> 01:33:07,400 And there's going to be one option for everyone who 1839 01:33:07,400 --> 01:33:10,700 isn't a passenger on this flight. 1840 01:33:10,700 --> 01:33:13,850 And so how do I get everyone who isn't a passenger on the flight? 1841 01:33:13,850 --> 01:33:17,150 Well, it seems that right now, the flight page only has access 1842 01:33:17,150 --> 01:33:21,020 to actual passengers, and doesn't yet have access to people 1843 01:33:21,020 --> 01:33:23,420 that are not passengers on the flight. 1844 01:33:23,420 --> 01:33:26,990 So it sounds like I need to add some additional context to this template. 1845 01:33:26,990 --> 01:33:29,870 Additional information that we want access to. 1846 01:33:29,870 --> 01:33:33,260 So I'll go ahead and give this flight access to additional information 1847 01:33:33,260 --> 01:33:35,540 that we'll call non-passengers. 1848 01:33:35,540 --> 01:33:38,000 For people that are not on the flight. 1849 01:33:38,000 --> 01:33:39,920 And how do we I non-passengers? 1850 01:33:39,920 --> 01:33:43,640 Well, just as I could say passenger.objects.filter 1851 01:33:43,640 --> 01:33:47,250 to only get passengers that match a particular query, 1852 01:33:47,250 --> 01:33:51,980 there's also a way in Django to say passenger.objects.exclude to say 1853 01:33:51,980 --> 01:33:55,800 exclude passengers that satisfy a particular query. 1854 01:33:55,800 --> 01:33:59,450 So I want to exclude passengers who, among their flights, 1855 01:33:59,450 --> 01:34:02,600 have this as one of their flights. 1856 01:34:02,600 --> 01:34:04,290 And so what does this actually mean? 1857 01:34:04,290 --> 01:34:06,920 Well, it means that when I render flight.html, 1858 01:34:06,920 --> 01:34:09,530 there's a couple pieces of information that it should have. 1859 01:34:09,530 --> 01:34:11,870 It needs to know what flight is being rendered. 1860 01:34:11,870 --> 01:34:15,000 It needs to know who is on the flight, who are the passengers. 1861 01:34:15,000 --> 01:34:17,690 But if I want a dropdown where I can choose from all the people 1862 01:34:17,690 --> 01:34:19,820 who aren't already on the flight, like I would 1863 01:34:19,820 --> 01:34:22,310 like to register you for the flight, well, I also 1864 01:34:22,310 --> 01:34:23,840 need all of the non-passengers. 1865 01:34:23,840 --> 01:34:29,360 Passengers except, excluding the ones who are already on the flight, 1866 01:34:29,360 --> 01:34:33,560 and get me all of them is what that .all is ultimately saying. 1867 01:34:33,560 --> 01:34:36,830 And so using that, I now have access to this variable 1868 01:34:36,830 --> 01:34:41,900 called non-passengers that I can use as I'm constructing this page. 1869 01:34:41,900 --> 01:34:49,640 So back on flight.html, I can say, for every passenger in non-passengers, 1870 01:34:49,640 --> 01:34:54,240 let me create a option that I can choose from. 1871 01:34:54,240 --> 01:34:59,260 And the options value is going to be the passenger's ID. 1872 01:34:59,260 --> 01:35:02,680 Because ultimately, when I submit the form, what I care about getting 1873 01:35:02,680 --> 01:35:06,825 is what is the ID of this passenger that I've chosen from this dropdown. 1874 01:35:06,825 --> 01:35:08,950 But of course, the user who's looking at this page, 1875 01:35:08,950 --> 01:35:10,540 they don't want to see people's IDs. 1876 01:35:10,540 --> 01:35:12,280 They want to see people's names. 1877 01:35:12,280 --> 01:35:14,980 So inside of the option tag, we'll go ahead 1878 01:35:14,980 --> 01:35:17,640 and just print out the passenger's name. 1879 01:35:17,640 --> 01:35:19,390 And we'll see in a moment what all of this 1880 01:35:19,390 --> 01:35:21,860 actually looks like in terms of HTML. 1881 01:35:21,860 --> 01:35:24,400 So now that I've created this form, I'll also 1882 01:35:24,400 --> 01:35:27,520 need at the bottom to add an input whose type is submit 1883 01:35:27,520 --> 01:35:30,460 to let myself submit this form to. 1884 01:35:30,460 --> 01:35:33,090 Let's now try running this application. 1885 01:35:33,090 --> 01:35:34,840 It looks like there's a slight error where 1886 01:35:34,840 --> 01:35:36,903 I said view.book instead of views.book. 1887 01:35:36,903 --> 01:35:39,820 Views is the name of the module, since it's in a file called views.py. 1888 01:35:39,820 --> 01:35:43,360 And now, it looks like my server is running OK. 1889 01:35:43,360 --> 01:35:45,430 I can go back to /flights. 1890 01:35:45,430 --> 01:35:48,960 Let me get one of the flights, like New York to London, flight number one. 1891 01:35:48,960 --> 01:35:49,600 And all right. 1892 01:35:49,600 --> 01:35:50,590 Name error. 1893 01:35:50,590 --> 01:35:52,150 Name passenger is not defined. 1894 01:35:52,150 --> 01:35:55,780 This is how Django renders to me errors that are occurring in my Python code. 1895 01:35:55,780 --> 01:36:00,430 Looks like it just means inside of veiws.py I'm referencing passenger, 1896 01:36:00,430 --> 01:36:01,450 but I never imported it. 1897 01:36:01,450 --> 01:36:05,450 So up at the top, I'll go ahead and import passenger as well. 1898 01:36:05,450 --> 01:36:07,750 Now, it seems that my web application is working OK. 1899 01:36:07,750 --> 01:36:11,840 So now, hopefully, I refresh this page, I see flight information. 1900 01:36:11,840 --> 01:36:12,960 I see passengers. 1901 01:36:12,960 --> 01:36:16,750 And I also down at the bottom now see an add passenger section 1902 01:36:16,750 --> 01:36:17,900 with a dropdown list. 1903 01:36:17,900 --> 01:36:19,900 Where I can click on it and see, all right, here 1904 01:36:19,900 --> 01:36:23,350 are the three people that are not already on this flight. 1905 01:36:23,350 --> 01:36:26,620 And so if I want to add Ginny Weasley to this flight, 1906 01:36:26,620 --> 01:36:30,460 I can click Ginny Weasley, click submit, and that submits the form. 1907 01:36:30,460 --> 01:36:32,950 And I'm redirected back to the same flight page. 1908 01:36:32,950 --> 01:36:35,110 Now, Harry and Ginny are both on the flight. 1909 01:36:35,110 --> 01:36:40,440 And in the add passenger list, I see Ron and Hermione as the options for me 1910 01:36:40,440 --> 01:36:41,780 there. 1911 01:36:41,780 --> 01:36:44,860 And so using Django's models, I've been able to very quickly build up 1912 01:36:44,860 --> 01:36:46,750 a reasonably sophisticated application. 1913 01:36:46,750 --> 01:36:50,410 An application that has models, that displays that information to me, 1914 01:36:50,410 --> 01:36:52,030 and lets me manipulate that data. 1915 01:36:52,030 --> 01:36:56,240 And that data is ultimately stored inside of a SQL database. 1916 01:36:56,240 --> 01:36:59,110 And one of the big powers of Django that it really gives to me 1917 01:36:59,110 --> 01:37:02,140 is this admin interface that I ordinarily 1918 01:37:02,140 --> 01:37:05,530 might have had to spend a lot of time designing a web interface that just 1919 01:37:05,530 --> 01:37:09,640 lets me do things like take some person and go ahead and update 1920 01:37:09,640 --> 01:37:11,680 what is their name, what flights are they on, 1921 01:37:11,680 --> 01:37:15,560 and the ability to very quickly add and delete and edit the models 1922 01:37:15,560 --> 01:37:18,880 is something that in a web application could take quite a lot of time 1923 01:37:18,880 --> 01:37:20,440 to be able to build from scratch. 1924 01:37:20,440 --> 01:37:24,280 But Django, very fortunately, gives all of that right to me. 1925 01:37:24,280 --> 01:37:27,310 And this admin interface, even though it is designed by Django, 1926 01:37:27,310 --> 01:37:29,170 it's very customizable in terms of things 1927 01:37:29,170 --> 01:37:33,640 that I can do on this admin interface if I want to manipulate it in certain ways 1928 01:37:33,640 --> 01:37:36,860 in order to add additional features to it as well. 1929 01:37:36,860 --> 01:37:39,070 So we'll see a couple of brief examples of this. 1930 01:37:39,070 --> 01:37:43,030 If I go into admin.py, here's my configuration 1931 01:37:43,030 --> 01:37:45,000 for Django's admin interface. 1932 01:37:45,000 --> 01:37:47,920 And I can say, I would like to configure the admin 1933 01:37:47,920 --> 01:37:49,750 interface in a particular way. 1934 01:37:49,750 --> 01:37:52,810 That in a flight, for example, by default all I saw 1935 01:37:52,810 --> 01:37:55,030 was the flight's origin and destination. 1936 01:37:55,030 --> 01:37:57,610 If I want to be able to see more information about a flight, 1937 01:37:57,610 --> 01:38:01,870 I can say, go ahead and give me a class called flight admin, which is 1938 01:38:01,870 --> 01:38:05,410 going to be a subclass of model admin. 1939 01:38:05,410 --> 01:38:08,170 Where I can specify any particular settings 1940 01:38:08,170 --> 01:38:12,470 that I want to apply to how the flight admin page is displayed. 1941 01:38:12,470 --> 01:38:15,030 So I can-- and all of this is documented on Django's website. 1942 01:38:15,030 --> 01:38:18,280 And you just have to read it to be able to know what configuration options are 1943 01:38:18,280 --> 01:38:19,460 available to you. 1944 01:38:19,460 --> 01:38:22,660 But I can say, in the list display, when you list all the flights 1945 01:38:22,660 --> 01:38:26,050 and display them all to me, what field should I have access to? 1946 01:38:26,050 --> 01:38:29,500 Well, go ahead and show me something like the origin 1947 01:38:29,500 --> 01:38:35,530 and the destination and the duration and maybe also show me the ID, for example. 1948 01:38:35,530 --> 01:38:38,950 So I want to see all of this information when you load a flight. 1949 01:38:38,950 --> 01:38:42,040 And when I register the flight, I'll say register this flight, 1950 01:38:42,040 --> 01:38:44,970 but use the flight admin settings when you do so. 1951 01:38:44,970 --> 01:38:48,580 So I can specify, I would like to use these particular settings 1952 01:38:48,580 --> 01:38:51,430 when you view the admin interface. 1953 01:38:51,430 --> 01:38:55,210 And so now, if I go back and go ahead and click on flights, 1954 01:38:55,210 --> 01:38:58,240 then now in this list display, whereas before I only 1955 01:38:58,240 --> 01:39:00,760 saw IDs and origins and destinations, now I 1956 01:39:00,760 --> 01:39:04,510 can configure it to show me all of the IDs and origins and destinations 1957 01:39:04,510 --> 01:39:05,890 and durations as well. 1958 01:39:05,890 --> 01:39:09,850 I've been able to configure this display to work the way I would like it to. 1959 01:39:09,850 --> 01:39:12,250 And there are other configurations you can do as well. 1960 01:39:12,250 --> 01:39:16,090 One that I quite like to use is if I want to update my passenger admin, 1961 01:39:16,090 --> 01:39:19,210 so when I'm editing a passenger, you can have 1962 01:39:19,210 --> 01:39:22,780 a special way of manipulating many to many relationships inside 1963 01:39:22,780 --> 01:39:25,630 of an attribute called filter horizontal. 1964 01:39:25,630 --> 01:39:29,110 And if I use a horizontal filter on flights, 1965 01:39:29,110 --> 01:39:31,900 this will just make it a little bit nicer for manipulating 1966 01:39:31,900 --> 01:39:33,520 the flights that a passenger is on. 1967 01:39:33,520 --> 01:39:36,580 And again, the specific syntax of this not as important as the idea 1968 01:39:36,580 --> 01:39:39,850 that these are all just configurable settings that Django has documented. 1969 01:39:39,850 --> 01:39:42,850 That you can look at to see how to configure the Admin interface to work 1970 01:39:42,850 --> 01:39:45,700 exactly the way you want it to work. 1971 01:39:45,700 --> 01:39:48,690 And so now, if I go back home and go to passengers, 1972 01:39:48,690 --> 01:39:51,270 and maybe click on a passenger like Harry Potter, 1973 01:39:51,270 --> 01:39:53,550 I now see this horizontal filter, which is 1974 01:39:53,550 --> 01:39:56,370 a very nice way of being able to manipulate flights 1975 01:39:56,370 --> 01:39:57,540 that the person is on. 1976 01:39:57,540 --> 01:39:59,700 I see on the left a list of their available flights 1977 01:39:59,700 --> 01:40:01,170 that I could add them to. 1978 01:40:01,170 --> 01:40:03,150 On the right, a list of their chosen flights. 1979 01:40:03,150 --> 01:40:04,680 Flights that they're already on. 1980 01:40:04,680 --> 01:40:08,430 And it becomes very easy for me to just take a flight and double click on it 1981 01:40:08,430 --> 01:40:12,480 to move it from an available flight to a flight that they're on and vise versa. 1982 01:40:12,480 --> 01:40:16,050 Just very quickly being able to control and manipulate these models. 1983 01:40:16,050 --> 01:40:20,940 And this is all stuff that Django just gives to you right out of the box. 1984 01:40:20,940 --> 01:40:23,260 So Django now has given us a lot of features. 1985 01:40:23,260 --> 01:40:25,860 The ability to represent models very succinctly. 1986 01:40:25,860 --> 01:40:28,500 A migration method for being able to very quickly apply 1987 01:40:28,500 --> 01:40:30,420 those changes to our database. 1988 01:40:30,420 --> 01:40:34,088 And the last thing we'll take a look at is this idea of authentication. 1989 01:40:34,088 --> 01:40:36,630 That on many websites, we want some method of authentication. 1990 01:40:36,630 --> 01:40:39,780 Some ability for users to be able to log in and log out. 1991 01:40:39,780 --> 01:40:43,350 For Django to remember who a particular user happens to be. 1992 01:40:43,350 --> 01:40:45,180 And what we're going to do now is introduce 1993 01:40:45,180 --> 01:40:48,880 an application that lets us interact with this authentication method. 1994 01:40:48,880 --> 01:40:51,660 Because Django has a whole bunch of authentication features 1995 01:40:51,660 --> 01:40:54,530 built right into the framework that we can take advantage of so 1996 01:40:54,530 --> 01:40:58,080 that we don't need to rewrite all the logic for how do you log someone in, 1997 01:40:58,080 --> 01:40:59,970 and what does it mean to represent the user. 1998 01:40:59,970 --> 01:41:02,670 Django has done a whole lot of that for us. 1999 01:41:02,670 --> 01:41:05,770 So we'll go ahead and create an application to do that now. 2000 01:41:05,770 --> 01:41:07,950 All right, so let's go back into my terminal now. 2001 01:41:07,950 --> 01:41:09,960 And now, I have this airline project inside 2002 01:41:09,960 --> 01:41:11,820 of which is one app called flights. 2003 01:41:11,820 --> 01:41:15,000 And I'd like to now create another app that's going to maintain users inside 2004 01:41:15,000 --> 01:41:16,300 of this application. 2005 01:41:16,300 --> 01:41:20,190 So I'll go ahead and run python manage.py start app users. 2006 01:41:20,190 --> 01:41:23,820 Which will just be an app that's going to allow me to represent users. 2007 01:41:23,820 --> 01:41:27,830 As before, when I create a new application, 2008 01:41:27,830 --> 01:41:29,910 I'll need to go into settings.py. 2009 01:41:29,910 --> 01:41:35,370 Add users as one of the installed apps inside of this project. 2010 01:41:35,370 --> 01:41:41,810 And I'll go into urls.py to say I'd also like when I go to users, 2011 01:41:41,810 --> 01:41:45,810 we'll go ahead and include users.urls. 2012 01:41:45,810 --> 01:41:49,560 So all the URLs that are associated with my user's application. 2013 01:41:49,560 --> 01:41:52,070 Now, I'll need to actually create those URLs. 2014 01:41:52,070 --> 01:41:55,120 So I'll go ahead and go down into my users application, 2015 01:41:55,120 --> 01:41:58,860 create a new file called urls.py, inside of which 2016 01:41:58,860 --> 01:42:02,010 is the same as what we've normally see inside of these URLs files. 2017 01:42:02,010 --> 01:42:06,135 I need to import path, import my views, and then define some URL patterns. 2018 01:42:06,135 --> 01:42:08,750 2019 01:42:08,750 --> 01:42:16,150 Where here what I'd like to do is define one path that takes me to views.index. 2020 01:42:16,150 --> 01:42:18,160 And we'll call this one index. 2021 01:42:18,160 --> 01:42:24,170 Then I'll create another path that takes me to log in, called the log in view. 2022 01:42:24,170 --> 01:42:26,530 And the name will be log in. 2023 01:42:26,530 --> 01:42:31,380 And we'll have another path called log out for a function called log out 2024 01:42:31,380 --> 01:42:33,638 view that will be associated with it. 2025 01:42:33,638 --> 01:42:35,680 So we'll effectively have three different routes. 2026 01:42:35,680 --> 01:42:38,770 One main index route that's just going to display information about the 2027 01:42:38,770 --> 01:42:40,370 currently signed in user. 2028 01:42:40,370 --> 01:42:42,310 One route for logging someone in, a form that 2029 01:42:42,310 --> 01:42:44,710 will display the place where they can type in a user name 2030 01:42:44,710 --> 01:42:45,970 and password to log in. 2031 01:42:45,970 --> 01:42:50,050 And then, one route to allow users to be able to log out from this application 2032 01:42:50,050 --> 01:42:51,335 as well. 2033 01:42:51,335 --> 01:42:53,710 So let's go ahead now and actually write these functions. 2034 01:42:53,710 --> 01:42:55,780 We need one function called index, one function 2035 01:42:55,780 --> 01:42:59,350 called log in view, one function called log out view. 2036 01:42:59,350 --> 01:43:03,570 So we'll go into views.py, and we'll start with index. 2037 01:43:03,570 --> 01:43:05,530 And so what does the index function need to do? 2038 01:43:05,530 --> 01:43:09,370 It's going to display information about the currently signed in user. 2039 01:43:09,370 --> 01:43:13,200 That I sign into this website, and then I'm presented with the index page. 2040 01:43:13,200 --> 01:43:15,340 Because if we think about this programmatically, 2041 01:43:15,340 --> 01:43:17,740 we first need to think about what should happen 2042 01:43:17,740 --> 01:43:21,640 if someone tries to access this page but they're not authenticated. 2043 01:43:21,640 --> 01:43:24,740 How would we even find that out, and what do we do in that situation? 2044 01:43:24,740 --> 01:43:30,990 Well, let's say, if not request.user.is_authenticated. 2045 01:43:30,990 --> 01:43:33,830 The request object that gets passed in as part of the request 2046 01:43:33,830 --> 01:43:36,100 to every user in Django automatically has 2047 01:43:36,100 --> 01:43:38,320 a user attribute associated with it. 2048 01:43:38,320 --> 01:43:42,400 And that user object has an is authenticated attribute that tells us 2049 01:43:42,400 --> 01:43:44,800 if the user is signed in or not. 2050 01:43:44,800 --> 01:43:48,700 If they're not signed in, we'll go ahead and HTTP response 2051 01:43:48,700 --> 01:43:53,142 redirect them to the log in view. 2052 01:43:53,142 --> 01:43:54,850 And in order to make this work, I'm going 2053 01:43:54,850 --> 01:44:00,310 to need to-- from django.http import HttpResponseRedirect. 2054 01:44:00,310 --> 01:44:06,470 And likewise, from django.urls, let's go ahead and import reverse as well. 2055 01:44:06,470 --> 01:44:08,920 So if the user is not authenticated, then 2056 01:44:08,920 --> 01:44:12,550 we're going to redirect them to the log in view, where what is the log in view 2057 01:44:12,550 --> 01:44:13,970 going to do? 2058 01:44:13,970 --> 01:44:18,940 Well, the log in view for now, let's just go ahead and render 2059 01:44:18,940 --> 01:44:21,670 users/login.html. 2060 01:44:21,670 --> 01:44:24,780 Some form where the user can log themselves in. 2061 01:44:24,780 --> 01:44:26,350 We'll need to create some templates. 2062 01:44:26,350 --> 01:44:30,490 I'll create a templates folder inside of which is a user's folder. 2063 01:44:30,490 --> 01:44:32,890 Inside of which we'll just create a basic layout, 2064 01:44:32,890 --> 01:44:36,200 as we've done multiple times now. 2065 01:44:36,200 --> 01:44:40,010 This is, again, going to be the general structure for pages in this app. 2066 01:44:40,010 --> 01:44:42,800 Title will be users, and the body will just 2067 01:44:42,800 --> 01:44:48,410 have a block called body that I can later fill in with other content. 2068 01:44:48,410 --> 01:44:52,340 And now that I have this HTML layout, I can go ahead and create a new file 2069 01:44:52,340 --> 01:45:00,070 called login.html, where login.html will extend user/layout.html. 2070 01:45:00,070 --> 01:45:06,622 And inside the body block, I can just display in HTML form. 2071 01:45:06,622 --> 01:45:08,330 So I can say something like, I would like 2072 01:45:08,330 --> 01:45:11,870 for there to be a form, whose action, when I submit the forum-- 2073 01:45:11,870 --> 01:45:14,870 let's go ahead and still go to the log in URL, 2074 01:45:14,870 --> 01:45:17,960 but let's do so using the post request method. 2075 01:45:17,960 --> 01:45:20,120 Again, I'm logging in, I'm submitting a form. 2076 01:45:20,120 --> 01:45:21,912 Generally, when you're doing that, you want 2077 01:45:21,912 --> 01:45:25,370 to submit form data via post, especially in the case of user name and password. 2078 01:45:25,370 --> 01:45:27,212 Because if you do this sort of thing, you 2079 01:45:27,212 --> 01:45:30,170 don't want the user name and password to be passed in as get parameters 2080 01:45:30,170 --> 01:45:32,600 because those show up in the URL. 2081 01:45:32,600 --> 01:45:36,530 Our form will have our CSRF token for security as before. 2082 01:45:36,530 --> 01:45:41,090 And input whose type is text, whose name is username, and just 2083 01:45:41,090 --> 01:45:44,240 for user friendliness, let's give it a placeholder also of user name 2084 01:45:44,240 --> 01:45:47,360 so the user knows to type in their user name here. 2085 01:45:47,360 --> 01:45:52,470 We'll also have an input whose type is password, whose name is also password, 2086 01:45:52,470 --> 01:45:54,620 and when an input's type is password, that just 2087 01:45:54,620 --> 01:45:58,190 means our HTML will know in the browser that chrome or Safari or whatnot 2088 01:45:58,190 --> 01:46:02,210 will know to show the password as dots instead of as characters. 2089 01:46:02,210 --> 01:46:05,690 And we'll give that a placeholder of password. 2090 01:46:05,690 --> 01:46:10,010 And then an input of type submit whose value is log in. 2091 01:46:10,010 --> 01:46:13,400 So we now have the ability to log in. 2092 01:46:13,400 --> 01:46:17,360 So if we go ahead and run this program, python manage.py run server, 2093 01:46:17,360 --> 01:46:21,800 we should see users.views has no attribute log in view. 2094 01:46:21,800 --> 01:46:25,250 All right, it looks like I called this function log in request. 2095 01:46:25,250 --> 01:46:27,140 It should actually be called log in view. 2096 01:46:27,140 --> 01:46:30,080 And I'll also need a function called log out view. 2097 01:46:30,080 --> 01:46:31,940 But I haven't implemented that yet. 2098 01:46:31,940 --> 01:46:33,850 So I'll just go ahead and say pass for now, 2099 01:46:33,850 --> 01:46:37,280 but I'll come back to that later to implement the log out view. 2100 01:46:37,280 --> 01:46:39,800 All right, so it looks like my web server is running now. 2101 01:46:39,800 --> 01:46:41,960 And before I actually go to the login page, 2102 01:46:41,960 --> 01:46:45,800 let me first go back to the admin page and actually just create some users. 2103 01:46:45,800 --> 01:46:49,140 I can go to users and then add, and let's add a user. 2104 01:46:49,140 --> 01:46:51,770 The user name will be Harry, for example. 2105 01:46:51,770 --> 01:46:54,467 And we'll go ahead and give Harry a password. 2106 01:46:54,467 --> 01:46:56,300 And we'll go ahead and save and add another. 2107 01:46:56,300 --> 01:46:59,860 Let's add maybe Ron as well. 2108 01:46:59,860 --> 01:47:01,310 Go ahead and add him. 2109 01:47:01,310 --> 01:47:02,572 We'll go ahead and save that. 2110 01:47:02,572 --> 01:47:04,780 And these users, they can have additional information 2111 01:47:04,780 --> 01:47:05,655 associated with them. 2112 01:47:05,655 --> 01:47:09,405 I can give Ron a name like Ron Weasley, RonWeasley@example.com 2113 01:47:09,405 --> 01:47:10,750 is his email address. 2114 01:47:10,750 --> 01:47:13,420 There are a bunch of default fields that Django gives you 2115 01:47:13,420 --> 01:47:14,732 for manipulating users. 2116 01:47:14,732 --> 01:47:17,440 And you can take these users and go ahead and add to those fields 2117 01:47:17,440 --> 01:47:18,010 if I want to. 2118 01:47:18,010 --> 01:47:21,690 Giving them a first name, last name, email address, and whatnot. 2119 01:47:21,690 --> 01:47:23,690 And you can also customize these fields as well. 2120 01:47:23,690 --> 01:47:26,980 If you'd like to add custom fields that you would like to keep track of with 2121 01:47:26,980 --> 01:47:29,590 regards to your individual users. 2122 01:47:29,590 --> 01:47:31,720 And I will go ahead and log out from Django admin 2123 01:47:31,720 --> 01:47:33,880 now because I don't need it anymore. 2124 01:47:33,880 --> 01:47:38,315 But now, if I go to /users, I'm not authenticated. 2125 01:47:38,315 --> 01:47:40,690 So what I see is a log in form that just looks like this. 2126 01:47:40,690 --> 01:47:44,020 A place for me to type in a user name and a password. 2127 01:47:44,020 --> 01:47:46,570 And of course, I could type in Harry's username and password. 2128 01:47:46,570 --> 01:47:49,840 But I haven't yet implemented the processing of that data yet. 2129 01:47:49,840 --> 01:47:52,280 So let's go ahead and do that now. 2130 01:47:52,280 --> 01:47:55,270 We'll go ahead and go back to views.py. 2131 01:47:55,270 --> 01:47:57,933 In the log in view, there are two ways the log in view function 2132 01:47:57,933 --> 01:47:58,600 could be called. 2133 01:47:58,600 --> 01:48:02,260 One is via the get request method, meaning just show me the log in form. 2134 01:48:02,260 --> 01:48:05,840 And one is via post, submit data to the log in form as well. 2135 01:48:05,840 --> 01:48:10,570 So if the request method is post, well then, 2136 01:48:10,570 --> 01:48:13,630 let me first get the user name which will be inside of the post 2137 01:48:13,630 --> 01:48:15,730 data in a field called user name. 2138 01:48:15,730 --> 01:48:17,710 And let me get the password, which will be 2139 01:48:17,710 --> 01:48:22,710 in a field in the request.post inside of password. 2140 01:48:22,710 --> 01:48:26,650 And now what I'd like to do is try to authenticate this user. 2141 01:48:26,650 --> 01:48:28,170 And how do I go about doing that? 2142 01:48:28,170 --> 01:48:30,170 Well, it turns out there are a couple of functions that Django 2143 01:48:30,170 --> 01:48:31,930 has given to me that I can import. 2144 01:48:31,930 --> 01:48:36,200 So from django.contrib.auth, auth for authentication. 2145 01:48:36,200 --> 01:48:38,990 I'm going to import three functions we're ultimately going to use. 2146 01:48:38,990 --> 01:48:42,660 One is authenticate that checks if user name and password are correct. 2147 01:48:42,660 --> 01:48:47,410 One is called log in, one is called log out. 2148 01:48:47,410 --> 01:48:50,930 And I can now use those functions inside of this log in view here. 2149 01:48:50,930 --> 01:48:52,880 After I've gotten the username and password, 2150 01:48:52,880 --> 01:48:54,410 I'd like to authenticate the user. 2151 01:48:54,410 --> 01:48:57,060 Check if the user name and password are correct. 2152 01:48:57,060 --> 01:49:02,240 So I'll go ahead and say user is equal to authenticate request username is 2153 01:49:02,240 --> 01:49:05,280 the username password equals password. 2154 01:49:05,280 --> 01:49:06,830 And so authenticate is a function. 2155 01:49:06,830 --> 01:49:10,400 Just takes the request, takes a user name, takes a password, 2156 01:49:10,400 --> 01:49:12,530 and if the user name and password are valid, 2157 01:49:12,530 --> 01:49:16,140 they give me back who the user actually is. 2158 01:49:16,140 --> 01:49:20,450 And as long as the user is not none, that 2159 01:49:20,450 --> 01:49:24,560 means the authentication was successful, and I can go ahead and log the user in. 2160 01:49:24,560 --> 01:49:26,060 How do I log the user in? 2161 01:49:26,060 --> 01:49:28,490 I use the log in function the Django gives me. 2162 01:49:28,490 --> 01:49:31,920 Logging in with this request, this user. 2163 01:49:31,920 --> 01:49:34,340 And now I can go ahead and redirect them. 2164 01:49:34,340 --> 01:49:38,540 HttpResponseRedirect the user back to the index route. 2165 01:49:38,540 --> 01:49:41,307 Back to the original route that the user started out as. 2166 01:49:41,307 --> 01:49:42,890 And so that's if the user is not none. 2167 01:49:42,890 --> 01:49:44,930 If the authentication was successful. 2168 01:49:44,930 --> 01:49:48,620 But otherwise, if the authentication failed, what should I do? 2169 01:49:48,620 --> 01:49:53,750 Well, let me go ahead and render the same users login page again. 2170 01:49:53,750 --> 01:49:56,270 But let me add some additional context. 2171 01:49:56,270 --> 01:50:02,400 The context will be a message that says invalid credentials. 2172 01:50:02,400 --> 01:50:05,060 And now, inside of login.html, I can just 2173 01:50:05,060 --> 01:50:08,450 add some logic that says, if there's a message, 2174 01:50:08,450 --> 01:50:12,620 then go ahead and display that message inside of a div. 2175 01:50:12,620 --> 01:50:14,090 And then endif to end that. 2176 01:50:14,090 --> 01:50:16,550 So if there is a message, we'll see the message printed. 2177 01:50:16,550 --> 01:50:19,410 Otherwise, we won't see it at all. 2178 01:50:19,410 --> 01:50:22,070 So now, if I go ahead and refresh the login page, 2179 01:50:22,070 --> 01:50:23,540 nothing seems to have changed. 2180 01:50:23,540 --> 01:50:25,900 But let's say I type in a user name that doesn't exist. 2181 01:50:25,900 --> 01:50:28,880 Hermione and some password and I log in. 2182 01:50:28,880 --> 01:50:30,380 Well, then I get this error message. 2183 01:50:30,380 --> 01:50:31,490 Invalid credentials. 2184 01:50:31,490 --> 01:50:33,840 We were not able to log the user in. 2185 01:50:33,840 --> 01:50:36,290 So what happens if we do successfully logged in? 2186 01:50:36,290 --> 01:50:40,468 Well, then the user is going to be taken to this index route. 2187 01:50:40,468 --> 01:50:43,010 And it looks like now we need to finish off this index route. 2188 01:50:43,010 --> 01:50:44,540 What does the index route do? 2189 01:50:44,540 --> 01:50:49,160 Well, let's go ahead and return render a template called users/user.html. 2190 01:50:49,160 --> 01:50:52,280 2191 01:50:52,280 --> 01:50:56,980 And inside of user.html, we'll go ahead and display 2192 01:50:56,980 --> 01:50:59,530 some information about the user. 2193 01:50:59,530 --> 01:51:02,230 We'll still extend user/layout because we're 2194 01:51:02,230 --> 01:51:04,360 going to use the same basic layout. 2195 01:51:04,360 --> 01:51:08,220 But in the body of this page, the information I want to show-- 2196 01:51:08,220 --> 01:51:13,060 if I want to say welcome and then like Harry or welcome Ron or whoever 2197 01:51:13,060 --> 01:51:15,050 the user happens to be. 2198 01:51:15,050 --> 01:51:17,440 And it turns out inside of Django templates, 2199 01:51:17,440 --> 01:51:21,880 I have access to the request that was used to make this HTTP request, which 2200 01:51:21,880 --> 01:51:26,290 means I also have access to request.user who is the user associated 2201 01:51:26,290 --> 01:51:27,580 with that request. 2202 01:51:27,580 --> 01:51:33,545 And if the user has a first name, I can access request.user.firstname. 2203 01:51:33,545 --> 01:51:35,920 And in addition to that, I can display other information. 2204 01:51:35,920 --> 01:51:39,700 Maybe their user name is request.user.username. 2205 01:51:39,700 --> 01:51:43,930 And maybe their email address is request.user.email. 2206 01:51:43,930 --> 01:51:47,650 And so I can show the user information about them. 2207 01:51:47,650 --> 01:51:51,490 Such that if Harry logs in, for example, I sign in as Harry, 2208 01:51:51,490 --> 01:51:56,050 sign in with Harry's credentials, click log in. 2209 01:51:56,050 --> 01:51:58,960 Well then, Harry sees a page that says welcome Harry. 2210 01:51:58,960 --> 01:52:00,040 Harry is logged in. 2211 01:52:00,040 --> 01:52:02,020 They are request.user. 2212 01:52:02,020 --> 01:52:07,150 And using that information, we can access first name, username, and email 2213 01:52:07,150 --> 01:52:11,950 as well just by accessing properties of request.user. 2214 01:52:11,950 --> 01:52:15,010 Now, last thing we need to add, which still doesn't yet exist, 2215 01:52:15,010 --> 01:52:17,510 is a way to actually log the user out. 2216 01:52:17,510 --> 01:52:21,370 And it turns out that just as Django has a log in function, 2217 01:52:21,370 --> 01:52:24,610 Django also has a log out function that handles log out for us so we 2218 01:52:24,610 --> 01:52:26,440 don't need to implement it ourselves. 2219 01:52:26,440 --> 01:52:31,690 So all our log out view needs to do is make a call to this log out function. 2220 01:52:31,690 --> 01:52:34,900 And then, figure out where should the user go after they've been logged out. 2221 01:52:34,900 --> 01:52:40,400 And you know what, let's go ahead and take them back to the login page 2222 01:52:40,400 --> 01:52:46,400 with a message of logged out to indicate that the user has now been logged out. 2223 01:52:46,400 --> 01:52:54,290 Then in user.html, we'll go ahead and add a link that will go to the log 2224 01:52:54,290 --> 01:52:59,900 out route that just says, log out, for example. 2225 01:52:59,900 --> 01:53:01,960 So now, when Harry goes back to Harry's page, 2226 01:53:01,960 --> 01:53:04,150 Harry sees a URL that says log out. 2227 01:53:04,150 --> 01:53:08,380 If Harry clicks log out, Harry gets logged out is brought back to this page 2228 01:53:08,380 --> 01:53:12,530 because now request.user.isauthenticated is going to be false. 2229 01:53:12,530 --> 01:53:14,410 There is no authenticated user. 2230 01:53:14,410 --> 01:53:17,470 And so they now see just the default login page. 2231 01:53:17,470 --> 01:53:21,310 And if now Ron were to log in, for example, using Ron's username 2232 01:53:21,310 --> 01:53:24,700 and Ron's password logging in, then Ron now 2233 01:53:24,700 --> 01:53:27,970 sees information associated with him as well. 2234 01:53:27,970 --> 01:53:30,010 So Django gives us a lot out of the box. 2235 01:53:30,010 --> 01:53:33,190 Gives us the ability to represent these models in admin interface, 2236 01:53:33,190 --> 01:53:34,540 to be able to manipulate them. 2237 01:53:34,540 --> 01:53:38,470 A migration system that allows us to very quickly make changes to our models 2238 01:53:38,470 --> 01:53:40,060 and apply them to our database. 2239 01:53:40,060 --> 01:53:42,520 And also, a built in user authentication system. 2240 01:53:42,520 --> 01:53:45,130 A system that allows us to very quickly enable 2241 01:53:45,130 --> 01:53:49,398 users to be able to log in, log out, from our web application as well. 2242 01:53:49,398 --> 01:53:51,190 So all of this are features that just helps 2243 01:53:51,190 --> 01:53:54,760 to make it so that we can very quickly take advantage of things like SQL 2244 01:53:54,760 --> 01:53:57,940 and models and migrations to build dynamic, interesting web 2245 01:53:57,940 --> 01:54:00,610 applications with data to back them up. 2246 01:54:00,610 --> 01:54:02,890 This was web programming with Python and JavaScript. 2247 01:54:02,890 --> 01:54:05,160 We will see you next time. 2248 01:54:05,160 --> 01:54:06,000