1 00:00:00,000 --> 00:00:00,500 2 00:00:00,500 --> 00:00:04,356 [MUSIC PLAYING] 3 00:00:04,356 --> 00:00:12,620 4 00:00:12,620 --> 00:00:14,986 BRIAN YU: Welcome back, everyone to CS50 Beyond. 5 00:00:14,986 --> 00:00:18,110 We'll just do a bit of a roadmap of where we are now and where we're going. 6 00:00:18,110 --> 00:00:20,300 This morning we took a look at SQL, some of which 7 00:00:20,300 --> 00:00:23,090 was hopefully review from CS50, and some of which was new. 8 00:00:23,090 --> 00:00:26,780 We introduced PostgreSQL, a way of using a different sort of database that 9 00:00:26,780 --> 00:00:29,690 doesn't exist just in a file the way SQLite did. 10 00:00:29,690 --> 00:00:33,650 Then we learned about how we can access that database programmatically using 11 00:00:33,650 --> 00:00:38,750 SQLAlchemy in order to add and select rows from the table. 12 00:00:38,750 --> 00:00:40,537 Then we gave you a morning project. 13 00:00:40,537 --> 00:00:43,370 I realize you might still be working on that, which is totally fine. 14 00:00:43,370 --> 00:00:46,580 The project consists of importing the data from a database of books 15 00:00:46,580 --> 00:00:47,480 into your database. 16 00:00:47,480 --> 00:00:50,220 You can continue working on that later this afternoon, as well. 17 00:00:50,220 --> 00:00:51,800 What we're going to do this afternoon is pick up 18 00:00:51,800 --> 00:00:54,410 from where we left off in the morning and continue diving more 19 00:00:54,410 --> 00:00:56,120 into the world of databases. 20 00:00:56,120 --> 00:00:58,370 In particular, we will be linking it to our discussion 21 00:00:58,370 --> 00:01:01,370 from a couple of days ago about object oriented programming in Python. 22 00:01:01,370 --> 00:01:04,370 We'll see how we can use this idea of objects and classes 23 00:01:04,370 --> 00:01:06,920 to allow ourselves to be very expressive with the way 24 00:01:06,920 --> 00:01:10,734 that we're working with databases in Python and with SQL. 25 00:01:10,734 --> 00:01:12,650 We'll do that with the rest of this afternoon. 26 00:01:12,650 --> 00:01:14,570 Tomorrow, we'll do a bit of a context switch 27 00:01:14,570 --> 00:01:17,236 and spend two days taking a look at React, a brand new framework 28 00:01:17,236 --> 00:01:18,500 for building user interfaces. 29 00:01:18,500 --> 00:01:22,040 It wasn't introduced in CS50, so it'll be new for almost all of you. 30 00:01:22,040 --> 00:01:25,040 It'll be an opportunity to show how we can use some more modern language 31 00:01:25,040 --> 00:01:27,530 features and some more modern libraries to make it easier 32 00:01:27,530 --> 00:01:29,738 to solve some of the problems that we've looked at so 33 00:01:29,738 --> 00:01:33,140 far in web applications over the course of this week. 34 00:01:33,140 --> 00:01:36,800 Let's go ahead and get started by diving back into the idea of classes. 35 00:01:36,800 --> 00:01:42,230 We introduced classes as a way of describing objects in Python. 36 00:01:42,230 --> 00:01:45,990 You might imagine that as we think about the databases that we've created-- 37 00:01:45,990 --> 00:01:48,970 for example, a table that we're going to store flights in-- 38 00:01:48,970 --> 00:01:53,630 that we could represent that using a Python class. 39 00:01:53,630 --> 00:01:56,250 Just to show an example of what that might look like, 40 00:01:56,250 --> 00:02:01,010 I'll go ahead and open up classes0.py. 41 00:02:01,010 --> 00:02:03,020 We'll make that bigger. 42 00:02:03,020 --> 00:02:06,410 Here's a Python class much like the one that we created a couple of days 43 00:02:06,410 --> 00:02:08,412 ago to represent a point in Python. 44 00:02:08,412 --> 00:02:10,745 This is a class that's just going to represent a flight. 45 00:02:10,745 --> 00:02:11,810 It's written in Python. 46 00:02:11,810 --> 00:02:13,170 There's no SQL just yet. 47 00:02:13,170 --> 00:02:15,110 We'll come back to SQL in just a moment. 48 00:02:15,110 --> 00:02:17,690 Here, the __init__ function, as you might recall from before, 49 00:02:17,690 --> 00:02:20,620 is the function or method that we use to create a new object, 50 00:02:20,620 --> 00:02:22,460 or in this case a new flight. 51 00:02:22,460 --> 00:02:25,820 When we create a new flight, we're going to give it three properties, an origin, 52 00:02:25,820 --> 00:02:28,040 a destination, and a duration. 53 00:02:28,040 --> 00:02:31,310 We're going to store all of those properties inside of the object. 54 00:02:31,310 --> 00:02:34,700 The object itself is just called "self," so we're going to store inside 55 00:02:34,700 --> 00:02:37,610 of self.origin whatever the origin of the flight is. 56 00:02:37,610 --> 00:02:41,060 We'll store the destination in self.destination and the duration 57 00:02:41,060 --> 00:02:42,210 self.duration. 58 00:02:42,210 --> 00:02:44,570 This is basically the same code that we saw with points. 59 00:02:44,570 --> 00:02:47,330 The only difference is that now we're calling it a "flight," 60 00:02:47,330 --> 00:02:49,940 and we're giving it the properties that a flight has. 61 00:02:49,940 --> 00:02:54,320 Once we have this class, we can begin to do interesting things with it. 62 00:02:54,320 --> 00:02:56,187 Take a look at classes1.py, for example. 63 00:02:56,187 --> 00:02:59,270 All the source code is available online, if you want to take a look at it. 64 00:02:59,270 --> 00:03:01,220 We define the flight at the top. 65 00:03:01,220 --> 00:03:03,140 Then in the main function, you might imagine 66 00:03:03,140 --> 00:03:06,139 that we could have code that looks a little something like this, whereby 67 00:03:06,139 --> 00:03:08,450 first, we create a brand new flight. 68 00:03:08,450 --> 00:03:13,160 And to create a flight, we use that __init__ method implicitly just 69 00:03:13,160 --> 00:03:16,790 by saying, "I would like to create a flight whose origin is New York, 70 00:03:16,790 --> 00:03:19,880 destination is Paris, and duration is some number of minutes." 71 00:03:19,880 --> 00:03:22,460 Line 12 is creating a new flight, where I'm 72 00:03:22,460 --> 00:03:26,720 providing arguments that define what the different properties of the flight 73 00:03:26,720 --> 00:03:28,430 are going to be. 74 00:03:28,430 --> 00:03:31,390 Once we have it, we can make changes to that object. 75 00:03:31,390 --> 00:03:35,000 On line 15, I was able to say, "OK, take the duration of the flight, 76 00:03:35,000 --> 00:03:37,850 then go ahead and add 10 to it," or, "I want to take the duration 77 00:03:37,850 --> 00:03:39,890 and increase it by some number." 78 00:03:39,890 --> 00:03:44,150 Later on, I can print out the origin of the flight, the destination, 79 00:03:44,150 --> 00:03:45,579 and the duration. 80 00:03:45,579 --> 00:03:47,870 This is just the basics of object oriented programming. 81 00:03:47,870 --> 00:03:51,230 When I create a flight object, I'm able to manipulate its properties, 82 00:03:51,230 --> 00:03:53,017 or the values stored inside the object. 83 00:03:53,017 --> 00:03:54,725 Then I can do something with those values 84 00:03:54,725 --> 00:03:56,840 like, for example, print them out. 85 00:03:56,840 --> 00:04:01,890 I type python classes1.pi, for example, and it prints out the origin, New York; 86 00:04:01,890 --> 00:04:09,950 the destination, Paris; and 550, which is the duration after you add 10 to it. 87 00:04:09,950 --> 00:04:12,200 These are all things that we saw a couple of days ago, 88 00:04:12,200 --> 00:04:14,465 but I'm showing it to you again just as a refresher. 89 00:04:14,465 --> 00:04:16,048 Are there questions about any of that? 90 00:04:16,048 --> 00:04:18,300 91 00:04:18,300 --> 00:04:18,800 All right. 92 00:04:18,800 --> 00:04:20,990 Once we have this Flight class, we can also 93 00:04:20,990 --> 00:04:25,460 add methods to it, which is code that we can use to operate on flights. 94 00:04:25,460 --> 00:04:28,980 I'll show you classes2.py, for example. 95 00:04:28,980 --> 00:04:33,560 You might have a print_info function, for example, which prints out 96 00:04:33,560 --> 00:04:35,720 information out about that flight. 97 00:04:35,720 --> 00:04:38,240 When I run the print_info function, or method, 98 00:04:38,240 --> 00:04:41,750 it's going to print out whatever the flight's origin is. 99 00:04:41,750 --> 00:04:45,200 In order to access the flight's origin, which is stored inside of the object, 100 00:04:45,200 --> 00:04:49,760 or inside of self, we say self.origin. 101 00:04:49,760 --> 00:04:53,180 We print out the destination of the flight via self.destination, 102 00:04:53,180 --> 00:04:58,730 and we print out the duration of the flight via self.duration. 103 00:04:58,730 --> 00:05:03,250 In the main function, we create two flights, flight F1 and F2. 104 00:05:03,250 --> 00:05:06,890 F1 is a flight from New York to Paris with a duration of 540 minutes. 105 00:05:06,890 --> 00:05:10,130 Then I can just run the print_info function on that flight 106 00:05:10,130 --> 00:05:12,740 to print out information about F1. 107 00:05:12,740 --> 00:05:14,630 I can create a separate flight, F2, which 108 00:05:14,630 --> 00:05:18,140 has its own origin, its own destination, and its own duration. 109 00:05:18,140 --> 00:05:20,990 These variables are what we would call "instanced variables." 110 00:05:20,990 --> 00:05:25,010 Every time we create a new flight object, that instance of the object 111 00:05:25,010 --> 00:05:28,280 has its own set of the variables origin, destination, and duration. 112 00:05:28,280 --> 00:05:32,670 The two flights can have distinct variables that are stored there. 113 00:05:32,670 --> 00:05:39,240 If I type "python classes2.py," I get the first flight, New York to Paris, 114 00:05:39,240 --> 00:05:42,390 and then I get the second flight to print out, which is Tokyo to Shanghai, 115 00:05:42,390 --> 00:05:42,960 for example. 116 00:05:42,960 --> 00:05:45,860 117 00:05:45,860 --> 00:05:47,842 Questions about any of that? 118 00:05:47,842 --> 00:05:48,804 Yeah? 119 00:05:48,804 --> 00:05:51,690 AUDIENCE: If you go into the code, is it necessary to say 120 00:05:51,690 --> 00:05:54,576 that origin = "New York" and destination = "Paris", 121 00:05:54,576 --> 00:05:57,070 or can you just leave it as [INAUDIBLE]? 122 00:05:57,070 --> 00:05:58,070 BRIAN YU: Good question. 123 00:05:58,070 --> 00:06:01,028 On line 16, do I need to say orgin = "New York", destination = "Paris", 124 00:06:01,028 --> 00:06:02,461 and duration = 540? 125 00:06:02,461 --> 00:06:02,960 No. 126 00:06:02,960 --> 00:06:06,320 In this particular case, I'm giving the arguments in their exact order. 127 00:06:06,320 --> 00:06:10,970 I can just use those names without needing to specify 128 00:06:10,970 --> 00:06:13,700 the "duration =" or "destination =". 129 00:06:13,700 --> 00:06:17,270 It will be useful to label them once we start diving 130 00:06:17,270 --> 00:06:20,450 back into the world of SQL, which we will in a couple of moments. 131 00:06:20,450 --> 00:06:22,050 For now, at least, it's not necessary. 132 00:06:22,050 --> 00:06:22,550 Yeah? 133 00:06:22,550 --> 00:06:27,613 AUDIENCE: For the left print_info function, [INAUDIBLE].. 134 00:06:27,613 --> 00:06:32,060 135 00:06:32,060 --> 00:06:34,220 BRIAN YU: We only need "self" for print_info 136 00:06:34,220 --> 00:06:36,511 because in order to print information about the flight, 137 00:06:36,511 --> 00:06:39,860 the only information the flight needs is the object itself. 138 00:06:39,860 --> 00:06:42,920 We already provided it with an origin, destination, and duration 139 00:06:42,920 --> 00:06:44,690 when we created the object. 140 00:06:44,690 --> 00:06:47,829 When we create the object on line 16, for example, 141 00:06:47,829 --> 00:06:50,120 we have to provide all the information about the origin 142 00:06:50,120 --> 00:06:52,578 of the flight, the destination of the flight, and so forth. 143 00:06:52,578 --> 00:06:55,755 But line 17, in order to print out information about that flight, 144 00:06:55,755 --> 00:06:58,880 I don't need to additionally tell it, "Oh, this is a flight from New York," 145 00:06:58,880 --> 00:07:01,910 because F1, the object that represents the flight, 146 00:07:01,910 --> 00:07:05,420 is already storing information about its own origin and destination. 147 00:07:05,420 --> 00:07:08,850 That information is contained in the object. 148 00:07:08,850 --> 00:07:09,350 Yeah? 149 00:07:09,350 --> 00:07:12,242 AUDIENCE: [INAUDIBLE] dot notation for the purposes of print_info, 150 00:07:12,242 --> 00:07:15,120 because [INAUDIBLE]? 151 00:07:15,120 --> 00:07:16,870 BRIAN YU: How does this dot notation work? 152 00:07:16,870 --> 00:07:18,910 F1, in this case, is an object. 153 00:07:18,910 --> 00:07:25,210 When I say F1.print_info, I am calling the print_info method, or function, 154 00:07:25,210 --> 00:07:27,010 which is located here. 155 00:07:27,010 --> 00:07:31,360 Whatever object I'm calling the print_info function with gets passed in 156 00:07:31,360 --> 00:07:33,670 as "self" automatically by Python. 157 00:07:33,670 --> 00:07:38,020 If I say F1.print_info, then "self" is going to be F1 the object, 158 00:07:38,020 --> 00:07:40,930 so I'm going to get F1's origin, destination, and duration. 159 00:07:40,930 --> 00:07:44,530 Whereas down here on line 20, when I print out information about flight two, 160 00:07:44,530 --> 00:07:45,970 F2 is going to be "self." 161 00:07:45,970 --> 00:07:49,771 So self.origin will be flight two's origin, which in this case is Tokyo. 162 00:07:49,771 --> 00:07:50,270 Yeah? 163 00:07:50,270 --> 00:07:56,620 AUDIENCE: Is there a function [INAUDIBLE] 164 00:07:56,620 --> 00:07:58,370 BRIAN YU: Yes, you could define a function 165 00:07:58,370 --> 00:07:59,930 that takes a flight as its arguments. 166 00:07:59,930 --> 00:08:02,655 That's certainly something you could do. 167 00:08:02,655 --> 00:08:06,061 AUDIENCE: [INAUDIBLE] 168 00:08:06,061 --> 00:08:07,560 BRIAN YU: The actual print function? 169 00:08:07,560 --> 00:08:08,060 Yes. 170 00:08:08,060 --> 00:08:11,040 171 00:08:11,040 --> 00:08:16,210 There is a special function in Python called __repr__. 172 00:08:16,210 --> 00:08:17,850 It stands for "representation." 173 00:08:17,850 --> 00:08:21,299 It will determine how an object will be represented 174 00:08:21,299 --> 00:08:24,090 when you try to print it out using the print function, for example. 175 00:08:24,090 --> 00:08:25,048 You could do that, too. 176 00:08:25,048 --> 00:08:28,060 177 00:08:28,060 --> 00:08:31,041 Other things? 178 00:08:31,041 --> 00:08:31,540 All right. 179 00:08:31,540 --> 00:08:37,000 Now what we'd like to do is introduce a new concept, namely the concept 180 00:08:37,000 --> 00:08:40,000 of ORMs, or Object Relational Mappings. 181 00:08:40,000 --> 00:08:42,521 The idea of an ORM is to be a tool-- 182 00:08:42,521 --> 00:08:45,020 one of which is SQLAlchemy, which we looked at this morning, 183 00:08:45,020 --> 00:08:46,270 but there are others as well-- 184 00:08:46,270 --> 00:08:50,170 that basically lets you treat rows and tables inside 185 00:08:50,170 --> 00:08:55,150 of a database the same way you would treat objects and classes in an object 186 00:08:55,150 --> 00:08:57,040 oriented language like Python. 187 00:08:57,040 --> 00:09:00,560 We are able to take advantage of this object syntax 188 00:09:00,560 --> 00:09:03,680 and still manipulate a database. 189 00:09:03,680 --> 00:09:07,910 I'll show you what that would look like in an example like our flights 190 00:09:07,910 --> 00:09:11,900 and passengers that we were looking at earlier this morning. 191 00:09:11,900 --> 00:09:15,125 I'll go ahead and open up models.py, which 192 00:09:15,125 --> 00:09:21,140 is a file that's going to store all of our classes 193 00:09:21,140 --> 00:09:23,900 that are going to represent database tables. 194 00:09:23,900 --> 00:09:26,480 I'm importing SQLAlchemy, this time from a package 195 00:09:26,480 --> 00:09:29,150 called Flask-SQLAlchemy, which is a separate package that 196 00:09:29,150 --> 00:09:32,780 uses SQLAlchemy and adds some convenience features that 197 00:09:32,780 --> 00:09:34,160 make it easier to use with Flask. 198 00:09:34,160 --> 00:09:37,460 You may need to install this in order to get it to work. 199 00:09:37,460 --> 00:09:41,690 We create a variable called db that's going to represent our database. 200 00:09:41,690 --> 00:09:44,240 Now we have a very similar syntax to before, 201 00:09:44,240 --> 00:09:45,800 although it's a little more complex. 202 00:09:45,800 --> 00:09:49,400 We're going to define one class for every table that we have, 203 00:09:49,400 --> 00:09:51,350 so we define a class called Flight that's 204 00:09:51,350 --> 00:09:53,390 going to represent our Flights table. 205 00:09:53,390 --> 00:09:57,110 Here we're giving it a special property called __tablename__. 206 00:09:57,110 --> 00:10:00,230 This is basically telling the ORM, SQLAlchemy, 207 00:10:00,230 --> 00:10:03,896 the name of the table in the database that corresponds to this class. 208 00:10:03,896 --> 00:10:07,020 Because I'm going to use these classes to be able to interact with my data, 209 00:10:07,020 --> 00:10:09,530 SQLAlchemy needs to know what the name of the table 210 00:10:09,530 --> 00:10:11,060 is that corresponds to the class. 211 00:10:11,060 --> 00:10:12,768 I'm telling it, "All right, this is going 212 00:10:12,768 --> 00:10:15,830 to correspond to the table called Flights," for example. 213 00:10:15,830 --> 00:10:21,050 Then I'm going to give the Flight class a property for every column 214 00:10:21,050 --> 00:10:24,350 that I want to have inside of my database. 215 00:10:24,350 --> 00:10:28,010 It has an id column that is an integer, for example. 216 00:10:28,010 --> 00:10:31,890 Primary key is set to true because the id is the primary key. 217 00:10:31,890 --> 00:10:34,790 I'm going to say that the origin is also going to be a column. 218 00:10:34,790 --> 00:10:36,470 It's going to be a string. 219 00:10:36,470 --> 00:10:39,570 nullable=false just means that every flight needs to have an origin. 220 00:10:39,570 --> 00:10:41,366 There's no way to have a null origin. 221 00:10:41,366 --> 00:10:43,490 Likewise, every flight needs to have a destination. 222 00:10:43,490 --> 00:10:45,680 That destination is also going to be a string. 223 00:10:45,680 --> 00:10:49,250 It's also also not nullable, meaning it needs to have a destination. 224 00:10:49,250 --> 00:10:51,770 Finally, the duration is also a column. 225 00:10:51,770 --> 00:10:53,930 That column is an integer. 226 00:10:53,930 --> 00:10:55,340 It also needs to have a duration. 227 00:10:55,340 --> 00:10:57,440 We can't have an empty duration. 228 00:10:57,440 --> 00:11:00,110 There's a lot of new syntax here. 229 00:11:00,110 --> 00:11:02,210 Since there is a lot of new syntax, I'll show you 230 00:11:02,210 --> 00:11:04,742 some slides that will introduce a lot of the syntax to you. 231 00:11:04,742 --> 00:11:07,200 There's no need to feel like you need to memorize all this. 232 00:11:07,200 --> 00:11:09,491 Feel free to refer back to the slides with the examples 233 00:11:09,491 --> 00:11:11,720 if you're looking for the specifics of the syntax. 234 00:11:11,720 --> 00:11:15,260 The idea to get across here is that I'm now using a Python 235 00:11:15,260 --> 00:11:20,030 class to represent what is a SQL table. 236 00:11:20,030 --> 00:11:21,777 I can do the same thing with passengers. 237 00:11:21,777 --> 00:11:23,610 I'm defining a class called Passenger that's 238 00:11:23,610 --> 00:11:26,820 going to stand in for a passenger inside of my Passengers table. 239 00:11:26,820 --> 00:11:30,030 I'll give it this __tablename__ property that says, "OK, 240 00:11:30,030 --> 00:11:32,190 the name of this table is called Passengers. 241 00:11:32,190 --> 00:11:35,970 Every passenger has an id, every passenger has a name, 242 00:11:35,970 --> 00:11:38,872 and every passenger also has a flight_id. 243 00:11:38,872 --> 00:11:41,580 Here, the flight_id is the one that looks a little bit different. 244 00:11:41,580 --> 00:11:43,890 The flight_id is an integer, but I'm also 245 00:11:43,890 --> 00:11:47,250 going to specify that the flight_id is a foreign key. 246 00:11:47,250 --> 00:11:50,580 In particular, the flight_id is going to reference some key of another table. 247 00:11:50,580 --> 00:11:52,890 That's what it means for something to be a foreign key. 248 00:11:52,890 --> 00:11:56,830 The flight_id is going to reference a table called Flights, 249 00:11:56,830 --> 00:11:59,430 and namely a column called id. 250 00:11:59,430 --> 00:12:07,750 The id column of the Flights table is what this db.foreignkey is referencing. 251 00:12:07,750 --> 00:12:11,150 Thus far, we haven't quite seen why we would want to do something like this, 252 00:12:11,150 --> 00:12:13,990 but hopefully we can at least now see that these classes are 253 00:12:13,990 --> 00:12:16,960 modeled or shaped the shapes of the tables 254 00:12:16,960 --> 00:12:18,820 that we created earlier this morning. 255 00:12:18,820 --> 00:12:20,084 Question? 256 00:12:20,084 --> 00:12:23,080 AUDIENCE: What's [INAUDIBLE]? 257 00:12:23,080 --> 00:12:24,580 BRIAN YU: What is a Python class? 258 00:12:24,580 --> 00:12:29,920 You can think of a Python class as a blueprint for what 259 00:12:29,920 --> 00:12:32,140 it is that an object looks like. 260 00:12:32,140 --> 00:12:34,510 In other words, what is it that a flight looks like? 261 00:12:34,510 --> 00:12:38,772 Well, a flight has an id, an origin, a destination, and a duration. 262 00:12:38,772 --> 00:12:40,480 The class is going to be a blueprint that 263 00:12:40,480 --> 00:12:41,980 lets me create many of these things. 264 00:12:41,980 --> 00:12:46,270 I can create many flights, each of which has an id, an origin, a destination, 265 00:12:46,270 --> 00:12:47,940 and a duration. 266 00:12:47,940 --> 00:12:51,197 That would be one way to think of it. 267 00:12:51,197 --> 00:12:52,030 Other things so far? 268 00:12:52,030 --> 00:12:55,480 269 00:12:55,480 --> 00:12:55,980 All right. 270 00:12:55,980 --> 00:12:59,130 The nice thing about defining classes this way 271 00:12:59,130 --> 00:13:05,740 is that we can begin to use these classes to interact with the database. 272 00:13:05,740 --> 00:13:08,620 I'll show you an example in create.py. 273 00:13:08,620 --> 00:13:15,359 In order to create tables when we were writing raw SQL, 274 00:13:15,359 --> 00:13:18,400 we either had to go into Adminer and say, "OK, let me create a new table. 275 00:13:18,400 --> 00:13:20,800 Give it these columns and choose things from a dropdown." 276 00:13:20,800 --> 00:13:22,633 Or you might have had to say something like, 277 00:13:22,633 --> 00:13:25,630 "All right, go to the raw SQL and type in a CREATE TABLE command." 278 00:13:25,630 --> 00:13:30,910 All you have to do now in Python is say, from models import *. 279 00:13:30,910 --> 00:13:34,300 In other words, from the models.py file that 280 00:13:34,300 --> 00:13:37,010 defines the Flight class and the Passenger class, 281 00:13:37,010 --> 00:13:39,550 go ahead and import *, or import everything 282 00:13:39,550 --> 00:13:42,040 into this file called create.py. 283 00:13:42,040 --> 00:13:45,280 Inside of main(), the only useful line here that we care about is 284 00:13:45,280 --> 00:13:47,590 db.create_all(). 285 00:13:47,590 --> 00:13:49,600 That is a function built into SQLAlchemy that 286 00:13:49,600 --> 00:13:54,010 basically says, take all of these models and create tables for them. 287 00:13:54,010 --> 00:13:56,770 Even if I didn't run any CREATE TABLE syntax at all, 288 00:13:56,770 --> 00:13:58,960 I could just run create.py and that would 289 00:13:58,960 --> 00:14:01,812 result in creating all of the tables inside of my database. 290 00:14:01,812 --> 00:14:03,520 A couple of you earlier this morning were 291 00:14:03,520 --> 00:14:06,200 asking, if I don't want to go into Adminer to create tables, 292 00:14:06,200 --> 00:14:09,220 is there a way in Python to programmatically create those tables? 293 00:14:09,220 --> 00:14:10,636 This would be how you would do it. 294 00:14:10,636 --> 00:14:13,120 You just define the classes that define the structure 295 00:14:13,120 --> 00:14:17,320 of what it is that your tables need, or what columns they should have. 296 00:14:17,320 --> 00:14:21,340 Then you call db.create_all(), and that will create all of your tables inside 297 00:14:21,340 --> 00:14:22,420 of your database. 298 00:14:22,420 --> 00:14:25,300 Again, we're pulling that database from an environment variable 299 00:14:25,300 --> 00:14:26,560 called DATABASE_URL. 300 00:14:26,560 --> 00:14:28,750 That's the same as what you were doing this morning. 301 00:14:28,750 --> 00:14:30,940 All you have to do is, on a Mac or on Linux, 302 00:14:30,940 --> 00:14:35,870 type "export DATABASE_URL =" followed by the URL of the database. 303 00:14:35,870 --> 00:14:38,200 On Windows, you would use "set" instead of "export" 304 00:14:38,200 --> 00:14:43,960 to set the database URL such that you can access it later. 305 00:14:43,960 --> 00:14:44,680 Questions so far? 306 00:14:44,680 --> 00:14:47,780 307 00:14:47,780 --> 00:14:49,030 Yeah? 308 00:14:49,030 --> 00:14:54,290 AUDIENCE: If you go back to the actual class, to the logo, it says db.string. 309 00:14:54,290 --> 00:14:56,948 What kind of string is that in the [INAUDIBLE]?? 310 00:14:56,948 --> 00:15:00,960 311 00:15:00,960 --> 00:15:03,170 BRIAN YU: In this case, db.string is just 312 00:15:03,170 --> 00:15:05,810 going to represent some text-bassed input. 313 00:15:05,810 --> 00:15:07,880 We leave it up to SQLAlchemy to decide, based 314 00:15:07,880 --> 00:15:10,940 on the specific database, what is the exact SQL query that needs 315 00:15:10,940 --> 00:15:12,590 to run in order to make that work. 316 00:15:12,590 --> 00:15:16,070 All we need to care about is that it is a string, or some sort of text. 317 00:15:16,070 --> 00:15:18,346 This is what we might consider an "abstraction." 318 00:15:18,346 --> 00:15:21,470 You don't need to worry about the low level details of what exact SQL query 319 00:15:21,470 --> 00:15:22,460 is being run. 320 00:15:22,460 --> 00:15:25,310 We just have to worry about the fact that it is, in fact, a string. 321 00:15:25,310 --> 00:15:28,460 That's going to be one of the themes of ORMs, in general. 322 00:15:28,460 --> 00:15:31,970 We are no longer actually going to need to write any SQL queries at all. 323 00:15:31,970 --> 00:15:35,150 No more typing INSERT or UPDATE in all capital letters 324 00:15:35,150 --> 00:15:37,590 and using the specifics of SQL syntax. 325 00:15:37,590 --> 00:15:41,120 We can just interact only with Python objects and Python classes 326 00:15:41,120 --> 00:15:43,430 to be able to achieve entirely the same things. 327 00:15:43,430 --> 00:15:47,695 We're going to slowly move away from that syntax, in general. 328 00:15:47,695 --> 00:15:49,000 YU: Yeah. 329 00:15:49,000 --> 00:15:52,030 AUDIENCE: In the passenger's class, the foreign key, 330 00:15:52,030 --> 00:15:55,530 is that [INAUDIBLE] or that column doesn't exist yet, 331 00:15:55,530 --> 00:16:00,330 because you're [INAUDIBLE] that table yet, will this still run? 332 00:16:00,330 --> 00:16:02,870 BRIAN YU: Will this still-- you'll need to-- 333 00:16:02,870 --> 00:16:04,620 AUDIENCE: You need to create like, those-- 334 00:16:04,620 --> 00:16:07,200 BRIAN YU: If you try to query the passenger table 335 00:16:07,200 --> 00:16:09,117 but there is no flights table, you'll probably 336 00:16:09,117 --> 00:16:12,283 run into issues, if you try and run any of the code that we're about to run, 337 00:16:12,283 --> 00:16:13,300 when this doesn't exist. 338 00:16:13,300 --> 00:16:13,932 Yeah. 339 00:16:13,932 --> 00:16:15,848 AUDIENCE: I don't know if you're [INAUDIBLE].. 340 00:16:15,848 --> 00:16:20,540 Does this take a lot more time like when it's running than [INAUDIBLE]?? 341 00:16:20,540 --> 00:16:23,560 BRIAN YU: Does this take more time? 342 00:16:23,560 --> 00:16:26,150 There is probably some overhead for the ORM, 343 00:16:26,150 --> 00:16:28,536 but ultimately in the scheme of like larger queries, 344 00:16:28,536 --> 00:16:30,410 it's not going to be particularly noticeable. 345 00:16:30,410 --> 00:16:32,490 And you'll find that from a programmatic perspective, 346 00:16:32,490 --> 00:16:34,460 it's actually going to be a lot easier most likely 347 00:16:34,460 --> 00:16:36,590 to look at that syntax and reason about the syntax. 348 00:16:36,590 --> 00:16:40,310 And it'll be ultimately a net benefit usually, 349 00:16:40,310 --> 00:16:43,280 that most people don't want to be writing the raw SQL queries. 350 00:16:43,280 --> 00:16:46,490 And it makes much more sense to them to be able to interact with the objects, 351 00:16:46,490 --> 00:16:47,360 for instance. 352 00:16:47,360 --> 00:16:50,193 And I'll show you examples of that in a moment that'll help motivate 353 00:16:50,193 --> 00:16:53,080 why it is that we're doing this. 354 00:16:53,080 --> 00:16:57,090 So OK, what did importing data into a database look like before? 355 00:16:57,090 --> 00:16:59,050 Before we had something that looks like this. 356 00:16:59,050 --> 00:17:01,140 This is import 0.pi, which is the same as what 357 00:17:01,140 --> 00:17:03,450 we were doing this morning in order to import data, 358 00:17:03,450 --> 00:17:07,349 where we were opening up a file, reading it into a CSV file, 359 00:17:07,349 --> 00:17:12,000 and then looping over that CSV file and saying, OK, for every row, 360 00:17:12,000 --> 00:17:14,609 every row has an origin, destination, and duration. 361 00:17:14,609 --> 00:17:16,920 And we were running this query, db.execute, 362 00:17:16,920 --> 00:17:21,390 and then executing a SQL query, insert into the flights table these columns 363 00:17:21,390 --> 00:17:24,900 and these values using these placeholder symbols so that we could avoid 364 00:17:24,900 --> 00:17:29,760 SQL injection attacks, and passing it in this Python dictionary of what 365 00:17:29,760 --> 00:17:33,460 the origin is, what the destination is, what the duration is. 366 00:17:33,460 --> 00:17:40,080 Here's what that same thing is going to look like if we use an ORM instead. 367 00:17:40,080 --> 00:17:42,720 So I make the small change of, OK, from models import star. 368 00:17:42,720 --> 00:17:48,370 I'm going to import all of my models, those classes, into this application. 369 00:17:48,370 --> 00:17:52,390 And then for every row in this CSV file, these first couple lines 370 00:17:52,390 --> 00:17:53,920 are exactly the same. 371 00:17:53,920 --> 00:17:57,220 To create the flight, rather than type insert into something, 372 00:17:57,220 --> 00:18:00,880 I'm just going to create a new flight object the same way that I would 373 00:18:00,880 --> 00:18:03,400 in Python object oriented programming more generally, 374 00:18:03,400 --> 00:18:05,650 with a line that looks a little something like line 16 375 00:18:05,650 --> 00:18:09,240 here, where I say, OK, let's create a new variable called flight. 376 00:18:09,240 --> 00:18:13,750 And that's going to be equal to capital F Flight, create a brand new flight. 377 00:18:13,750 --> 00:18:16,090 Origin equals whatever the origin was. 378 00:18:16,090 --> 00:18:18,430 The destination equals whatever the destination was. 379 00:18:18,430 --> 00:18:21,160 The duration equals whatever the duration was. 380 00:18:21,160 --> 00:18:23,770 And I've now created a new flight object. 381 00:18:23,770 --> 00:18:26,020 And in creating the new flight object, with SQLAlchemy 382 00:18:26,020 --> 00:18:27,880 is going to effectively treat that as is, 383 00:18:27,880 --> 00:18:31,360 I've created a new flight inside of my flights table. 384 00:18:31,360 --> 00:18:35,440 And to add it to the database it's as simple as saying db.session.add, 385 00:18:35,440 --> 00:18:37,780 followed by the name of this variable. 386 00:18:37,780 --> 00:18:39,860 Name of the variable happens to be flight. 387 00:18:39,860 --> 00:18:41,540 So I've created the object. 388 00:18:41,540 --> 00:18:43,810 And then I've said, add it to the database. 389 00:18:43,810 --> 00:18:46,630 And at the very end I do db.session.commit to say, OK, commit 390 00:18:46,630 --> 00:18:47,690 these changes. 391 00:18:47,690 --> 00:18:50,339 These are the changes that I actually want to make. 392 00:18:50,339 --> 00:18:51,380 And so slight difference. 393 00:18:51,380 --> 00:18:54,380 Now I'm inserting data into the database but I'm never 394 00:18:54,380 --> 00:18:59,270 using any SQL queries that are raw SQL queries. 395 00:18:59,270 --> 00:19:02,750 I'm just using these objects and saying create a new flight object 396 00:19:02,750 --> 00:19:05,260 and add it to the database. 397 00:19:05,260 --> 00:19:07,150 Questions about that? 398 00:19:07,150 --> 00:19:07,720 This is new. 399 00:19:07,720 --> 00:19:09,940 We're using Python object oriented syntax. 400 00:19:09,940 --> 00:19:11,860 And so it'll look a little bit different. 401 00:19:11,860 --> 00:19:14,140 But we're moving to higher levels of abstraction 402 00:19:14,140 --> 00:19:17,251 that are going to let us reason about larger problems as we work with SQL. 403 00:19:17,251 --> 00:19:17,750 Yeah. 404 00:19:17,750 --> 00:19:20,696 AUDIENCE: For line 16, you say origin, destination, duration. 405 00:19:20,696 --> 00:19:24,133 Do those have to show up in like a subsequent loop 406 00:19:24,133 --> 00:19:27,079 or do you just specify those as titles for each 407 00:19:27,079 --> 00:19:31,010 of your categories [INAUDIBLE]? 408 00:19:31,010 --> 00:19:32,740 BRIAN YU: So when I loop over the reader, 409 00:19:32,740 --> 00:19:35,170 I'm looping over one row at a time. 410 00:19:35,170 --> 00:19:40,390 And each row of the CSV file has three parts. 411 00:19:40,390 --> 00:19:43,172 And all I'm doing here is assigning a variable name 412 00:19:43,172 --> 00:19:44,380 to each of those three parts. 413 00:19:44,380 --> 00:19:46,980 I'm saying let Origin be the variable name for the first column. 414 00:19:46,980 --> 00:19:49,000 Let Destination be the variable name for the second column 415 00:19:49,000 --> 00:19:49,960 and Duration be the third. 416 00:19:49,960 --> 00:19:51,710 AUDIENCE: But do you have to [INAUDIBLE]?? 417 00:19:51,710 --> 00:19:56,420 418 00:19:56,420 --> 00:19:59,150 BRIAN YU: I'm just naming them so that I can reference them later 419 00:19:59,150 --> 00:20:00,483 when I actually add the flights. 420 00:20:00,483 --> 00:20:03,061 So I could say this flight has this origin and destination. 421 00:20:03,061 --> 00:20:05,060 If I wanted to, I don't have to separate it out. 422 00:20:05,060 --> 00:20:09,470 I could say for every row in the reader and just read one row at a time. 423 00:20:09,470 --> 00:20:12,770 And then I'll know, OK, the origin that's stored in row square brackets 0, 424 00:20:12,770 --> 00:20:14,270 it's the first thing in the row. 425 00:20:14,270 --> 00:20:16,490 The destination is row square bracket 1. 426 00:20:16,490 --> 00:20:18,860 And the duration is row square bracket 2. 427 00:20:18,860 --> 00:20:22,190 And so long as I make some changes to line 18 to avoid this variable name 428 00:20:22,190 --> 00:20:24,787 issue, this will work just as well. 429 00:20:24,787 --> 00:20:27,620 You can just take the row and access the different parts of the row. 430 00:20:27,620 --> 00:20:30,947 I'm just giving them names to make it a little bit clearer. 431 00:20:30,947 --> 00:20:31,945 Yeah. 432 00:20:31,945 --> 00:20:35,940 AUDIENCE: Why is line 22 added at the footer? 433 00:20:35,940 --> 00:20:38,950 BRIAN YU: Oh why is line 22 added at the footer? 434 00:20:38,950 --> 00:20:43,910 app.up context, this has to do with the Flask-SQLAlchemy library 435 00:20:43,910 --> 00:20:45,590 that we're using in order to run this. 436 00:20:45,590 --> 00:20:48,770 And the idea here is that what Flask-SQLAlchemy is going to help to do 437 00:20:48,770 --> 00:20:54,980 is help to make sure that we're able to run database queries in response 438 00:20:54,980 --> 00:20:57,192 to web requests when they're incoming web requests. 439 00:20:57,192 --> 00:20:59,650 So this is looking forward a little bit to when we're going 440 00:20:59,650 --> 00:21:01,147 to put this into a web application. 441 00:21:01,147 --> 00:21:03,980 And we want different users to be able to interact with the database 442 00:21:03,980 --> 00:21:05,610 simultaneously. 443 00:21:05,610 --> 00:21:09,290 So it's a nuance of Flask, basically, that Flask can only 444 00:21:09,290 --> 00:21:12,380 run certain types of code when it's in what's called an app context. 445 00:21:12,380 --> 00:21:15,680 But I wouldn't worry about that too much for the purposes of these simpler 446 00:21:15,680 --> 00:21:16,716 examples. 447 00:21:16,716 --> 00:21:18,204 Yeah. 448 00:21:18,204 --> 00:21:20,350 AUDIENCE: It looks like in this code there's 449 00:21:20,350 --> 00:21:28,020 a new session thing, or db.session, whereas before it was 450 00:21:28,020 --> 00:21:31,510 db.commit without session [INAUDIBLE]? 451 00:21:31,510 --> 00:21:33,572 BRIAN YU: Yes, so this is, again, a difference 452 00:21:33,572 --> 00:21:36,280 is coming about because we're using a slightly different package, 453 00:21:36,280 --> 00:21:38,560 Flask-SQLAlchemy, which just makes it easier 454 00:21:38,560 --> 00:21:41,050 to do this sort of ORM-type syntax. 455 00:21:41,050 --> 00:21:43,720 Although you can do it without Flask-SQLAlchemy. 456 00:21:43,720 --> 00:21:47,950 And the idea here is that our session is going 457 00:21:47,950 --> 00:21:49,930 to be our interaction with this database, 458 00:21:49,930 --> 00:21:53,290 that multiple different people can have multiple different sessions interacting 459 00:21:53,290 --> 00:21:55,420 with the database, adding their own objects to it, 460 00:21:55,420 --> 00:21:58,060 committing their own new objects to the database. 461 00:21:58,060 --> 00:22:02,699 And so this db.session represents our current interaction with the database, 462 00:22:02,699 --> 00:22:04,240 is what that's going to stand in for. 463 00:22:04,240 --> 00:22:07,950 464 00:22:07,950 --> 00:22:11,810 OK, so what about selecting data from the database? 465 00:22:11,810 --> 00:22:14,150 What did that look like before? 466 00:22:14,150 --> 00:22:16,940 List 0.pi was what we were doing before. 467 00:22:16,940 --> 00:22:18,860 We would say db.execute. 468 00:22:18,860 --> 00:22:21,420 And we would say, OK, select the origin, destination, 469 00:22:21,420 --> 00:22:25,280 and duration from the flight and get all of those flights. 470 00:22:25,280 --> 00:22:28,100 And then we would say for every flight in our list of flights, 471 00:22:28,100 --> 00:22:30,650 go ahead and print out Origin to Destination 472 00:22:30,650 --> 00:22:33,810 lasting this number of minutes. 473 00:22:33,810 --> 00:22:37,410 If we want to do the same thing using ORMs now, 474 00:22:37,410 --> 00:22:42,720 we would first from Models import star, import all these classes. 475 00:22:42,720 --> 00:22:47,730 And then the line is as simple as flights=flight.query.all. 476 00:22:47,730 --> 00:22:49,521 Flight is the name of the class. 477 00:22:49,521 --> 00:22:52,020 And I want to take that class in order or run a query on it. 478 00:22:52,020 --> 00:22:53,790 And the query I want to run is all. 479 00:22:53,790 --> 00:22:56,760 In other words, get me everything from this particular class, all 480 00:22:56,760 --> 00:23:00,780 of the flight objects that exist inside of the database. 481 00:23:00,780 --> 00:23:03,910 And then for each one of those flights I'm going to loop through, 482 00:23:03,910 --> 00:23:05,680 going through every flight one at a time, 483 00:23:05,680 --> 00:23:09,190 printing out details about each one, the Origin, the Destination, 484 00:23:09,190 --> 00:23:11,680 and the Duration of that flight. 485 00:23:11,680 --> 00:23:13,690 So we've gotten rid of any select syntax. 486 00:23:13,690 --> 00:23:16,120 The word select doesn't show up inside the code. 487 00:23:16,120 --> 00:23:18,220 But by doing flight.query.all we've effectively 488 00:23:18,220 --> 00:23:21,595 replicated that idea of that syntax just by using objects. 489 00:23:21,595 --> 00:23:25,036 490 00:23:25,036 --> 00:23:25,910 Questions about that? 491 00:23:25,910 --> 00:23:31,450 492 00:23:31,450 --> 00:23:34,140 OK, so we'll take a look at some of the syntax 493 00:23:34,140 --> 00:23:36,737 that we can use with regards to ORMs. 494 00:23:36,737 --> 00:23:38,070 And I know a lot of this is new. 495 00:23:38,070 --> 00:23:40,590 I'm going to show you a lot of syntax in a very short amount of time. 496 00:23:40,590 --> 00:23:42,840 Please do not feel like you need to memorize this all. 497 00:23:42,840 --> 00:23:44,310 I don't have all this memorized. 498 00:23:44,310 --> 00:23:46,600 This is just showing you the types of things that you can do, 499 00:23:46,600 --> 00:23:48,510 so that if you have a question later on about oh, 500 00:23:48,510 --> 00:23:51,760 how do I do this type of query with an ORM, you can refer back to these slides 501 00:23:51,760 --> 00:23:52,676 and take a look at it. 502 00:23:52,676 --> 00:23:56,110 But the expectation is not that you're memorizing all of this by any means. 503 00:23:56,110 --> 00:24:00,930 So once we have a class that allows us to define all of the fields, 504 00:24:00,930 --> 00:24:03,840 we were able to say db.create_all in order to create everything, 505 00:24:03,840 --> 00:24:05,070 just as a review. 506 00:24:05,070 --> 00:24:08,250 Instead of something like insert into the flights, 507 00:24:08,250 --> 00:24:11,520 these origin, destination, duration, these particular values, 508 00:24:11,520 --> 00:24:15,676 what we would instead do is say OK, flight is equal to a new flight object. 509 00:24:15,676 --> 00:24:17,550 We would specify, OK, the origin of New York. 510 00:24:17,550 --> 00:24:18,930 The destination of Paris. 511 00:24:18,930 --> 00:24:20,700 Duration is 540. 512 00:24:20,700 --> 00:24:24,600 And then we would say db.session.add flight in order 513 00:24:24,600 --> 00:24:28,194 to say add this flight to the database. 514 00:24:28,194 --> 00:24:29,610 What did a select query look like? 515 00:24:29,610 --> 00:24:32,730 We saw a select star from flights was the way of getting everything 516 00:24:32,730 --> 00:24:34,260 from our list of flights. 517 00:24:34,260 --> 00:24:39,000 We would instead say flight.query.all to mean all right, take all my flights. 518 00:24:39,000 --> 00:24:40,620 Go ahead and run a query on them. 519 00:24:40,620 --> 00:24:43,440 And get me everything back. 520 00:24:43,440 --> 00:24:46,554 If instead I wanted to limit myself, run a filter 521 00:24:46,554 --> 00:24:49,470 to make sure that I'm only getting back certain results instead of all 522 00:24:49,470 --> 00:24:52,470 the results, whereas before I would have said something like select star 523 00:24:52,470 --> 00:24:55,800 from flights where origin equals Paris, the syntax in Python 524 00:24:55,800 --> 00:24:57,360 is going to look something like this. 525 00:24:57,360 --> 00:25:02,280 Flight.query, but not flight.query.all, I want to filter it first. 526 00:25:02,280 --> 00:25:08,130 I want to say flight.query.filter_by origin equals Paris. 527 00:25:08,130 --> 00:25:11,850 And this .filter_by means, OK, take this query and run a filter through it, 528 00:25:11,850 --> 00:25:15,930 namely filtering out only those rows where the origin of the flight is 529 00:25:15,930 --> 00:25:20,630 Paris, and then .all again meaning get me back all of the results from that. 530 00:25:20,630 --> 00:25:23,850 And you'll notice once again we see what we saw in JavaScript yesterday, 531 00:25:23,850 --> 00:25:26,840 this idea of chaining things together, something.somethinge 532 00:25:26,840 --> 00:25:28,330 lse.somethingelse.somethingelse. 533 00:25:28,330 --> 00:25:31,350 And you can imagine programmatically, you can change these filter bys 534 00:25:31,350 --> 00:25:34,104 together, filter by something and then .filter_by something else. 535 00:25:34,104 --> 00:25:37,020 So if you really wanted to filter down a large data set into something 536 00:25:37,020 --> 00:25:40,110 a bit smaller you can run these subsequent filters 537 00:25:40,110 --> 00:25:43,420 in order to filter your data down to only what it is that you want. 538 00:25:43,420 --> 00:25:46,170 And this programmatic way of trying to use 539 00:25:46,170 --> 00:25:48,602 classes to be able to describe SQL queries 540 00:25:48,602 --> 00:25:50,310 is going to give us that flexibility that 541 00:25:50,310 --> 00:25:53,560 would have been a little bit tougher if we were just writing raw SQL commands. 542 00:25:53,560 --> 00:25:56,382 543 00:25:56,382 --> 00:25:57,590 Questions on anything so far? 544 00:25:57,590 --> 00:26:00,730 545 00:26:00,730 --> 00:26:04,570 All right, .all at the end of an ORM query basically means get me back all 546 00:26:04,570 --> 00:26:05,440 of the results. 547 00:26:05,440 --> 00:26:07,840 If I only want the first result, only one thing, 548 00:26:07,840 --> 00:26:10,630 whereas in SQL you would have just said limit one, for example, 549 00:26:10,630 --> 00:26:14,230 in order to do that, the Python code for doing that in an ORM, 550 00:26:14,230 --> 00:26:16,750 in particular in SQLAlchemy looks very similar. 551 00:26:16,750 --> 00:26:19,970 It's just that instead of .all at the end, we just say .first. 552 00:26:19,970 --> 00:26:24,190 So .first will just get you the first result coming back from a list 553 00:26:24,190 --> 00:26:25,180 of possible results. 554 00:26:25,180 --> 00:26:25,823 Yeah. 555 00:26:25,823 --> 00:26:28,573 AUDIENCE: How do you put like a variable into one of these things? 556 00:26:28,573 --> 00:26:30,753 So like for that example, if you have origin Paris, 557 00:26:30,753 --> 00:26:34,142 let's say you want to, if you have a [INAUDIBLE]?? 558 00:26:34,142 --> 00:26:36,350 BRIAN YU: Yeah, how would you put a variable into it? 559 00:26:36,350 --> 00:26:39,830 This string here, Paris, could be any Python value. 560 00:26:39,830 --> 00:26:42,920 So this could be a variable that you just substitute in here. 561 00:26:42,920 --> 00:26:45,020 Because this is just a Python expression. 562 00:26:45,020 --> 00:26:48,320 And so you could put a variable into origin equals Foo, 563 00:26:48,320 --> 00:26:50,429 or whatever your variable name happens to be. 564 00:26:50,429 --> 00:26:52,470 And that will filter by that particular variable. 565 00:26:52,470 --> 00:26:53,990 Then you don't have to worry about escaping anything. 566 00:26:53,990 --> 00:26:55,823 You don't have to worry about SQL injection. 567 00:26:55,823 --> 00:26:58,220 The ORM handles all of that. 568 00:26:58,220 --> 00:26:59,022 Question? 569 00:26:59,022 --> 00:26:59,605 Another thing. 570 00:26:59,605 --> 00:27:00,896 AUDIENCE: That was my question. 571 00:27:00,896 --> 00:27:04,300 BRIAN YU: Oh, OK, got it. 572 00:27:04,300 --> 00:27:10,570 So .all returns all of the results. .first returns only the first result. 573 00:27:10,570 --> 00:27:13,490 If you were trying to count how many things are in a particular table, 574 00:27:13,490 --> 00:27:15,970 so how many flights are from Paris, for example, 575 00:27:15,970 --> 00:27:19,180 which you would do via syntax like this, SELECT COUNT star from flights, 576 00:27:19,180 --> 00:27:20,860 where the origin equals Paris. 577 00:27:20,860 --> 00:27:23,560 The equivalent of that in the ORM is the same thing, 578 00:27:23,560 --> 00:27:26,680 flight.query.filter_by origin equals Paris. 579 00:27:26,680 --> 00:27:30,910 And then instead of .all or .first, would just say .count. 580 00:27:30,910 --> 00:27:33,160 And .count is just going to return to you an integer, 581 00:27:33,160 --> 00:27:37,010 some number representing how many row has got returned from this particular 582 00:27:37,010 --> 00:27:37,510 query. 583 00:27:37,510 --> 00:27:40,360 584 00:27:40,360 --> 00:27:43,507 Select star from flights where id equals 28, 585 00:27:43,507 --> 00:27:45,340 this is something we've already seen before. 586 00:27:45,340 --> 00:27:47,410 We're only selecting one particular thing. 587 00:27:47,410 --> 00:27:52,330 So we could say flight.query.filter_by id=28.first meaning, OK, 588 00:27:52,330 --> 00:27:56,020 take all the flights and find the one that has this particular id 589 00:27:56,020 --> 00:27:57,280 by filtering down to it. 590 00:27:57,280 --> 00:27:59,420 And then just get the first result. 591 00:27:59,420 --> 00:28:03,190 But this type of query is one that happens very, very frequently. 592 00:28:03,190 --> 00:28:05,597 It's a type of query where very commonly I'll have an id. 593 00:28:05,597 --> 00:28:07,930 And I'll want to get the flight with this particular id, 594 00:28:07,930 --> 00:28:09,970 or the passenger with that particular id. 595 00:28:09,970 --> 00:28:13,360 And so rather than do this more complicated syntax, 596 00:28:13,360 --> 00:28:16,720 SQLAlchemy has a bit of an easier way of getting it the same idea 597 00:28:16,720 --> 00:28:22,260 and introduces this syntax, just flight.query.get, followed by a number. 598 00:28:22,260 --> 00:28:24,010 And that's just going to be the id number. 599 00:28:24,010 --> 00:28:27,059 So if you run flight.query.get 28 you will get back 600 00:28:27,059 --> 00:28:29,350 what effectively you would have gotten from this query, 601 00:28:29,350 --> 00:28:32,440 just returning to you the flight with an id of 28, 602 00:28:32,440 --> 00:28:34,060 or whatever the idea happens to be. 603 00:28:34,060 --> 00:28:37,090 If there's no flight with id 28 what you'll get back 604 00:28:37,090 --> 00:28:38,845 is just the none value in Python. 605 00:28:38,845 --> 00:28:39,880 So you can check. 606 00:28:39,880 --> 00:28:44,215 If it's equal to none, then there is no flight with that id, for example. 607 00:28:44,215 --> 00:28:44,840 Yeah, question? 608 00:28:44,840 --> 00:28:50,660 AUDIENCE: Is the get just [INAUDIBLE] for an id, like [INAUDIBLE]?? 609 00:28:50,660 --> 00:28:54,080 BRIAN YU: The get is going to get the primary key of the table. 610 00:28:54,080 --> 00:28:56,390 And so so long as your id is your primary key, 611 00:28:56,390 --> 00:28:59,520 we're going to try and get the idea of that table. 612 00:28:59,520 --> 00:29:00,606 Yep. 613 00:29:00,606 --> 00:29:04,530 AUDIENCE: So if you just use filter_by, shouldn't you have [INAUDIBLE] id? 614 00:29:04,530 --> 00:29:08,389 So like shouldn't that just return [INAUDIBLE]?? 615 00:29:08,389 --> 00:29:10,430 BRIAN YU: Yeah, why do you still need first here? 616 00:29:10,430 --> 00:29:14,120 The ORM doesn't know necessarily that there will only 617 00:29:14,120 --> 00:29:15,770 be one result even if you know. 618 00:29:15,770 --> 00:29:21,110 So if you did filter_by id=28.all what you would get back is a list. 619 00:29:21,110 --> 00:29:23,660 And that list would probably only have one thing in it. 620 00:29:23,660 --> 00:29:27,680 But it's still a list and you would have to do that square bracket 0 in order 621 00:29:27,680 --> 00:29:29,014 to then get at the first object. 622 00:29:29,014 --> 00:29:31,929 And this might be something you might be familiar with if you remember 623 00:29:31,929 --> 00:29:34,610 working on CS50 finance, that even if you were selecting a row 624 00:29:34,610 --> 00:29:37,280 and you knew there was only one row that was going to come back, 625 00:29:37,280 --> 00:29:40,336 you still had to index into it and say, OK, get me the first row. 626 00:29:40,336 --> 00:29:41,960 And then let me get a property of this. 627 00:29:41,960 --> 00:29:44,800 Whereas this .first syntax will just get you back the object. 628 00:29:44,800 --> 00:29:47,450 It's not going to return to you a list of possible values. 629 00:29:47,450 --> 00:29:50,272 630 00:29:50,272 --> 00:29:51,730 Other things, before we keep going? 631 00:29:51,730 --> 00:29:54,687 632 00:29:54,687 --> 00:29:56,270 All right, how would you do an update? 633 00:29:56,270 --> 00:29:59,090 Update the flight, set the duration equal to some number 634 00:29:59,090 --> 00:30:01,100 where the id equals some other number. 635 00:30:01,100 --> 00:30:04,050 Well, the way I might do it looks a little something like this. 636 00:30:04,050 --> 00:30:07,160 First let me go ahead and get the flight, flight id number 6. 637 00:30:07,160 --> 00:30:11,300 So flight.query.get, get flight number 6. 638 00:30:11,300 --> 00:30:13,700 And just save that in a variable called flight. 639 00:30:13,700 --> 00:30:16,190 And then to update something you update it the same way 640 00:30:16,190 --> 00:30:18,020 you would update any other Python object. 641 00:30:18,020 --> 00:30:20,000 I want to update the duration of the flight. 642 00:30:20,000 --> 00:30:26,300 I just say flight.duration= whatever I want the new duration to be. 643 00:30:26,300 --> 00:30:28,760 And so I'm able to use the Python class and object 644 00:30:28,760 --> 00:30:33,270 syntax to be able to create these same general ideas. 645 00:30:33,270 --> 00:30:36,577 To delete a flight, delete from flights where id is a particular id, 646 00:30:36,577 --> 00:30:38,910 again, I would query for the flight by flight.query.get, 647 00:30:38,910 --> 00:30:41,760 get that particular flight, save it in flight. 648 00:30:41,760 --> 00:30:45,180 And in the same way that I could say db.session.add to add something 649 00:30:45,180 --> 00:30:49,260 to the database I can likewise say db.session.delete this flight 650 00:30:49,260 --> 00:30:53,000 to delete that same flight from the database as well, 651 00:30:53,000 --> 00:30:55,356 so with updates and deletes, yeah. 652 00:30:55,356 --> 00:30:58,356 AUDIENCE: So once you start having multiple flights inside the database, 653 00:30:58,356 --> 00:31:01,200 you probably [INAUDIBLE] objects. 654 00:31:01,200 --> 00:31:06,882 Instead of flight would you put like F1, or would you put like your own thing? 655 00:31:06,882 --> 00:31:09,340 BRIAN YU: Yeah, this flight variable could be any variable. 656 00:31:09,340 --> 00:31:10,210 It could be F1. 657 00:31:10,210 --> 00:31:15,076 It could be-- it doesn't even need to necessarily be just a single variable. 658 00:31:15,076 --> 00:31:17,950 You could, if you wanted to, store a whole bunch of flights in a list 659 00:31:17,950 --> 00:31:20,749 and then loop over them and do some operation with all of them. 660 00:31:20,749 --> 00:31:21,790 And that would be OK too. 661 00:31:21,790 --> 00:31:24,980 662 00:31:24,980 --> 00:31:27,129 All right, and much like this morning when 663 00:31:27,129 --> 00:31:28,920 we were talking about transactions, meaning 664 00:31:28,920 --> 00:31:31,212 that we were grouping all of our transactions together, 665 00:31:31,212 --> 00:31:33,253 and when we were done making a whole bunch of SQL 666 00:31:33,253 --> 00:31:34,500 queries we would commit them. 667 00:31:34,500 --> 00:31:36,510 We're going to do the same thing here whereas before 668 00:31:36,510 --> 00:31:39,570 and SQL if you were typing raw SQL you would just type the word commit. 669 00:31:39,570 --> 00:31:42,524 If you type db.session.commit and run that commit function, 670 00:31:42,524 --> 00:31:45,690 that will take all of your changes, your inserts, your updates, and deletes, 671 00:31:45,690 --> 00:31:47,932 and actually save them to the database. 672 00:31:47,932 --> 00:31:50,764 673 00:31:50,764 --> 00:31:53,362 OK, I'll show you a couple other examples of SQL syntax. 674 00:31:53,362 --> 00:31:55,820 These are going to start to get a little bit more involved, 675 00:31:55,820 --> 00:31:58,280 but just showing them to use that you know that they're out there. 676 00:31:58,280 --> 00:32:00,350 If I wanted to SELECT star FROM flights order 677 00:32:00,350 --> 00:32:03,470 by origin, in other words sort all the flights in alphabetical order 678 00:32:03,470 --> 00:32:06,110 by where they're coming from, whereas I had filter 679 00:32:06,110 --> 00:32:09,687 by to say I want to filter a particular class, 680 00:32:09,687 --> 00:32:12,770 we also have order by to say I want to order things by a particular class. 681 00:32:12,770 --> 00:32:17,810 So I could say flight.query.order_ by flight.origin, 682 00:32:17,810 --> 00:32:20,450 and then .all to say get me everything. 683 00:32:20,450 --> 00:32:22,554 So adding a .order_by by allows me to sort things. 684 00:32:22,554 --> 00:32:24,470 And you could imagine chaining these together. 685 00:32:24,470 --> 00:32:27,020 I could do a filter_by and then an order_by, 686 00:32:27,020 --> 00:32:30,350 or an order_by and then a filter_by or multiple different filter_bys in order 687 00:32:30,350 --> 00:32:33,950 to filter and order data however I want to and then return to me 688 00:32:33,950 --> 00:32:37,807 all of the resulting information. 689 00:32:37,807 --> 00:32:39,140 Questions about anything so far? 690 00:32:39,140 --> 00:32:42,130 691 00:32:42,130 --> 00:32:44,560 Couple other quick examples, if I wanted to sort something 692 00:32:44,560 --> 00:32:47,860 in descending order instead of ascending order, instead of ordering 693 00:32:47,860 --> 00:32:52,090 by flight.origin, you just order by flight.origin.descending 694 00:32:52,090 --> 00:32:56,310 to put that in descending order, for example. 695 00:32:56,310 --> 00:33:00,115 And if you want to sort select where the origin is not equal to something, 696 00:33:00,115 --> 00:33:03,515 have like arbitrary logic or arbitrary expressions, 697 00:33:03,515 --> 00:33:05,890 then you'll have to do something a little bit more fancy. 698 00:33:05,890 --> 00:33:09,580 Rather than filter by, you're just going to use filter. 699 00:33:09,580 --> 00:33:13,660 And inside a filter that takes any arbitrary Boolean expression, 700 00:33:13,660 --> 00:33:17,710 where I could say filter where flight.origin is not equal to Paris. 701 00:33:17,710 --> 00:33:21,559 And you can add additional logic to that as well. 702 00:33:21,559 --> 00:33:23,350 This comes up a little bit less frequently, 703 00:33:23,350 --> 00:33:25,590 but just showing to you in case it were to come up. 704 00:33:25,590 --> 00:33:28,130 705 00:33:28,130 --> 00:33:29,072 Yeah? 706 00:33:29,072 --> 00:33:33,340 AUDIENCE: So are these [INAUDIBLE] the SQLAlchemy? 707 00:33:33,340 --> 00:33:36,700 Or [INAUDIBLE]? 708 00:33:36,700 --> 00:33:40,480 BRIAN YU: These are unique to SQLAlchemy, 709 00:33:40,480 --> 00:33:43,930 but different, all ORMs usually have a similar set of functions 710 00:33:43,930 --> 00:33:46,690 that are along these lines. 711 00:33:46,690 --> 00:33:48,840 But these at least are specific to SQLAlchemy. 712 00:33:48,840 --> 00:33:51,480 713 00:33:51,480 --> 00:33:55,450 OK, so one thing I want to talk about now is relationships between tables. 714 00:33:55,450 --> 00:34:00,371 So in the past, if we had a book, for example-- and this 715 00:34:00,371 --> 00:34:02,370 is where our ORMs really start to become useful. 716 00:34:02,370 --> 00:34:04,411 Because right now you might be thinking, OK, this 717 00:34:04,411 --> 00:34:07,200 is neat an interesting, but so far things are 718 00:34:07,200 --> 00:34:09,060 taking about the same number of steps. 719 00:34:09,060 --> 00:34:12,270 The relationships that ORMs allow for is where things really start 720 00:34:12,270 --> 00:34:14,190 to become a little bit more powerful. 721 00:34:14,190 --> 00:34:18,540 Imagine we had our passengers and flights again. 722 00:34:18,540 --> 00:34:22,949 And every passenger knew which flight it was associated with, 723 00:34:22,949 --> 00:34:25,500 because our passengers table kept track of a column 724 00:34:25,500 --> 00:34:28,440 called flight id, for example, that referenced the flights table 725 00:34:28,440 --> 00:34:31,344 and said this passenger is on that flight. 726 00:34:31,344 --> 00:34:33,719 But going the other way around with a little bit tougher. 727 00:34:33,719 --> 00:34:37,170 If I had a flight, a row in the flight's table 728 00:34:37,170 --> 00:34:40,287 doesn't have any notion of what passengers is associated with. 729 00:34:40,287 --> 00:34:42,120 I would have to separately take that flight, 730 00:34:42,120 --> 00:34:44,880 look it up in the passenger's table in order to figure out which 731 00:34:44,880 --> 00:34:46,497 passengers it's associated with. 732 00:34:46,497 --> 00:34:48,330 And so what relationships are going to allow 733 00:34:48,330 --> 00:34:53,010 us to do is instead of saying SELECT star FROM passengers where flight 734 00:34:53,010 --> 00:34:57,360 id equals 1, where I wanted to say, OK, get me all the passengers on flight 735 00:34:57,360 --> 00:35:00,120 number 1, I could really just say something like, 736 00:35:00,120 --> 00:35:02,970 and what I want to be able to say, is something like this. 737 00:35:02,970 --> 00:35:07,060 Flight.query.get 1, meaning get flight number 1. 738 00:35:07,060 --> 00:35:11,060 And then I want to just be able to say .passengers. 739 00:35:11,060 --> 00:35:14,960 And .passengers is interesting because .passengers is not a row in the flights 740 00:35:14,960 --> 00:35:15,740 table. 741 00:35:15,740 --> 00:35:18,980 The flight's table only has origin and destination and duration. 742 00:35:18,980 --> 00:35:23,280 But using relationships that ORMs are going to give us the ability to use, 743 00:35:23,280 --> 00:35:27,200 we can now just take a flight and say .passengers to get at all 744 00:35:27,200 --> 00:35:30,117 of the passengers that happen to be on that particular flight. 745 00:35:30,117 --> 00:35:33,200 I'll show you an example of what that looks like in just a moment as well. 746 00:35:33,200 --> 00:35:37,290 747 00:35:37,290 --> 00:35:41,850 And one final example, if we wanted to join tables together, 748 00:35:41,850 --> 00:35:44,640 if we wanted to say SELECT star FROM flights, 749 00:35:44,640 --> 00:35:49,440 JOIN passengers on flights.id=passengers.flight_id, 750 00:35:49,440 --> 00:35:54,990 where passengers.name=Alice, what exactly am I trying to select here? 751 00:35:54,990 --> 00:35:58,320 Long complicated SQL query, let's try and tease it apart and figure out 752 00:35:58,320 --> 00:36:00,080 what it is that I'm querying for. 753 00:36:00,080 --> 00:36:07,430 754 00:36:07,430 --> 00:36:08,900 Yeah? 755 00:36:08,900 --> 00:36:10,370 AUDIENCE: Flight Alice is on? 756 00:36:10,370 --> 00:36:12,389 BRIAN YU: Yeah, the flight that Alice is on. 757 00:36:12,389 --> 00:36:15,430 Right now we have a situation where each passenger is only on one flight. 758 00:36:15,430 --> 00:36:17,263 You could imagine generalizing this to allow 759 00:36:17,263 --> 00:36:19,180 passengers to be on multiple flights. 760 00:36:19,180 --> 00:36:23,110 And so in order to be able to turn this into an ORM query, 761 00:36:23,110 --> 00:36:24,990 it might look something a little like this. 762 00:36:24,990 --> 00:36:30,249 Passenger.query.filter_by name=Alice and then .first just to say, 763 00:36:30,249 --> 00:36:32,290 just get the first passenger whose name is Alice, 764 00:36:32,290 --> 00:36:36,220 even if there might be multiple, and then .flight will get me that 765 00:36:36,220 --> 00:36:40,170 passenger's flight, for example. 766 00:36:40,170 --> 00:36:41,510 So a lot of new syntax. 767 00:36:41,510 --> 00:36:43,640 I'll show you some examples to hopefully make this a little bit clearer. 768 00:36:43,640 --> 00:36:46,056 And then we'll give you time to play with this on your own 769 00:36:46,056 --> 00:36:49,360 as well that will hopefully help with this as well. 770 00:36:49,360 --> 00:36:56,790 And so let's take a look at airline 2. 771 00:36:56,790 --> 00:37:04,210 772 00:37:04,210 --> 00:37:06,892 So airline 2 is going to be a Flask application that basically 773 00:37:06,892 --> 00:37:08,600 looks the same as the airline application 774 00:37:08,600 --> 00:37:11,440 we were doing before, whereby I can take a flight, 775 00:37:11,440 --> 00:37:14,390 say a Shanghai to Paris flight and say, OK, Josh 776 00:37:14,390 --> 00:37:17,910 is going to go on the Shanghai to Paris flight, book the flight, and then OK, 777 00:37:17,910 --> 00:37:18,410 success. 778 00:37:18,410 --> 00:37:20,370 You've successfully booked the flight. 779 00:37:20,370 --> 00:37:24,680 And if I now go to /flights for example, and click on the Shanghai to Paris 780 00:37:24,680 --> 00:37:27,780 flight, OK, Josh is now a passenger on that flight. 781 00:37:27,780 --> 00:37:30,250 And this is something that I can do here. 782 00:37:30,250 --> 00:37:32,260 And so what is the code that makes that work? 783 00:37:32,260 --> 00:37:35,240 784 00:37:35,240 --> 00:37:40,450 Inside of models.pi, I have exactly what I had before. 785 00:37:40,450 --> 00:37:41,710 I've defined classes now. 786 00:37:41,710 --> 00:37:44,830 A flight class and a passenger class, each of which 787 00:37:44,830 --> 00:37:50,530 is going to represent the flight's table and the passenger's table respectively. 788 00:37:50,530 --> 00:37:54,250 And now let me go to application.pi. 789 00:37:54,250 --> 00:37:55,810 What does the index route look like? 790 00:37:55,810 --> 00:37:58,101 Well the index route, I just want to be able to display 791 00:37:58,101 --> 00:37:59,860 a dropdown of all the possible flights. 792 00:37:59,860 --> 00:38:01,930 So I first need to query for all the flights. 793 00:38:01,930 --> 00:38:05,620 So flights=flight.query.all, get all the flights. 794 00:38:05,620 --> 00:38:10,900 And go ahead and pass those in to index.html, just after all the flights, 795 00:38:10,900 --> 00:38:13,950 provide them to the index.html template. 796 00:38:13,950 --> 00:38:15,982 In order to book a flight, what do I do? 797 00:38:15,982 --> 00:38:18,690 The first part of this code is the same, just getting information 798 00:38:18,690 --> 00:38:19,710 from the forum. 799 00:38:19,710 --> 00:38:21,360 The interesting part is here. 800 00:38:21,360 --> 00:38:24,070 Let's make sure the flight they're booking actually exists. 801 00:38:24,070 --> 00:38:28,530 So flight.query.get_flight_id is saying go ahead and try and get that flight 802 00:38:28,530 --> 00:38:31,410 from the database, the one with id flight id. 803 00:38:31,410 --> 00:38:34,620 If the flight is none, well, OK, the flight doesn't exist. 804 00:38:34,620 --> 00:38:37,200 And so I return an error saying, sorry, there 805 00:38:37,200 --> 00:38:40,170 is no flight with that particular id. 806 00:38:40,170 --> 00:38:42,090 And now assuming the flight does exist, we 807 00:38:42,090 --> 00:38:44,190 can go ahead and add the passenger to the flight. 808 00:38:44,190 --> 00:38:47,550 And to do that it's as simple as creating a new passenger object, 809 00:38:47,550 --> 00:38:49,860 saying, OK, here is the name of the passenger. 810 00:38:49,860 --> 00:38:52,480 And here is the flight id of the passenger, 811 00:38:52,480 --> 00:38:55,230 saving it inside of a variable that we're going to call passenger, 812 00:38:55,230 --> 00:38:57,210 though it could be called anything. 813 00:38:57,210 --> 00:39:00,510 Adding the passenger to the database, and then committing the results, 814 00:39:00,510 --> 00:39:03,690 saying actually want to run these queries on the database 815 00:39:03,690 --> 00:39:05,700 and then returning a success message. 816 00:39:05,700 --> 00:39:10,990 817 00:39:10,990 --> 00:39:12,504 How do we display all the flights? 818 00:39:12,504 --> 00:39:14,420 Again, all we do is query for all the flights, 819 00:39:14,420 --> 00:39:17,080 flight.query.all gets all the flights. 820 00:39:17,080 --> 00:39:19,420 And then we render the flights.html template, 821 00:39:19,420 --> 00:39:22,990 providing in that data about all of the flights. 822 00:39:22,990 --> 00:39:26,530 And then finally if we want to see one particular flight, 823 00:39:26,530 --> 00:39:28,620 well here's how we do it. 824 00:39:28,620 --> 00:39:31,970 We would say flight=flight.query.get_flight_id, 825 00:39:31,970 --> 00:39:35,410 in other words, query for the flight that has id flight id, 826 00:39:35,410 --> 00:39:38,100 just get that one flight. 827 00:39:38,100 --> 00:39:41,529 If there is no flight with that id, display some error message. 828 00:39:41,529 --> 00:39:43,320 And then we care about knowing, OK, who are 829 00:39:43,320 --> 00:39:45,637 the passengers that are on that flight? 830 00:39:45,637 --> 00:39:48,470 And so to do that, and we'll find simpler ways of doing this in just 831 00:39:48,470 --> 00:39:52,520 a moment, I could say passenger.query.filter_by, 832 00:39:52,520 --> 00:39:55,490 so I want to get passengers but not all the passengers, 833 00:39:55,490 --> 00:39:57,650 only those that meet a certain condition, 834 00:39:57,650 --> 00:40:02,460 namely the ones that are on this flight, filter by flight_id= whatever this 835 00:40:02,460 --> 00:40:05,720 flight id is, and get all of the results. 836 00:40:05,720 --> 00:40:07,520 And then we're going to render a template, 837 00:40:07,520 --> 00:40:09,800 passing it information about the flight and also 838 00:40:09,800 --> 00:40:14,490 information about the passengers who were on that flight. 839 00:40:14,490 --> 00:40:17,190 So ultimately not too much code, but a lot of new syntax. 840 00:40:17,190 --> 00:40:19,260 So I'll pause here for questions about why 841 00:40:19,260 --> 00:40:21,476 particular lines are the way they are, or why 842 00:40:21,476 --> 00:40:23,100 we're doing things in a particular way. 843 00:40:23,100 --> 00:40:26,495 844 00:40:26,495 --> 00:40:28,435 Yeah. 845 00:40:28,435 --> 00:40:31,279 AUDIENCE: What are the triple quotes? 846 00:40:31,279 --> 00:40:32,820 BRIAN YU: What are the triple quotes? 847 00:40:32,820 --> 00:40:35,190 These are what we call in Python doc strings. 848 00:40:35,190 --> 00:40:37,440 They're basically a way of describing what it is 849 00:40:37,440 --> 00:40:39,030 that-- they're effectively a comment. 850 00:40:39,030 --> 00:40:41,113 It's a different way of having comments in Python. 851 00:40:41,113 --> 00:40:44,400 And it's common for functions to have a doc string, just triple 852 00:40:44,400 --> 00:40:47,520 quotation marks, that just describe what it is that the function does. 853 00:40:47,520 --> 00:40:49,990 And so for readability sake, we'll often add doc strings 854 00:40:49,990 --> 00:40:51,240 to functions just for clarity. 855 00:40:51,240 --> 00:40:56,476 856 00:40:56,476 --> 00:40:57,280 Oh, yeah. 857 00:40:57,280 --> 00:40:58,790 So Athena wants me to show you-- 858 00:40:58,790 --> 00:41:03,020 if I hover over a function like this flights function, 859 00:41:03,020 --> 00:41:04,790 you'll see information about the function 860 00:41:04,790 --> 00:41:06,770 and you'll also see whatever the doc string happens to be. 861 00:41:06,770 --> 00:41:07,880 So it lists that as well. 862 00:41:07,880 --> 00:41:11,930 And so oftentimes more complex functions are documented in the doc string 863 00:41:11,930 --> 00:41:13,220 in terms of how they're used. 864 00:41:13,220 --> 00:41:16,460 And so you can hover over the function and some text editors like VSCode 865 00:41:16,460 --> 00:41:19,580 will provide you with information about how they work, for example. 866 00:41:19,580 --> 00:41:23,760 867 00:41:23,760 --> 00:41:26,825 Other things, yeah? 868 00:41:26,825 --> 00:41:29,280 AUDIENCE: [INAUDIBLE] 869 00:41:29,280 --> 00:41:30,262 BRIAN YU: Yep. 870 00:41:30,262 --> 00:41:33,208 AUDIENCE: [INAUDIBLE] 871 00:41:33,208 --> 00:41:41,927 872 00:41:41,927 --> 00:41:42,510 BRIAN YU: Yes. 873 00:41:42,510 --> 00:41:45,820 So this is-- I'm getting information from request.form. 874 00:41:45,820 --> 00:41:49,360 And the form that the user is filling out is this-- 875 00:41:49,360 --> 00:41:53,110 I need to run the application, flask run-- 876 00:41:53,110 --> 00:41:58,120 is this form, just on the default route, this dropdown box and this input field 877 00:41:58,120 --> 00:41:59,830 together are going to comprise the form. 878 00:41:59,830 --> 00:42:02,080 And when someone submits this form, that data 879 00:42:02,080 --> 00:42:06,010 is going to be sent to the server and request.form.getName 880 00:42:06,010 --> 00:42:09,574 will get the name that the user typed in and request.form.getFlightID 881 00:42:09,574 --> 00:42:11,740 is going to get which dropdown option they selected. 882 00:42:11,740 --> 00:42:16,480 883 00:42:16,480 --> 00:42:18,018 Yeah? 884 00:42:18,018 --> 00:42:20,760 AUDIENCE: [INAUDIBLE] 885 00:42:20,760 --> 00:42:22,350 BRIAN YU: Yes, it is through post. 886 00:42:22,350 --> 00:42:27,500 So if we look at index.html, we see that this form uses the post method. 887 00:42:27,500 --> 00:42:30,830 And we'll talk a bit more about request methods in just a moment. 888 00:42:30,830 --> 00:42:35,720 And in particular, this route specifies method post, 889 00:42:35,720 --> 00:42:37,640 meaning post is one of the allowed request 890 00:42:37,640 --> 00:42:40,395 methods for this particular route. 891 00:42:40,395 --> 00:42:44,195 AUDIENCE: Are you [INAUDIBLE] request.post.get for-- 892 00:42:44,195 --> 00:42:48,010 893 00:42:48,010 --> 00:42:51,460 BRIAN YU: In flask, at least, the way that you get at form data 894 00:42:51,460 --> 00:42:55,420 is through request.form for post requests that are submitting form data. 895 00:42:55,420 --> 00:42:59,760 896 00:42:59,760 --> 00:43:02,690 Other things? 897 00:43:02,690 --> 00:43:03,190 OK. 898 00:43:03,190 --> 00:43:04,570 I'll show you a couple other examples, just 899 00:43:04,570 --> 00:43:06,580 to show you what it is that ORMs can do. 900 00:43:06,580 --> 00:43:11,830 So inside of the code now, we are adding passengers like this. 901 00:43:11,830 --> 00:43:14,590 We create a new passenger object, add it to the database, 902 00:43:14,590 --> 00:43:16,465 and then commit the results, saying actually, 903 00:43:16,465 --> 00:43:18,490 we want to save this information. 904 00:43:18,490 --> 00:43:20,770 But you might imagine that in a larger application, 905 00:43:20,770 --> 00:43:24,280 there might be multiple places in the code where I'm adding passengers. 906 00:43:24,280 --> 00:43:27,100 Maybe there's a form to add passengers and maybe whoever 907 00:43:27,100 --> 00:43:29,500 manages the airline also has an interface via which 908 00:43:29,500 --> 00:43:31,360 they can add passengers to flights. 909 00:43:31,360 --> 00:43:39,340 And so I'll show you airline3.html, or airline3 source code. 910 00:43:39,340 --> 00:43:41,637 And it's going to behave basically the exact same way. 911 00:43:41,637 --> 00:43:42,720 But here's the difference. 912 00:43:42,720 --> 00:43:47,170 If I go to models.py, I've defined a class called flight. 913 00:43:47,170 --> 00:43:49,260 And most of it looks exactly the same. 914 00:43:49,260 --> 00:43:53,190 But I've also added a method to flight. 915 00:43:53,190 --> 00:43:56,400 Instead of just leaving it as just the columns, 916 00:43:56,400 --> 00:43:59,730 I've added a method called add passenger, just a function 917 00:43:59,730 --> 00:44:01,890 that I can use on a flight. 918 00:44:01,890 --> 00:44:06,569 And so a flight now has the ability to add a passenger to itself. 919 00:44:06,569 --> 00:44:09,360 This is, again, getting at the idea of object oriented programming, 920 00:44:09,360 --> 00:44:12,030 taking a flight and adding a passenger to it. 921 00:44:12,030 --> 00:44:15,090 And how exactly does a flight, which we're going to call self, 922 00:44:15,090 --> 00:44:17,910 add a passenger called name to the database? 923 00:44:17,910 --> 00:44:21,180 Well, it's going to create a passenger object whose name is 924 00:44:21,180 --> 00:44:23,640 whatever the name of the passenger is. 925 00:44:23,640 --> 00:44:27,360 And the flight ID, well, OK, self is the flight. 926 00:44:27,360 --> 00:44:31,470 And so self.id is going to be the flight's ID. 927 00:44:31,470 --> 00:44:34,710 And so if a flight wants to add a passenger to itself, 928 00:44:34,710 --> 00:44:40,150 it's going to use self.id as the id of the flight. 929 00:44:40,150 --> 00:44:43,300 We add that passenger to the database via DD.session.add 930 00:44:43,300 --> 00:44:46,070 to say add this passenger to the database, 931 00:44:46,070 --> 00:44:49,032 and then finally commit the results, same as before. 932 00:44:49,032 --> 00:44:50,740 So the only change here-- none of this is 933 00:44:50,740 --> 00:44:53,349 going to functionally change the way the program behaves. 934 00:44:53,349 --> 00:44:55,390 But I've changed the way my program is organized. 935 00:44:55,390 --> 00:44:57,790 I've added to the flight class now the ability 936 00:44:57,790 --> 00:45:00,550 to allow flights to add passengers. 937 00:45:00,550 --> 00:45:04,320 And now in application.py, if I look down 938 00:45:04,320 --> 00:45:07,690 at how to add a passenger to a flight, all I need to do 939 00:45:07,690 --> 00:45:12,130 is get the name of the passenger, make sure the flight exists 940 00:45:12,130 --> 00:45:16,090 by doing flight.query.getFlightID to try and get that flight. 941 00:45:16,090 --> 00:45:18,220 And then to add the passenger to the flight, 942 00:45:18,220 --> 00:45:20,470 it's as simple as this line of code. 943 00:45:20,470 --> 00:45:24,100 Flight.addPassenger then passing in the name. 944 00:45:24,100 --> 00:45:26,800 I want to take this flight, this flight object, 945 00:45:26,800 --> 00:45:30,660 run the addPassenger method or the addPassenger function, 946 00:45:30,660 --> 00:45:33,210 and the passenger's name is whatever it is 947 00:45:33,210 --> 00:45:35,940 that the user typed in into the form. 948 00:45:35,940 --> 00:45:38,460 So I've been able to simplify application.py a little bit, 949 00:45:38,460 --> 00:45:42,720 adding this addPassenger function, by extending my models a little bit, 950 00:45:42,720 --> 00:45:47,209 by adding some code there, as well. 951 00:45:47,209 --> 00:45:48,500 Questions about how I did that? 952 00:45:48,500 --> 00:45:49,131 Yeah. 953 00:45:49,131 --> 00:45:51,047 AUDIENCE: Yeah, just in models, when you did p 954 00:45:51,047 --> 00:45:55,246 equals passenger and then parentheses name, that passenger, 955 00:45:55,246 --> 00:45:57,222 that looks like a function. 956 00:45:57,222 --> 00:45:58,210 Did you define that-- 957 00:45:58,210 --> 00:45:58,710 OK. 958 00:45:58,710 --> 00:46:00,270 BRIAN YU: It's defined here. 959 00:46:00,270 --> 00:46:02,354 AUDIENCE: So that's a separate class [INAUDIBLE].. 960 00:46:02,354 --> 00:46:02,936 BRIAN YU: Yes. 961 00:46:02,936 --> 00:46:04,400 Passenger is the name of a class. 962 00:46:04,400 --> 00:46:07,940 So I'm creating a new passenger by using passenger here. 963 00:46:07,940 --> 00:46:12,020 And just in standard Python convention, if it starts with a lowercase letter, 964 00:46:12,020 --> 00:46:13,490 then it's generally a function. 965 00:46:13,490 --> 00:46:16,040 If it starts with an uppercase letter, generally it's a class 966 00:46:16,040 --> 00:46:17,414 and I'm constructing a new class. 967 00:46:17,414 --> 00:46:23,830 968 00:46:23,830 --> 00:46:24,970 OK. 969 00:46:24,970 --> 00:46:30,737 I'll show you one last example of ORMs. 970 00:46:30,737 --> 00:46:33,153 And then we'll move into something a little bit different. 971 00:46:33,153 --> 00:46:36,260 972 00:46:36,260 --> 00:46:39,530 Now inside of models I'm going to add what I was referring 973 00:46:39,530 --> 00:46:41,240 to before, which is a relationship. 974 00:46:41,240 --> 00:46:45,260 So before, passenger had access to a flight ID, 975 00:46:45,260 --> 00:46:49,310 namely this is how a passenger gets at what flight ID it's associated with, 976 00:46:49,310 --> 00:46:51,230 what flight is that passenger on? 977 00:46:51,230 --> 00:46:53,660 But in the past, we haven't had a way for a flight 978 00:46:53,660 --> 00:46:56,480 to be able to access what passengers are on it. 979 00:46:56,480 --> 00:46:59,890 So we had sort of a one-way relationship happening here. 980 00:46:59,890 --> 00:47:04,340 And so inside of flight we can add a new relationship to the flight, information 981 00:47:04,340 --> 00:47:06,290 that we want the flight to know about. 982 00:47:06,290 --> 00:47:07,550 The flight has an ID. 983 00:47:07,550 --> 00:47:08,802 The flight has an origin. 984 00:47:08,802 --> 00:47:10,010 The flight has a destination. 985 00:47:10,010 --> 00:47:11,540 And the flight has a duration. 986 00:47:11,540 --> 00:47:15,350 And ID, origin, destination, and duration, those are all columns. 987 00:47:15,350 --> 00:47:18,080 And so we see they're all equal to DB.column. 988 00:47:18,080 --> 00:47:23,030 But a flight semantically also has passengers, passengers that are on it. 989 00:47:23,030 --> 00:47:24,710 Now, those passengers are not a column. 990 00:47:24,710 --> 00:47:27,790 There is no column inside of the flight's table called passengers 991 00:47:27,790 --> 00:47:30,080 because passengers are stored in a separate table. 992 00:47:30,080 --> 00:47:33,290 But there is a relationship between flights and passengers 993 00:47:33,290 --> 00:47:35,851 that is not just a column in the flights table. 994 00:47:35,851 --> 00:47:37,850 And so what we're saying here is that passengers 995 00:47:37,850 --> 00:47:42,800 is going to be a relationship, namely with the passenger class. 996 00:47:42,800 --> 00:47:45,260 So in quotation marks I'm defining the name of the class. 997 00:47:45,260 --> 00:47:46,990 The class is passenger. 998 00:47:46,990 --> 00:47:51,320 And what I'm saying here is that if I try and access the passenger's property 999 00:47:51,320 --> 00:47:56,092 of a particular flight, what I'm going to get is all of the passenger objects 1000 00:47:56,092 --> 00:47:58,050 that the flight is related to-- in other words, 1001 00:47:58,050 --> 00:48:01,489 all of the passengers that are on the flight. 1002 00:48:01,489 --> 00:48:03,780 You don't need to worry about the rest of this as much. 1003 00:48:03,780 --> 00:48:05,890 Back graph equals flight, it just gives me 1004 00:48:05,890 --> 00:48:08,640 a way of going the other way around, if I have a passenger getting 1005 00:48:08,640 --> 00:48:10,470 at the flight, though this is optional. 1006 00:48:10,470 --> 00:48:13,020 And lazy equals true has to do with something 1007 00:48:13,020 --> 00:48:15,300 called lazy evaluation, the idea that I don't 1008 00:48:15,300 --> 00:48:18,090 want to load this information unless I actually need it. 1009 00:48:18,090 --> 00:48:21,612 More on lazy evaluation if you go on to take classes like CS51, for example. 1010 00:48:21,612 --> 00:48:23,820 We'll dive a little more deeply into what that means. 1011 00:48:23,820 --> 00:48:25,653 The core of what you need to understand here 1012 00:48:25,653 --> 00:48:29,610 is just this first half, the idea that the flight has passengers, 1013 00:48:29,610 --> 00:48:32,160 but those passengers are not a column of the flights table, 1014 00:48:32,160 --> 00:48:34,368 they're just something that the flight is related to. 1015 00:48:34,368 --> 00:48:37,287 1016 00:48:37,287 --> 00:48:38,370 Questions about that idea? 1017 00:48:38,370 --> 00:48:43,990 1018 00:48:43,990 --> 00:48:52,040 So in application.py now, when I wanted to take a flight 1019 00:48:52,040 --> 00:48:55,160 and get access to all of its passengers, I would first just 1020 00:48:55,160 --> 00:48:57,050 get the flight on line 49 here. 1021 00:48:57,050 --> 00:49:01,520 Flight.query.getFlightID means I want to get a particular flight, namely 1022 00:49:01,520 --> 00:49:06,080 the one that has this flight ID, save that in a variable called flight. 1023 00:49:06,080 --> 00:49:08,150 If there is no flight, render some error. 1024 00:49:08,150 --> 00:49:11,360 And then to get the passengers on the flight, that's as simple as just 1025 00:49:11,360 --> 00:49:13,607 saying flight.passengers. 1026 00:49:13,607 --> 00:49:16,190 I can take the flight, get at all the passengers on the flight 1027 00:49:16,190 --> 00:49:18,740 by accessing the passenger's property of the flight 1028 00:49:18,740 --> 00:49:21,224 and then pass that into the template. 1029 00:49:21,224 --> 00:49:23,890 So moral of this story is that there are a lot of different ways 1030 00:49:23,890 --> 00:49:25,510 to do the same thing with ORMs. 1031 00:49:25,510 --> 00:49:26,920 You can use more queries. 1032 00:49:26,920 --> 00:49:28,090 You can add relationships. 1033 00:49:28,090 --> 00:49:30,785 You can add methods on all the various different classes. 1034 00:49:30,785 --> 00:49:33,160 I'd encourage you to look through some of these examples. 1035 00:49:33,160 --> 00:49:33,701 Try them out. 1036 00:49:33,701 --> 00:49:34,540 Tinker with them. 1037 00:49:34,540 --> 00:49:37,270 But definitely no need to feel like you can do all of that syntax 1038 00:49:37,270 --> 00:49:40,030 right away, because there is a lot of syntax in SQLAlchemy. 1039 00:49:40,030 --> 00:49:42,430 Their documentation is huge and extensive. 1040 00:49:42,430 --> 00:49:45,560 And it takes quite a while to get familiar with, get comfortable with. 1041 00:49:45,560 --> 00:49:49,000 So totally OK if a lot of this feels a little bit unfamiliar. 1042 00:49:49,000 --> 00:49:51,010 But questions about any of this so far? 1043 00:49:51,010 --> 00:49:53,540 1044 00:49:53,540 --> 00:49:54,040 Yeah. 1045 00:49:54,040 --> 00:49:54,706 AUDIENCE: Sorry. 1046 00:49:54,706 --> 00:50:00,476 Just on the db.relationship, [INAUDIBLE] how 1047 00:50:00,476 --> 00:50:05,310 is that linked to the passengers database? 1048 00:50:05,310 --> 00:50:07,337 Or can you describe once again? 1049 00:50:07,337 --> 00:50:07,920 BRIAN YU: Yep. 1050 00:50:07,920 --> 00:50:11,010 So db.relationship is just indicating the fact 1051 00:50:11,010 --> 00:50:14,460 that there is some relationship between the flight and the passengers. 1052 00:50:14,460 --> 00:50:17,610 And I would like to store any of the passengers that 1053 00:50:17,610 --> 00:50:21,270 are related to this flight inside of this variable called passengers. 1054 00:50:21,270 --> 00:50:24,090 And what SQLAlchemy is going to do ultimately 1055 00:50:24,090 --> 00:50:26,580 is try and figure out what that relationship is. 1056 00:50:26,580 --> 00:50:30,780 And it's going to find that this passenger has a flight ID column that 1057 00:50:30,780 --> 00:50:32,460 references the flight's table. 1058 00:50:32,460 --> 00:50:36,240 This foreign key is how SQLAlchemy knows that there is this relationship 1059 00:50:36,240 --> 00:50:38,010 between flights and passengers. 1060 00:50:38,010 --> 00:50:40,380 And here we're just giving that relationship a name, 1061 00:50:40,380 --> 00:50:42,572 calling it passengers, such that from a flight 1062 00:50:42,572 --> 00:50:45,030 I can access any of the passengers that are on that flight. 1063 00:50:45,030 --> 00:50:49,120 1064 00:50:49,120 --> 00:50:49,890 OK. 1065 00:50:49,890 --> 00:50:52,030 So ORMs, a lot of new syntax there. 1066 00:50:52,030 --> 00:50:55,960 Last thing I wanted to talk about today are APIs, or Application Programming 1067 00:50:55,960 --> 00:50:59,830 Interfaces, which are ways that we can imagine that applications can interact 1068 00:50:59,830 --> 00:51:03,160 with each other or that we can try and get programmatic information 1069 00:51:03,160 --> 00:51:04,870 from some sort of service. 1070 00:51:04,870 --> 00:51:07,810 And we saw an API yesterday, actually, when 1071 00:51:07,810 --> 00:51:09,876 we were looking at the currency converter 1072 00:51:09,876 --> 00:51:13,000 where we wanted to be able to look up what the conversion rate was between, 1073 00:51:13,000 --> 00:51:15,250 like, US dollars and euros, for example. 1074 00:51:15,250 --> 00:51:19,360 And we went to some API in order to get what the current exchange rates are. 1075 00:51:19,360 --> 00:51:22,600 And so APIs, at least more modern APIs, will often 1076 00:51:22,600 --> 00:51:27,220 return data in a format called JSON, or JavaScript Object Notation. 1077 00:51:27,220 --> 00:51:30,760 And that looks something like this, generally speaking. 1078 00:51:30,760 --> 00:51:33,020 It's a combination of key value pairs. 1079 00:51:33,020 --> 00:51:35,980 And so this might be what an API for our flight's application 1080 00:51:35,980 --> 00:51:39,317 would return if it's returning information about a flight. 1081 00:51:39,317 --> 00:51:41,650 It's going to say, OK, what is the origin of the flight? 1082 00:51:41,650 --> 00:51:43,233 What is the destination of the flight? 1083 00:51:43,233 --> 00:51:44,950 And what is the duration of the flight? 1084 00:51:44,950 --> 00:51:47,590 And this resembles a JavaScript object. 1085 00:51:47,590 --> 00:51:50,680 If we add some application that got this JavaScript object, 1086 00:51:50,680 --> 00:51:54,160 it could look at the origin value, the destination value, the duration value, 1087 00:51:54,160 --> 00:51:57,760 and do something interesting with this information. 1088 00:51:57,760 --> 00:52:00,250 JSON allows for more than just strings and integers 1089 00:52:00,250 --> 00:52:01,660 to be keys and value pairs. 1090 00:52:01,660 --> 00:52:03,380 It allows arrays, as well. 1091 00:52:03,380 --> 00:52:06,820 So if I wanted my API to return information about which passengers 1092 00:52:06,820 --> 00:52:09,610 are on the flight, for example, I might extend the JSON object 1093 00:52:09,610 --> 00:52:13,270 to look a little something like this, adding in a passenger's key that 1094 00:52:13,270 --> 00:52:16,060 is equal to an array of all the passengers 1095 00:52:16,060 --> 00:52:18,130 that happen to be on that flight. 1096 00:52:18,130 --> 00:52:22,100 And you can nest JSON objects within each other, as well. 1097 00:52:22,100 --> 00:52:24,882 So if I wanted to add more information about the origin the flight 1098 00:52:24,882 --> 00:52:26,590 or the destination of the flight, I might 1099 00:52:26,590 --> 00:52:30,130 have syntax that looks like this, whereby the origin of the flight 1100 00:52:30,130 --> 00:52:32,980 is itself a JavaScript object that contains, OK, 1101 00:52:32,980 --> 00:52:36,490 what is the name of the city, and also what is the code of the airport? 1102 00:52:36,490 --> 00:52:40,880 And likewise do the same thing for the destination, as well. 1103 00:52:40,880 --> 00:52:43,220 So that's JSON syntax, and the type of syntax 1104 00:52:43,220 --> 00:52:45,500 that you'll see quite frequently if you begin 1105 00:52:45,500 --> 00:52:49,460 interacting with APIs and with databases more generally. 1106 00:52:49,460 --> 00:52:50,568 Yeah? 1107 00:52:50,568 --> 00:52:53,472 AUDIENCE: [INAUDIBLE] 1108 00:52:53,472 --> 00:52:54,930 1109 00:52:54,930 --> 00:52:58,530 BRIAN YU: Python has support for what are Python dictionaries, which are 1110 00:52:58,530 --> 00:53:00,855 effectively equivalent to JSON objects. 1111 00:53:00,855 --> 00:53:02,230 And you can convert between the-- 1112 00:53:02,230 --> 00:53:02,970 AUDIENCE: --dictionaries. 1113 00:53:02,970 --> 00:53:03,847 BRIAN YU: Repeat that? 1114 00:53:03,847 --> 00:53:05,040 AUDIENCE: Can you treat them as dictionaries? 1115 00:53:05,040 --> 00:53:07,860 BRIAN YU: You can treat them in Python as dictionaries, yes. 1116 00:53:07,860 --> 00:53:09,160 Yeah. 1117 00:53:09,160 --> 00:53:10,480 Excellent question. 1118 00:53:10,480 --> 00:53:17,110 And APIs also will generally obey various different HTTP methods. 1119 00:53:17,110 --> 00:53:20,170 And so this is common of what's called a REST API, whereby 1120 00:53:20,170 --> 00:53:22,600 if I want to get information from an API, 1121 00:53:22,600 --> 00:53:26,800 I want to get information about what the flights are, for example, 1122 00:53:26,800 --> 00:53:29,710 I might use the get method, which we've seen before in flask already, 1123 00:53:29,710 --> 00:53:32,050 to mean I want to get information about something. 1124 00:53:32,050 --> 00:53:35,800 If I want to send information, I want to create a new resource, 1125 00:53:35,800 --> 00:53:38,710 add a new row to a table, like I want to book a flight, 1126 00:53:38,710 --> 00:53:40,570 for example, or add myself as a passenger 1127 00:53:40,570 --> 00:53:45,400 to a flight, that will generally be done via the post HTTP method, where 1128 00:53:45,400 --> 00:53:48,640 the post HTTP method is understood in the REST world 1129 00:53:48,640 --> 00:53:50,770 to mean I want to add some new data. 1130 00:53:50,770 --> 00:53:54,380 I want to register myself for a flight, for instance. 1131 00:53:54,380 --> 00:53:56,500 And there are other HTTP methods that you'll often 1132 00:53:56,500 --> 00:54:00,280 see, as well, for replacing data, for updating data, for deleting data, 1133 00:54:00,280 --> 00:54:03,820 much in the same way that we can update rows of a SQL database 1134 00:54:03,820 --> 00:54:06,430 or delete rows from a SQL database. 1135 00:54:06,430 --> 00:54:09,240 Those both use different HTTP verbs, as well. 1136 00:54:09,240 --> 00:54:09,915 Yeah? 1137 00:54:09,915 --> 00:54:12,390 AUDIENCE: [INAUDIBLE] 1138 00:54:12,390 --> 00:54:13,380 1139 00:54:13,380 --> 00:54:15,800 BRIAN YU: Patch is generally used if I want to, like, 1140 00:54:15,800 --> 00:54:20,000 update a field of a resource, if I want update one particular column. 1141 00:54:20,000 --> 00:54:22,881 Put is generally used for replacing an entire resource. 1142 00:54:22,881 --> 00:54:26,040 1143 00:54:26,040 --> 00:54:28,950 And so in order to be able to interact with APIs one 1144 00:54:28,950 --> 00:54:32,700 common library you'll often see used in Python is the requests library. 1145 00:54:32,700 --> 00:54:37,100 It is generally used for making any kind of HTTP request to somewhere else. 1146 00:54:37,100 --> 00:54:39,420 And so I'll show you an example of that. 1147 00:54:39,420 --> 00:54:45,270 If I open up Python and import the requests library, I'll do request.get. 1148 00:54:45,270 --> 00:54:47,250 And then I can type in basically any URL. 1149 00:54:47,250 --> 00:54:48,990 I'll say, you know, Google.com. 1150 00:54:48,990 --> 00:54:51,570 1151 00:54:51,570 --> 00:54:55,470 And I'll save the results of this in a variable that I'll call the response. 1152 00:54:55,470 --> 00:54:57,870 So I'm going to make an HTTP request and I'm 1153 00:54:57,870 --> 00:55:01,275 going to get back some sort of response. 1154 00:55:01,275 --> 00:55:05,880 And OK, what is the response's status code? 1155 00:55:05,880 --> 00:55:08,830 Status code is 200, meaning everything went OK with this request. 1156 00:55:08,830 --> 00:55:11,340 It got back some OK response. 1157 00:55:11,340 --> 00:55:14,334 If it was 404 that would mean that whatever my request was wasn't found 1158 00:55:14,334 --> 00:55:17,250 and there are other status codes as well for different types of errors 1159 00:55:17,250 --> 00:55:19,110 that you might recall from CS50. 1160 00:55:19,110 --> 00:55:21,330 And if I want to see the contents of this response 1161 00:55:21,330 --> 00:55:23,830 I can say something like response.text. 1162 00:55:23,830 --> 00:55:24,960 See OK. 1163 00:55:24,960 --> 00:55:28,210 Here is the HTML that corresponds to Google's website. 1164 00:55:28,210 --> 00:55:30,354 Doesn't look particularly pretty, but at least 1165 00:55:30,354 --> 00:55:32,020 I see something that says Google search. 1166 00:55:32,020 --> 00:55:34,200 So something in there allows me to search 1167 00:55:34,200 --> 00:55:36,990 for something on Google's web page. 1168 00:55:36,990 --> 00:55:39,290 And so this is the request library. 1169 00:55:39,290 --> 00:55:41,280 And I show you this mainly so that we can 1170 00:55:41,280 --> 00:55:45,229 begin to use it in order to make API requests of your own 1171 00:55:45,229 --> 00:55:46,020 should you wish to. 1172 00:55:46,020 --> 00:55:51,420 So I'll show you an example inside of currency. 1173 00:55:51,420 --> 00:55:58,240 I'll show currency1.py, where what I'm doing here 1174 00:55:58,240 --> 00:56:03,160 is saying request.get and requesting data from the exchange rates API, 1175 00:56:03,160 --> 00:56:05,830 that same API that we were using yesterday, to say I 1176 00:56:05,830 --> 00:56:09,760 would like to get some information about the exchange rates between US dollars 1177 00:56:09,760 --> 00:56:14,410 and euros, for example, where here I'm saying base equals US dollars 1178 00:56:14,410 --> 00:56:15,970 and symbol equals euro. 1179 00:56:15,970 --> 00:56:19,930 And I only know this from looking at the documentation for this particular API. 1180 00:56:19,930 --> 00:56:22,720 But this is how I get information about what the exchange 1181 00:56:22,720 --> 00:56:24,910 rate is between those two currencies. 1182 00:56:24,910 --> 00:56:31,090 If I were to go directly to that URL, type it in, here's what I get back-- 1183 00:56:31,090 --> 00:56:34,540 a JavaScript object that has a key called rates, 1184 00:56:34,540 --> 00:56:39,040 and then inside of that, a key called EUR, for euros, and inside of that, 1185 00:56:39,040 --> 00:56:42,610 there is the exchange rate between US dollars and euros. 1186 00:56:42,610 --> 00:56:46,090 And so programmatically, if I want to get at that idea, what I'll first 1187 00:56:46,090 --> 00:56:50,350 do when I get a response is check to make sure that the status code is 200. 1188 00:56:50,350 --> 00:56:53,560 If the status code is not 200, something went wrong. 1189 00:56:53,560 --> 00:56:56,850 And then I'll go ahead and say, response.json 1190 00:56:56,850 --> 00:57:00,400 to mean take the HTTP response, convert it into a JSON object for me, 1191 00:57:00,400 --> 00:57:02,650 a Python dictionary, effectively. 1192 00:57:02,650 --> 00:57:05,830 And then in order to get what the exchange rate is, well if you remember, 1193 00:57:05,830 --> 00:57:06,850 here's the data. 1194 00:57:06,850 --> 00:57:08,800 It's inside of the rates keyword, and then 1195 00:57:08,800 --> 00:57:11,140 inside of that in a key called EUR. 1196 00:57:11,140 --> 00:57:15,580 So I'll say data, square bracket rates, square bracket EUR. 1197 00:57:15,580 --> 00:57:17,902 Save that inside of a variable called rate. 1198 00:57:17,902 --> 00:57:19,360 And then I can print something out. 1199 00:57:19,360 --> 00:57:23,170 One US dollar is equal to that number of euros. 1200 00:57:23,170 --> 00:57:29,590 Or by now if I run Python currency1.py what I get is one 1201 00:57:29,590 --> 00:57:34,220 US dollar is equal to that number of euros, for example. 1202 00:57:34,220 --> 00:57:37,760 Currency2.py, if you want to take a look at it, basically does the same thing, 1203 00:57:37,760 --> 00:57:40,330 but it just prompts me to type in currencies first, a base 1204 00:57:40,330 --> 00:57:42,260 currency and another currency. 1205 00:57:42,260 --> 00:57:45,460 And then it performs that request, passing in those currencies 1206 00:57:45,460 --> 00:57:49,660 that I wanted to provide, as opposed to just whatever ones were fixed. 1207 00:57:49,660 --> 00:57:55,540 And so if I do Python currency2.py type in euros and Japanese yen, for example, 1208 00:57:55,540 --> 00:57:59,680 I get one euro is equal to 124.25 Japanese yen. 1209 00:57:59,680 --> 00:58:04,786 And so I can write Python scripts that interact with APIs in this way. 1210 00:58:04,786 --> 00:58:07,660 And so you'll often find that APIs can be very powerful in giving you 1211 00:58:07,660 --> 00:58:10,534 the ability to get access to information that you wouldn't ordinarily 1212 00:58:10,534 --> 00:58:14,080 have access to, information about currency exchange rates, for instance. 1213 00:58:14,080 --> 00:58:16,000 There are APIs for dealing with the weather, 1214 00:58:16,000 --> 00:58:18,160 for example, that allow you to get what the current weather is, 1215 00:58:18,160 --> 00:58:21,040 if that's something you care about displaying on your web page. 1216 00:58:21,040 --> 00:58:23,560 And there are all sorts of different other APIs 1217 00:58:23,560 --> 00:58:26,830 that you can use for financial transactions and getting stock market 1218 00:58:26,830 --> 00:58:29,530 data and whatnot, all that you can use just 1219 00:58:29,530 --> 00:58:33,070 by making web requests to some other endpoint and then taking that data 1220 00:58:33,070 --> 00:58:35,140 and doing something interesting with that data. 1221 00:58:35,140 --> 00:58:36,726 Yeah, question? 1222 00:58:36,726 --> 00:58:38,501 AUDIENCE: How does params work? 1223 00:58:38,501 --> 00:58:39,750 BRIAN YU: How does prams work? 1224 00:58:39,750 --> 00:58:41,070 Good question. 1225 00:58:41,070 --> 00:58:46,860 So in currency1 what you notice is I put in some hard coded HTTP parameters 1226 00:58:46,860 --> 00:58:49,950 where I said the base is going to be US dollars 1227 00:58:49,950 --> 00:58:51,900 and the symbol is going to be euros, meaning I 1228 00:58:51,900 --> 00:58:54,900 want to convert from US dollars to euros. 1229 00:58:54,900 --> 00:58:59,460 The request library in Python has a way of generating those URLs for me. 1230 00:58:59,460 --> 00:59:02,370 And all I have to do is specify what the parameters are. 1231 00:59:02,370 --> 00:59:05,700 So I say the base is this variable called base 1232 00:59:05,700 --> 00:59:09,600 and the symbols are whatever the other variable is. 1233 00:59:09,600 --> 00:59:11,550 And based on that, the request library is 1234 00:59:11,550 --> 00:59:17,190 going to construct a URL that's going to look like that, turning it into base 1235 00:59:17,190 --> 00:59:19,380 equals something and symbols equals something else. 1236 00:59:19,380 --> 00:59:21,213 It's just a bit of an abstraction so I don't 1237 00:59:21,213 --> 00:59:23,280 have to type the ampersand and the question mark 1238 00:59:23,280 --> 00:59:24,660 and the equal signs on my own. 1239 00:59:24,660 --> 00:59:27,618 I just let the library handle it for me so that I don't make a mistake, 1240 00:59:27,618 --> 00:59:29,532 for example. 1241 00:59:29,532 --> 00:59:30,815 AUDIENCE: [INAUDIBLE] 1242 00:59:30,815 --> 00:59:32,690 BRIAN YU: You could have used a format string 1243 00:59:32,690 --> 00:59:34,280 just to insert something in there, yes. 1244 00:59:34,280 --> 00:59:37,321 But if you've imagined starting to deal with situations where there might 1245 00:59:37,321 --> 00:59:39,710 be spaces in the text that you then need to escape, 1246 00:59:39,710 --> 00:59:42,650 it becomes a lot easier just to let the library handle it for you 1247 00:59:42,650 --> 00:59:45,350 instead of having to reinvent the wheel. 1248 00:59:45,350 --> 00:59:45,850 Yeah? 1249 00:59:45,850 --> 00:59:48,670 AUDIENCE: [INAUDIBLE] 1250 00:59:48,670 --> 00:59:50,550 1251 00:59:50,550 --> 00:59:53,210 BRIAN YU: So there are differences between APIs, 1252 00:59:53,210 --> 00:59:56,570 but oftentimes there is a standard set of conventions. 1253 00:59:56,570 --> 01:00:00,440 And generally, it's conventional that when you have a get request, 1254 01:00:00,440 --> 01:00:02,241 the parameters to the get request are going 1255 01:00:02,241 --> 01:00:05,240 to be the arguments that are going to determine what sort of information 1256 01:00:05,240 --> 01:00:06,290 is going to come back. 1257 01:00:06,290 --> 01:00:08,206 Every API is a little bit different, so you'll 1258 01:00:08,206 --> 01:00:11,210 want to look to that particular API documentation. 1259 01:00:11,210 --> 01:00:14,400 But looking at the API documentation will usually tell you how it works. 1260 01:00:14,400 --> 01:00:16,640 So for instance, GitHub has an API that you 1261 01:00:16,640 --> 01:00:20,150 can use in order to request information about commits on a repository 1262 01:00:20,150 --> 01:00:22,470 or who are the collaborators on a repository. 1263 01:00:22,470 --> 01:00:28,130 So if I just go to GitHub API, I can find GitHub's API documentation, 1264 01:00:28,130 --> 01:00:32,600 whereby I can say, all right, if I want to get information about a repository 1265 01:00:32,600 --> 01:00:37,040 and I want to get information about the commits on a repository, 1266 01:00:37,040 --> 01:00:39,950 I can say, OK, great, if I run a get request, 1267 01:00:39,950 --> 01:00:45,650 remember get to get information to this particular URL slash repo slash whoever 1268 01:00:45,650 --> 01:00:49,220 the owner of the repo is slash the name of the repo slash commits, 1269 01:00:49,220 --> 01:00:51,380 then I'll get back all of the commits. 1270 01:00:51,380 --> 01:00:54,260 And then most APIs will also give you information about, OK, here 1271 01:00:54,260 --> 01:00:55,850 are the parameters you can provide. 1272 01:00:55,850 --> 01:01:00,230 I can provide a parameter of since to say-- if I say 1273 01:01:00,230 --> 01:01:05,090 and since equal some date, it will only get me commits since a particular date, 1274 01:01:05,090 --> 01:01:06,120 for example. 1275 01:01:06,120 --> 01:01:08,030 And so every API is a little bit different. 1276 01:01:08,030 --> 01:01:09,830 But if you refer to the documentation, it 1277 01:01:09,830 --> 01:01:12,530 will usually tell you what are the types of parameters that you 1278 01:01:12,530 --> 01:01:16,940 can use in any particular API request. 1279 01:01:16,940 --> 01:01:17,530 Other things? 1280 01:01:17,530 --> 01:01:21,400 1281 01:01:21,400 --> 01:01:23,860 All right, so there was a lot of information today. 1282 01:01:23,860 --> 01:01:24,571 And I know that. 1283 01:01:24,571 --> 01:01:27,070 So we'll give you plenty of time to work on practicing this. 1284 01:01:27,070 --> 01:01:29,470 Tomorrow, though, we're going to do basically a complete context switch, 1285 01:01:29,470 --> 01:01:30,610 just to give some context for where we're 1286 01:01:30,610 --> 01:01:32,020 going in the latter half of the week. 1287 01:01:32,020 --> 01:01:32,920 We're done with flask. 1288 01:01:32,920 --> 01:01:34,961 We're done with databases after the end of today. 1289 01:01:34,961 --> 01:01:38,080 We're going to go back to the world of just HTML pages and JavaScript 1290 01:01:38,080 --> 01:01:41,080 and look in particular about how to build dynamic web applications using 1291 01:01:41,080 --> 01:01:43,840 JavaScript, creating interactive user interfaces, 1292 01:01:43,840 --> 01:01:45,760 and starting to solve a lot of the problems 1293 01:01:45,760 --> 01:01:48,610 that we've been seeing over the course of these past couple of days 1294 01:01:48,610 --> 01:01:52,300 where we've had to write a lot of code to get fairly simple things to work 1295 01:01:52,300 --> 01:01:53,990 inside of our user interfaces. 1296 01:01:53,990 --> 01:01:57,550 So this is going to be our last project that has to do with flask and SQL 1297 01:01:57,550 --> 01:02:00,790 before we move away from this, at least for the rest of CS50 Beyond. 1298 01:02:00,790 --> 01:02:02,819 And the project that I'll have you work on 1299 01:02:02,819 --> 01:02:06,110 is going to be an extension of the books that you were working on this morning. 1300 01:02:06,110 --> 01:02:08,234 So if you haven't yet gotten all the books imported 1301 01:02:08,234 --> 01:02:11,560 into your Heroku postgres database, feel free to continue working on that. 1302 01:02:11,560 --> 01:02:15,010 And we're happy to help you as you go about taking that CSV file of books 1303 01:02:15,010 --> 01:02:16,660 and importing it into your database. 1304 01:02:16,660 --> 01:02:19,076 My recommendation would be take it a little bit at a time. 1305 01:02:19,076 --> 01:02:21,790 Try and see if you can get one row imported into the database, 1306 01:02:21,790 --> 01:02:23,469 one book added to the database. 1307 01:02:23,469 --> 01:02:25,510 And then if that works, try doing a couple books. 1308 01:02:25,510 --> 01:02:28,180 And only then maybe consider trying all the 5,000 books 1309 01:02:28,180 --> 01:02:30,499 that we gave you inside of the file. 1310 01:02:30,499 --> 01:02:33,790 But then-- and you can use ORMs for this next part if you would like to, or you 1311 01:02:33,790 --> 01:02:35,860 don't have to if you would rather not-- 1312 01:02:35,860 --> 01:02:40,240 try creating a web application around the work that you've been doing. 1313 01:02:40,240 --> 01:02:43,042 So I'll show you books. 1314 01:02:43,042 --> 01:02:45,250 A simple web application that you might want to build 1315 01:02:45,250 --> 01:02:47,320 might look a little something like this. 1316 01:02:47,320 --> 01:02:49,170 We open up the web page and it's just going 1317 01:02:49,170 --> 01:02:52,400 to be a place where I can find a book by a title or by an author. 1318 01:02:52,400 --> 01:02:56,059 So if I type in something like Joe, for example, and press Submit, 1319 01:02:56,059 --> 01:02:58,600 well, what I'll get is a returning a listing of all the books 1320 01:02:58,600 --> 01:03:01,720 that have the word Joe in it, all the authors that have Joe as the name 1321 01:03:01,720 --> 01:03:03,700 or as a part of the name, for example. 1322 01:03:03,700 --> 01:03:07,310 And if I click on a book, I might see details about that book, for example. 1323 01:03:07,310 --> 01:03:09,490 And so you can imagine creating a web application. 1324 01:03:09,490 --> 01:03:11,448 You can model it off of the flights application 1325 01:03:11,448 --> 01:03:14,980 that's just querying flights that's instead querying your book database. 1326 01:03:14,980 --> 01:03:18,272 And so think about the possibilities that you might want to do with that. 1327 01:03:18,272 --> 01:03:20,980 You can also feel free to continue working on the projects you've 1328 01:03:20,980 --> 01:03:22,330 been working on earlier in this week. 1329 01:03:22,330 --> 01:03:24,913 I know some of you are still working on adding features to you 1330 01:03:24,913 --> 01:03:26,980 tic-tac-toe program or your drawing application 1331 01:03:26,980 --> 01:03:29,480 or any of the other applications that you've been working on 1332 01:03:29,480 --> 01:03:31,300 over the course of CS50 beyond so far. 1333 01:03:31,300 --> 01:03:34,420 And so we'll go ahead and break now into our afternoon project. 1334 01:03:34,420 --> 01:03:36,627 Feel free to continue working on importing books. 1335 01:03:36,627 --> 01:03:38,710 Feel free to start working on this web application 1336 01:03:38,710 --> 01:03:40,690 if that's something that's of interest to you. 1337 01:03:40,690 --> 01:03:43,780 What we'll have is we'll have this group and this group go ahead 1338 01:03:43,780 --> 01:03:46,091 and stay in the auditorium. 1339 01:03:46,091 --> 01:03:48,590 The back half of the middle group we'll have go to room 136. 1340 01:03:48,590 --> 01:03:50,530 And the front half will go up to room 212. 1341 01:03:50,530 --> 01:03:55,170 And tomorrow morning at 10:00 AM, we'll continue diving into React. 1342 01:03:55,170 --> 01:03:56,771