1 00:00:00,000 --> 00:00:04,365 [MUSIC PLAYING] 2 00:00:04,365 --> 00:00:16,232 3 00:00:16,232 --> 00:00:18,440 BRIAN YU: OK welcome back everyone to web programming 4 00:00:18,440 --> 00:00:19,760 with Python and JavaScript. 5 00:00:19,760 --> 00:00:21,870 So, last week, at the end of last week's lecture, 6 00:00:21,870 --> 00:00:25,160 we talked a little bit about how we might design a web application that's 7 00:00:25,160 --> 00:00:26,360 designed to store data. 8 00:00:26,360 --> 00:00:28,820 So we looked at an example of a note taking application, 9 00:00:28,820 --> 00:00:31,357 where our goal was to be able to have users type in notes 10 00:00:31,357 --> 00:00:34,190 and save those notes so that they could have a list of all the notes 11 00:00:34,190 --> 00:00:35,240 that they have saved. 12 00:00:35,240 --> 00:00:38,360 And we found a solution that works just by storing 13 00:00:38,360 --> 00:00:42,860 the notes inside of a list of strings in Python, in our flossed application. 14 00:00:42,860 --> 00:00:46,580 But it wasn't great, because if we were to ever shut down the web server 15 00:00:46,580 --> 00:00:49,910 and restart it again, we would suddenly lose access to all of that data. 16 00:00:49,910 --> 00:00:51,390 All that data would be lost. 17 00:00:51,390 --> 00:00:54,230 So what we're going to begin to talk about today is databases. 18 00:00:54,230 --> 00:00:57,920 And in particular, trying to find some way to use 19 00:00:57,920 --> 00:01:01,130 databases to make it easier for our web applications to store 20 00:01:01,130 --> 00:01:03,604 and manipulate and use data ultimately. 21 00:01:03,604 --> 00:01:06,520 And the types of databases that we're going to be talking about today, 22 00:01:06,520 --> 00:01:07,910 are relational databases. 23 00:01:07,910 --> 00:01:11,730 Which you can think of as effectively storing data inside of a table. 24 00:01:11,730 --> 00:01:13,730 So the example I keep coming back to today, 25 00:01:13,730 --> 00:01:16,190 is thinking about trying to design a database that's 26 00:01:16,190 --> 00:01:18,500 going to potentially be used by an airline, 27 00:01:18,500 --> 00:01:20,574 for example, to store flight information. 28 00:01:20,574 --> 00:01:23,240 And so you might imagine that if an airline wanted to store data 29 00:01:23,240 --> 00:01:25,309 about flights, in the simplest of senses, 30 00:01:25,309 --> 00:01:27,600 they might have a table that looks something like this. 31 00:01:27,600 --> 00:01:30,080 Where we have for each one of these rows, 32 00:01:30,080 --> 00:01:32,330 each row represents an individual flight, 33 00:01:32,330 --> 00:01:35,940 and each flight has an origin, New York, Shanghai, Istanbul, somewhere else. 34 00:01:35,940 --> 00:01:39,230 A destination, and a duration in some units, maybe minutes. 35 00:01:39,230 --> 00:01:41,540 So we might imagine that you could design 36 00:01:41,540 --> 00:01:45,470 a spreadsheet, for instance, that would have three columns, origin, 37 00:01:45,470 --> 00:01:46,700 destination, and duration. 38 00:01:46,700 --> 00:01:49,250 And that you have one row for each individual flight. 39 00:01:49,250 --> 00:01:52,400 And each one of those individual flights would have an origin, destination, 40 00:01:52,400 --> 00:01:53,784 and duration, respectively. 41 00:01:53,784 --> 00:01:55,700 So that's what we're going to be trying to do. 42 00:01:55,700 --> 00:01:59,540 Designing these sorts of tables, but we're going to do so using technology 43 00:01:59,540 --> 00:02:02,550 called SQL, or S-Q-L. Structured query language, 44 00:02:02,550 --> 00:02:07,190 which is a language designed to allow us to very easily interact with databases. 45 00:02:07,190 --> 00:02:10,926 Interact with tables of data that have rows and columns, for instance. 46 00:02:10,926 --> 00:02:12,800 And in particular, in this class, we're going 47 00:02:12,800 --> 00:02:16,940 to be using a particular version of SQL called PostgreSQL. 48 00:02:16,940 --> 00:02:19,597 Although there are a bunch of different versions of SQL 49 00:02:19,597 --> 00:02:22,430 that have slightly different features and slightly different aspects 50 00:02:22,430 --> 00:02:24,320 to their languages and the way that they work, 51 00:02:24,320 --> 00:02:25,778 but they're all very, very similar. 52 00:02:25,778 --> 00:02:27,710 So once you know how to interact with one, 53 00:02:27,710 --> 00:02:30,110 it's very easy to see the parallels in others 54 00:02:30,110 --> 00:02:32,540 because the syntax ultimately is very, very similar 55 00:02:32,540 --> 00:02:34,640 from one language to the next. 56 00:02:34,640 --> 00:02:36,887 So we'll go ahead and dive right in and start just 57 00:02:36,887 --> 00:02:38,720 by thinking of the first thing that we would 58 00:02:38,720 --> 00:02:44,260 want to do once we have a SQL database, which is going to be creating a table. 59 00:02:44,260 --> 00:02:46,010 So when we're creating a table we're going 60 00:02:46,010 --> 00:02:50,420 to need to decide what sort of data that table is going to hold. 61 00:02:50,420 --> 00:02:52,520 So we saw before in the table that I showed 62 00:02:52,520 --> 00:02:57,140 before that we had an origin column, a destination column, and a duration 63 00:02:57,140 --> 00:03:00,530 column for keeping track of different information about a particular flight. 64 00:03:00,530 --> 00:03:04,280 So all of that information needs to have a particular type. 65 00:03:04,280 --> 00:03:06,710 And so what we see here are a couple examples 66 00:03:06,710 --> 00:03:11,390 of the types of data that a SQL database might have. 67 00:03:11,390 --> 00:03:13,520 So data might be in the form of an integer, 68 00:03:13,520 --> 00:03:15,950 or a decimal, which can have a decimal point, 69 00:03:15,950 --> 00:03:20,430 serial is a common data type that you'll see in SQL or in PostgreSQL 70 00:03:20,430 --> 00:03:21,380 specifically. 71 00:03:21,380 --> 00:03:24,890 A serial data type is basically just like an integer, 72 00:03:24,890 --> 00:03:26,930 except it counts automatically. 73 00:03:26,930 --> 00:03:30,920 So anytime you add a new thing or a new record into your database, 74 00:03:30,920 --> 00:03:33,300 it will automatically increase its value. 75 00:03:33,300 --> 00:03:37,400 So this will be common If I want to give each flight a flight 76 00:03:37,400 --> 00:03:38,386 number, for instance. 77 00:03:38,386 --> 00:03:40,760 Where I want the first flight that I put into my database 78 00:03:40,760 --> 00:03:43,635 to have flight number one, the second flight to be flight number two, 79 00:03:43,635 --> 00:03:45,350 third flight number three, and so on. 80 00:03:45,350 --> 00:03:48,140 Serial is a useful data type that will just automatically increase 81 00:03:48,140 --> 00:03:50,560 its value one at a time. 82 00:03:50,560 --> 00:03:53,930 Varchar is another popular data type in SQL. 83 00:03:53,930 --> 00:03:56,760 Varchar stands for variable length of characters. 84 00:03:56,760 --> 00:04:00,300 So if I wanted to store a string, just some sort of text in a SQL database, 85 00:04:00,300 --> 00:04:02,990 varchar is generally the data type I would use for that. 86 00:04:02,990 --> 00:04:05,073 And there are a bunch of other data types as well. 87 00:04:05,073 --> 00:04:07,520 Data types for storing time stamps for a date and a time, 88 00:04:07,520 --> 00:04:11,000 a Boolean value for true or false, or an enum, which is just 89 00:04:11,000 --> 00:04:15,120 a way of saying one of a finite number of discrete possible values 90 00:04:15,120 --> 00:04:15,920 then I might have. 91 00:04:15,920 --> 00:04:20,839 We'll focus mostly on these top four here though for the purposes of today. 92 00:04:20,839 --> 00:04:23,480 So now that we have these data types, we need 93 00:04:23,480 --> 00:04:26,870 to use them in order to actually create a database table that we're ultimately 94 00:04:26,870 --> 00:04:27,774 going to use. 95 00:04:27,774 --> 00:04:29,690 So the first thing we'll look at is the syntax 96 00:04:29,690 --> 00:04:34,160 for how we would go about creating a table in Postgres, or in SQL 97 00:04:34,160 --> 00:04:35,610 more generally. 98 00:04:35,610 --> 00:04:41,000 So the syntax for doing that, we'll notice we'll find in create dot SQL, 99 00:04:41,000 --> 00:04:43,730 and this is an example of SQL syntax. 100 00:04:43,730 --> 00:04:47,562 Of text that we're going to be feeding into a database, 101 00:04:47,562 --> 00:04:49,520 and that database is going to interpret the SQL 102 00:04:49,520 --> 00:04:52,520 commands, and use it in order to create a table. 103 00:04:52,520 --> 00:04:54,200 So what's going on here. 104 00:04:54,200 --> 00:04:57,917 Online one, I said create table flights. 105 00:04:57,917 --> 00:05:00,500 And so that's telling the database to create a new table, just 106 00:05:00,500 --> 00:05:04,160 a set of rows and columns, and we're going to name it flights. 107 00:05:04,160 --> 00:05:07,700 And then when I create the table, I need to tell the database in advance 108 00:05:07,700 --> 00:05:11,750 what types of information my flights table is going to contain. 109 00:05:11,750 --> 00:05:14,450 And so you'll see that information, one on each row. 110 00:05:14,450 --> 00:05:17,940 The first thing that each table is going to contain is an ID. 111 00:05:17,940 --> 00:05:20,540 This is a very common paradigm when designing databases. 112 00:05:20,540 --> 00:05:23,810 We want it to be easy to reference individual flights. 113 00:05:23,810 --> 00:05:26,210 To be able to say, get flight number 28, and give me 114 00:05:26,210 --> 00:05:28,040 some details about that particular flight. 115 00:05:28,040 --> 00:05:30,740 And it's much easier to say get flight number 28, 116 00:05:30,740 --> 00:05:35,700 than it is to say get me that flight that goes from Moscow to New York, 117 00:05:35,700 --> 00:05:36,440 for instance. 118 00:05:36,440 --> 00:05:39,800 And so having an ID on every single table that makes it easy to say, 119 00:05:39,800 --> 00:05:43,190 this is the way that I want to reference flights, is often very practical. 120 00:05:43,190 --> 00:05:45,690 And so this ID is going to be of type serial. 121 00:05:45,690 --> 00:05:48,950 Recall that serial is just an integer that will automatically count. 122 00:05:48,950 --> 00:05:51,324 The first flight will be flight one and the second flight 123 00:05:51,324 --> 00:05:52,970 will be flight two, et cetera. 124 00:05:52,970 --> 00:05:57,307 And then primary key just means this is the primary way via which 125 00:05:57,307 --> 00:05:58,640 I'm going to reference a flight. 126 00:05:58,640 --> 00:06:02,000 That every flight is going to have a unique ID. 127 00:06:02,000 --> 00:06:05,150 And therefore, if I tell you ID number 28, 128 00:06:05,150 --> 00:06:08,550 that will map to one and only one flight. 129 00:06:08,550 --> 00:06:11,300 Then on line three, I'm starting to define the other columns 130 00:06:11,300 --> 00:06:12,050 that I care about. 131 00:06:12,050 --> 00:06:13,709 What else goes into a flight? 132 00:06:13,709 --> 00:06:15,500 Well, it needs an origin, which for now I'm 133 00:06:15,500 --> 00:06:17,630 just going to represent to the Varchar, just text. 134 00:06:17,630 --> 00:06:20,660 Just the name of the place from which the flight is departing. 135 00:06:20,660 --> 00:06:25,130 And I can additionally specify other properties of this column. 136 00:06:25,130 --> 00:06:27,530 In particular, I'm saying not null. 137 00:06:27,530 --> 00:06:32,150 So SQL databases support the idea that a column may or may not have a value. 138 00:06:32,150 --> 00:06:36,320 And so in some cases, you might want it to be the case that a column doesn't 139 00:06:36,320 --> 00:06:40,440 necessarily need to have a value, but can have a value if you wanted to. 140 00:06:40,440 --> 00:06:42,720 But in this particular case, if there is a flight. 141 00:06:42,720 --> 00:06:43,940 It needs to have an origin. 142 00:06:43,940 --> 00:06:46,065 And I don't want there to be a flight in my flights 143 00:06:46,065 --> 00:06:50,210 table unless it does have an origin or city from which it's flying out from. 144 00:06:50,210 --> 00:06:55,710 And so I specified not null to mean that the origin column cannot be empty. 145 00:06:55,710 --> 00:06:59,540 And if you try to add a flight where the origin column is empty, 146 00:06:59,540 --> 00:07:01,162 the database should reject that. 147 00:07:01,162 --> 00:07:04,370 So you can begin to enforce constraints on what the database will or will not 148 00:07:04,370 --> 00:07:07,040 allow by adding these additional constraints 149 00:07:07,040 --> 00:07:10,940 after the name of the column and the type of the column. 150 00:07:10,940 --> 00:07:14,120 Likewise, on line four, I'm defining a destination, which is also just going 151 00:07:14,120 --> 00:07:15,620 to be a varchar for now, just text. 152 00:07:15,620 --> 00:07:17,390 And I'm saying that this is not null. 153 00:07:17,390 --> 00:07:20,816 And then finally, I'm specifying a duration, this will just be in minutes. 154 00:07:20,816 --> 00:07:22,940 Which is going to be an integer, and also not null. 155 00:07:22,940 --> 00:07:25,370 Every flight needs to have a duration for how long 156 00:07:25,370 --> 00:07:26,960 that flight is going to last. 157 00:07:26,960 --> 00:07:31,950 And so this is the syntax for how we would go about creating a table. 158 00:07:31,950 --> 00:07:35,842 So once I have a SQL database up and running, and generally speaking, 159 00:07:35,842 --> 00:07:37,550 in order to do this in Postgres at least, 160 00:07:37,550 --> 00:07:40,250 you'd need to start up a Postgres server. 161 00:07:40,250 --> 00:07:43,637 So you can start a server up locally on your own computer, which is 162 00:07:43,637 --> 00:07:45,220 what I'll be doing in today's lecture. 163 00:07:45,220 --> 00:07:49,250 But you can also find PostgreSQL databases that are hosted online. 164 00:07:49,250 --> 00:07:53,900 In particular, for the next project we all will work on, what you'll do, 165 00:07:53,900 --> 00:07:56,300 is you'll be interacting with a Postgres database, 166 00:07:56,300 --> 00:07:58,820 and that database will be living on hiroki, 167 00:07:58,820 --> 00:08:02,274 which is an online platform for hosting web sites and databases. 168 00:08:02,274 --> 00:08:04,190 And you'll be able to connect to that database 169 00:08:04,190 --> 00:08:07,296 remotely using your computer to connect to some database that's online. 170 00:08:07,296 --> 00:08:09,170 But for now I'll just connect to the database 171 00:08:09,170 --> 00:08:11,130 that's local to my own computer. 172 00:08:11,130 --> 00:08:14,270 So I'll go ahead and type, go into psql, it's 173 00:08:14,270 --> 00:08:19,720 just a command that that lets me type in PostgreSQL commands. 174 00:08:19,720 --> 00:08:24,080 And I'm saying lecture 3 because that's the name of this particular database. 175 00:08:24,080 --> 00:08:25,790 In reality, if you wanted to use the Psql 176 00:08:25,790 --> 00:08:29,450 command with a database that exists somewhere on the internet, 177 00:08:29,450 --> 00:08:31,880 that database would have a URL. 178 00:08:31,880 --> 00:08:33,840 And information about where to get that URL 179 00:08:33,840 --> 00:08:35,419 will be in the project instructions. 180 00:08:35,419 --> 00:08:38,690 You can paste that URL in after the word Psql in order 181 00:08:38,690 --> 00:08:41,909 to access that database as well. 182 00:08:41,909 --> 00:08:46,440 So now I want to create the table inside of this database. 183 00:08:46,440 --> 00:08:48,260 So I'll go ahead and copy this text, this 184 00:08:48,260 --> 00:08:51,110 create table flights with all of those individual columns listed one 185 00:08:51,110 --> 00:08:52,440 after the other. 186 00:08:52,440 --> 00:08:54,530 And I'll paste it in here. 187 00:08:54,530 --> 00:08:56,660 And now that table is created. 188 00:08:56,660 --> 00:08:59,484 So after that semi-colon, that means the end of the command, 189 00:08:59,484 --> 00:09:01,400 Postgres said back to me create table, meaning 190 00:09:01,400 --> 00:09:04,608 that it successfully acknowledged the fact that I wanted to create the table, 191 00:09:04,608 --> 00:09:05,960 and it created the table for me. 192 00:09:05,960 --> 00:09:08,320 And I can verify that if I type backslash 193 00:09:08,320 --> 00:09:12,770 d, which just shows me all of the different parts of my database 194 00:09:12,770 --> 00:09:13,920 that exist right now. 195 00:09:13,920 --> 00:09:16,460 And I can see that I have this table called 196 00:09:16,460 --> 00:09:19,550 flights that exists right now inside of my database 197 00:09:19,550 --> 00:09:21,270 that I have now just created. 198 00:09:21,270 --> 00:09:27,800 So that was how we might go about creating a table in Postgres. 199 00:09:27,800 --> 00:09:30,170 And we talked about the various possible constraints 200 00:09:30,170 --> 00:09:32,672 that we can place when we want to create a table 201 00:09:32,672 --> 00:09:34,880 and we want to define what the individual columns do. 202 00:09:34,880 --> 00:09:38,270 So I can specify that a column is not null, 203 00:09:38,270 --> 00:09:39,899 meaning that it needs to have a value. 204 00:09:39,899 --> 00:09:41,690 I can specify that a column must be unique. 205 00:09:41,690 --> 00:09:44,900 If I don't want two people to share the same value in a particular column, 206 00:09:44,900 --> 00:09:46,910 I can enforce that in the database by saying, 207 00:09:46,910 --> 00:09:49,140 this is going to be a unique column. 208 00:09:49,140 --> 00:09:51,800 So for instance, if you were managing a database of users 209 00:09:51,800 --> 00:09:54,740 and every user has a user name, you might want that user name field 210 00:09:54,740 --> 00:09:57,860 to be a unique column because you don't want two different users to have 211 00:09:57,860 --> 00:09:59,660 the same username, for example. 212 00:09:59,660 --> 00:10:02,630 Primary key is just the single way by which you're primarily 213 00:10:02,630 --> 00:10:04,100 going to reference a table. 214 00:10:04,100 --> 00:10:07,467 Usually, that's going to be the ID, just the integer that's 215 00:10:07,467 --> 00:10:10,550 going to keep counting every time that keeps track of which individual row 216 00:10:10,550 --> 00:10:11,900 you're referring to. 217 00:10:11,900 --> 00:10:13,880 You can give columns a default value. 218 00:10:13,880 --> 00:10:18,600 So if most of the time your column is going to have a value of 0, 219 00:10:18,600 --> 00:10:21,570 you might say this column default 0 to mean, 220 00:10:21,570 --> 00:10:24,900 if I don't tell you what to put in the column, just put in a 0 by default 221 00:10:24,900 --> 00:10:27,300 and only put something else in if I tell you to do that. 222 00:10:27,300 --> 00:10:29,070 So that can often save time. 223 00:10:29,070 --> 00:10:31,680 And you can also add additional constraints. 224 00:10:31,680 --> 00:10:34,410 Check is another constraint that you can use to say, 225 00:10:34,410 --> 00:10:38,250 I only want to allow values that are less than 50 or less than 100, 226 00:10:38,250 --> 00:10:39,030 for example. 227 00:10:39,030 --> 00:10:42,790 And you can begin to add more constraints on those databases as well. 228 00:10:42,790 --> 00:10:46,980 Questions about how to go about creating tables or what we were thinking about 229 00:10:46,980 --> 00:10:49,530 in terms of deciding what types each column should have 230 00:10:49,530 --> 00:10:52,961 and how that process works? 231 00:10:52,961 --> 00:10:53,460 OK. 232 00:10:53,460 --> 00:10:56,400 So we've created a table, and we've created this flight's table. 233 00:10:56,400 --> 00:10:58,050 But right now that table is empty. 234 00:10:58,050 --> 00:11:01,660 It's got no rows in it, so it's not actually storing any data just yet. 235 00:11:01,660 --> 00:11:03,040 So let's go ahead and fix that. 236 00:11:03,040 --> 00:11:04,831 The next thing we're going to talk about is 237 00:11:04,831 --> 00:11:07,690 how to go about inserting data into a data table. 238 00:11:07,690 --> 00:11:10,900 So this is what the syntax for that might look like. 239 00:11:10,900 --> 00:11:15,840 So if I wanted to take a flight and insert a flight into my flights table, 240 00:11:15,840 --> 00:11:17,320 this would be the syntax. 241 00:11:17,320 --> 00:11:21,120 First I say insert into, which is the SQL syntax to say, 242 00:11:21,120 --> 00:11:24,150 I want to add data to one of my tables. 243 00:11:24,150 --> 00:11:28,189 After that I specify the name of the table that I want to add to. 244 00:11:28,189 --> 00:11:30,480 So in this case, I'm going to add to the flights table. 245 00:11:30,480 --> 00:11:33,120 In the previous example, we created a table called flights, 246 00:11:33,120 --> 00:11:37,380 now I'm saying I want to insert into this flight's table some data. 247 00:11:37,380 --> 00:11:40,860 What comes next, and this could all be on one line, but for the sake of space 248 00:11:40,860 --> 00:11:43,170 it's broken out across a couple lines. 249 00:11:43,170 --> 00:11:45,300 I've specified in parentheses and separated 250 00:11:45,300 --> 00:11:49,500 by commas, the names of the columns that I want to add information for. 251 00:11:49,500 --> 00:11:53,580 So I'm going to add an origin, a destination and a duration 252 00:11:53,580 --> 00:11:55,050 for this new row that I'm adding. 253 00:11:55,050 --> 00:11:58,080 And you don't necessarily always need to add something 254 00:11:58,080 --> 00:11:59,790 for every single possible row. 255 00:11:59,790 --> 00:12:02,400 Note that in this case, I didn't add in ID 256 00:12:02,400 --> 00:12:05,040 for example, which is the fourth column in the flights table, 257 00:12:05,040 --> 00:12:07,680 because ID is going to automatically increment on its own. 258 00:12:07,680 --> 00:12:10,300 We defined it as a serial type just a moment ago. 259 00:12:10,300 --> 00:12:14,640 And if you had a column that allowed no values, was allowed to be empty, 260 00:12:14,640 --> 00:12:18,030 you wouldn't need to specify it inside of the insert if you didn't want to. 261 00:12:18,030 --> 00:12:20,760 Or if it had a default value, and you didn't specify it here, 262 00:12:20,760 --> 00:12:22,740 it would just go to the default value since you 263 00:12:22,740 --> 00:12:24,317 didn't tell it what value to take on. 264 00:12:24,317 --> 00:12:27,150 But in this case, I'm going to add a new flight to my flights table. 265 00:12:27,150 --> 00:12:30,184 I want to give it an origin, a destination and a duration. 266 00:12:30,184 --> 00:12:32,100 And now I need to tell the database all right, 267 00:12:32,100 --> 00:12:35,250 here are the columns that I'm going to add for this particular row. 268 00:12:35,250 --> 00:12:36,960 What should their values be? 269 00:12:36,960 --> 00:12:38,710 And so we say values. 270 00:12:38,710 --> 00:12:43,710 And then in parentheses, again, in the same order that I defined my columns, 271 00:12:43,710 --> 00:12:48,150 first I'm going to specify my origin, New York, then the destination, London, 272 00:12:48,150 --> 00:12:51,755 and then the duration, 415 minutes in this case. 273 00:12:51,755 --> 00:12:55,050 And so this would be the syntax for how to go about inserting 274 00:12:55,050 --> 00:12:57,075 a record into that database. 275 00:12:57,075 --> 00:12:58,950 Let's take a look at what that would actually 276 00:12:58,950 --> 00:13:03,720 look like if I were to go over here and go back into this database. 277 00:13:03,720 --> 00:13:07,980 I would say something to be effect of, insert into flights. 278 00:13:07,980 --> 00:13:14,070 And then I would specify origin, destination, and duration. 279 00:13:14,070 --> 00:13:23,120 And then these values, I want to specify New York, and London and 415 minutes. 280 00:13:23,120 --> 00:13:26,130 So semi-colon, I press Return, SQL says that I've now 281 00:13:26,130 --> 00:13:28,140 inserted that row into the database. 282 00:13:28,140 --> 00:13:30,660 And so now I've added a row to that table. 283 00:13:30,660 --> 00:13:34,170 We haven't yet looked at how to see all that data in the table just yet, 284 00:13:34,170 --> 00:13:36,270 but I have inserted that row into the table. 285 00:13:36,270 --> 00:13:39,450 And just for the sake of adding some sample data into that table, 286 00:13:39,450 --> 00:13:43,117 I have here insert dot SQL, which just contains 287 00:13:43,117 --> 00:13:44,700 a whole bunch of other insert queries. 288 00:13:44,700 --> 00:13:48,780 So these are all identical queries, each one just has insert into flights, 289 00:13:48,780 --> 00:13:50,970 here are the individual values I want to insert, 290 00:13:50,970 --> 00:13:55,890 and so I'm going to go ahead and copy these and paste them in here. 291 00:13:55,890 --> 00:13:58,170 So that now I've got a couple of different flights 292 00:13:58,170 --> 00:14:00,430 that are inside of this database. 293 00:14:00,430 --> 00:14:05,110 So that was inserting values into the database. 294 00:14:05,110 --> 00:14:09,580 But next up is figuring out once I have values in the database, 295 00:14:09,580 --> 00:14:11,170 how do I look at them? 296 00:14:11,170 --> 00:14:12,720 How do I see what's going on? 297 00:14:12,720 --> 00:14:16,470 And the way to do that, is via what's called a select query. 298 00:14:16,470 --> 00:14:20,730 And so we've looked at create table, which creates a new table, insert into, 299 00:14:20,730 --> 00:14:23,350 which inserts a row into the database. 300 00:14:23,350 --> 00:14:24,840 So writing to the database. 301 00:14:24,840 --> 00:14:27,780 And select is a query that is meant for reading from a database. 302 00:14:27,780 --> 00:14:31,350 I already have rows in the database, and now I care about accessing those rows. 303 00:14:31,350 --> 00:14:34,200 Figuring out what they are, under certain conditions, 304 00:14:34,200 --> 00:14:36,580 and that's when I would go about using a SELECT query. 305 00:14:36,580 --> 00:14:39,510 So what does a select query look like? 306 00:14:39,510 --> 00:14:46,710 Well the simplest select query that I can run is a select star from flights. 307 00:14:46,710 --> 00:14:50,970 Select star, star just means select everything, meaning all columns. 308 00:14:50,970 --> 00:14:53,989 And then from just means what table do I want to select from, 309 00:14:53,989 --> 00:14:55,530 because I might have multiple tables. 310 00:14:55,530 --> 00:14:57,977 I ran Create Table once to create a flights table, 311 00:14:57,977 --> 00:15:00,810 but I could just as well have created multiple other tables that are 312 00:15:00,810 --> 00:15:03,150 stored as part of the same database. 313 00:15:03,150 --> 00:15:06,030 So I'm saying select star from flights, meeting select everything 314 00:15:06,030 --> 00:15:08,430 from my flights table, and when I press Return 315 00:15:08,430 --> 00:15:13,064 and run that query, when I get back is all of the data that is in my table. 316 00:15:13,064 --> 00:15:16,230 And It looks very similar to the table that we showed at the very beginning. 317 00:15:16,230 --> 00:15:20,460 But this is SQL telling me that OK, here are my six flights that I currently 318 00:15:20,460 --> 00:15:24,240 have, each one has an ID, each one has an origin, a destination 319 00:15:24,240 --> 00:15:25,410 and a duration. 320 00:15:25,410 --> 00:15:29,590 I only ever added the origin and the destination and the duration. 321 00:15:29,590 --> 00:15:33,180 But in particular, SQL has automatically added the ID for me, 322 00:15:33,180 --> 00:15:35,520 since I specified the ID is the serial column. 323 00:15:35,520 --> 00:15:37,680 And so this gives me access to all of that data 324 00:15:37,680 --> 00:15:39,720 just by running that select query. 325 00:15:39,720 --> 00:15:43,590 Questions about inserts or selects in terms of what we've seen so far? 326 00:15:43,590 --> 00:15:47,700 327 00:15:47,700 --> 00:15:49,320 All right. 328 00:15:49,320 --> 00:15:53,250 So let's look at some of the other types of select queries that we can run. 329 00:15:53,250 --> 00:15:56,400 So select star from flights is the first query that we saw. 330 00:15:56,400 --> 00:15:58,127 So if our data looks something like this, 331 00:15:58,127 --> 00:16:00,960 with six different flights, each one with an ID, origin, destination 332 00:16:00,960 --> 00:16:02,070 and duration. 333 00:16:02,070 --> 00:16:04,860 Select star from flight is going to give me back all of that data. 334 00:16:04,860 --> 00:16:06,990 Where this light blue just means all of this data 335 00:16:06,990 --> 00:16:08,580 is information that you'll get back, and you 336 00:16:08,580 --> 00:16:11,496 saw when I ran that query a moment ago, this is exactly what happened. 337 00:16:11,496 --> 00:16:13,500 All of this data was displayed back to me 338 00:16:13,500 --> 00:16:15,630 and handed back to me from the database. 339 00:16:15,630 --> 00:16:21,600 But oftentimes, when I want to query for data inside of my web application, 340 00:16:21,600 --> 00:16:24,051 it's not the case that I need all of the data all at once. 341 00:16:24,051 --> 00:16:26,050 In fact, that'd be probably quite a lot of data, 342 00:16:26,050 --> 00:16:28,260 especially if your web application starts getting big 343 00:16:28,260 --> 00:16:30,010 and starts getting a lot of data in it. 344 00:16:30,010 --> 00:16:34,320 So what I'd likely want to do is only select the things that I care about. 345 00:16:34,320 --> 00:16:37,170 So select star from flight, means select everything 346 00:16:37,170 --> 00:16:41,539 across all of the columns of the table get me that information. 347 00:16:41,539 --> 00:16:43,830 I could also do a query that looks something like this. 348 00:16:43,830 --> 00:16:47,370 Select origin comma destination from flights. 349 00:16:47,370 --> 00:16:49,710 Where I've replaced the star meaning select everything 350 00:16:49,710 --> 00:16:52,740 with just two columns, origin and destination. 351 00:16:52,740 --> 00:16:55,290 And so if I were to run that query instead, 352 00:16:55,290 --> 00:16:57,360 then the only data that's handed back to me 353 00:16:57,360 --> 00:17:00,340 are the contents of the origin and the destination columns. 354 00:17:00,340 --> 00:17:02,530 So I don't care about what the ID of the flight is. 355 00:17:02,530 --> 00:17:04,321 I don't care how long the flight is, I just 356 00:17:04,321 --> 00:17:06,599 care where am I flying from where am I flying to, 357 00:17:06,599 --> 00:17:10,200 and I'll get back that data for all of the flights 358 00:17:10,200 --> 00:17:12,520 that are inside of my table. 359 00:17:12,520 --> 00:17:16,200 And so it's easy and SQL to manipulate the data in the table 360 00:17:16,200 --> 00:17:18,630 to get back only the information that you want, just 361 00:17:18,630 --> 00:17:20,957 by specifying in the query what information it 362 00:17:20,957 --> 00:17:22,290 is that you actually care about. 363 00:17:22,290 --> 00:17:24,998 If you only care about two columns, no need to select everything, 364 00:17:24,998 --> 00:17:28,764 you only need to select the columns that you actually want to get back. 365 00:17:28,764 --> 00:17:30,930 There are other ways to modify your queries as well. 366 00:17:30,930 --> 00:17:35,270 So select star from flight, star again means select all of the columns. 367 00:17:35,270 --> 00:17:37,350 But I can add this WHERE clause. 368 00:17:37,350 --> 00:17:40,110 And the WHERE clause is going to allow me to 369 00:17:40,110 --> 00:17:42,630 instead of restricting what columns I'm selecting, 370 00:17:42,630 --> 00:17:44,850 restrict what rows I'm selecting. 371 00:17:44,850 --> 00:17:47,010 So normally, select star from flights would 372 00:17:47,010 --> 00:17:50,040 give me back all of the rows inside of my database. 373 00:17:50,040 --> 00:17:52,531 But chances are, if my database starts getting big 374 00:17:52,531 --> 00:17:54,780 and I have a lot of different rows inside of my table, 375 00:17:54,780 --> 00:17:59,130 I don't want to necessarily select every single row every time I make a query. 376 00:17:59,130 --> 00:18:03,300 I only care about selecting particular rows that I care about 377 00:18:03,300 --> 00:18:04,830 in this particular instance. 378 00:18:04,830 --> 00:18:08,761 And so if I say select star from flights where ID equals 3, 379 00:18:08,761 --> 00:18:11,010 as you might reasonably guess, what that's going to do 380 00:18:11,010 --> 00:18:14,610 is instead of giving me back all six of the rows inside of my table, 381 00:18:14,610 --> 00:18:17,160 it's only going to take row number three. 382 00:18:17,160 --> 00:18:18,990 And this is going to be the only row that's 383 00:18:18,990 --> 00:18:21,752 handed back to me from the database. 384 00:18:21,752 --> 00:18:23,460 Questions about that WHERE clause so far? 385 00:18:23,460 --> 00:18:26,330 386 00:18:26,330 --> 00:18:28,837 That WHERE clause doesn't have to be just a number. 387 00:18:28,837 --> 00:18:30,170 It could be of any type as well. 388 00:18:30,170 --> 00:18:33,417 So I could say, select star from flights were origin equals New York. 389 00:18:33,417 --> 00:18:35,250 And that's going to go through the database, 390 00:18:35,250 --> 00:18:37,291 find me any flight that's flying out of New York, 391 00:18:37,291 --> 00:18:38,959 and only hand me back that information. 392 00:18:38,959 --> 00:18:41,750 So I ignore all the other flights that aren't coming from New York, 393 00:18:41,750 --> 00:18:43,041 and I'm only getting these two. 394 00:18:43,041 --> 00:18:45,920 And you can imagine that being practical if you're actually 395 00:18:45,920 --> 00:18:48,860 running a airline's web application, and someone 396 00:18:48,860 --> 00:18:51,650 is looking for flights out of New York, rather than query 397 00:18:51,650 --> 00:18:54,050 for every single flight and show all of that, 398 00:18:54,050 --> 00:18:56,930 you would only want to query for the flights that are flying out 399 00:18:56,930 --> 00:19:02,640 of New York, for instance, and only give back that data back to the user. 400 00:19:02,640 --> 00:19:05,330 So you can also, in addition to specifying equality, 401 00:19:05,330 --> 00:19:07,942 use the WHERE clause to specify an inequality. 402 00:19:07,942 --> 00:19:09,650 So in this case, select star from flights 403 00:19:09,650 --> 00:19:11,980 where duration is greater than 500. 404 00:19:11,980 --> 00:19:14,360 What that's ultimately going to do for me is to say, 405 00:19:14,360 --> 00:19:17,840 I want to select from flights, but only where this duration 406 00:19:17,840 --> 00:19:19,610 column is greater than 500. 407 00:19:19,610 --> 00:19:21,860 Giving me back only the longer of the flights that 408 00:19:21,860 --> 00:19:24,380 happened to be part of this database so far. 409 00:19:24,380 --> 00:19:27,660 And so those inequalities are supported as well. 410 00:19:27,660 --> 00:19:31,490 And I can also use Boolean logic, ands and ors, 411 00:19:31,490 --> 00:19:33,420 to manipulate my queries as well. 412 00:19:33,420 --> 00:19:37,100 So in this case, I've said, select star from flights where destination equals 413 00:19:37,100 --> 00:19:39,900 Paris and duration is greater than 500. 414 00:19:39,900 --> 00:19:43,280 And as you might reasonably imagine, the only row that's going to return here 415 00:19:43,280 --> 00:19:44,540 is row number two. 416 00:19:44,540 --> 00:19:48,640 Because the destination is Paris, and the duration is greater than 500. 417 00:19:48,640 --> 00:19:52,220 And even though there are other flights that are duration over 500, 418 00:19:52,220 --> 00:19:54,680 or have a destination of Paris, those aren't selected. 419 00:19:54,680 --> 00:19:58,080 Because I only want to select ones that need both criteria. 420 00:19:58,080 --> 00:20:02,270 And just as we can use an and inside of a Boolean expression in SQL, 421 00:20:02,270 --> 00:20:05,330 we can just as easily use an or inside of that expression. 422 00:20:05,330 --> 00:20:08,330 So I can say select star from flights where destination equals 423 00:20:08,330 --> 00:20:11,180 Paris, or duration is greater than 500. 424 00:20:11,180 --> 00:20:14,450 And now all four of these are going to be handed back to me. 425 00:20:14,450 --> 00:20:18,200 This one, because it satisfies both of the criteria, 426 00:20:18,200 --> 00:20:21,650 row number three because the duration is greater than 500. 427 00:20:21,650 --> 00:20:25,010 And then rows four and five because I have 428 00:20:25,010 --> 00:20:28,370 it such that they both have a destination of Paris. 429 00:20:28,370 --> 00:20:32,210 And so there's a variety of different types of SQL queries 430 00:20:32,210 --> 00:20:36,020 that I can run in order to do a variety of different things. 431 00:20:36,020 --> 00:20:39,000 Questions on those so far? 432 00:20:39,000 --> 00:20:39,680 OK. 433 00:20:39,680 --> 00:20:42,680 Let's take a look at a couple of other types of queries that we can run, 434 00:20:42,680 --> 00:20:46,220 and a couple of others of these examples of select queries. 435 00:20:46,220 --> 00:20:47,840 So I'll go back into the database. 436 00:20:47,840 --> 00:20:50,610 And we'll do a couple of examples from ones we've seen before. 437 00:20:50,610 --> 00:20:53,330 So select star from flights gave me back everything. 438 00:20:53,330 --> 00:20:57,860 If I only care about maybe the destination and the duration, 439 00:20:57,860 --> 00:21:03,080 I could say select destination comma duration from flights. 440 00:21:03,080 --> 00:21:06,230 And that gives me back only those two columns, for instance. 441 00:21:06,230 --> 00:21:07,730 And then I can begin to restrict it. 442 00:21:07,730 --> 00:21:15,890 Select star from flights where destination equals Paris. 443 00:21:15,890 --> 00:21:19,400 And that gives me only the rows whose destination is Paris, 444 00:21:19,400 --> 00:21:21,080 and ignores everything else. 445 00:21:21,080 --> 00:21:23,660 But where SQL starts to get even more powerful, 446 00:21:23,660 --> 00:21:26,570 is when I can start using functions to begin 447 00:21:26,570 --> 00:21:33,080 to specify with even more precision what it is that I actually care about. 448 00:21:33,080 --> 00:21:37,240 So SQL, for example, has an average function, called AVG. 449 00:21:37,240 --> 00:21:41,000 And so maybe I care about knowing what is the average length 450 00:21:41,000 --> 00:21:42,840 of a flight in my database? 451 00:21:42,840 --> 00:21:48,260 So I could say, select the average duration from flights. 452 00:21:48,260 --> 00:21:51,560 Where AVG is my an average function and being the average of the duration. 453 00:21:51,560 --> 00:21:54,560 And what I get is 501 point 6667. 454 00:21:54,560 --> 00:21:57,530 Which happens to be the average duration of a flight in my flights 455 00:21:57,530 --> 00:21:58,760 table right now. 456 00:21:58,760 --> 00:22:01,112 And this is still just a select query. 457 00:22:01,112 --> 00:22:04,070 It just happens to have a function that's modifying one of the columns. 458 00:22:04,070 --> 00:22:05,840 I'm modifying the Duration column. 459 00:22:05,840 --> 00:22:09,030 But I could add a WHERE clause to this, for example. 460 00:22:09,030 --> 00:22:11,780 I could say select the average duration from the flights 461 00:22:11,780 --> 00:22:14,060 where the origin is New York. 462 00:22:14,060 --> 00:22:18,690 So that will only give me the average length of a flight from New York, 463 00:22:18,690 --> 00:22:19,520 for instance. 464 00:22:19,520 --> 00:22:21,560 And so you can begin to take these pieces 465 00:22:21,560 --> 00:22:26,480 and begin to put them together in order to construct more interesting queries. 466 00:22:26,480 --> 00:22:29,990 Another particular popular function is the count function, 467 00:22:29,990 --> 00:22:34,650 which just counts how many rows that are returned from the database. 468 00:22:34,650 --> 00:22:41,425 So I could say, for instance, select COUNT star from flights to mean select 469 00:22:41,425 --> 00:22:44,550 all the rows from flights-- that's with select star from flights would do-- 470 00:22:44,550 --> 00:22:46,610 but instead of giving back all the rows, just 471 00:22:46,610 --> 00:22:48,290 give me the count of the number of rows. 472 00:22:48,290 --> 00:22:50,580 Tell me how many rows there are in my table. 473 00:22:50,580 --> 00:22:52,520 And that will give me back six. 474 00:22:52,520 --> 00:22:55,200 But I could equivalently or relatedly, try 475 00:22:55,200 --> 00:23:01,459 to say select COUNT star from flights where origin equals New York. 476 00:23:01,459 --> 00:23:02,750 And that will give me back two. 477 00:23:02,750 --> 00:23:06,740 Because there were only two flights that are flying out from New York. 478 00:23:06,740 --> 00:23:11,420 And likewise if I were to type in flights from origin of Moscow, 479 00:23:11,420 --> 00:23:12,920 there is only one of those flights. 480 00:23:12,920 --> 00:23:15,050 And so counts can be used in order to figure out 481 00:23:15,050 --> 00:23:18,810 how many entries inside of my table have this particular property. 482 00:23:18,810 --> 00:23:23,040 And so that can often be very, very useful as well. 483 00:23:23,040 --> 00:23:27,480 Questions about any of those things that we've seen so far? 484 00:23:27,480 --> 00:23:29,910 Other popular functions like Min and max which 485 00:23:29,910 --> 00:23:33,420 can be used if I want to select the minimum duration 486 00:23:33,420 --> 00:23:35,070 from my list of flights. 487 00:23:35,070 --> 00:23:37,920 OK, the shortest flight is 245 minutes. 488 00:23:37,920 --> 00:23:40,290 If I'm curious as to which flight is that one that 489 00:23:40,290 --> 00:23:43,800 has a duration of 245 minutes, I could say select star 490 00:23:43,800 --> 00:23:48,270 from flights where duration equals 245. 491 00:23:48,270 --> 00:23:51,410 And that will tell me OK, that flight is the flight from Moscow to Paris. 492 00:23:51,410 --> 00:23:54,940 That's the flight with ID number five. 493 00:23:54,940 --> 00:23:57,328 And so that database insertion. 494 00:23:57,328 --> 00:24:01,160 495 00:24:01,160 --> 00:24:03,060 A couple of other things you can do. 496 00:24:03,060 --> 00:24:05,870 You can also specify using the in keyword. 497 00:24:05,870 --> 00:24:07,800 A range of possible values. 498 00:24:07,800 --> 00:24:10,370 So in this case, I'm saying select star from flights 499 00:24:10,370 --> 00:24:13,610 where originated in New York and Lima. 500 00:24:13,610 --> 00:24:18,470 And the result of that is that we get flights that are from either New York 501 00:24:18,470 --> 00:24:22,100 or Lima that get selected as a result. And likewise, I 502 00:24:22,100 --> 00:24:23,510 can start to do string matching. 503 00:24:23,510 --> 00:24:24,900 Something like this. 504 00:24:24,900 --> 00:24:30,620 Which is a situation in which I select star from flights where origin like 505 00:24:30,620 --> 00:24:32,930 %a%. 506 00:24:32,930 --> 00:24:37,040 So common nowadays are things like auto complete, or where you can 507 00:24:37,040 --> 00:24:39,660 type in something partial into a search query, 508 00:24:39,660 --> 00:24:41,199 and you'll get back the full thing. 509 00:24:41,199 --> 00:24:42,740 This is doing something very similar. 510 00:24:42,740 --> 00:24:46,370 Select star from flights where origin like %a%. 511 00:24:46,370 --> 00:24:49,130 These percents stand in as placeholders effectively where 512 00:24:49,130 --> 00:24:51,200 they can represent any text. 513 00:24:51,200 --> 00:24:54,620 And so select star from flights where origin like %a%, 514 00:24:54,620 --> 00:24:59,341 says select any rows from flights where the origin has an A in it. 515 00:24:59,341 --> 00:24:59,840 Right. 516 00:24:59,840 --> 00:25:03,530 It has some amount of text, possibly none, followed by an A, 517 00:25:03,530 --> 00:25:05,540 followed by some amount of text. 518 00:25:05,540 --> 00:25:09,470 And the result of that is that all of the things that have an A in it 519 00:25:09,470 --> 00:25:13,294 are going to be returned back to the user. 520 00:25:13,294 --> 00:25:16,460 And you can imagine this being helpful if you have a database of information 521 00:25:16,460 --> 00:25:18,085 that you want a user to search through. 522 00:25:18,085 --> 00:25:22,520 And they might search for ultimately a sub-string of the actual text that's 523 00:25:22,520 --> 00:25:24,680 contained inside of the column. 524 00:25:24,680 --> 00:25:28,400 But you can use that information using a like query as opposed to origin 525 00:25:28,400 --> 00:25:31,130 equals something, to say find me all the things that 526 00:25:31,130 --> 00:25:33,770 match that thing that the user typed in, even if it's only 527 00:25:33,770 --> 00:25:35,810 a sub-string of the total thing. 528 00:25:35,810 --> 00:25:38,250 And so that can be useful as well. 529 00:25:38,250 --> 00:25:41,000 And so we looked at some of these functions, sum and count and Min 530 00:25:41,000 --> 00:25:43,139 and max and average that can be used as well. 531 00:25:43,139 --> 00:25:45,430 All of these are just different ways of selecting data. 532 00:25:45,430 --> 00:25:48,200 Of specifying which columns we want to select, 533 00:25:48,200 --> 00:25:50,060 of specifying which rows we want to select, 534 00:25:50,060 --> 00:25:51,782 and restricting what rows come back. 535 00:25:51,782 --> 00:25:53,490 And ultimately that is insertion of data. 536 00:25:53,490 --> 00:25:54,325 Question? 537 00:25:54,325 --> 00:25:57,300 AUDIENCE: So the last query that you executed, 538 00:25:57,300 --> 00:26:01,062 I guess my question is can you execute-- not a query, I mean a function. 539 00:26:01,062 --> 00:26:03,020 Can you execute a function in the where clause? 540 00:26:03,020 --> 00:26:06,892 So the last query that you executed, you did it in two different queries. 541 00:26:06,892 --> 00:26:11,248 You got the min and then you looked at the value and put it in a second query. 542 00:26:11,248 --> 00:26:13,190 Could you have done that in one query? 543 00:26:13,190 --> 00:26:13,760 BRIAN YU: Good question. 544 00:26:13,760 --> 00:26:15,468 So the question is about whether we could 545 00:26:15,468 --> 00:26:18,800 consolidate some of these multiple queries into a single query. 546 00:26:18,800 --> 00:26:22,542 We can, and will take a look at ways to do that in just a moment. 547 00:26:22,542 --> 00:26:24,500 So that was selection of data, and how we would 548 00:26:24,500 --> 00:26:26,660 go about reading data from a database. 549 00:26:26,660 --> 00:26:29,450 But what happens if we need to change data that's already there. 550 00:26:29,450 --> 00:26:29,630 Right. 551 00:26:29,630 --> 00:26:31,421 So that might be the other common use case. 552 00:26:31,421 --> 00:26:34,620 That I have data, I know how to insert data into the database. 553 00:26:34,620 --> 00:26:36,350 I know how to read data from a database. 554 00:26:36,350 --> 00:26:39,330 What if that data needs to be updated or changed? 555 00:26:39,330 --> 00:26:41,660 So that is where the update query is going to come in. 556 00:26:41,660 --> 00:26:44,480 Where that is going to allow us to take data inside of our database 557 00:26:44,480 --> 00:26:47,180 and modify it in some way. 558 00:26:47,180 --> 00:26:49,200 And here is what that query might look like. 559 00:26:49,200 --> 00:26:54,560 So the query will start with the word update, which is just the SQL key word 560 00:26:54,560 --> 00:26:57,140 to say I want to update some data inside the database. 561 00:26:57,140 --> 00:27:00,680 Following that is the name of the table that I want to update, in this case 562 00:27:00,680 --> 00:27:02,450 the flights table. 563 00:27:02,450 --> 00:27:05,690 Then I say, set duration equals 430, I'm setting 564 00:27:05,690 --> 00:27:09,680 the duration of some row to 430, or possibly multiple rows. 565 00:27:09,680 --> 00:27:14,820 And where is telling me which rows I want to update. 566 00:27:14,820 --> 00:27:17,630 So in this case, I'm saying, update the flights table, 567 00:27:17,630 --> 00:27:21,740 set the duration to be 430 minutes, but only do that for columns 568 00:27:21,740 --> 00:27:25,550 where the origin is New York and where the destination is London. 569 00:27:25,550 --> 00:27:28,160 So any other columns that don't meet that where query, 570 00:27:28,160 --> 00:27:30,350 are not going to be set to 430. 571 00:27:30,350 --> 00:27:32,840 If I omitted these two rows altogether, and just 572 00:27:32,840 --> 00:27:35,660 said update flights set duration equals 430, 573 00:27:35,660 --> 00:27:38,690 what that would have the effect of doing is changing every single row 574 00:27:38,690 --> 00:27:41,330 in my table to have a duration of 430. 575 00:27:41,330 --> 00:27:44,310 Which is likely not what you want in this particular case. 576 00:27:44,310 --> 00:27:46,700 So the WHERE clause is very important for specifying 577 00:27:46,700 --> 00:27:49,250 these are the only rows that I actually care about modifying, 578 00:27:49,250 --> 00:27:53,570 that I actually care about editing, as I go about updating that table. 579 00:27:53,570 --> 00:27:55,165 Questions about the update query? 580 00:27:55,165 --> 00:27:55,664 Yeah. 581 00:27:55,664 --> 00:27:56,990 AUDIENCE: Is there a control V? 582 00:27:56,990 --> 00:28:03,110 BRIAN YU: Is there a control V. SQL does not have a Control V. However, 583 00:28:03,110 --> 00:28:07,160 one common paradigm you'll see as databases start to get bigger, 584 00:28:07,160 --> 00:28:10,010 is the idea that you always want to keep database backups around. 585 00:28:10,010 --> 00:28:13,731 You always want to take a database and have some second copy of it, 586 00:28:13,731 --> 00:28:15,980 such that if something goes wrong on the main database 587 00:28:15,980 --> 00:28:17,660 you're using you can always restore it. 588 00:28:17,660 --> 00:28:21,840 And if you begin to use databases in production environments, 589 00:28:21,840 --> 00:28:25,180 Amazon Web Services, for example, offers as one of its services 590 00:28:25,180 --> 00:28:27,110 a relational database just like this. 591 00:28:27,110 --> 00:28:31,307 And it offers built in ways to help you keep backups of your database 592 00:28:31,307 --> 00:28:33,140 such that if you mess something up like this 593 00:28:33,140 --> 00:28:36,290 you can have some way of going back to what you had before. 594 00:28:36,290 --> 00:28:37,580 Good question. 595 00:28:37,580 --> 00:28:40,400 So we've been able to add data into our tables, 596 00:28:40,400 --> 00:28:43,670 we've been able to query for data from our tables, 597 00:28:43,670 --> 00:28:46,117 we've been able to update data in our tables. 598 00:28:46,117 --> 00:28:48,200 The last thing that we might reasonably want to do 599 00:28:48,200 --> 00:28:49,640 is delete data from our tables. 600 00:28:49,640 --> 00:28:51,770 Take a row and just get rid of it entirely. 601 00:28:51,770 --> 00:28:55,520 And the key word in the SQL for that, is quite logically just called delete. 602 00:28:55,520 --> 00:28:57,350 And that might look something like this. 603 00:28:57,350 --> 00:29:01,210 Delete from countries where destination equals Tokyo. 604 00:29:01,210 --> 00:29:04,394 That is going to, sorry, this should be flights. 605 00:29:04,394 --> 00:29:06,810 Sorry, delete from flights where destination equals Tokyo. 606 00:29:06,810 --> 00:29:10,270 That will go through the table deleting all of the flights that 607 00:29:10,270 --> 00:29:13,340 have that particular destination. 608 00:29:13,340 --> 00:29:21,010 So questions on creating data or updating data or deleting data 609 00:29:21,010 --> 00:29:23,672 from our database? 610 00:29:23,672 --> 00:29:28,385 AUDIENCE: Let's say I delete one row and then add 611 00:29:28,385 --> 00:29:33,030 a new row what would happen to ID? 612 00:29:33,030 --> 00:29:34,030 BRIAN YU: Good question. 613 00:29:34,030 --> 00:29:37,474 So the question is, I delete one row, and then I add another row. 614 00:29:37,474 --> 00:29:38,890 What's going to happen to the IDs? 615 00:29:38,890 --> 00:29:42,480 So maybe I have IDs one through six, I delete the thing with ID number three 616 00:29:42,480 --> 00:29:43,912 and then I add a new thing. 617 00:29:43,912 --> 00:29:45,870 Is it going to be number three or number seven? 618 00:29:45,870 --> 00:29:47,640 The answer is that generally it will be number seven. 619 00:29:47,640 --> 00:29:50,556 That the numbers just keep adding up and they don't fill in the blanks 620 00:29:50,556 --> 00:29:51,060 from before. 621 00:29:51,060 --> 00:29:53,351 As we'll see in a moment, there's good reason for that, 622 00:29:53,351 --> 00:29:56,940 and the reason has to do with the fact that because we will oftentimes 623 00:29:56,940 --> 00:30:00,690 want to represent relationships between tables, which we'll soon see, 624 00:30:00,690 --> 00:30:04,860 it can often be very, very helpful to be able to know 625 00:30:04,860 --> 00:30:07,770 that if I had the thing with ID three, nothing else will ever 626 00:30:07,770 --> 00:30:08,967 have that ID again. 627 00:30:08,967 --> 00:30:11,550 Everything else will always have a later ID, even if I go back 628 00:30:11,550 --> 00:30:12,810 and delete that data. 629 00:30:12,810 --> 00:30:15,780 So yes, it will just keep counting even if I delete older things that 630 00:30:15,780 --> 00:30:18,880 existed in the database before. 631 00:30:18,880 --> 00:30:20,560 So other clauses that could be useful. 632 00:30:20,560 --> 00:30:22,590 And here we'll get into answering your question about how you might be 633 00:30:22,590 --> 00:30:24,910 able to group multiple things together. 634 00:30:24,910 --> 00:30:29,100 So let's say I wanted to select all of my flights. 635 00:30:29,100 --> 00:30:30,570 Select star from flights. 636 00:30:30,570 --> 00:30:32,670 And right now I'm getting back six results. 637 00:30:32,670 --> 00:30:35,160 But you might reasonably imagine that I don't 638 00:30:35,160 --> 00:30:38,130 want to get back all of the results, especially if the table is large. 639 00:30:38,130 --> 00:30:39,720 I only want to get back a couple. 640 00:30:39,720 --> 00:30:42,780 And so there is a keyword called limit which 641 00:30:42,780 --> 00:30:45,370 means I only want to get back a certain number of results. 642 00:30:45,370 --> 00:30:50,970 So select star from flights limit two, is going to mean get all the flights, 643 00:30:50,970 --> 00:30:53,520 but only give me back a maximum of two rows. 644 00:30:53,520 --> 00:30:55,950 And so I only get back two rows from there. 645 00:30:55,950 --> 00:30:58,200 But of course this is probably more useful 646 00:30:58,200 --> 00:31:00,250 if this data is ordered in some way. 647 00:31:00,250 --> 00:31:00,750 Right? 648 00:31:00,750 --> 00:31:03,660 I don't really care about getting the flight with ID one or ID two, 649 00:31:03,660 --> 00:31:06,810 but I might care about, for instance, getting the two shortest 650 00:31:06,810 --> 00:31:09,150 flights in my table, for example. 651 00:31:09,150 --> 00:31:13,280 And so I would want some way of sorting this table by a particular column. 652 00:31:13,280 --> 00:31:15,590 And the way to do that is via the order by keyword, 653 00:31:15,590 --> 00:31:18,090 which is one that will prove quite useful if you ever trying 654 00:31:18,090 --> 00:31:23,430 to get a sorted list of your data and only extract out parts of that. 655 00:31:23,430 --> 00:31:28,710 I can say select star from flights, order by duration, 656 00:31:28,710 --> 00:31:30,810 and I'll say ASC for ascending, although strictly 657 00:31:30,810 --> 00:31:32,850 speaking that's optional in this case. 658 00:31:32,850 --> 00:31:36,960 And what I get now, is rather than all of the flights in the order 659 00:31:36,960 --> 00:31:40,180 that I inserted them like they were in before, now I 660 00:31:40,180 --> 00:31:41,430 get them in order of duration. 661 00:31:41,430 --> 00:31:44,790 With the shortest flight at the top Moscow to Paris 245 minutes, 662 00:31:44,790 --> 00:31:48,940 and the longest one, Shanghai to Paris 760 minutes all the way at the bottom. 663 00:31:48,940 --> 00:31:53,040 And so if I only wanted to get the three shortest flights in at my table, 664 00:31:53,040 --> 00:31:54,940 I might say select star from flights, ordered 665 00:31:54,940 --> 00:31:57,960 by duration ascending, limit three. 666 00:31:57,960 --> 00:32:02,900 And that only gets me the three shortest flights in my database. 667 00:32:02,900 --> 00:32:06,030 And just as there is a key word ASC for ascending order. 668 00:32:06,030 --> 00:32:08,730 There's also DESC for descending order, that 669 00:32:08,730 --> 00:32:11,310 gets me all the flights starting with the longest one 670 00:32:11,310 --> 00:32:14,460 and going down until the shortest. 671 00:32:14,460 --> 00:32:15,940 Questions about any of that? 672 00:32:15,940 --> 00:32:16,439 Yeah. 673 00:32:16,439 --> 00:32:20,280 AUDIENCE: Can we use the roll back commit to give us the transaction? 674 00:32:20,280 --> 00:32:21,280 BRIAN YU: Good question. 675 00:32:21,280 --> 00:32:22,680 Can you use roll back and commit. 676 00:32:22,680 --> 00:32:23,690 Yes. 677 00:32:23,690 --> 00:32:27,780 We'll talk about how you would go about committing changes a little later. 678 00:32:27,780 --> 00:32:30,600 But yes, you can rollback changes if you make changes 679 00:32:30,600 --> 00:32:33,225 and realize they aren't in the changes that you wanted to make. 680 00:32:33,225 --> 00:32:35,380 That's similar to the control V type of feature, 681 00:32:35,380 --> 00:32:39,850 but you have to tell the database that you are starting a quote "transaction", 682 00:32:39,850 --> 00:32:41,600 and then start adding to that transaction. 683 00:32:41,600 --> 00:32:43,080 We'll talk more about that in just a moment. 684 00:32:43,080 --> 00:32:45,900 AUDIENCE: Is there any tool that I could use for this [INAUDIBLE] 685 00:32:45,900 --> 00:32:48,300 SQL like a [INAUDIBLE]? 686 00:32:48,300 --> 00:32:49,300 BRIAN YU: Good question. 687 00:32:49,300 --> 00:32:52,030 Is there a tool for interacting with the database? 688 00:32:52,030 --> 00:32:53,200 Yeah, great question. 689 00:32:53,200 --> 00:32:54,510 So there are a couple of different ways that you can 690 00:32:54,510 --> 00:32:56,307 go about interacting with the database. 691 00:32:56,307 --> 00:32:58,390 One is via the command line, like we're doing now, 692 00:32:58,390 --> 00:33:01,050 where I'm just typing in SQL commands directly 693 00:33:01,050 --> 00:33:06,300 into PSQL, which is a program that lets me run commands in the database. 694 00:33:06,300 --> 00:33:08,680 One that we're going to provide for you in this course, 695 00:33:08,680 --> 00:33:11,388 which will prove quite useful to you as you work on your project, 696 00:33:11,388 --> 00:33:14,020 is called Adminer.cs50.net. 697 00:33:14,020 --> 00:33:18,060 So Adminer is a third party database service 698 00:33:18,060 --> 00:33:20,730 that lets you interact with a database online. 699 00:33:20,730 --> 00:33:23,670 And so what you'll do once you get a database for the next project, 700 00:33:23,670 --> 00:33:26,400 is you'll likely want to go to Adminer.cs50.net, 701 00:33:26,400 --> 00:33:29,059 you'll paste in the credentials for accessing your database, 702 00:33:29,059 --> 00:33:31,350 which you'll get once you start working on the project, 703 00:33:31,350 --> 00:33:35,790 and from there, there are tabs from which you can run SQL commands, 704 00:33:35,790 --> 00:33:38,869 from which you can look at all the data in a nice graphical interface. 705 00:33:38,869 --> 00:33:41,910 And so that'll just make it a little more convenient to look at that data 706 00:33:41,910 --> 00:33:42,410 as well. 707 00:33:42,410 --> 00:33:45,457 So you can either do it through Adminer or an interface like Adminer, 708 00:33:45,457 --> 00:33:47,790 there are a whole bunch of services like this out there. 709 00:33:47,790 --> 00:33:49,830 Or you can do it via the command line. 710 00:33:49,830 --> 00:33:52,470 Or as we'll see later today, we'll find ways 711 00:33:52,470 --> 00:33:55,950 of using our Python code to be able to interact with our database such 712 00:33:55,950 --> 00:33:59,280 that we can have a flask application that itself is running SQL commands 713 00:33:59,280 --> 00:34:01,110 and interacting with the database as well. 714 00:34:01,110 --> 00:34:04,170 But we see all that in just a moment. 715 00:34:04,170 --> 00:34:06,120 Good question. 716 00:34:06,120 --> 00:34:08,730 So couple of other things I want to say about how 717 00:34:08,730 --> 00:34:10,420 you can go about selecting data. 718 00:34:10,420 --> 00:34:15,360 We were talking about how to go about performing 719 00:34:15,360 --> 00:34:17,250 what might ordinarily take multiple queries, 720 00:34:17,250 --> 00:34:19,330 but combining them into a single query. 721 00:34:19,330 --> 00:34:25,469 Let's say that I wanted to find out what are the most popular places from which 722 00:34:25,469 --> 00:34:26,260 people are flying. 723 00:34:26,260 --> 00:34:30,010 So I say select star from flights, I see you know, New York, Shanghai, Istanbul. 724 00:34:30,010 --> 00:34:32,750 What are the most common origins of flights? 725 00:34:32,750 --> 00:34:34,699 Well I can run a query like this. 726 00:34:34,699 --> 00:34:37,884 Select origin and count star. 727 00:34:37,884 --> 00:34:40,550 Remember, that origin is just going to select the origin column. 728 00:34:40,550 --> 00:34:43,699 Count star is going to select the count of the number of rows that are 729 00:34:43,699 --> 00:34:46,909 being returned from my flights table. 730 00:34:46,909 --> 00:34:50,100 And group them by origin. 731 00:34:50,100 --> 00:34:52,850 And so what that's going to say is, group by 732 00:34:52,850 --> 00:34:55,070 is going to say, take all of the rows and put them 733 00:34:55,070 --> 00:34:56,577 in groups based on their origin. 734 00:34:56,577 --> 00:34:58,910 So all the flights that are from Moscow go in one group. 735 00:34:58,910 --> 00:35:00,950 All the flights from New York go in another group. 736 00:35:00,950 --> 00:35:03,283 And then count star is going to say, well count them up. 737 00:35:03,283 --> 00:35:06,740 How many of them are there from each of these individual locations? 738 00:35:06,740 --> 00:35:09,782 And so select origin, count start from flights, group by origin, 739 00:35:09,782 --> 00:35:11,990 give me back something that looks a little like this. 740 00:35:11,990 --> 00:35:14,510 I have a list of all of the different origins. 741 00:35:14,510 --> 00:35:17,990 I have five different origins from which flights are coming from. 742 00:35:17,990 --> 00:35:20,390 And then I have this count column which is telling me 743 00:35:20,390 --> 00:35:23,180 how many flights have that particular origin. 744 00:35:23,180 --> 00:35:27,170 So in this case, New York has two and all the rest have one. 745 00:35:27,170 --> 00:35:34,659 And so what if I wanted to select only the origins that have 746 00:35:34,659 --> 00:35:36,200 more than one flight going out of it? 747 00:35:36,200 --> 00:35:39,110 I want to select the popular places from which people are flying, 748 00:35:39,110 --> 00:35:41,120 and my definition of popular, in this case, 749 00:35:41,120 --> 00:35:44,300 is it's going to be a flight that has, or a location that 750 00:35:44,300 --> 00:35:46,070 has multiple flights coming from it. 751 00:35:46,070 --> 00:35:49,370 So I can say select origin, count star from flights 752 00:35:49,370 --> 00:35:51,770 grouped by origin, same as before. 753 00:35:51,770 --> 00:35:57,440 But after a group by, you can optionally specify what's called a HAVING clause. 754 00:35:57,440 --> 00:36:01,340 Which is similar to a WHERE clause, except it follows the group by. 755 00:36:01,340 --> 00:36:05,360 So I group by origin, but I only want to select those origins that have what 756 00:36:05,360 --> 00:36:06,920 particular property? 757 00:36:06,920 --> 00:36:10,880 Well, I want to select the ones that have count star greater than one. 758 00:36:10,880 --> 00:36:12,770 So to break down this query. 759 00:36:12,770 --> 00:36:14,000 It's a little bit long. 760 00:36:14,000 --> 00:36:17,900 I'm selecting the origin, this is going to be a list of locations. 761 00:36:17,900 --> 00:36:22,190 Count star, this is how many flights are from that location from my flights 762 00:36:22,190 --> 00:36:23,120 table. 763 00:36:23,120 --> 00:36:26,420 Grouping them by origin, this is how I get all the New York flights together, 764 00:36:26,420 --> 00:36:29,270 for example, and all the Lima flights together. 765 00:36:29,270 --> 00:36:33,890 And then having count star greater than one is going to mean I only 766 00:36:33,890 --> 00:36:37,010 want to get the origins that have a count of greater than one. 767 00:36:37,010 --> 00:36:39,230 And so the only thing that I get back from that query 768 00:36:39,230 --> 00:36:44,930 is New York, which in this case has two flights coming out of it. 769 00:36:44,930 --> 00:36:48,470 So that would be high might do a more complicated query to get back 770 00:36:48,470 --> 00:36:51,050 particular information that I care about. 771 00:36:51,050 --> 00:36:53,120 Questions about how that worked? 772 00:36:53,120 --> 00:36:56,530 773 00:36:56,530 --> 00:36:57,830 OK. 774 00:36:57,830 --> 00:37:02,790 Next up, we're going to talk a little bit about foreign keys. 775 00:37:02,790 --> 00:37:08,420 So a foreign key is going to be a method that we're going to use in order 776 00:37:08,420 --> 00:37:10,370 to connect multiple tables together. 777 00:37:10,370 --> 00:37:12,910 And so SQL is often called a relational database. 778 00:37:12,910 --> 00:37:15,410 And it's called a relational database because one thing that 779 00:37:15,410 --> 00:37:20,090 makes it quite powerful is the ability to take multiple different tables 780 00:37:20,090 --> 00:37:22,130 and relate them together in some way. 781 00:37:22,130 --> 00:37:24,950 So you imagine that I have a flight table, 782 00:37:24,950 --> 00:37:27,500 for example, that's got an ID, origin, destination, 783 00:37:27,500 --> 00:37:29,730 duration just like we had before. 784 00:37:29,730 --> 00:37:32,397 But you might imagine that as my airline application gets 785 00:37:32,397 --> 00:37:34,730 a little more complicated and I add more features to it, 786 00:37:34,730 --> 00:37:36,855 I might start to need more out of this application. 787 00:37:36,855 --> 00:37:39,080 I might, for example, want to keep track not only 788 00:37:39,080 --> 00:37:40,970 of the name of the place I'm flying from, 789 00:37:40,970 --> 00:37:46,382 but I might care about the airport code of that particular location. 790 00:37:46,382 --> 00:37:48,590 So I've just picked out a representative airport code 791 00:37:48,590 --> 00:37:50,140 from each of these individual cities. 792 00:37:50,140 --> 00:37:53,240 And so New York I've picked JFK, and each of these different origins 793 00:37:53,240 --> 00:37:56,990 has a origin code, and each destination has a destination code 794 00:37:56,990 --> 00:38:01,820 to tell me which specific code represents the airline that I'm 795 00:38:01,820 --> 00:38:03,260 flying out of or flying into. 796 00:38:03,260 --> 00:38:05,150 But now as it starts to get more complicated, 797 00:38:05,150 --> 00:38:08,660 you can start to see some of the potential design improvements 798 00:38:08,660 --> 00:38:10,190 that we could make to this table. 799 00:38:10,190 --> 00:38:14,030 In particular, notice that I've repeated a whole bunch of data. 800 00:38:14,030 --> 00:38:18,140 For instance, I specified here, New York, JFK, and here also 801 00:38:18,140 --> 00:38:19,550 New York and JFK. 802 00:38:19,550 --> 00:38:22,760 And therefore there's a lot of redundancy here. 803 00:38:22,760 --> 00:38:26,990 And it might be nice if I could treat this information as separate. 804 00:38:26,990 --> 00:38:31,670 If I could say, I want to keep track of locations as its own entity. 805 00:38:31,670 --> 00:38:35,520 And then I want to keep track of flights that are somehow related to locations. 806 00:38:35,520 --> 00:38:38,900 So I keep track of a table of locations, or places, 807 00:38:38,900 --> 00:38:42,410 and then I keep track of a table of flights where my flights somehow 808 00:38:42,410 --> 00:38:46,880 know about that table that contains New York and Shanghai, et cetera. 809 00:38:46,880 --> 00:38:49,280 So what I might do is define a table called locations, 810 00:38:49,280 --> 00:38:50,720 that looks something like this. 811 00:38:50,720 --> 00:38:53,944 Where each one has an ID, each one has a code, and each one has a name. 812 00:38:53,944 --> 00:38:55,860 This is just like the stuff we've seen before, 813 00:38:55,860 --> 00:38:58,220 except now I have a table exclusively just 814 00:38:58,220 --> 00:39:01,430 for representing individual locations. 815 00:39:01,430 --> 00:39:04,260 And then what I would do in my flights table is, 816 00:39:04,260 --> 00:39:07,130 instead of having an origin column and a destination column, 817 00:39:07,130 --> 00:39:10,805 I'll have an origin ID column, and a destination ID column. 818 00:39:10,805 --> 00:39:12,930 These columns technically could be called anything. 819 00:39:12,930 --> 00:39:16,060 But by convention, it's something underscore ID. 820 00:39:16,060 --> 00:39:20,480 To mean this is the ID of the origin of this flight. 821 00:39:20,480 --> 00:39:24,420 And which flight or what location has ID number one? 822 00:39:24,420 --> 00:39:26,640 I could go back to this locations table. 823 00:39:26,640 --> 00:39:28,550 Here's the thing of ID one, OK this flight 824 00:39:28,550 --> 00:39:31,050 must be flying out of JFK in New York. 825 00:39:31,050 --> 00:39:34,080 I go back to my flights table, where is it flying into? 826 00:39:34,080 --> 00:39:38,010 Its destination is four, destination ID four. 827 00:39:38,010 --> 00:39:41,040 So I go back to the locations table, here is the thing with ID four, 828 00:39:41,040 --> 00:39:42,780 that's London. 829 00:39:42,780 --> 00:39:47,140 So you can begin to use these numbers just to represent locations. 830 00:39:47,140 --> 00:39:49,140 That way I don't need to have New York, JFK, New 831 00:39:49,140 --> 00:39:52,050 York, JFK repeated over and over again inside of my table, 832 00:39:52,050 --> 00:39:54,626 I just need to have an individual number to represent here 833 00:39:54,626 --> 00:39:56,250 is the location that I'm flying out of. 834 00:39:56,250 --> 00:39:59,160 Now this might seem harder to read, in particular for us humans. 835 00:39:59,160 --> 00:40:02,190 Looking at this, this is perhaps even less clear 836 00:40:02,190 --> 00:40:04,232 than the table was before if I want to figure out 837 00:40:04,232 --> 00:40:06,898 like where is this flight flying from and where is it flying to. 838 00:40:06,898 --> 00:40:09,960 Now I need to take this number three and look it up in a different table. 839 00:40:09,960 --> 00:40:13,110 But SQL databases are actually quite good at dealing with this. 840 00:40:13,110 --> 00:40:15,150 And this is where they really excel and thrive, 841 00:40:15,150 --> 00:40:17,730 is having these relationships between tables 842 00:40:17,730 --> 00:40:21,120 and being able to connect them in a way that's meaningful. 843 00:40:21,120 --> 00:40:25,494 Questions about this general idea of what we're trying to go for here? 844 00:40:25,494 --> 00:40:27,910 And you can imagine extensions that we could make to this. 845 00:40:27,910 --> 00:40:30,701 If we wanted to keep track of passengers on individual flights? 846 00:40:30,701 --> 00:40:32,950 Well then I might have something that looks like this. 847 00:40:32,950 --> 00:40:35,350 Right, each passenger also has an ID. 848 00:40:35,350 --> 00:40:37,060 Each passenger has a name. 849 00:40:37,060 --> 00:40:41,440 And each passenger has a flight ID, namely the ID of the flight 850 00:40:41,440 --> 00:40:43,030 that they are a passenger on. 851 00:40:43,030 --> 00:40:45,700 And so I've got two people registered for flight number one, 852 00:40:45,700 --> 00:40:49,269 two people registered flight number two so on and so forth. 853 00:40:49,269 --> 00:40:51,060 Questions about what we're going for, yeah? 854 00:40:51,060 --> 00:40:55,919 AUDIENCE: So you do this relationship to like save space for example? 855 00:40:55,919 --> 00:40:56,960 BRIAN YU: It can be used. 856 00:40:56,960 --> 00:40:59,680 So the question is why would we be using this relationship? 857 00:40:59,680 --> 00:41:03,070 Certainly, we can be using it to save space, because now I don't need to, 858 00:41:03,070 --> 00:41:04,872 if I have long pieces of text I don't need 859 00:41:04,872 --> 00:41:07,330 to have them appearing multiple times throughout the table. 860 00:41:07,330 --> 00:41:10,270 I can just have it appear in one row, and then reference it 861 00:41:10,270 --> 00:41:12,040 by a number in some other table. 862 00:41:12,040 --> 00:41:14,810 And the other advantage is that it helps to keep things organized. 863 00:41:14,810 --> 00:41:16,390 In particular with passengers. 864 00:41:16,390 --> 00:41:20,320 You can imagine, if we only had this flights table as we had it before, 865 00:41:20,320 --> 00:41:23,420 where all of the flights had origins and destinations, 866 00:41:23,420 --> 00:41:27,970 it would be pretty tough now to in this same table, keep track of passengers. 867 00:41:27,970 --> 00:41:31,371 Right, there's no easy way to add like a column for a passenger, 868 00:41:31,371 --> 00:41:33,620 because it might be multiple passengers, for instance. 869 00:41:33,620 --> 00:41:36,120 And so it makes a lot more intuitive sense to say, you know, 870 00:41:36,120 --> 00:41:39,520 flights are one type of entity, we'll keep track of that in one table. 871 00:41:39,520 --> 00:41:41,740 Passengers are a whole different entity, let's keep 872 00:41:41,740 --> 00:41:43,226 track of that in a different table. 873 00:41:43,226 --> 00:41:45,100 But there's relationships between the tables. 874 00:41:45,100 --> 00:41:48,520 Each passenger is associated with one flight. 875 00:41:48,520 --> 00:41:51,580 And so that's what the flight ID on the passengers table 876 00:41:51,580 --> 00:41:53,620 is ultimately going to be used for. 877 00:41:53,620 --> 00:41:54,272 Good question. 878 00:41:54,272 --> 00:41:56,230 So let's take a look at how that might actually 879 00:41:56,230 --> 00:42:02,110 work in terms of how we would go about creating foreign keys. 880 00:42:02,110 --> 00:42:04,510 And a foreign key it's just a fancy way of saying we're 881 00:42:04,510 --> 00:42:07,700 referencing the key from a different table. 882 00:42:07,700 --> 00:42:13,390 So my flight ID column is referencing the primary key of that flights table. 883 00:42:13,390 --> 00:42:17,710 So what I might do is create a table, and I'll call it passengers. 884 00:42:17,710 --> 00:42:20,470 And my passengers table will have an ID, which 885 00:42:20,470 --> 00:42:23,510 will be a serial primary key, just like the other table. 886 00:42:23,510 --> 00:42:26,290 It will have a name for who the passenger is, 887 00:42:26,290 --> 00:42:29,920 and that should be text Varchar, not null in this case. 888 00:42:29,920 --> 00:42:32,860 And then finally, a flight ID, which is going 889 00:42:32,860 --> 00:42:36,340 to be an integer that references the flights table. 890 00:42:36,340 --> 00:42:39,040 In particular, flight ID is going to be an integer that 891 00:42:39,040 --> 00:42:43,280 is going to reference the ID of whatever is in the flights table. 892 00:42:43,280 --> 00:42:46,580 So I'm linking these two tables together effectively. 893 00:42:46,580 --> 00:42:49,940 And so I've created that table as well. 894 00:42:49,940 --> 00:42:54,520 So I'm going to go ahead and I have a couple of these insert lines. 895 00:42:54,520 --> 00:42:57,910 These insert lines are just going to add a couple of passengers into my table. 896 00:42:57,910 --> 00:43:01,849 So notice I'm inserting into passengers, inserting a name and a flight ID, 897 00:43:01,849 --> 00:43:04,390 and their values are just their name and the ID of the flight 898 00:43:04,390 --> 00:43:07,010 that I'm adding them to. 899 00:43:07,010 --> 00:43:08,560 So I'm going to insert all those. 900 00:43:08,560 --> 00:43:11,000 And now if I select star from passengers, 901 00:43:11,000 --> 00:43:14,260 you'll notice that I have seven rows, each one of them 902 00:43:14,260 --> 00:43:18,710 is an individual passenger, and each one is an individual flight ID. 903 00:43:18,710 --> 00:43:23,990 And so I can see, OK, let's say I wanted to know what flight is Alice on? 904 00:43:23,990 --> 00:43:28,590 So I could say select star from passengers where name equals Alice. 905 00:43:28,590 --> 00:43:30,684 Then I get, OK, Alice is on flight ID number one. 906 00:43:30,684 --> 00:43:33,100 And so now I want to say, OK, what's flight ID number one. 907 00:43:33,100 --> 00:43:37,660 Select star from flights where ID equals one.OK, 908 00:43:37,660 --> 00:43:40,280 Alice is on the flight going from New York to London. 909 00:43:40,280 --> 00:43:42,200 And that would be my answer. 910 00:43:42,200 --> 00:43:44,470 But in order to do this, I had to do two queries. 911 00:43:44,470 --> 00:43:46,690 Right, I first queried the passengers table, 912 00:43:46,690 --> 00:43:49,060 to say what flight number is Alice on? 913 00:43:49,060 --> 00:43:51,430 And then I queried the flights table to say, all right, 914 00:43:51,430 --> 00:43:52,827 which flight has ID number one? 915 00:43:52,827 --> 00:43:55,160 And it would be nice if I didn't need to do two queries. 916 00:43:55,160 --> 00:43:59,350 If I could join these two queries into one, and in fact in SQL you can, 917 00:43:59,350 --> 00:44:01,870 using a special type of syntax called a join. 918 00:44:01,870 --> 00:44:05,950 And joins are very useful once you start dealing with multiple tables. 919 00:44:05,950 --> 00:44:07,960 Because when a join allows you to do is take 920 00:44:07,960 --> 00:44:12,190 two different tables that are related in some way, and group 921 00:44:12,190 --> 00:44:15,830 them together in one when you try to select them. 922 00:44:15,830 --> 00:44:18,080 So what might that look like? 923 00:44:18,080 --> 00:44:24,760 Well what I'm going to do, is I want to select now from my passengers table 924 00:44:24,760 --> 00:44:27,250 and my flights table simultaneously. 925 00:44:27,250 --> 00:44:32,020 And in particular, I want to select from those tables under certain conditions. 926 00:44:32,020 --> 00:44:36,070 I want to select maybe the origin of the flight 927 00:44:36,070 --> 00:44:39,730 and the destination of the flight and the name of the person that's flying. 928 00:44:39,730 --> 00:44:44,230 So origin and destination are both columns of my flights table. 929 00:44:44,230 --> 00:44:46,270 Name is a column of my passengers table, these 930 00:44:46,270 --> 00:44:49,430 are two different tables that I'm selecting information from now. 931 00:44:49,430 --> 00:44:52,570 So I'm going to say, OK, let's select this from my flights table. 932 00:44:52,570 --> 00:44:57,935 But I want to join this with my passengers table. 933 00:44:57,935 --> 00:45:00,790 So I'm selecting the origin, destination, and name, 934 00:45:00,790 --> 00:45:03,370 those are the columns I care about from the flights table. 935 00:45:03,370 --> 00:45:05,650 Joining that with the passengers table. 936 00:45:05,650 --> 00:45:10,810 But the last step is telling my query how these two tables are related. 937 00:45:10,810 --> 00:45:15,310 I need to tell my table, in particular, my query in particular, 938 00:45:15,310 --> 00:45:16,630 what do I want to join them on? 939 00:45:16,630 --> 00:45:18,780 What is the relationship between these two tables? 940 00:45:18,780 --> 00:45:22,969 And in particular, the relationship here, is that passengers-- 941 00:45:22,969 --> 00:45:26,010 and I'm going onto a new line, but this could theoretically be one line-- 942 00:45:26,010 --> 00:45:31,170 passengers dot flight ID equals the flights dot ID. 943 00:45:31,170 --> 00:45:33,630 So I'm joining the flights and the passengers table, 944 00:45:33,630 --> 00:45:35,400 and saying join these two tables together, 945 00:45:35,400 --> 00:45:36,780 and here is how they're related. 946 00:45:36,780 --> 00:45:40,320 If you take the passengers table and get the flight ID column, 947 00:45:40,320 --> 00:45:44,470 that should match the ID column of the flights table. 948 00:45:44,470 --> 00:45:46,830 And so I press Return, and this is what I get back. 949 00:45:46,830 --> 00:45:49,980 I get back Alice on a flight from New York 950 00:45:49,980 --> 00:45:53,230 to London, Bob on the flight from New York to London as well. 951 00:45:53,230 --> 00:45:56,640 And so I get back all of these rows from two different tables that 952 00:45:56,640 --> 00:46:02,010 have now been matched up, that have been grouped together into a single table. 953 00:46:02,010 --> 00:46:05,740 And so if I wanted to find out what flight is Alice on, for example, 954 00:46:05,740 --> 00:46:08,550 I might reasonably do the exact same thing as before. 955 00:46:08,550 --> 00:46:15,270 Select origin, destination, name from flights, join passengers on passengers 956 00:46:15,270 --> 00:46:22,500 dot flight ID equals flights dot ID, where name equals Alice. 957 00:46:22,500 --> 00:46:26,520 And that will tell me that Alice is on the flight from New York to London. 958 00:46:26,520 --> 00:46:30,347 Questions about that joins syntax and how that worked? 959 00:46:30,347 --> 00:46:33,180 So it was how we were taking two tables and combining them together. 960 00:46:33,180 --> 00:46:33,673 Yeah. 961 00:46:33,673 --> 00:46:34,166 Yeah. 962 00:46:34,166 --> 00:46:36,249 AUDIENCE: I actually have back from the reference, 963 00:46:36,249 --> 00:46:40,075 when you say reference when you're defining the tables the passengers 964 00:46:40,075 --> 00:46:40,575 table. 965 00:46:40,575 --> 00:46:44,026 When you insert it, if you put some ID that was illegal. 966 00:46:44,026 --> 00:46:45,620 Would the row not go in? 967 00:46:45,620 --> 00:46:46,620 BRIAN YU: Good question. 968 00:46:46,620 --> 00:46:51,510 So what is this references key word doing? 969 00:46:51,510 --> 00:46:52,760 What is it specifically doing. 970 00:46:52,760 --> 00:46:54,884 And yeah, it helps, what it ultimately helps to do, 971 00:46:54,884 --> 00:46:58,110 is it helps to enforce constraints to make sure that I can't do something 972 00:46:58,110 --> 00:46:59,410 wrong, for instance. 973 00:46:59,410 --> 00:47:03,600 So if I try to-- so Alice we just saw was on flight number one, right? 974 00:47:03,600 --> 00:47:07,335 So if I now tried to delete from flights where ID equals one, 975 00:47:07,335 --> 00:47:09,330 what I'm going to get back is an error. 976 00:47:09,330 --> 00:47:13,320 And the error that I get back is that key ID one is still referenced 977 00:47:13,320 --> 00:47:15,450 from table passengers, for instance. 978 00:47:15,450 --> 00:47:19,890 So something in the passengers table is referencing flight ID number one, 979 00:47:19,890 --> 00:47:22,380 and therefore I can't delete the flight that 980 00:47:22,380 --> 00:47:25,110 has ID number one because that would result 981 00:47:25,110 --> 00:47:27,130 in a violation of that constraint. 982 00:47:27,130 --> 00:47:29,940 So the reference of this key word is used 983 00:47:29,940 --> 00:47:31,690 to help us enforce that type of constraint 984 00:47:31,690 --> 00:47:33,190 so that we can't do something wrong. 985 00:47:33,190 --> 00:47:36,870 If I try to delete a flight while there are still passengers registered for it, 986 00:47:36,870 --> 00:47:40,060 the database will physically stop me from being able to do that. 987 00:47:40,060 --> 00:47:42,350 Good question. 988 00:47:42,350 --> 00:47:43,218 Yeah. 989 00:47:43,218 --> 00:47:45,708 AUDIENCE: Is the connection between flight ID and ID 990 00:47:45,708 --> 00:47:49,200 sort of implicit in MySQL? 991 00:47:49,200 --> 00:47:52,920 BRIAN YU: So, OK, the question is, is the relationship between flight ID 992 00:47:52,920 --> 00:47:54,550 and ID implicit? 993 00:47:54,550 --> 00:47:57,510 So generally speaking, when we use the references keyword, 994 00:47:57,510 --> 00:48:01,030 we are referencing the primary key of some other table. 995 00:48:01,030 --> 00:48:03,789 So because in the flights table I said that the ID was 996 00:48:03,789 --> 00:48:07,080 the primary key, the main thing that I'm going to be referencing that table by, 997 00:48:07,080 --> 00:48:09,810 it assumes that yes, I mean the ID column of the flights table 998 00:48:09,810 --> 00:48:11,820 is the thing to reference. 999 00:48:11,820 --> 00:48:14,940 There are ways to specify a different column 1000 00:48:14,940 --> 00:48:16,410 that you want to reference instead. 1001 00:48:16,410 --> 00:48:18,600 But generally speaking, the references keyword 1002 00:48:18,600 --> 00:48:22,530 is almost always used with referencing an ID of a different table. 1003 00:48:22,530 --> 00:48:25,360 Good question. 1004 00:48:25,360 --> 00:48:28,180 So what we saw just now was an example of a join. 1005 00:48:28,180 --> 00:48:31,410 And the way that that join worked was it took two different tables 1006 00:48:31,410 --> 00:48:34,410 and it, based on a particular condition, matching up 1007 00:48:34,410 --> 00:48:38,190 the flight ID of the passenger with the ID of the flight, 1008 00:48:38,190 --> 00:48:41,310 was able to find all the matching rows and give me them back. 1009 00:48:41,310 --> 00:48:47,100 But what you noticed in that query, was that if I go back to it, 1010 00:48:47,100 --> 00:48:49,347 what I get back or only the matches. 1011 00:48:49,347 --> 00:48:51,930 In other words, I get back only the origin and the destination 1012 00:48:51,930 --> 00:48:56,100 and the name where there was a match between the name of the passenger 1013 00:48:56,100 --> 00:48:57,210 and the flight itself. 1014 00:48:57,210 --> 00:49:00,420 But there might be flights that have no passengers, for example. 1015 00:49:00,420 --> 00:49:02,700 And so there are different types of joins. 1016 00:49:02,700 --> 00:49:05,370 The join we just did was called an inner join. 1017 00:49:05,370 --> 00:49:07,860 And in SQL, when you just specify the word 1018 00:49:07,860 --> 00:49:10,020 join without specifying what type of join, 1019 00:49:10,020 --> 00:49:11,790 it assumes that you mean an inner join. 1020 00:49:11,790 --> 00:49:14,730 In other words, only get the things that match. 1021 00:49:14,730 --> 00:49:19,260 You also have the option of doing what's called a left join. 1022 00:49:19,260 --> 00:49:21,960 So what left join is going to do, is it's 1023 00:49:21,960 --> 00:49:23,790 going to take the table on the left-- 1024 00:49:23,790 --> 00:49:25,680 in this case the flights table-- 1025 00:49:25,680 --> 00:49:30,720 and it's going to make sure that all of the rows in the flights table 1026 00:49:30,720 --> 00:49:34,330 are included in the final result, even if they don't have a match. 1027 00:49:34,330 --> 00:49:38,130 So if I left to join flights and passengers together, what I get back 1028 00:49:38,130 --> 00:49:39,120 is this. 1029 00:49:39,120 --> 00:49:41,380 Which is the first seven rows are the same as before, 1030 00:49:41,380 --> 00:49:43,880 it's the result of that direct matching, but I also get back 1031 00:49:43,880 --> 00:49:45,421 the flights that didn't have a match. 1032 00:49:45,421 --> 00:49:48,264 In particular, Moscow to Paris doesn't have any passengers on it, 1033 00:49:48,264 --> 00:49:50,430 Istanbul to Tokyo doesn't have any passengers on it, 1034 00:49:50,430 --> 00:49:52,860 but I still get those rows back as well. 1035 00:49:52,860 --> 00:49:57,600 And likewise there's an equivalent right join that selects all of the things 1036 00:49:57,600 --> 00:50:01,050 from the right table, even if there are no matches on the left table. 1037 00:50:01,050 --> 00:50:04,012 Where the left table is just the one that comes first. 1038 00:50:04,012 --> 00:50:05,970 So those are different types of joins and those 1039 00:50:05,970 --> 00:50:11,710 can be used in order to help to combine data coming from different places. 1040 00:50:11,710 --> 00:50:16,350 Couple other things we'll say about SQL, and then I'll take a brief break. 1041 00:50:16,350 --> 00:50:17,140 So joins. 1042 00:50:17,140 --> 00:50:18,979 We talked about different types of joins. 1043 00:50:18,979 --> 00:50:20,770 Another thing that's frequent in databases, 1044 00:50:20,770 --> 00:50:23,490 especially as databases get large, are indexes. 1045 00:50:23,490 --> 00:50:28,060 And when an index is, is you can think of an index like an index in a book, 1046 00:50:28,060 --> 00:50:28,590 for example. 1047 00:50:28,590 --> 00:50:31,380 It's an easy way that if I'm looking for something 1048 00:50:31,380 --> 00:50:33,640 I can quickly reference something else. 1049 00:50:33,640 --> 00:50:38,850 And so what you can do in SQL is add an index to a particular column. 1050 00:50:38,850 --> 00:50:41,040 To say that if I'm going to be referencing flights 1051 00:50:41,040 --> 00:50:44,550 by their origin a lot, it might be helpful 1052 00:50:44,550 --> 00:50:48,510 if I had an index of the origin of all of my flights. 1053 00:50:48,510 --> 00:50:51,000 Some way that I could very quickly say, you know, 1054 00:50:51,000 --> 00:50:53,250 if I want to look up all the flights from New York, 1055 00:50:53,250 --> 00:50:56,490 I just go to New York in my index and look up all the flights from New York. 1056 00:50:56,490 --> 00:50:57,990 And make that faster. 1057 00:50:57,990 --> 00:51:00,900 So I can create an index on an individual column, 1058 00:51:00,900 --> 00:51:03,690 and the syntax for that is just create index, 1059 00:51:03,690 --> 00:51:06,970 followed by the name of the index-- which can be an arbitrary name-- 1060 00:51:06,970 --> 00:51:10,260 and then what table and what column you want to create an index on. 1061 00:51:10,260 --> 00:51:12,080 And what that will do is it will speed up 1062 00:51:12,080 --> 00:51:18,340 any time you try to select from that table, based on that particular column. 1063 00:51:18,340 --> 00:51:20,520 So if we have this way of creating an index, 1064 00:51:20,520 --> 00:51:23,610 that just makes it easy to look up a particular value from a column 1065 00:51:23,610 --> 00:51:25,040 and go to it immediately-- 1066 00:51:25,040 --> 00:51:28,140 or go to it much more quickly than you could otherwise-- why wouldn't we 1067 00:51:28,140 --> 00:51:30,522 just create an index on every column? 1068 00:51:30,522 --> 00:51:32,730 I mean, can anyone think of reasons why you might not 1069 00:51:32,730 --> 00:51:34,510 do that just every column? 1070 00:51:34,510 --> 00:51:35,186 Yeah. 1071 00:51:35,186 --> 00:51:39,470 AUDIENCE: You said creating a new table under the hood, 1072 00:51:39,470 --> 00:51:42,326 with the [INAUDIBLE] being the column that you mentioned, would it 1073 00:51:42,326 --> 00:51:43,487 take a lot space maybe? 1074 00:51:43,487 --> 00:51:44,320 BRIAN YU: OK, great. 1075 00:51:44,320 --> 00:51:45,700 So space is one potential concern. 1076 00:51:45,700 --> 00:51:47,940 That creating the index does, in fact, take up space. 1077 00:51:47,940 --> 00:51:49,800 And the other potential concern is time. 1078 00:51:49,800 --> 00:51:53,220 That while creating an index on the origin column, 1079 00:51:53,220 --> 00:51:55,770 for example, of my flights table, might make it faster 1080 00:51:55,770 --> 00:51:57,400 to look up a flight by its origin. 1081 00:51:57,400 --> 00:51:59,774 If I want to look up New York, all flights from New York, 1082 00:51:59,774 --> 00:52:00,870 I can easily look that up. 1083 00:52:00,870 --> 00:52:03,330 What it will do is actually slow down things 1084 00:52:03,330 --> 00:52:05,160 like updating data or inserting data. 1085 00:52:05,160 --> 00:52:07,420 Because If I now insert a flight from New York, 1086 00:52:07,420 --> 00:52:09,450 for instance, it's not as simple as before. 1087 00:52:09,450 --> 00:52:11,730 I can't just insert the flight into the table. 1088 00:52:11,730 --> 00:52:14,790 I need to insert the flight into the table, and then go into the index 1089 00:52:14,790 --> 00:52:17,250 and update the index so that if I look for New York again, 1090 00:52:17,250 --> 00:52:18,820 I'll be able to find it more easily. 1091 00:52:18,820 --> 00:52:21,370 So it's a trade there and just one to be mindful of. 1092 00:52:21,370 --> 00:52:25,230 That as data gets big, it can often be helpful to add an index to your table 1093 00:52:25,230 --> 00:52:27,490 in order to make it faster to look up that data. 1094 00:52:27,490 --> 00:52:30,420 But the trade off, the cost there, is A space, but B, 1095 00:52:30,420 --> 00:52:33,210 some time in terms of the time it takes to update that data 1096 00:52:33,210 --> 00:52:35,862 or insert that data as well. 1097 00:52:35,862 --> 00:52:38,570 One final thing we'll take a look at before we take a brief break 1098 00:52:38,570 --> 00:52:39,600 are nested queries. 1099 00:52:39,600 --> 00:52:43,560 When we try to combine multiple queries together into one, 1100 00:52:43,560 --> 00:52:45,370 beyond just what we've seen before. 1101 00:52:45,370 --> 00:52:49,260 So we saw before we had a flights table with each column, ID, origin, 1102 00:52:49,260 --> 00:52:50,610 destination, and duration. 1103 00:52:50,610 --> 00:52:54,060 We had a passengers column where each passenger has a name and a flight 1104 00:52:54,060 --> 00:52:56,800 that they're associated with. 1105 00:52:56,800 --> 00:52:59,160 So if we look at a query like this, this is 1106 00:52:59,160 --> 00:53:02,460 a relatively on the more sophisticated side of queries that we've seen so far. 1107 00:53:02,460 --> 00:53:05,220 Select flight ID from passengers. 1108 00:53:05,220 --> 00:53:09,690 Group by flight ID having count star greater than one. 1109 00:53:09,690 --> 00:53:12,041 Any guesses as to what that query is going to do? 1110 00:53:12,041 --> 00:53:14,790 It's all syntax we've seen before, but take a moment to look at it 1111 00:53:14,790 --> 00:53:15,300 think about it. 1112 00:53:15,300 --> 00:53:17,216 What is this query going to do when I actually 1113 00:53:17,216 --> 00:53:21,070 try to run this on the passengers table which is right here? 1114 00:53:21,070 --> 00:53:23,340 So each passenger has an ID, a name, and a flight ID. 1115 00:53:23,340 --> 00:53:26,112 1116 00:53:26,112 --> 00:53:27,570 Thoughts on what will happen, yeah? 1117 00:53:27,570 --> 00:53:32,160 AUDIENCE: [INAUDIBLE] select passengers with more than one flight. 1118 00:53:32,160 --> 00:53:33,160 BRIAN YU: Good thoughts. 1119 00:53:33,160 --> 00:53:35,260 Selecting passengers with more than one flight 1120 00:53:35,260 --> 00:53:37,885 it's very close, although not quite. 1121 00:53:37,885 --> 00:53:42,560 1122 00:53:42,560 --> 00:53:43,070 Other ideas? 1123 00:53:43,070 --> 00:53:48,020 AUDIENCE: When the-- they want flights that have multiple numbers. 1124 00:53:48,020 --> 00:53:48,770 BRIAN YU: Exactly. 1125 00:53:48,770 --> 00:53:51,126 So what we're doing, since we're grouping by flight ID, 1126 00:53:51,126 --> 00:53:53,750 we're saying take all the passengers that have flight ID number 1127 00:53:53,750 --> 00:53:54,800 one, put them here. 1128 00:53:54,800 --> 00:53:58,370 Take all the flight passengers that have flight ID two, put them here. 1129 00:53:58,370 --> 00:54:01,130 Group them based on their flight, and then count them up. 1130 00:54:01,130 --> 00:54:03,140 And so in particular, here, we're going to only 1131 00:54:03,140 --> 00:54:09,717 be selecting flight IDs when that flight has more than one passenger on it. 1132 00:54:09,717 --> 00:54:12,800 And so the result that I'll get is a table that looks something like that, 1133 00:54:12,800 --> 00:54:16,490 because flight IDs one, two and six are the only flights that 1134 00:54:16,490 --> 00:54:19,640 have more than one passenger on it. 1135 00:54:19,640 --> 00:54:22,340 But what if then, so now I've created this syntax, 1136 00:54:22,340 --> 00:54:24,980 select flight ID from passengers grouped by flight ID 1137 00:54:24,980 --> 00:54:26,690 having counts star greater than one. 1138 00:54:26,690 --> 00:54:30,530 Which is this long syntax that means get me all of the flight IDs 1139 00:54:30,530 --> 00:54:33,410 for flights that have multiple passengers. 1140 00:54:33,410 --> 00:54:37,970 What if now I wanted to select all of the flights themselves 1141 00:54:37,970 --> 00:54:40,040 that have multiple passengers? 1142 00:54:40,040 --> 00:54:42,080 I want to select from my flights table, but I 1143 00:54:42,080 --> 00:54:45,320 want to select from the flights table only on the flights that 1144 00:54:45,320 --> 00:54:46,700 have more than one passenger. 1145 00:54:46,700 --> 00:54:49,130 Well, I can begin to nest these queries within each other. 1146 00:54:49,130 --> 00:54:52,010 And this is where SQL starts to get a little more complicated. 1147 00:54:52,010 --> 00:54:56,000 I can say, well, if this is the query that gets me all of the flight IDs 1148 00:54:56,000 --> 00:54:59,940 that have multiple passengers, well let's try this query instead. 1149 00:54:59,940 --> 00:55:02,300 These two lines are the same but wrapped in parentheses. 1150 00:55:02,300 --> 00:55:07,160 But now I'm going to select star from flights where the ID of the flight 1151 00:55:07,160 --> 00:55:10,110 is in the result of this query. 1152 00:55:10,110 --> 00:55:13,220 So this query, these bottom two lines in parentheses, those 1153 00:55:13,220 --> 00:55:15,620 were the lines that were saying get me all the flight 1154 00:55:15,620 --> 00:55:18,140 IDs that have multiple passengers. 1155 00:55:18,140 --> 00:55:20,630 And this top line up here is saying select everything 1156 00:55:20,630 --> 00:55:22,940 from flights, all the columns from flights, 1157 00:55:22,940 --> 00:55:27,290 as long as the ID of the flight is in the result of this query. 1158 00:55:27,290 --> 00:55:29,030 Where this is the result of the query. 1159 00:55:29,030 --> 00:55:31,988 So what's going to happen is if I select flight star from flights where 1160 00:55:31,988 --> 00:55:35,030 ID is in the result of this query, these are the rows 1161 00:55:35,030 --> 00:55:39,020 that get selected, one two and six, because those are the flights that 1162 00:55:39,020 --> 00:55:42,290 have multiple passengers on them. 1163 00:55:42,290 --> 00:55:44,660 So if this starts to get confusing, I usually 1164 00:55:44,660 --> 00:55:47,340 recommend just breaking it down into one query at a time. 1165 00:55:47,340 --> 00:55:50,990 First take a look at these two lines and make sure you get a sense for why is it 1166 00:55:50,990 --> 00:55:54,680 that these two lines are giving me this table, the one that has just the flight 1167 00:55:54,680 --> 00:55:57,530 IDs with multiple passengers on them. 1168 00:55:57,530 --> 00:56:00,680 And once you have that in mind, if the result of this is just one, 1169 00:56:00,680 --> 00:56:03,290 two, six, then this big query is really just saying, 1170 00:56:03,290 --> 00:56:06,680 select star from flights where ID is in one, two, and six. 1171 00:56:06,680 --> 00:56:09,890 Where the ID is one of those three, where we got those three based 1172 00:56:09,890 --> 00:56:11,230 on running the second query. 1173 00:56:11,230 --> 00:56:13,610 And so SQL allows you to nest these queries together 1174 00:56:13,610 --> 00:56:15,350 to begin to do more complicated things. 1175 00:56:15,350 --> 00:56:17,780 To combine multiple queries into one, to get really 1176 00:56:17,780 --> 00:56:20,842 just the data that you care about by relating multiple tables together. 1177 00:56:20,842 --> 00:56:24,050 We'll take a quick break, and when we come back, we'll talk a little bit more 1178 00:56:24,050 --> 00:56:27,020 about SQL and dive in to how we actually use this inside of a web 1179 00:56:27,020 --> 00:56:30,230 application using flask. 1180 00:56:30,230 --> 00:56:31,227 OK, welcome back. 1181 00:56:31,227 --> 00:56:34,560 So right now what we'll do is we'll take a step back from the syntax of the SQL. 1182 00:56:34,560 --> 00:56:37,550 We looked at how we will go about creating tables and inserting rows 1183 00:56:37,550 --> 00:56:38,480 into tables. 1184 00:56:38,480 --> 00:56:42,020 Selecting rows from tables using a whole variety of different syntaxes, 1185 00:56:42,020 --> 00:56:44,120 including joining multiple tables together. 1186 00:56:44,120 --> 00:56:46,944 And we'll talk about a couple of security considerations that 1187 00:56:46,944 --> 00:56:48,860 should be going through your head if you think 1188 00:56:48,860 --> 00:56:50,964 about how to interact with databases. 1189 00:56:50,964 --> 00:56:52,880 And after that, we'll dive into actually using 1190 00:56:52,880 --> 00:56:55,496 SQL to build them into the flask with applications 1191 00:56:55,496 --> 00:56:57,620 that we started building last week, and see how can 1192 00:56:57,620 --> 00:57:01,440 we can use SQL to leverage them to be quite a bit more powerful. 1193 00:57:01,440 --> 00:57:04,830 So the first thing that we want to talk about is SQL injection. 1194 00:57:04,830 --> 00:57:08,240 And so the context for this is imagine that you're running some web site, 1195 00:57:08,240 --> 00:57:10,100 and that web site allows users to log in. 1196 00:57:10,100 --> 00:57:11,960 You've got some form on your website that 1197 00:57:11,960 --> 00:57:15,447 has a user name field and a password field, as many web sites do, 1198 00:57:15,447 --> 00:57:17,780 that just let the user type in the username and password 1199 00:57:17,780 --> 00:57:19,190 and log themselves in. 1200 00:57:19,190 --> 00:57:23,450 Now start to think, once a user submits that form, what sort of SQL command 1201 00:57:23,450 --> 00:57:29,360 might you be running in order to verify whether or not that user is actually 1202 00:57:29,360 --> 00:57:30,590 successfully liking it? 1203 00:57:30,590 --> 00:57:32,006 How might you go about doing that? 1204 00:57:32,006 --> 00:57:35,870 If you had a, imagine you had a table that had at all the users in it. 1205 00:57:35,870 --> 00:57:39,600 That had usernames and passwords for instance, what type of query, 1206 00:57:39,600 --> 00:57:40,969 and what would it look like? 1207 00:57:40,969 --> 00:57:43,010 Yeah, it's going to be some kind of select query, 1208 00:57:43,010 --> 00:57:45,539 where I'm probably selecting from a database. 1209 00:57:45,539 --> 00:57:47,330 In fact, it might look something like this. 1210 00:57:47,330 --> 00:57:51,290 Select star from users where username is-- and I've 1211 00:57:51,290 --> 00:57:54,150 highlighted this in yellow to mean this would be just substituted 1212 00:57:54,150 --> 00:57:56,870 in for whatever they type in-- username is their username, 1213 00:57:56,870 --> 00:57:58,740 and password is password. 1214 00:57:58,740 --> 00:58:01,790 And so you might imagine that if the user typed in their username 1215 00:58:01,790 --> 00:58:03,530 and password correctly, well then there's 1216 00:58:03,530 --> 00:58:08,180 probably going to be a row somewhere in that table that has a matching username 1217 00:58:08,180 --> 00:58:10,130 and has a matching password, and therefore 1218 00:58:10,130 --> 00:58:12,020 the select will return that row. 1219 00:58:12,020 --> 00:58:14,480 And if the user didn't input valid credentials, 1220 00:58:14,480 --> 00:58:17,540 either because the user name didn't exist or the user name did 1221 00:58:17,540 --> 00:58:21,290 exist but they typed in the wrong password, then the result of this query 1222 00:58:21,290 --> 00:58:23,320 would be that no rows got returned. 1223 00:58:23,320 --> 00:58:25,699 But of course, in reality, the query probably 1224 00:58:25,699 --> 00:58:26,990 doesn't look exactly like this. 1225 00:58:26,990 --> 00:58:29,150 For security reasons, most databases will not 1226 00:58:29,150 --> 00:58:32,660 store the actual password of a user and will instead 1227 00:58:32,660 --> 00:58:34,670 store a hash of the password. 1228 00:58:34,670 --> 00:58:36,760 But more on that a little later. 1229 00:58:36,760 --> 00:58:39,926 But you imagine that the query is going to look something along these lines. 1230 00:58:39,926 --> 00:58:43,790 So if a user comes along, they type in username of Alice password is 12345. 1231 00:58:43,790 --> 00:58:47,460 Then the query that ultimately gets run is going to look something like this. 1232 00:58:47,460 --> 00:58:52,230 Where username and password are substituted in for Alice and 12345. 1233 00:58:52,230 --> 00:58:56,130 So I'm selecting from the users table where the user name is Alice 1234 00:58:56,130 --> 00:58:57,880 and the password is 12345. 1235 00:58:57,880 --> 00:59:01,640 So if Alice is indeed in the database and her password is 12345, 1236 00:59:01,640 --> 00:59:04,770 then this select query will return that row. 1237 00:59:04,770 --> 00:59:08,550 And if either of those things are wrong then it won't return a row. 1238 00:59:08,550 --> 00:59:13,470 But now imagine what happens if a hacker is trying to hack into my website. 1239 00:59:13,470 --> 00:59:16,650 Is trying to do something malicious and guesses 1240 00:59:16,650 --> 00:59:20,440 that maybe the SQL code that I'm using looks something like this. 1241 00:59:20,440 --> 00:59:22,842 Maybe I'm running a SQL command, like select star 1242 00:59:22,842 --> 00:59:25,800 from users where user name is something and password is something else. 1243 00:59:25,800 --> 00:59:29,380 And a clever hacker might be able to do something like this. 1244 00:59:29,380 --> 00:59:31,560 Where in the user name they type in hacker, 1245 00:59:31,560 --> 00:59:34,897 or whatever user they want to get access to, and the password 1246 00:59:34,897 --> 00:59:35,980 they do something strange. 1247 00:59:35,980 --> 00:59:40,530 They type one single quote or single quote 1248 00:59:40,530 --> 00:59:43,299 one single quote equal a single quote one. 1249 00:59:43,299 --> 00:59:44,590 Now this seems sort of strange. 1250 00:59:44,590 --> 00:59:46,809 Why would anyone ever type this in as the password? 1251 00:59:46,809 --> 00:59:48,600 But let's go back to the SQL query and look 1252 00:59:48,600 --> 00:59:52,664 at what actually happens now when we try and take this username and password 1253 00:59:52,664 --> 00:59:54,330 and try and run it through our database. 1254 00:59:54,330 --> 00:59:57,510 Well, what will happen is we select star from users where username is 1255 00:59:57,510 --> 00:59:59,740 the username and password is password. 1256 00:59:59,740 --> 01:00:02,160 And if we plug-in hacker as the username, 1257 01:00:02,160 --> 01:00:08,040 and one single quote or single quote one single quote equals one. 1258 01:00:08,040 --> 01:00:10,140 Well, the result of this as interpreted by SQL 1259 01:00:10,140 --> 01:00:14,640 might reasonably be, select star from users where username is hacker, 1260 01:00:14,640 --> 01:00:17,430 or whatever username the hacker is trying to get access to. 1261 01:00:17,430 --> 01:00:24,180 And the password is one or the string one equals the string one. 1262 01:00:24,180 --> 01:00:27,810 Well of course the string one equals the string one, those are the same thing. 1263 01:00:27,810 --> 01:00:30,060 So it doesn't even matter what this password 1264 01:00:30,060 --> 01:00:33,510 is, it's still going to return back to the user with that matching row 1265 01:00:33,510 --> 01:00:36,300 even if the user didn't get the right password. 1266 01:00:36,300 --> 01:00:38,160 Which in this case they didn't. 1267 01:00:38,160 --> 01:00:40,770 And so this is an example of what we call SQL injection. 1268 01:00:40,770 --> 01:00:43,830 What happens if we're not careful about the way that we run our queries 1269 01:00:43,830 --> 01:00:48,630 and we let some user potentially input arbitrary SQL commands. 1270 01:00:48,630 --> 01:00:52,500 They can potentially take over what secret code is running just 1271 01:00:52,500 --> 01:00:54,600 by allowing the user to type something in 1272 01:00:54,600 --> 01:00:57,610 and using that input directly inside of a SQL query. 1273 01:00:57,610 --> 01:01:00,570 And so in this example SQL injection can be 1274 01:01:00,570 --> 01:01:02,860 used to get access to someone else's user account. 1275 01:01:02,860 --> 01:01:05,151 But you might imagine an even more malicious situation. 1276 01:01:05,151 --> 01:01:07,980 You could imagine that we talked before about how 1277 01:01:07,980 --> 01:01:13,110 if you do like an update something from the particular table, 1278 01:01:13,110 --> 01:01:15,617 and you don't specify what's being updated. 1279 01:01:15,617 --> 01:01:17,700 It could just update all of the rows in the table, 1280 01:01:17,700 --> 01:01:19,783 or likewise you can delete all the rows of a table 1281 01:01:19,783 --> 01:01:21,390 or delete a table altogether. 1282 01:01:21,390 --> 01:01:24,150 If you let users just type something in and you 1283 01:01:24,150 --> 01:01:27,750 take whatever they type in to a form and run it as SQL code, 1284 01:01:27,750 --> 01:01:32,344 then a user could theoretically type in whatever SQL they want and manipulate 1285 01:01:32,344 --> 01:01:33,510 your data however they like. 1286 01:01:33,510 --> 01:01:34,968 Delete your data however they like. 1287 01:01:34,968 --> 01:01:38,562 So this is a pretty significant security concern 1288 01:01:38,562 --> 01:01:40,020 that you want to be thinking about. 1289 01:01:40,020 --> 01:01:41,770 And so what's the solution to this? 1290 01:01:41,770 --> 01:01:46,420 Well, in SQL you can do what's called escape particular characters. 1291 01:01:46,420 --> 01:01:51,390 So if the password actually has a single quotation mark in it, 1292 01:01:51,390 --> 01:01:55,560 you don't want it to be interpreted as the end of the password itself. 1293 01:01:55,560 --> 01:01:59,340 You want it to be interpreted as the single quotation mark character. 1294 01:01:59,340 --> 01:02:05,102 And in SQL you can do that by, I think it's doing two single quotation 1295 01:02:05,102 --> 01:02:06,810 marks in a row back to back, just to mean 1296 01:02:06,810 --> 01:02:09,120 this should be an actual single quotation mark, and not 1297 01:02:09,120 --> 01:02:12,420 the end of the password field. 1298 01:02:12,420 --> 01:02:14,400 But ultimately, the long story short of this, 1299 01:02:14,400 --> 01:02:16,230 is that we want to come up with ways that 1300 01:02:16,230 --> 01:02:20,670 allow us to avoid this type of problem, rather than just directly substituting 1301 01:02:20,670 --> 01:02:22,380 user input into the query. 1302 01:02:22,380 --> 01:02:24,240 We want to be intelligent about it, and make 1303 01:02:24,240 --> 01:02:27,000 sure that we are escaping or sanitizing our input 1304 01:02:27,000 --> 01:02:29,670 as it might be called in order to make sure that we 1305 01:02:29,670 --> 01:02:31,480 don't run into situations like this. 1306 01:02:31,480 --> 01:02:35,130 And so in a moment, when we transition into looking at actual Python code 1307 01:02:35,130 --> 01:02:37,880 and how we can use that Python code to be able to run SQL queries, 1308 01:02:37,880 --> 01:02:40,046 this is something we're going to be keeping in mind. 1309 01:02:40,046 --> 01:02:42,150 Because if the user is ever typing in information, 1310 01:02:42,150 --> 01:02:45,380 we want to be careful not to just plug it directly into the query, 1311 01:02:45,380 --> 01:02:49,090 we want to make sure that we don't run the risk of SQL injection there. 1312 01:02:49,090 --> 01:02:53,820 But questions about SQL injection and why this has the potential 1313 01:02:53,820 --> 01:02:57,330 to lead to a security risk? 1314 01:02:57,330 --> 01:02:57,870 OK. 1315 01:02:57,870 --> 01:02:59,661 One other thing related to SQL that's worth 1316 01:02:59,661 --> 01:03:03,060 talking about, especially with security, is the idea of race conditions. 1317 01:03:03,060 --> 01:03:06,240 And so a race condition comes about when there's a possibility that you've 1318 01:03:06,240 --> 01:03:08,406 got a database somewhere that's potentially accessed 1319 01:03:08,406 --> 01:03:10,410 by multiple different people, and you run 1320 01:03:10,410 --> 01:03:12,870 the possibility of things going wrong if people 1321 01:03:12,870 --> 01:03:18,430 try to do things at the same time, and operations execute in a funky order. 1322 01:03:18,430 --> 01:03:19,880 And so what do I mean by this? 1323 01:03:19,880 --> 01:03:23,250 Well, imagine that I've got a database that represents a bank, for instance. 1324 01:03:23,250 --> 01:03:28,200 Where I've got a bank database where every row is an individual person who 1325 01:03:28,200 --> 01:03:31,800 has an account at my bank, and we have a column representing how much money they 1326 01:03:31,800 --> 01:03:33,480 currently have in the bank. 1327 01:03:33,480 --> 01:03:35,940 So imagine that I've got some data in my bank account, 1328 01:03:35,940 --> 01:03:37,565 and the data is storing that right now. 1329 01:03:37,565 --> 01:03:40,020 This particular user has $100 in their bank account. 1330 01:03:40,020 --> 01:03:42,180 And the person goes up to an ATM somewhere 1331 01:03:42,180 --> 01:03:45,570 and they try to withdraw $100. 1332 01:03:45,570 --> 01:03:49,070 Well if you are running the bank's database, 1333 01:03:49,070 --> 01:03:51,860 and the user tries to withdraw $100, what's 1334 01:03:51,860 --> 01:03:56,030 probably the first query you should run on this database? 1335 01:03:56,030 --> 01:03:58,280 Doesn't have to be a precise query, but what would you 1336 01:03:58,280 --> 01:04:01,896 want to know from the database if a user says please withdraw $100? 1337 01:04:01,896 --> 01:04:03,020 AUDIENCE: Do you have $100? 1338 01:04:03,020 --> 01:04:04,144 BRIAN YU: Do you have $100? 1339 01:04:04,144 --> 01:04:05,030 Great question. 1340 01:04:05,030 --> 01:04:07,820 So what might that look like in terms of a SQL query? 1341 01:04:07,820 --> 01:04:10,490 SELECT balance from bank where user ID equals one. 1342 01:04:10,490 --> 01:04:12,740 Here I'm assuming I've got a table called bank 1343 01:04:12,740 --> 01:04:17,510 that's got a column called user ID, representing the user, 1344 01:04:17,510 --> 01:04:19,280 and a column called balance. 1345 01:04:19,280 --> 01:04:20,690 So imagine that I have that. 1346 01:04:20,690 --> 01:04:23,600 So imagine that this current user ID is user ID number one, 1347 01:04:23,600 --> 01:04:27,320 and I want to select a balance from the bank to make sure that whatever balance 1348 01:04:27,320 --> 01:04:29,480 I get back it better be at least $100, otherwise 1349 01:04:29,480 --> 01:04:32,070 I don't want to let you withdraw $100 from the bank. 1350 01:04:32,070 --> 01:04:35,030 So I run the query, and right now in the bank account I have $100. 1351 01:04:35,030 --> 01:04:38,510 So the SQL query gives me back 100, we're all set. 1352 01:04:38,510 --> 01:04:41,030 Next thing that happens is that I actually get the $100. 1353 01:04:41,030 --> 01:04:44,889 But what query needs to run now on this table? 1354 01:04:44,889 --> 01:04:45,680 AUDIENCE: Updating. 1355 01:04:45,680 --> 01:04:46,846 BRIAN YU: Updating, exactly. 1356 01:04:46,846 --> 01:04:48,380 So I want to update the bank. 1357 01:04:48,380 --> 01:04:51,530 Setting the balance equal to balance minus 100 1358 01:04:51,530 --> 01:04:53,480 where the user ID is equal to one. 1359 01:04:53,480 --> 01:04:57,420 So I update the bank balance, I want to decrease the balance by 100. 1360 01:04:57,420 --> 01:04:58,740 That's what the set is doing. 1361 01:04:58,740 --> 01:05:01,031 And I don't want to decrease everyone's balance by 100, 1362 01:05:01,031 --> 01:05:04,520 I only want to decrease by 100 for user ID number one. 1363 01:05:04,520 --> 01:05:08,060 And so I get the $100 back and my bank account balance goes down to zero. 1364 01:05:08,060 --> 01:05:09,740 And that interaction seems fine. 1365 01:05:09,740 --> 01:05:12,200 I selected to make sure I had $100, then I 1366 01:05:12,200 --> 01:05:15,530 updated the bank to make sure that it reflects my new balance. 1367 01:05:15,530 --> 01:05:19,370 But what now happens if two people, for some reason, 1368 01:05:19,370 --> 01:05:22,670 are going to different ATMs, but they both have my ATM card. 1369 01:05:22,670 --> 01:05:26,240 They're both using the same ATM card and they're going to a different ATMs. 1370 01:05:26,240 --> 01:05:27,906 And they're doing this at the same time. 1371 01:05:27,906 --> 01:05:30,410 And they're both trying to withdraw $100. 1372 01:05:30,410 --> 01:05:31,490 What happens? 1373 01:05:31,490 --> 01:05:35,780 So you might imagine that if they both press withdraw at the same time, 1374 01:05:35,780 --> 01:05:37,610 same thing will happen on the first ATM. 1375 01:05:37,610 --> 01:05:39,818 right, I need to make sure that I actually have $100. 1376 01:05:39,818 --> 01:05:42,450 Select balance from bank where user ID is equal to one. 1377 01:05:42,450 --> 01:05:44,100 Do I actually have $100? 1378 01:05:44,100 --> 01:05:46,010 Well Yeah, I've got $100. 1379 01:05:46,010 --> 01:05:49,459 But then person over here on ATM number two is doing the same thing. 1380 01:05:49,459 --> 01:05:50,750 They're running the same query. 1381 01:05:50,750 --> 01:05:54,057 They're also selecting a balance from the bank where user ID equals one, 1382 01:05:54,057 --> 01:05:56,420 and maybe this is happening a split millisecond 1383 01:05:56,420 --> 01:05:58,092 after that first select query happened. 1384 01:05:58,092 --> 01:06:00,300 But it doesn't matter, there's still $100 in the bank 1385 01:06:00,300 --> 01:06:02,630 and so this still gets me back 100. 1386 01:06:02,630 --> 01:06:05,030 So what happens now over here on side number one? 1387 01:06:05,030 --> 01:06:07,790 Maybe right after this happens and right after that happens, now 1388 01:06:07,790 --> 01:06:08,840 we're running our update. 1389 01:06:08,840 --> 01:06:12,110 We update the bank, set the balance equal to balance minus 100 1390 01:06:12,110 --> 01:06:14,729 where user ID is equal to one, same as before. 1391 01:06:14,729 --> 01:06:17,520 And same as before, I get the $100 back and my bank account balance 1392 01:06:17,520 --> 01:06:19,130 goes down to zero. 1393 01:06:19,130 --> 01:06:21,950 But then the problem comes in over on this side. 1394 01:06:21,950 --> 01:06:25,280 Because I've just, we decided that the way to withdraw money from the bank 1395 01:06:25,280 --> 01:06:26,450 was a 2-step process, right? 1396 01:06:26,450 --> 01:06:27,860 Number one, select. 1397 01:06:27,860 --> 01:06:29,540 Make sure you've got $100. 1398 01:06:29,540 --> 01:06:32,720 And if you do, then fine, update in order to subtract $100 1399 01:06:32,720 --> 01:06:34,760 and then give the user $100. 1400 01:06:34,760 --> 01:06:39,152 From the perspective of ATM number two, I've just selected and I have $100. 1401 01:06:39,152 --> 01:06:39,860 So we're all set. 1402 01:06:39,860 --> 01:06:43,010 ATM number two thinks it's totally fine to withdraw. 1403 01:06:43,010 --> 01:06:46,910 ATM number two runs update the bank, decrease the balance by $100 1404 01:06:46,910 --> 01:06:48,710 where the user ID is number one. 1405 01:06:48,710 --> 01:06:52,700 ATM number two gets back $100 and the bank account goes down to negative 100. 1406 01:06:52,700 --> 01:06:56,926 But now from a bank account with $100, I've managed to extract $200 from it 1407 01:06:56,926 --> 01:06:58,550 by taking advantage of race conditions. 1408 01:06:58,550 --> 01:07:00,450 By running things simultaneously. 1409 01:07:00,450 --> 01:07:04,070 And even though the logic of select, make sure I have the money 1410 01:07:04,070 --> 01:07:06,980 and update feels like it's the right logic. 1411 01:07:06,980 --> 01:07:09,350 When you consider what happens when there's concurrency, 1412 01:07:09,350 --> 01:07:11,600 when multiple things are happening at the same time. 1413 01:07:11,600 --> 01:07:14,720 These are the sorts of concerns that begin to take place. 1414 01:07:14,720 --> 01:07:16,230 So what's the solution to this? 1415 01:07:16,230 --> 01:07:21,287 How do we solve the problem of dealing with multiple people that 1416 01:07:21,287 --> 01:07:23,120 could be using the database at the same time 1417 01:07:23,120 --> 01:07:24,828 and trying to prevent something like this 1418 01:07:24,828 --> 01:07:28,530 where someone can get $200 from the ATM when they only have $100 in the bank? 1419 01:07:28,530 --> 01:07:31,802 AUDIENCE: Transactions? 1420 01:07:31,802 --> 01:07:32,760 BRIAN YU: Transactions. 1421 01:07:32,760 --> 01:07:33,260 Exactly. 1422 01:07:33,260 --> 01:07:35,430 And so the idea of a transaction is that I 1423 01:07:35,430 --> 01:07:40,040 want to, or the idea of its transactions and the idea of locking the database. 1424 01:07:40,040 --> 01:07:43,500 That I want to lock the database first, to make sure 1425 01:07:43,500 --> 01:07:48,300 nobody else can touch the database while I'm running a transaction. 1426 01:07:48,300 --> 01:07:50,100 A sequence of SQL commands. 1427 01:07:50,100 --> 01:07:53,460 And so that way, I can say, all right, I'm starting a transaction now. 1428 01:07:53,460 --> 01:07:55,020 Nobody else touch the database. 1429 01:07:55,020 --> 01:07:57,900 Let me select the balance and then let me update the bank, 1430 01:07:57,900 --> 01:08:00,840 and now that I'm done with the transaction now you, ATM number two, 1431 01:08:00,840 --> 01:08:01,935 feel free to go ahead. 1432 01:08:01,935 --> 01:08:03,060 And then everything's fine. 1433 01:08:03,060 --> 01:08:05,893 Because when ATM number two gets a chance to go to the bank account, 1434 01:08:05,893 --> 01:08:06,720 it's zero. 1435 01:08:06,720 --> 01:08:10,800 And so the select query is going to return a zero instead of 100. 1436 01:08:10,800 --> 01:08:14,970 And so the syntax for that in SQL transactions are, if you type begin, 1437 01:08:14,970 --> 01:08:17,430 followed by semi-colon, that begins the transaction. 1438 01:08:17,430 --> 01:08:20,250 And then all of the commands that you run 1439 01:08:20,250 --> 01:08:22,551 are executed effectively together as a unit. 1440 01:08:22,551 --> 01:08:24,300 And then at the end of the transaction you 1441 01:08:24,300 --> 01:08:26,091 say commit, to say these are the commands I 1442 01:08:26,091 --> 01:08:27,899 want to run, go ahead and run them. 1443 01:08:27,899 --> 01:08:30,880 And so that is a way that we can begin to group these things together. 1444 01:08:30,880 --> 01:08:34,109 So the idea of race conditions, the idea of SQL injection, these 1445 01:08:34,109 --> 01:08:36,210 are just good security considerations to be 1446 01:08:36,210 --> 01:08:40,410 taking in mind as we start to approach designing databases that are ultimately 1447 01:08:40,410 --> 01:08:42,240 going to go into our web applications. 1448 01:08:42,240 --> 01:08:44,729 But now how do we actually take these databases 1449 01:08:44,729 --> 01:08:46,649 and put them into our web applications? 1450 01:08:46,649 --> 01:08:48,930 Last week, if you recall, we explored flask, 1451 01:08:48,930 --> 01:08:51,060 which was a micro framework written in Python 1452 01:08:51,060 --> 01:08:54,101 that let us pretty easily get a web application up and running in Python. 1453 01:08:54,101 --> 01:08:56,279 That had a whole bunch of routes that we were 1454 01:08:56,279 --> 01:08:58,800 able to use in order to direct users to different pages, 1455 01:08:58,800 --> 01:09:00,359 depending on what site they visited. 1456 01:09:00,359 --> 01:09:04,350 What we'd like to do now is bridge the gap between Python and SQL, 1457 01:09:04,350 --> 01:09:09,029 to allow our Python code to be able to run SQL commands on databases. 1458 01:09:09,029 --> 01:09:12,990 And so what we'll be using for that is a popular library called SQL alchemy. 1459 01:09:12,990 --> 01:09:16,560 SQL alchemy is a Python library that is used to connect Python and SQL. 1460 01:09:16,560 --> 01:09:19,229 To connect these two worlds and to allow Python code 1461 01:09:19,229 --> 01:09:21,609 to be able to run SQL queries. 1462 01:09:21,609 --> 01:09:24,430 And so we'll take a look at that right now. 1463 01:09:24,430 --> 01:09:26,856 So let's imagine that I want to run, and for now we'll 1464 01:09:26,856 --> 01:09:28,439 start with just command line programs. 1465 01:09:28,439 --> 01:09:31,063 Programs written in Python that don't yet live on the internet, 1466 01:09:31,063 --> 01:09:34,319 but in just a moment we'll transition to looking at web applications as well. 1467 01:09:34,319 --> 01:09:37,319 But imagine for now I just want a Python program that 1468 01:09:37,319 --> 01:09:42,810 prints out all of the flights that are currently in my flights table. 1469 01:09:42,810 --> 01:09:44,680 What might that code look like? 1470 01:09:44,680 --> 01:09:48,180 Well, let's go ahead and look at list.py. 1471 01:09:48,180 --> 01:09:49,840 as an example of this. 1472 01:09:49,840 --> 01:09:52,920 And so what we have up here are a couple of import lines. 1473 01:09:52,920 --> 01:09:56,780 So import OS is just importing an operating system library 1474 01:09:56,780 --> 01:09:59,370 is going to have a useful feature that we'll see in a moment. 1475 01:09:59,370 --> 01:10:04,712 And then I'm importing a bunch of names from SQL alchemy. 1476 01:10:04,712 --> 01:10:07,920 Which is the library that we're going to be using to interact with databases. 1477 01:10:07,920 --> 01:10:11,529 Create engine, scopes session, and session maker. 1478 01:10:11,529 --> 01:10:13,320 I'll briefly describe what these are doing, 1479 01:10:13,320 --> 01:10:16,060 but don't worry about them all that much. 1480 01:10:16,060 --> 01:10:20,230 What's happening effectively is on line six, I'm creating a database engine. 1481 01:10:20,230 --> 01:10:23,629 The engine is, just think of it as an object created by SQL alchemy-- 1482 01:10:23,629 --> 01:10:25,920 this third party library that we're going to be using-- 1483 01:10:25,920 --> 01:10:28,320 that is used to manage connections to the database. 1484 01:10:28,320 --> 01:10:30,960 The engine is going to take care of, for us, the process 1485 01:10:30,960 --> 01:10:33,450 of talking to the database and making sure 1486 01:10:33,450 --> 01:10:35,940 that Python is able to send commands to the database 1487 01:10:35,940 --> 01:10:37,740 and get results back from the database. 1488 01:10:37,740 --> 01:10:40,110 Instead of us needing to worry about that database, 1489 01:10:40,110 --> 01:10:42,490 the engine is going to take care of that for us. 1490 01:10:42,490 --> 01:10:46,110 So on line six I say create engine. 1491 01:10:46,110 --> 01:10:50,529 And then here, I have os.getenv database URL. 1492 01:10:50,529 --> 01:10:52,320 Last week, if you recall, we talked briefly 1493 01:10:52,320 --> 01:10:54,240 about the idea of environment variables. 1494 01:10:54,240 --> 01:10:58,300 Variables that exist inside of my terminal environment. 1495 01:10:58,300 --> 01:11:01,426 We saw that for a setting like flask app or flask debug. 1496 01:11:01,426 --> 01:11:03,300 The environment variable in this case is just 1497 01:11:03,300 --> 01:11:05,970 going to be some variable that's set in my terminal. 1498 01:11:05,970 --> 01:11:09,310 The same place where I'm running flask or running this program. 1499 01:11:09,310 --> 01:11:12,560 And in this case, I'm specifying in an environment variable the database URL. 1500 01:11:12,560 --> 01:11:14,670 The URL of where my database lives. 1501 01:11:14,670 --> 01:11:17,317 In my case, that URL is local host, my own computer. 1502 01:11:17,317 --> 01:11:20,400 But if you've got a database that's living somewhere else on the internet, 1503 01:11:20,400 --> 01:11:21,690 that might actually be a URL. 1504 01:11:21,690 --> 01:11:23,670 And when you start working on the next project, 1505 01:11:23,670 --> 01:11:26,790 and start working with SQL alchemy, we'll give you one of these URLs 1506 01:11:26,790 --> 01:11:29,975 that you can use as your database online. 1507 01:11:29,975 --> 01:11:33,960 On line seven now, what we're doing is we're creating a scope session. 1508 01:11:33,960 --> 01:11:36,480 And so this is less relevant for this particular example, 1509 01:11:36,480 --> 01:11:39,485 but you imagine that once we take our web application to the internet 1510 01:11:39,485 --> 01:11:41,610 and we have multiple people that are simultaneously 1511 01:11:41,610 --> 01:11:43,690 trying to use our website at the same time, 1512 01:11:43,690 --> 01:11:46,939 we want to make sure that the stuff that person A is doing with the database 1513 01:11:46,939 --> 01:11:49,980 is kept separate from the stuff that person B is doing with the database. 1514 01:11:49,980 --> 01:11:52,800 And so creating different sessions for different people 1515 01:11:52,800 --> 01:11:54,300 is just a way of managing that. 1516 01:11:54,300 --> 01:11:57,817 But ultimately now we have this object called d-b which is going to be the one 1517 01:11:57,817 --> 01:11:59,400 that we're really going to care about. 1518 01:11:59,400 --> 01:12:03,450 Because that's what's going to allow us to run SQL commands. 1519 01:12:03,450 --> 01:12:06,180 And this code will all be included for you in the projects, 1520 01:12:06,180 --> 01:12:08,670 you won't need to worry about rewriting that. 1521 01:12:08,670 --> 01:12:12,630 So what's happening in the main function of my code now? 1522 01:12:12,630 --> 01:12:13,740 Here's what's happening. 1523 01:12:13,740 --> 01:12:15,960 On line ten, I say flight. 1524 01:12:15,960 --> 01:12:18,570 I want a query for all of my flights, so how do I do that? 1525 01:12:18,570 --> 01:12:21,120 I say db.execute. 1526 01:12:21,120 --> 01:12:22,810 And then in quotation marks. 1527 01:12:22,810 --> 01:12:26,100 I can specify whatever SQL command I want to run. 1528 01:12:26,100 --> 01:12:29,220 We just talked about SQL syntax, here, just as a string, 1529 01:12:29,220 --> 01:12:32,870 I'm going to feed in SQL syntax into db.execute to say 1530 01:12:32,870 --> 01:12:34,760 I want to run all of this SQL code. 1531 01:12:34,760 --> 01:12:38,899 In particular, I'm selecting origin, destination, and duration from flights. 1532 01:12:38,899 --> 01:12:41,690 Those are the three columns I want to select from my flights table. 1533 01:12:41,690 --> 01:12:43,400 I want to select them all. 1534 01:12:43,400 --> 01:12:47,960 And then at the end I'm doing this fetch all query, which is just to say, 1535 01:12:47,960 --> 01:12:50,840 run the query and get me all of the results. 1536 01:12:50,840 --> 01:12:54,200 There is likewise a fetch one function that just gets me back one row, 1537 01:12:54,200 --> 01:12:55,310 of I only care about one. 1538 01:12:55,310 --> 01:12:57,601 But in this case I want to get back all of the flights. 1539 01:12:57,601 --> 01:12:59,250 So I'm saying db.execute. 1540 01:12:59,250 --> 01:13:01,640 Here is the SQL command I want to run, the select query 1541 01:13:01,640 --> 01:13:03,200 we saw just a moment ago. 1542 01:13:03,200 --> 01:13:06,200 And then fetch all says get me back everything. 1543 01:13:06,200 --> 01:13:09,770 So now, I have inside of this variable flights, 1544 01:13:09,770 --> 01:13:15,520 a list of all of the individual rows that came back from that SQL query. 1545 01:13:15,520 --> 01:13:17,660 And so now on line 11 I'm running a loop. 1546 01:13:17,660 --> 01:13:22,730 For flight in flights, I'm going to print out this formatted string. 1547 01:13:22,730 --> 01:13:24,470 Flight dot origin, that's going to get me 1548 01:13:24,470 --> 01:13:26,540 the origin of this individual flight. 1549 01:13:26,540 --> 01:13:29,930 Because I can use each individual flight, each row, 1550 01:13:29,930 --> 01:13:33,140 and to get in a particular column, I use the dot notation. 1551 01:13:33,140 --> 01:13:35,840 So flight is the name of one of those individual rows. 1552 01:13:35,840 --> 01:13:39,500 To get just the origin of that row, I use flight dot origin. 1553 01:13:39,500 --> 01:13:44,670 So I'm printing out flight dot origin to flight dot destination. 1554 01:13:44,670 --> 01:13:45,620 And how long is it? 1555 01:13:45,620 --> 01:13:49,027 Flight dot duration minutes. 1556 01:13:49,027 --> 01:13:50,360 And that's all that's happening. 1557 01:13:50,360 --> 01:13:53,870 I first run a query to say select me all the data from the flights table. 1558 01:13:53,870 --> 01:13:56,990 Then I run a loop for each individual flight that I get back, 1559 01:13:56,990 --> 01:14:03,260 printout that result. And so now if I go ahead and run list.py, what I get back 1560 01:14:03,260 --> 01:14:04,250 is one per line. 1561 01:14:04,250 --> 01:14:08,930 New York to London 415 minutes Shanghai to Paris 760, so on and so forth. 1562 01:14:08,930 --> 01:14:12,830 Giving me all of the data coming back from those individual flights. 1563 01:14:12,830 --> 01:14:17,920 Questions about the syntax of how that code worked? 1564 01:14:17,920 --> 01:14:20,136 That was me using queries to select, yeah. 1565 01:14:20,136 --> 01:14:22,080 AUDIENCE: Does it always return as a list? 1566 01:14:22,080 --> 01:14:23,111 [INAUDIBLE] 1567 01:14:23,111 --> 01:14:24,860 BRIAN YU: Does it always return as a list? 1568 01:14:24,860 --> 01:14:27,800 If I use this fetch all command that is how I get it back as a list. 1569 01:14:27,800 --> 01:14:30,440 Fetch all says, get me back all the results from the query 1570 01:14:30,440 --> 01:14:35,280 and give it back to me as a list of all of these individual results. 1571 01:14:35,280 --> 01:14:37,050 Good question. 1572 01:14:37,050 --> 01:14:42,372 So that was selecting data from my database. 1573 01:14:42,372 --> 01:14:45,330 One thing we might also want to do using Python code is importing data. 1574 01:14:45,330 --> 01:14:47,340 Inserting data into the database. 1575 01:14:47,340 --> 01:14:49,260 Because if you remember from before, when 1576 01:14:49,260 --> 01:14:52,510 I wanted to insert flights into my flights table, 1577 01:14:52,510 --> 01:14:53,760 I had to use syntax like this. 1578 01:14:53,760 --> 01:14:56,520 I had a whole bunch of these insert queries, each one of which 1579 01:14:56,520 --> 01:14:58,350 was an origin, destination, value. 1580 01:14:58,350 --> 01:15:00,216 Here are the values of the things to insert. 1581 01:15:00,216 --> 01:15:03,090 But generally speaking, if I have a whole bunch of flights to insert, 1582 01:15:03,090 --> 01:15:06,240 it's not going to be nicely formatted like this, already in SQL query form 1583 01:15:06,240 --> 01:15:08,340 ready to be inserted into the database. 1584 01:15:08,340 --> 01:15:11,010 Likely, it's just going to look something like this. 1585 01:15:11,010 --> 01:15:12,920 This is flights dot CSV. 1586 01:15:12,920 --> 01:15:16,042 CSV stands for comma separated value, just an easy way to represent data. 1587 01:15:16,042 --> 01:15:18,000 And I'll likely have data that looks like this. 1588 01:15:18,000 --> 01:15:19,770 That's just one row for each flight. 1589 01:15:19,770 --> 01:15:23,510 And instead of it being formatted nicely as insert into something, 1590 01:15:23,510 --> 01:15:25,942 it's just the origin comma the destination comma 1591 01:15:25,942 --> 01:15:27,280 and number of minutes. 1592 01:15:27,280 --> 01:15:29,260 And that's all I have to deal with. 1593 01:15:29,260 --> 01:15:33,027 So if I just give this to SQL, SQL doesn't immediately 1594 01:15:33,027 --> 01:15:34,860 know how to understand the CSV, that you can 1595 01:15:34,860 --> 01:15:36,930 tell it to try to import it from a CSV. 1596 01:15:36,930 --> 01:15:40,890 So what I might want to do is write Python code that reads this CSV file, 1597 01:15:40,890 --> 01:15:43,350 and takes each row and inserts it into my table. 1598 01:15:43,350 --> 01:15:45,550 So what might that look like? 1599 01:15:45,550 --> 01:15:49,560 Let's go into import.py and look at what's happening here. 1600 01:15:49,560 --> 01:15:52,180 These first couple of lines are really the same. 1601 01:15:52,180 --> 01:15:53,722 But here is the interesting activity. 1602 01:15:53,722 --> 01:15:55,513 And again, this code will be made available 1603 01:15:55,513 --> 01:15:58,175 after, so if you're interested in importing data into a table 1604 01:15:58,175 --> 01:16:00,300 as well-- which you'll need to do for project one-- 1605 01:16:00,300 --> 01:16:03,670 you can use this as a reference or a guide for how to go about doing that. 1606 01:16:03,670 --> 01:16:07,351 So on line 11, I'm opening up this flights.CSV file. 1607 01:16:07,351 --> 01:16:10,350 That's the file that contains all of my flight data so I'm opening it up 1608 01:16:10,350 --> 01:16:11,820 and I'm calling it f. 1609 01:16:11,820 --> 01:16:17,250 And Python has a built in module called CSV specifically meant 1610 01:16:17,250 --> 01:16:19,230 for reading and writing CSV files. 1611 01:16:19,230 --> 01:16:22,530 And so I'm going to use CSV dot reader, which I only 1612 01:16:22,530 --> 01:16:25,710 know about because I've looked up CSV reader's documentation online. 1613 01:16:25,710 --> 01:16:31,140 And I'm saying, I want to read file f as a CSV file. 1614 01:16:31,140 --> 01:16:35,400 And now line 13 what I'm saying, is I'm looping over this reader. 1615 01:16:35,400 --> 01:16:39,270 I'm looping over every single line that the reader is going to read. 1616 01:16:39,270 --> 01:16:43,560 And for each one the three columns are origin, destination, and duration. 1617 01:16:43,560 --> 01:16:48,840 So I loop over the reader, and I'm going to for each row, call the first column 1618 01:16:48,840 --> 01:16:51,360 the origin, call the second column destination, 1619 01:16:51,360 --> 01:16:53,870 call the third column duration. 1620 01:16:53,870 --> 01:16:56,250 And for each one of those rows what do I want to do? 1621 01:16:56,250 --> 01:16:57,930 Well I want to do a db.execute. 1622 01:16:57,930 --> 01:17:00,030 But instead of a select like I did last time, 1623 01:17:00,030 --> 01:17:02,580 I want my db.execute to be an insertion. 1624 01:17:02,580 --> 01:17:04,560 And so what is this insertion look like? 1625 01:17:04,560 --> 01:17:09,420 I'm going to insert into flights origin, destination, duration. 1626 01:17:09,420 --> 01:17:11,020 Values. 1627 01:17:11,020 --> 01:17:14,490 But notice that when I'm at the time that I'm writing this code, 1628 01:17:14,490 --> 01:17:17,100 before I look at flights dot CSV, I don't yet 1629 01:17:17,100 --> 01:17:19,770 know what values I want to insert into my database. 1630 01:17:19,770 --> 01:17:24,090 I don't know if the flights coming from Lima or Shanghai or wherever. 1631 01:17:24,090 --> 01:17:26,370 All I know is that I want to insert some origin, 1632 01:17:26,370 --> 01:17:28,590 some destinations, some duration. 1633 01:17:28,590 --> 01:17:31,770 And I want to fill in those blanks later. 1634 01:17:31,770 --> 01:17:35,010 And so SQL alchemy supports the idea of placeholders for this purpose. 1635 01:17:35,010 --> 01:17:38,880 Where I can say values and colon origin, means that this 1636 01:17:38,880 --> 01:17:40,774 is a placeholder for the origin. 1637 01:17:40,774 --> 01:17:42,690 Meaning something is going to go in that spot, 1638 01:17:42,690 --> 01:17:44,930 but I don't yet know what it is at the time that I'm writing the code, 1639 01:17:44,930 --> 01:17:47,550 I only know when I run the code and actually read 1640 01:17:47,550 --> 01:17:49,710 that flights dot CSV file. 1641 01:17:49,710 --> 01:17:52,960 So colon origin means this is a placeholder for the origin. 1642 01:17:52,960 --> 01:17:55,980 Colon destination means this is a placeholder for the destination, 1643 01:17:55,980 --> 01:17:59,130 and colon duration means this is a placeholder for the duration. 1644 01:17:59,130 --> 01:18:03,520 And then to fill in those placeholders, what I do immediately after it, 1645 01:18:03,520 --> 01:18:06,750 this could be one line but I've broken it up into two just for clarity. 1646 01:18:06,750 --> 01:18:12,210 After a comma, I'm providing a Python dictionary, which is this right here, 1647 01:18:12,210 --> 01:18:18,090 that tells the query what to fill in to each of those individual placeholders. 1648 01:18:18,090 --> 01:18:21,390 So in the origin placeholder, fill in origin. 1649 01:18:21,390 --> 01:18:27,030 Where this origin is this variable over here, that I read from a row of my CSV. 1650 01:18:27,030 --> 01:18:29,310 And likewise, I'm filling in the destination 1651 01:18:29,310 --> 01:18:31,750 placeholder with this destination. 1652 01:18:31,750 --> 01:18:34,740 And the duration placeholder with whatever the variable duration is. 1653 01:18:34,740 --> 01:18:38,430 And if it makes it clear, I can just call this like O and destination 1654 01:18:38,430 --> 01:18:42,750 and duration, just to signify that these are just 1655 01:18:42,750 --> 01:18:45,500 variable names, the things after the colons. 1656 01:18:45,500 --> 01:18:47,400 They're whatever variables I'm using. 1657 01:18:47,400 --> 01:18:48,900 But these things in quotation marks. 1658 01:18:48,900 --> 01:18:51,420 These are the placeholder values that I'm filling in for. 1659 01:18:51,420 --> 01:18:55,920 This origin, destination, and duration, correlate with this origin, 1660 01:18:55,920 --> 01:18:57,360 destination, and duration. 1661 01:18:57,360 --> 01:18:58,740 Those are the placeholders. 1662 01:18:58,740 --> 01:19:00,900 And then immediately after it, I specify, 1663 01:19:00,900 --> 01:19:04,410 all right, now fill things into the placeholder spots. 1664 01:19:04,410 --> 01:19:06,360 And then afterwards I'm just printing a line 1665 01:19:06,360 --> 01:19:10,260 that says, I added a flight from origin to destination lasting duration 1666 01:19:10,260 --> 01:19:13,800 minutes, just so I can keep track of that for myself. 1667 01:19:13,800 --> 01:19:16,860 And then at the end, SQL alchemy automatically does 1668 01:19:16,860 --> 01:19:18,450 this kind of transaction thing for me. 1669 01:19:18,450 --> 01:19:20,790 It automatically says, you know what, let's assume 1670 01:19:20,790 --> 01:19:23,530 you want to group all your actions together. 1671 01:19:23,530 --> 01:19:26,710 And then at the end you better just say you want to commit those changes. 1672 01:19:26,710 --> 01:19:29,880 And so at the end then you need to tell my database db.commit, meaning 1673 01:19:29,880 --> 01:19:31,740 save the changes that I just made. 1674 01:19:31,740 --> 01:19:33,420 I didn't need to do that last time, because I was just 1675 01:19:33,420 --> 01:19:36,290 reading data from the database, I didn't actually update anything. 1676 01:19:36,290 --> 01:19:38,480 But now that I'm inserting rows into the database, 1677 01:19:38,480 --> 01:19:41,660 SQL alchemy wants me to at the end of all my inserts say, all right, 1678 01:19:41,660 --> 01:19:42,590 now I'm done. 1679 01:19:42,590 --> 01:19:44,422 Commit these changes, actually run them. 1680 01:19:44,422 --> 01:19:46,880 This is similar to the commit that we saw a moment ago when 1681 01:19:46,880 --> 01:19:49,320 we were talking about race conditions. 1682 01:19:49,320 --> 01:19:51,350 So let's try and run that now. 1683 01:19:51,350 --> 01:19:54,770 If I try and, so right now if I run Python list.py, 1684 01:19:54,770 --> 01:19:58,850 that gives me the six flights that I've had inside my database this whole time. 1685 01:19:58,850 --> 01:20:02,635 But now if I run Python import,py, what that's going to do is it tells me, 1686 01:20:02,635 --> 01:20:04,760 all right, I've added all these individual flights. 1687 01:20:04,760 --> 01:20:09,090 Paris to New York lasting 540, Tokyo to Shanghai lasting 185 minutes. 1688 01:20:09,090 --> 01:20:12,410 And these are all the flights from this flights.CSV file. 1689 01:20:12,410 --> 01:20:16,020 Where each row corresponding to a flight that I wanted to insert. 1690 01:20:16,020 --> 01:20:20,210 So now if I run Python list.py again, what I get is a longer list of flights. 1691 01:20:20,210 --> 01:20:24,780 Because now I've added all these additional flights to the database. 1692 01:20:24,780 --> 01:20:29,235 Questions about that import process or how that works from the CSV file? 1693 01:20:29,235 --> 01:20:29,735 Yeah. 1694 01:20:29,735 --> 01:20:32,585 AUDIENCE: Is this synchronous? 1695 01:20:32,585 --> 01:20:36,680 Is SQL synchronous or asynchronous? 1696 01:20:36,680 --> 01:20:37,680 BRIAN YU: Good question. 1697 01:20:37,680 --> 01:20:39,150 Is SQL synchronous or asynchronous? 1698 01:20:39,150 --> 01:20:40,457 In what context do you mean? 1699 01:20:40,457 --> 01:20:42,445 AUDIENCE: Like if you did this DB commit, 1700 01:20:42,445 --> 01:20:46,421 you didn't do the transactional strategy with other things. 1701 01:20:46,421 --> 01:20:50,894 If you a really long SQL query and then someone 1702 01:20:50,894 --> 01:20:53,379 did a really short one, if you start that query, 1703 01:20:53,379 --> 01:20:55,935 will no one else be able to access your database? 1704 01:20:55,935 --> 01:20:57,060 BRIAN YU: Oh good question. 1705 01:20:57,060 --> 01:21:01,710 So what happens if you are, you have a lock on the database. 1706 01:21:01,710 --> 01:21:04,470 You're running things but someone else is running something short. 1707 01:21:04,470 --> 01:21:06,553 Generally speaking, if you've locked the database, 1708 01:21:06,553 --> 01:21:08,050 nobody else can get access to it. 1709 01:21:08,050 --> 01:21:11,760 So they cannot get in there while you're in the middle of running those 1710 01:21:11,760 --> 01:21:13,650 individual queries. 1711 01:21:13,650 --> 01:21:14,160 Repeat that. 1712 01:21:14,160 --> 01:21:15,850 AUDIENCE: If you don't lock it? 1713 01:21:15,850 --> 01:21:16,850 BRIAN YU: If you don't lock it then there 1714 01:21:16,850 --> 01:21:18,974 is no guarantee on what order things are happening. 1715 01:21:18,974 --> 01:21:20,940 And so something else could theoretically 1716 01:21:20,940 --> 01:21:22,771 come in and run commands as well. 1717 01:21:22,771 --> 01:21:23,270 Yeah. 1718 01:21:23,270 --> 01:21:27,650 AUDIENCE: So once you do the dot execute, does that lock the database? 1719 01:21:27,650 --> 01:21:28,650 BRIAN YU: Good question. 1720 01:21:28,650 --> 01:21:32,310 So what happens after I run db.execute relative to db.commit? 1721 01:21:32,310 --> 01:21:36,010 I don't actually start running these commands until the db.commit happens. 1722 01:21:36,010 --> 01:21:39,420 What happens is that effectively SQL alchemy is keeping track 1723 01:21:39,420 --> 01:21:41,080 of all the changes I want to make. 1724 01:21:41,080 --> 01:21:43,260 And it's not until I run db.commit that we actually 1725 01:21:43,260 --> 01:21:47,030 go ahead and run the actual queries in the database to make those changes. 1726 01:21:47,030 --> 01:21:48,570 Good question. 1727 01:21:48,570 --> 01:21:50,970 So this is the code that will allow me to import. 1728 01:21:50,970 --> 01:21:53,920 The result of that is that I have imported all these flights here. 1729 01:21:53,920 --> 01:21:58,029 And so now let's look at something a little more complicated. 1730 01:21:58,029 --> 01:21:59,820 Let's look at an example where I want to do 1731 01:21:59,820 --> 01:22:03,750 a combination of multiple different types of queries 1732 01:22:03,750 --> 01:22:05,747 all happening within the same Python program. 1733 01:22:05,747 --> 01:22:07,080 We'll just look at this briefly. 1734 01:22:07,080 --> 01:22:08,421 This is passengers.py. 1735 01:22:08,421 --> 01:22:10,170 And what this is going to do is it's going 1736 01:22:10,170 --> 01:22:15,700 to give me a text based way of looking up what passengers are on a flight. 1737 01:22:15,700 --> 01:22:19,175 And so I'll just briefly go over at a high level what's going on here. 1738 01:22:19,175 --> 01:22:21,300 And then you can look at this in more detail later. 1739 01:22:21,300 --> 01:22:24,460 But effectively, what's happening, is that in my main function 1740 01:22:24,460 --> 01:22:26,910 the first thing that I do is execute this command. 1741 01:22:26,910 --> 01:22:30,491 Select ID, origin, destination, and duration from flights, 1742 01:22:30,491 --> 01:22:32,740 meaning get me all the columns from the flights table. 1743 01:22:32,740 --> 01:22:35,940 Save it inside of this flight's variable, this list. 1744 01:22:35,940 --> 01:22:37,410 Then loop over that list. 1745 01:22:37,410 --> 01:22:41,220 And for each row in my table, for each element in the list, 1746 01:22:41,220 --> 01:22:43,290 go ahead and print out dot flight data. 1747 01:22:43,290 --> 01:22:48,012 Flight ID, origin to destination, lasting duration minutes. 1748 01:22:48,012 --> 01:22:50,220 So that will do much like we did in the list example, 1749 01:22:50,220 --> 01:22:52,317 just print out one thing per line. 1750 01:22:52,317 --> 01:22:54,150 But now let's imagine we want a program that 1751 01:22:54,150 --> 01:22:56,233 lets a user say, all right, you know flight number 1752 01:22:56,233 --> 01:22:57,540 five, tell me who's on it. 1753 01:22:57,540 --> 01:23:01,050 So what happens next is I can prompt the user for input 1754 01:23:01,050 --> 01:23:04,200 using that input function, and convert that input to an integer. 1755 01:23:04,200 --> 01:23:05,130 Just so it's a number. 1756 01:23:05,130 --> 01:23:07,560 And save that result as a flight ID variable. 1757 01:23:07,560 --> 01:23:09,860 So I'm just going to ask the user type in a flight ID. 1758 01:23:09,860 --> 01:23:12,600 I type in flight ID 28, or whatever. 1759 01:23:12,600 --> 01:23:15,060 And that saves it inside the flight ID variable. 1760 01:23:15,060 --> 01:23:18,150 And now I'm going to run another execute query. 1761 01:23:18,150 --> 01:23:21,780 Which is that I'm going to select origin, destination, 1762 01:23:21,780 --> 01:23:27,060 duration from flights, where ID is colon ID, this placeholder. 1763 01:23:27,060 --> 01:23:30,000 Where that placeholder is going to be filled in by flight ID. 1764 01:23:30,000 --> 01:23:31,822 And here I'm just fetching one thing. 1765 01:23:31,822 --> 01:23:34,530 And so the long story short of what that's doing, is it's saying, 1766 01:23:34,530 --> 01:23:36,690 all right, whatever number you typed in, let's 1767 01:23:36,690 --> 01:23:39,840 just query the flights table again and make sure that there actually 1768 01:23:39,840 --> 01:23:41,150 is a flight with that ID. 1769 01:23:41,150 --> 01:23:41,649 Right? 1770 01:23:41,649 --> 01:23:45,600 If I type in flight ID 255, and there is no flight 255, 1771 01:23:45,600 --> 01:23:49,380 then this fetch one is going to return back nothing, or none. 1772 01:23:49,380 --> 01:23:52,310 Because no flight matched that query. 1773 01:23:52,310 --> 01:23:54,589 And so if the flight is none, if there was 1774 01:23:54,589 --> 01:23:57,630 no flight, if it was an invalid flight, it'll print error no such flight. 1775 01:23:57,630 --> 01:23:59,250 And just return and quit. 1776 01:23:59,250 --> 01:24:01,960 But otherwise, if the flight was a valid flight ID, then 1777 01:24:01,960 --> 01:24:03,510 I'm going to get the passengers. 1778 01:24:03,510 --> 01:24:06,540 So I run a db.execute that says, select name 1779 01:24:06,540 --> 01:24:10,620 from passengers where flight ID equals whatever the flight ID is, 1780 01:24:10,620 --> 01:24:11,910 and get me all of those rows. 1781 01:24:11,910 --> 01:24:14,400 So get me the names of all the passengers. 1782 01:24:14,400 --> 01:24:18,270 And then for each passenger in that list of passengers, 1783 01:24:18,270 --> 01:24:21,090 I'm going to print out that passenger's name. 1784 01:24:21,090 --> 01:24:23,610 And if there were no passengers, I print out no passengers. 1785 01:24:23,610 --> 01:24:27,550 So what does that look like? 1786 01:24:27,550 --> 01:24:30,900 I run Python passengers.py, it prints out all my flights 1787 01:24:30,900 --> 01:24:34,080 giving me their ID, their origin, their destination, and how long. 1788 01:24:34,080 --> 01:24:37,874 And if I want to know who is on flight number two, I type in flight two, 1789 01:24:37,874 --> 01:24:40,290 and then it says, OK, here are the passengers on flight 2. 1790 01:24:40,290 --> 01:24:41,091 Charlie and Dave. 1791 01:24:41,091 --> 01:24:42,590 By running those individual queries. 1792 01:24:42,590 --> 01:24:46,080 So first it queried for all the flights, then I type in a flight ID, 1793 01:24:46,080 --> 01:24:48,640 and based on that ID it queried for the passengers table, 1794 01:24:48,640 --> 01:24:52,720 getting me all the passengers that are on that flight as well. 1795 01:24:52,720 --> 01:24:55,970 Questions about how we did that generally speaking? 1796 01:24:55,970 --> 01:24:58,640 1797 01:24:58,640 --> 01:25:00,700 OK, so we've gotten a sense for a SQL alchemy 1798 01:25:00,700 --> 01:25:05,331 and how we can use it to actually run SQL commands in Python code. 1799 01:25:05,331 --> 01:25:07,330 Now let's make the next leap and say, all right, 1800 01:25:07,330 --> 01:25:10,680 how do we take that and merge it in with one of our flask applications, 1801 01:25:10,680 --> 01:25:13,180 so that our web applications that are living on the internet 1802 01:25:13,180 --> 01:25:16,150 that people can go to where URL to and look for. 1803 01:25:16,150 --> 01:25:19,430 How can those be using SQL databases as well? 1804 01:25:19,430 --> 01:25:25,800 So the example we'll take a look at here is airline zero. 1805 01:25:25,800 --> 01:25:27,610 And this is a flask application. 1806 01:25:27,610 --> 01:25:31,820 So what we do, is we have a flask application just like before, 1807 01:25:31,820 --> 01:25:33,940 but we also are setting up a SQL alchemy engine. 1808 01:25:33,940 --> 01:25:37,600 And the SQL alchemy session called db that we're going to be using. 1809 01:25:37,600 --> 01:25:42,010 And what's happening inside of application.py? 1810 01:25:42,010 --> 01:25:44,311 Well in my index function, this default route, 1811 01:25:44,311 --> 01:25:46,060 recall that this is just what happens when 1812 01:25:46,060 --> 01:25:47,976 the user goes to the slash route on my website 1813 01:25:47,976 --> 01:25:49,660 and just wants to see the website. 1814 01:25:49,660 --> 01:25:51,806 It runs this function. 1815 01:25:51,806 --> 01:25:53,680 And here's what happens in my index function. 1816 01:25:53,680 --> 01:25:55,600 First, I query for all the flights. 1817 01:25:55,600 --> 01:25:59,500 I say db.execute, select star from flights dot fetch all, 1818 01:25:59,500 --> 01:26:01,330 just get me all of the flights. 1819 01:26:01,330 --> 01:26:06,130 And then render the template index.html where flights is equal to flights. 1820 01:26:06,130 --> 01:26:10,090 So I'm going to render index.html passing in that list of flights. 1821 01:26:10,090 --> 01:26:12,850 Because I want my web page to display those flights, because maybe 1822 01:26:12,850 --> 01:26:17,510 I'm making a website that lets someone register for a flight, for example. 1823 01:26:17,510 --> 01:26:20,830 So what's happening in index.html? 1824 01:26:20,830 --> 01:26:23,170 Well the first thing to look at is layout.html. 1825 01:26:23,170 --> 01:26:26,170 This is exactly the same as layout,html from last week. 1826 01:26:26,170 --> 01:26:28,960 Just a simple layout for an HTML website, 1827 01:26:28,960 --> 01:26:31,690 where I've got a spot to put in a title for the page, 1828 01:26:31,690 --> 01:26:36,150 and a spot to put in a body for the page to have as well. 1829 01:26:36,150 --> 01:26:38,690 So those are the places where I can just insert content. 1830 01:26:38,690 --> 01:26:40,690 And now let's look at index.html. 1831 01:26:40,690 --> 01:26:42,410 What's happening here? 1832 01:26:42,410 --> 01:26:46,180 First thing that's happening on line one, is I'm extending layout.html. 1833 01:26:46,180 --> 01:26:49,780 Same as last week, layout.html is this HTML file 1834 01:26:49,780 --> 01:26:52,990 that is the template from which I want to base this template on. 1835 01:26:52,990 --> 01:26:55,150 It wants to look exactly the same, except I 1836 01:26:55,150 --> 01:26:57,010 might fill in something for the title, and I 1837 01:26:57,010 --> 01:26:59,390 might fill in something for the body. 1838 01:26:59,390 --> 01:27:05,470 So inside of index.html, now I say, as the title just make the title flights. 1839 01:27:05,470 --> 01:27:09,100 And then as the body, here's what I want to happen. 1840 01:27:09,100 --> 01:27:13,222 There'd be a heading of the top that says Book a flight and a form. 1841 01:27:13,222 --> 01:27:16,180 A form is just going to be a place where users can fill in information. 1842 01:27:16,180 --> 01:27:20,632 When a user submits the form, let's take them to the book URL. 1843 01:27:20,632 --> 01:27:22,590 And we'll take a look at book in just a moment. 1844 01:27:22,590 --> 01:27:24,610 But this is going to be where I'm going to book a flight. 1845 01:27:24,610 --> 01:27:26,380 And the method for that request is going to be post. 1846 01:27:26,380 --> 01:27:29,213 Because I'm submitting data, and oftentimes when I'm submitting data 1847 01:27:29,213 --> 01:27:31,720 I'll submit data via post. 1848 01:27:31,720 --> 01:27:36,100 Then the key elements inside this form, I've into a couple 1849 01:27:36,100 --> 01:27:38,080 divs just for organizational purposes. 1850 01:27:38,080 --> 01:27:40,300 But the key here is that I have a select here, 1851 01:27:40,300 --> 01:27:41,890 and a select is just a drop down menu. 1852 01:27:41,890 --> 01:27:45,670 A dropdown where you click and you can pick from one of multiple options. 1853 01:27:45,670 --> 01:27:49,240 And so I'm going to select class equals form control is just bootstraps 1854 01:27:49,240 --> 01:27:49,990 way of styling. 1855 01:27:49,990 --> 01:27:52,930 Recall the bootstrap, which we looked at back in lecture one, 1856 01:27:52,930 --> 01:27:55,974 is a CSS library that makes our dropdown boxes 1857 01:27:55,974 --> 01:27:59,140 and buttons and menus look a little bit nicer and look a little more modern. 1858 01:27:59,140 --> 01:28:01,630 So form control is just a way of telling bootstrap this is 1859 01:28:01,630 --> 01:28:03,850 something I want you to format nicely. 1860 01:28:03,850 --> 01:28:06,580 And I'm giving it a name, flight ID. 1861 01:28:06,580 --> 01:28:10,657 And I give this select dropdown a name, because later on in my Python code 1862 01:28:10,657 --> 01:28:11,740 I'll want to reference it. 1863 01:28:11,740 --> 01:28:14,323 And when I reference it I'll want to reference it by its name, 1864 01:28:14,323 --> 01:28:16,690 in this case flight ID. 1865 01:28:16,690 --> 01:28:19,690 What's happening next on line 14, I'm going 1866 01:28:19,690 --> 01:28:21,460 to loop through all of the flights. 1867 01:28:21,460 --> 01:28:25,750 Recall that in application.py, when I rendered index.html, 1868 01:28:25,750 --> 01:28:27,614 I passed in flights as a variable. 1869 01:28:27,614 --> 01:28:29,530 Meeting flights is going to be a variable that 1870 01:28:29,530 --> 01:28:34,420 exists inside the index.html And so inside of an x or index.html I 1871 01:28:34,420 --> 01:28:36,010 can loop over them in a for loop. 1872 01:28:36,010 --> 01:28:39,430 For flight in flights, for each one of those individual flights, 1873 01:28:39,430 --> 01:28:40,510 here's what I want. 1874 01:28:40,510 --> 01:28:43,510 I want an option in the drop down menu where 1875 01:28:43,510 --> 01:28:46,750 the value of that option the value it will take on if I pick it, 1876 01:28:46,750 --> 01:28:49,090 is just the ID of the flight. 1877 01:28:49,090 --> 01:28:51,580 But the text that's displayed in the option, 1878 01:28:51,580 --> 01:28:55,000 is going to be flight,origin to flight.destination. 1879 01:28:55,000 --> 01:28:57,400 And recall from last week that the double curly braces 1880 01:28:57,400 --> 01:29:00,280 is a way of me signifying in this templating language, 1881 01:29:00,280 --> 01:29:02,410 I want to plug-in a value here. 1882 01:29:02,410 --> 01:29:05,050 Flight is the name of that row of the flight 1883 01:29:05,050 --> 01:29:09,620 that I want to create an option for, and I use dot to get at a specific column. 1884 01:29:09,620 --> 01:29:12,590 So curly brace curly brace flight.origin, means take the flight, 1885 01:29:12,590 --> 01:29:16,480 get its origin, and paste that into the HTML template right there. 1886 01:29:16,480 --> 01:29:21,850 And I'm going to do that for each flight in my list of flights. 1887 01:29:21,850 --> 01:29:24,790 After that drop down I have another input. 1888 01:29:24,790 --> 01:29:27,670 This input is going to be called Name, and it's just 1889 01:29:27,670 --> 01:29:30,670 going to be a place for the passenger to fill in their name. 1890 01:29:30,670 --> 01:29:34,070 And then at the bottom I have a button that's just going to say, 1891 01:29:34,070 --> 01:29:35,710 book a flight. 1892 01:29:35,710 --> 01:29:36,640 So that's all. 1893 01:29:36,640 --> 01:29:39,880 What's that going to look like now if I go into airline zero 1894 01:29:39,880 --> 01:29:42,660 and run a flask run to run this web application? 1895 01:29:42,660 --> 01:29:44,920 It's running at this URL. 1896 01:29:44,920 --> 01:29:48,100 And so if I go to this URL now, what I see is book a flight, 1897 01:29:48,100 --> 01:29:49,652 that big heading at the top. 1898 01:29:49,652 --> 01:29:50,860 And then I see this dropdown. 1899 01:29:50,860 --> 01:29:54,070 Where this dropdown is populated by the data from my database. 1900 01:29:54,070 --> 01:29:57,870 When I click on it, and I see all of the individual flights 1901 01:29:57,870 --> 01:29:59,411 that are inside my database. 1902 01:29:59,411 --> 01:30:02,160 Because I queried for all of them, and then I looped over each one 1903 01:30:02,160 --> 01:30:05,190 and made each one an option inside of my web page. 1904 01:30:05,190 --> 01:30:08,520 And if I look at the page source of this web page, 1905 01:30:08,520 --> 01:30:13,560 I can see that I've got right here, option value one, New York to London, 1906 01:30:13,560 --> 01:30:15,330 option value two, Shanghai to Paris, where 1907 01:30:15,330 --> 01:30:17,217 the value is the ID of that flight. 1908 01:30:17,217 --> 01:30:19,800 And then the text of it is just the origin to the destination. 1909 01:30:19,800 --> 01:30:22,080 It looped over each one of those individual flights, 1910 01:30:22,080 --> 01:30:27,930 filling in to those placeholder values the values that I actually cared about. 1911 01:30:27,930 --> 01:30:31,020 And so if I wanted to book a flight, I'd go ahead and click on the flight 1912 01:30:31,020 --> 01:30:34,320 that I want to book, maybe New York to Paris. 1913 01:30:34,320 --> 01:30:37,706 I type in my name, Brian, and I press book flight. 1914 01:30:37,706 --> 01:30:39,580 And it's a success you've booked your flight. 1915 01:30:39,580 --> 01:30:46,110 In fact, if I check that passengers file that I created before, and I check, 1916 01:30:46,110 --> 01:30:50,070 you know, here is the New York to Paris flight, flight ID number four. 1917 01:30:50,070 --> 01:30:52,290 If I type flight ID number four, who is on it, 1918 01:30:52,290 --> 01:30:55,890 I now see that I am now on that flight to Paris. 1919 01:30:55,890 --> 01:30:56,890 So how did that happen? 1920 01:30:56,890 --> 01:30:58,230 How did that book route work? 1921 01:30:58,230 --> 01:31:00,563 Because what happened is I submitted a form and suddenly 1922 01:31:00,563 --> 01:31:03,220 it said success, what was going on behind the scenes there? 1923 01:31:03,220 --> 01:31:05,949 Let's take a look at that. 1924 01:31:05,949 --> 01:31:08,490 So for that we're going to need to go back to Application.py. 1925 01:31:08,490 --> 01:31:12,300 Because if you recall from index.html, when this form gets submitted, 1926 01:31:12,300 --> 01:31:15,800 what happens is I submit a post request to the URL 1927 01:31:15,800 --> 01:31:18,840 for the function called book. 1928 01:31:18,840 --> 01:31:22,920 And so let's look at the function called book now. 1929 01:31:22,920 --> 01:31:25,560 I have a function called book, the route is just slash book, 1930 01:31:25,560 --> 01:31:28,050 and it only accepts post requests. 1931 01:31:28,050 --> 01:31:30,100 So what am I going to do here? 1932 01:31:30,100 --> 01:31:33,930 The first thing that I'm going to do is use a request.form.get, 1933 01:31:33,930 --> 01:31:35,670 which is my way of extracting information 1934 01:31:35,670 --> 01:31:37,044 from the form the user submitted. 1935 01:31:37,044 --> 01:31:41,230 Request.form.get name, says take the thing in the HTML form 1936 01:31:41,230 --> 01:31:45,250 that had a name of name, and just get me that information. 1937 01:31:45,250 --> 01:31:48,900 So I get the name and save that in a variable called name. 1938 01:31:48,900 --> 01:31:53,070 Now, this is a special syntax in Python called try and except. 1939 01:31:53,070 --> 01:31:55,950 This is what I do when there is a possibility that something could 1940 01:31:55,950 --> 01:31:57,576 go wrong, that there might be an error. 1941 01:31:57,576 --> 01:32:00,366 But if there's an error, I don't want Python to just throw an error 1942 01:32:00,366 --> 01:32:01,770 and have my whole website crash. 1943 01:32:01,770 --> 01:32:04,270 I want to handle that error and deal with it in some way. 1944 01:32:04,270 --> 01:32:06,580 So what am I trying to do here? 1945 01:32:06,580 --> 01:32:11,140 Well I'm saying take request.form.get flight ID, whatever that flight ID was. 1946 01:32:11,140 --> 01:32:15,320 And recall that in index.html this select dropdown menu, 1947 01:32:15,320 --> 01:32:16,700 I gave a name of flight ID. 1948 01:32:16,700 --> 01:32:20,460 So that's how application.py knows this is the select Dropbox that I'm 1949 01:32:20,460 --> 01:32:22,360 referring to. 1950 01:32:22,360 --> 01:32:25,890 So get that result and try to convert it to an integer. 1951 01:32:25,890 --> 01:32:28,420 If there's a value error, for some reason, in other words, 1952 01:32:28,420 --> 01:32:31,830 if for some reason someone tried to book a flight not to flight ID one, two, 1953 01:32:31,830 --> 01:32:35,080 three, or four, but to flight ID Foo, or just some word, 1954 01:32:35,080 --> 01:32:36,810 something that's not an integer. 1955 01:32:36,810 --> 01:32:39,754 Rather than have my whole website crash, which would probably be bad, 1956 01:32:39,754 --> 01:32:40,920 let's return something nice. 1957 01:32:40,920 --> 01:32:45,150 Let's return error.html saying, invalid flight number. 1958 01:32:45,150 --> 01:32:47,040 Where the message is invalid flight number. 1959 01:32:47,040 --> 01:32:52,440 And all error.html is, is nice error page that also extends layout.html. 1960 01:32:52,440 --> 01:32:55,620 The title just says error, there's a heading that says error, 1961 01:32:55,620 --> 01:32:57,870 and then I fill in using those double quotes whenever 1962 01:32:57,870 --> 01:32:59,550 the message happened to be. 1963 01:32:59,550 --> 01:33:02,760 And so for a generic way to represent an error message, 1964 01:33:02,760 --> 01:33:06,810 I can create this error.html, and if I want to represent any error now, 1965 01:33:06,810 --> 01:33:10,200 I just need to return the template error.html saying here 1966 01:33:10,200 --> 01:33:12,180 is the message to fill in to the error page. 1967 01:33:12,180 --> 01:33:16,000 In this case, invalid flight number, for instance. 1968 01:33:16,000 --> 01:33:19,320 So now I have the name, now I have the flight ID, 1969 01:33:19,320 --> 01:33:24,340 and now all that I need to do is run a couple SQL queries. 1970 01:33:24,340 --> 01:33:29,201 So if you tell me that I want Alice to book flight number five, 1971 01:33:29,201 --> 01:33:31,950 the first thing I'd probably want to do is make sure flight number 1972 01:33:31,950 --> 01:33:32,850 five actually exists. 1973 01:33:32,850 --> 01:33:35,160 Make sure flight number five is a real flight. 1974 01:33:35,160 --> 01:33:38,190 So let's make sure the flight exists. 1975 01:33:38,190 --> 01:33:40,030 If db.execute. 1976 01:33:40,030 --> 01:33:41,940 So I'm running another SQL query. 1977 01:33:41,940 --> 01:33:46,500 SELECT star from flights where ID is plug-in an ID here, 1978 01:33:46,500 --> 01:33:48,000 what ID do I plug-in? 1979 01:33:48,000 --> 01:33:51,570 I want to plug-in flight ID, that variable that I extracted earlier. 1980 01:33:51,570 --> 01:33:53,605 Dot row count is a special SQL alchemy feature 1981 01:33:53,605 --> 01:33:55,980 that just says, you know, how many rows did you get back? 1982 01:33:55,980 --> 01:33:58,440 How many rows were returned from this query? 1983 01:33:58,440 --> 01:34:01,620 And so dot row count equals equals zero, it's just going to say, all right. 1984 01:34:01,620 --> 01:34:09,120 Well that means no flight, zero rows had an ID of this flight ID. 1985 01:34:09,120 --> 01:34:12,390 So what should I do if that's the case, if there were no matching rows? 1986 01:34:12,390 --> 01:34:15,840 Well let's also again, return error.html. 1987 01:34:15,840 --> 01:34:18,900 This time saying message no such flight with that ID. 1988 01:34:18,900 --> 01:34:23,170 There is no flight that has that ID, sorry you can't register for it. 1989 01:34:23,170 --> 01:34:26,760 Now, finally, we've made sure that the flight ID is an integer. 1990 01:34:26,760 --> 01:34:28,650 We've made sure that flight ID exists. 1991 01:34:28,650 --> 01:34:30,360 Last step is to run that insert query. 1992 01:34:30,360 --> 01:34:33,930 To say, we've got a person, we've got a flight, let's put them into the table. 1993 01:34:33,930 --> 01:34:35,670 So I run db.execute. 1994 01:34:35,670 --> 01:34:37,890 Insert into the passengers table. 1995 01:34:37,890 --> 01:34:39,580 What columns do I want to insert? 1996 01:34:39,580 --> 01:34:42,330 Well, I want to insert their name and I went into their flight ID. 1997 01:34:42,330 --> 01:34:43,355 What values? 1998 01:34:43,355 --> 01:34:46,230 Well I didn't know yet at the time of my writing the web application. 1999 01:34:46,230 --> 01:34:48,564 I don't know who is going to register for a flight, what 2000 01:34:48,564 --> 01:34:51,438 their name is going to be, what flight they're going to register for. 2001 01:34:51,438 --> 01:34:53,720 I just know they're going to register for something. 2002 01:34:53,720 --> 01:34:56,010 And so I put these placeholders here, and then 2003 01:34:56,010 --> 01:35:00,430 I substituted in name for name, and flight ID for flight ID. 2004 01:35:00,430 --> 01:35:02,950 And when I do this placeholder syntax, as a good note, 2005 01:35:02,950 --> 01:35:05,770 SQL alchemy will take care of for me automatically 2006 01:35:05,770 --> 01:35:09,460 the problem of escaping the characters that might cause race conditions. 2007 01:35:09,460 --> 01:35:12,160 So, so long as I'm using this placeholder syntax, 2008 01:35:12,160 --> 01:35:15,310 and then using this dictionary to say plug-in these values, 2009 01:35:15,310 --> 01:35:19,090 SQL alchemy will take care of avoiding those race conditions by escaping 2010 01:35:19,090 --> 01:35:21,010 any potentially dangerous characters. 2011 01:35:21,010 --> 01:35:25,990 As opposed to, if I just did values plus names to just join 2012 01:35:25,990 --> 01:35:29,590 multiple strings together, that would not be safe against race conditions. 2013 01:35:29,590 --> 01:35:33,820 Because if there were a single quote and some other potentially malicious syntax 2014 01:35:33,820 --> 01:35:37,270 in the name, plugging it right into the query just like that 2015 01:35:37,270 --> 01:35:39,110 would result in me running that query. 2016 01:35:39,110 --> 01:35:42,490 So you want to be careful never to directly run potential user 2017 01:35:42,490 --> 01:35:48,640 input into the database unless you're either sanitizing the input yourself, 2018 01:35:48,640 --> 01:35:54,190 or you are using this placeholder syntax in SQL alchemy get stuff. 2019 01:35:54,190 --> 01:35:56,810 So we insert the passenger into the database. 2020 01:35:56,810 --> 01:35:59,770 We commit those changes to say, yes, this is the change I want to make. 2021 01:35:59,770 --> 01:36:03,130 And then I return the template success.html. 2022 01:36:03,130 --> 01:36:06,070 And all that success.html is, is another template 2023 01:36:06,070 --> 01:36:09,580 that extends from the same layout, the title is success, and in the body 2024 01:36:09,580 --> 01:36:10,340 I say success. 2025 01:36:10,340 --> 01:36:12,660 You have successfully booked your flight. 2026 01:36:12,660 --> 01:36:17,710 And the result of that is that if I now go back to my web application, 2027 01:36:17,710 --> 01:36:20,830 and I say, you know what, let's take this Hong Kong to Shanghai flight. 2028 01:36:20,830 --> 01:36:23,622 And I type in my name and I book the flight. 2029 01:36:23,622 --> 01:36:25,580 It'll say success, I've booked my flight, and I 2030 01:36:25,580 --> 01:36:28,750 I'm now registered for that flight. 2031 01:36:28,750 --> 01:36:31,387 So that was a lot of things going on at once. 2032 01:36:31,387 --> 01:36:33,220 We were talking about HTML, but also talking 2033 01:36:33,220 --> 01:36:36,370 about flask and how we go about passing values into templates 2034 01:36:36,370 --> 01:36:39,490 and then using SQL alchemy to be able to run database commands. 2035 01:36:39,490 --> 01:36:41,960 What questions do we have about that? 2036 01:36:41,960 --> 01:36:45,337 And how any of those moving pieces fit together. 2037 01:36:45,337 --> 01:36:46,291 Yeah. 2038 01:36:46,291 --> 01:36:50,080 AUDIENCE: Should we be using a try catch for the db.commit too? 2039 01:36:50,080 --> 01:36:51,080 BRIAN YU: Good question. 2040 01:36:51,080 --> 01:36:52,997 Should you be using a try catch for db.commit? 2041 01:36:52,997 --> 01:36:54,330 Certainly that's not a bad idea. 2042 01:36:54,330 --> 01:36:56,890 In fact, it's probably a good idea from a design perspective. 2043 01:36:56,890 --> 01:36:58,869 To put a try catch in the db.commit. 2044 01:36:58,869 --> 01:37:00,910 Because if, for some reason, something goes wrong 2045 01:37:00,910 --> 01:37:07,351 as I try to run my SQL commands and there's a problem that gets 2046 01:37:07,351 --> 01:37:09,100 encountered, and that will throw an error. 2047 01:37:09,100 --> 01:37:12,880 Rather than have my website crash it'd probably be nice if, instead, 2048 01:37:12,880 --> 01:37:17,620 it just gave me some sort of nice error message in response to. 2049 01:37:17,620 --> 01:37:19,930 Another thing that you could also do to deal with that, 2050 01:37:19,930 --> 01:37:25,000 is have a global error handler for your flask application. 2051 01:37:25,000 --> 01:37:28,930 So flask supports the idea of, if there is an internal server 2052 01:37:28,930 --> 01:37:33,934 error inside the application, then return some specific template. 2053 01:37:33,934 --> 01:37:36,100 And so if you've ever run into internal server error 2054 01:37:36,100 --> 01:37:39,915 on a website, that's likely because some part of the code, 2055 01:37:39,915 --> 01:37:41,290 there was some error in the code. 2056 01:37:41,290 --> 01:37:43,040 Something the programmer didn't anticipate 2057 01:37:43,040 --> 01:37:44,710 that resulted in something going wrong. 2058 01:37:44,710 --> 01:37:48,304 And you can have flask say, you know, if there is an error, 2059 01:37:48,304 --> 01:37:50,470 there is an internal server error, let's handle that 2060 01:37:50,470 --> 01:37:52,520 by rendering a specific error page. 2061 01:37:52,520 --> 01:37:53,650 So you can do that as well. 2062 01:37:53,650 --> 01:37:58,309 But certainly, you could put the commit inside of a try catch too. 2063 01:37:58,309 --> 01:37:58,975 Other questions? 2064 01:37:58,975 --> 01:38:03,496 AUDIENCE: Well, you should have a try catch for the name too, right? 2065 01:38:03,496 --> 01:38:04,870 BRIAN YU: You, so, good question. 2066 01:38:04,870 --> 01:38:06,912 Do you need a try catch for the name? 2067 01:38:06,912 --> 01:38:08,620 You probably don't need one for the name. 2068 01:38:08,620 --> 01:38:12,550 Because even if the user doesn't type in a name, the result of this 2069 01:38:12,550 --> 01:38:14,872 will still just be, it'll just be empty. 2070 01:38:14,872 --> 01:38:16,580 So maybe you do want to handle that case. 2071 01:38:16,580 --> 01:38:23,050 Maybe I want to say, if name is none, or if name is the empty string 2072 01:38:23,050 --> 01:38:25,180 if they literally just typed in nothing, maybe you 2073 01:38:25,180 --> 01:38:28,202 want to not let someone register for a flight if they don't have a name, 2074 01:38:28,202 --> 01:38:28,743 for instance. 2075 01:38:28,743 --> 01:38:32,690 So that can be something you do as well. 2076 01:38:32,690 --> 01:38:33,350 OK. 2077 01:38:33,350 --> 01:38:36,530 We'll take a look at one last example before wrapping up for now. 2078 01:38:36,530 --> 01:38:38,690 We'll look at extending this web application 2079 01:38:38,690 --> 01:38:40,490 to allow some additional features. 2080 01:38:40,490 --> 01:38:43,160 In particular, allowing me to see who is currently 2081 01:38:43,160 --> 01:38:44,751 registered for a given flight. 2082 01:38:44,751 --> 01:38:47,000 Which in reality, is probably not information you want 2083 01:38:47,000 --> 01:38:49,083 made public if you're building an airline website, 2084 01:38:49,083 --> 01:38:51,684 but for now we're just going to use it as an example. 2085 01:38:51,684 --> 01:38:53,600 So before we created a Python application that 2086 01:38:53,600 --> 01:38:55,520 generated a list of all the passengers. 2087 01:38:55,520 --> 01:38:59,820 Let's do the same thing with a flask web application as well. 2088 01:38:59,820 --> 01:39:05,930 So let's go ahead and take a look at airline one. 2089 01:39:05,930 --> 01:39:09,299 So inside of airline one, the index function is the same. 2090 01:39:09,299 --> 01:39:12,590 We're just going to select the flights, display it for people to register them. 2091 01:39:12,590 --> 01:39:14,690 Book a flight is the same way as well. 2092 01:39:14,690 --> 01:39:16,040 Works exactly the same. 2093 01:39:16,040 --> 01:39:18,900 But I've added a couple of routes here as well. 2094 01:39:18,900 --> 01:39:22,040 So let's take a look at this route real quick and see what it does. 2095 01:39:22,040 --> 01:39:23,419 This is slash flights. 2096 01:39:23,419 --> 01:39:26,460 So if I go to slash flights on my website, here's what's going to happen. 2097 01:39:26,460 --> 01:39:27,980 I'm going to list all the flights. 2098 01:39:27,980 --> 01:39:29,790 And how do I do that? 2099 01:39:29,790 --> 01:39:33,120 Well first, I select star from flights, just select everything from my flights 2100 01:39:33,120 --> 01:39:33,860 table. 2101 01:39:33,860 --> 01:39:36,020 Fetch all the rows, call that flights. 2102 01:39:36,020 --> 01:39:39,410 And now return this flights.html template 2103 01:39:39,410 --> 01:39:41,750 passing in that flights variable. 2104 01:39:41,750 --> 01:39:44,660 So what's happening in flights.html? 2105 01:39:44,660 --> 01:39:46,730 Well, the title is going to be flights again. 2106 01:39:46,730 --> 01:39:48,530 It's extending from the same layout file. 2107 01:39:48,530 --> 01:39:51,020 I'm using that same layout file for all my HTML 2108 01:39:51,020 --> 01:39:53,390 files to avoid the need to redundantly repeat 2109 01:39:53,390 --> 01:39:55,950 that HTML over and over and over again. 2110 01:39:55,950 --> 01:39:57,270 Then inside the body. 2111 01:39:57,270 --> 01:40:00,260 I have this heading that says all flights, and then a UL. 2112 01:40:00,260 --> 01:40:02,604 Recalling that UL stands for un-ordered list. 2113 01:40:02,604 --> 01:40:05,270 And I'm going to have an unordered bulleted list of items, where 2114 01:40:05,270 --> 01:40:07,670 each item is just going to be a flight. 2115 01:40:07,670 --> 01:40:09,290 So I'm going to loop. 2116 01:40:09,290 --> 01:40:14,000 For each flight in my list of flights, create an LI, a list item. 2117 01:40:14,000 --> 01:40:17,540 A bulleted item in my list, where that item is an AH ref, 2118 01:40:17,540 --> 01:40:19,531 recalling that AH ref is a link. 2119 01:40:19,531 --> 01:40:21,530 So I'm going to have a bulleted list of flights, 2120 01:40:21,530 --> 01:40:22,910 which just tells me the flight. 2121 01:40:22,910 --> 01:40:25,430 And clicking on each any individual flight, 2122 01:40:25,430 --> 01:40:29,082 will link me to, presumably, another page with details about that flight. 2123 01:40:29,082 --> 01:40:31,790 The common paradigm that happens when you've got a bunch of data, 2124 01:40:31,790 --> 01:40:34,331 but you want to click on something to go see more information 2125 01:40:34,331 --> 01:40:35,870 about it in more detail. 2126 01:40:35,870 --> 01:40:37,980 And so what's going to happen here? 2127 01:40:37,980 --> 01:40:41,120 Well, the link is going to be itself just labeled 2128 01:40:41,120 --> 01:40:44,720 as flight.origin to flight.destination just like we've been using before. 2129 01:40:44,720 --> 01:40:47,330 But where am I going to link to? 2130 01:40:47,330 --> 01:40:50,450 If we look back to Application.py, I have this other route 2131 01:40:50,450 --> 01:40:52,850 that's going to be used not for accessing all flights, 2132 01:40:52,850 --> 01:40:55,820 but for accessing data about one specific flight. 2133 01:40:55,820 --> 01:40:58,760 And the route I'm using for that is slash flights 2134 01:40:58,760 --> 01:41:02,240 slash an integer called flight ID. 2135 01:41:02,240 --> 01:41:05,150 This is a placeholder that I'm putting into this route that 2136 01:41:05,150 --> 01:41:07,760 could be substituted for any integer. 2137 01:41:07,760 --> 01:41:10,250 Recall that in last week's lecture, we talked about how we 2138 01:41:10,250 --> 01:41:12,110 could use this to say hello to anyone. 2139 01:41:12,110 --> 01:41:14,060 Where you could do slash David, slash Brian 2140 01:41:14,060 --> 01:41:16,580 slash Maria to say hello to a variety of different people. 2141 01:41:16,580 --> 01:41:18,655 Just based on what gets passed into the URL. 2142 01:41:18,655 --> 01:41:21,020 This is another common use case, where if I 2143 01:41:21,020 --> 01:41:24,710 want to access a URL for a particular flight, 2144 01:41:24,710 --> 01:41:27,800 it would be unreasonable and impractical to create one separate route 2145 01:41:27,800 --> 01:41:28,954 for each individual flight. 2146 01:41:28,954 --> 01:41:30,620 I wouldn't even know how many to create. 2147 01:41:30,620 --> 01:41:32,950 Instead, I just create a generic one. 2148 01:41:32,950 --> 01:41:35,690 Slash flights, slash an integer flight ID 2149 01:41:35,690 --> 01:41:39,034 that is going to represent accessing slash flight slash two 2150 01:41:39,034 --> 01:41:39,950 for the second flight. 2151 01:41:39,950 --> 01:41:42,740 Or slash flight slash 28 for the 28th flight. 2152 01:41:42,740 --> 01:41:45,290 And inside this flight function, this is where 2153 01:41:45,290 --> 01:41:48,920 all the code for generating details about that flight is going to be. 2154 01:41:48,920 --> 01:41:54,380 So what does that mean for where I'm linking to in my flights.html template? 2155 01:41:54,380 --> 01:41:57,620 Well, I want to link to the URL for flight, 2156 01:41:57,620 --> 01:42:00,040 where flight is the name of that function. 2157 01:42:00,040 --> 01:42:05,150 But I can't just give flight anymore, because this URL 2158 01:42:05,150 --> 01:42:07,640 is a flight function that also has this flight ID 2159 01:42:07,640 --> 01:42:10,550 parameter that's passed in by the URL. 2160 01:42:10,550 --> 01:42:13,940 So if I just say, take me to the flight function, flask is going to complain 2161 01:42:13,940 --> 01:42:16,790 and say, wait a minute, I don't know which flight you actually 2162 01:42:16,790 --> 01:42:17,990 care about going to. 2163 01:42:17,990 --> 01:42:22,535 So in my URL for I need to tell flask what flight I actually care about. 2164 01:42:22,535 --> 01:42:23,660 And so that's what this is. 2165 01:42:23,660 --> 01:42:29,210 URL for flight, comma, flight ID equals flight.ID. 2166 01:42:29,210 --> 01:42:32,030 So flight ID, that is the name of that variable 2167 01:42:32,030 --> 01:42:35,280 that we were using just a moment ago in application.py. 2168 01:42:35,280 --> 01:42:38,930 And flight.ID, well, flight is this individual row 2169 01:42:38,930 --> 01:42:41,380 that I got back from the flights table from that query. 2170 01:42:41,380 --> 01:42:45,280 And to access the ID column of that row, once again were using the dot notation. 2171 01:42:45,280 --> 01:42:48,950 To say flight.ID, is the ID the thing I want to go to. 2172 01:42:48,950 --> 01:42:53,720 And so using this syntax I can say, take me to that specific flight page. 2173 01:42:53,720 --> 01:42:55,695 Now what happens on that specific flight page? 2174 01:42:55,695 --> 01:42:58,070 Well the first thing I do is make sure the flight exists. 2175 01:42:58,070 --> 01:42:58,570 Right. 2176 01:42:58,570 --> 01:43:02,750 Select star from flights where ID is that ID, fetching that one row. 2177 01:43:02,750 --> 01:43:04,746 If that row is none, if no row gets returned 2178 01:43:04,746 --> 01:43:06,620 when I try and select that flight, well there 2179 01:43:06,620 --> 01:43:08,078 is no such flight with that number. 2180 01:43:08,078 --> 01:43:10,760 So I better return an error message that says sorry, 2181 01:43:10,760 --> 01:43:13,280 no such flight by that number. 2182 01:43:13,280 --> 01:43:15,980 But if that flight does exist, now I want to get the passengers. 2183 01:43:15,980 --> 01:43:19,310 So I'm selecting names from that passengers list where 2184 01:43:19,310 --> 01:43:22,190 flight ID is equal to plugging in that flight ID 2185 01:43:22,190 --> 01:43:25,610 there, and getting all the rows. 2186 01:43:25,610 --> 01:43:28,370 And I save that in a variable called passengers. 2187 01:43:28,370 --> 01:43:33,370 And now on line 54 I say render template flight.html, passing in both the flight 2188 01:43:33,370 --> 01:43:36,220 that I got back from when I queried for the flight up here. 2189 01:43:36,220 --> 01:43:38,800 Select star from flights. 2190 01:43:38,800 --> 01:43:42,010 And also passing in all the passengers. 2191 01:43:42,010 --> 01:43:45,890 So now my flight.html knows information about the flight, 2192 01:43:45,890 --> 01:43:47,740 its destination, its duration. 2193 01:43:47,740 --> 01:43:50,680 And also its passenger list. 2194 01:43:50,680 --> 01:43:54,430 So inside of flight.html, what's happening here? 2195 01:43:54,430 --> 01:43:57,540 It extends layout.html again, it's got a title of flight. 2196 01:43:57,540 --> 01:44:00,520 And in the body I have a heading that says flight details. 2197 01:44:00,520 --> 01:44:02,650 I've got this unordered list just of information. 2198 01:44:02,650 --> 01:44:05,560 Origin is flight.origin, destination is flight.destination, 2199 01:44:05,560 --> 01:44:08,585 duration is flight.duration minutes. 2200 01:44:08,585 --> 01:44:10,960 So that's nothing too crazy, just using that double curly 2201 01:44:10,960 --> 01:44:12,880 braces to say plug-in a value here. 2202 01:44:12,880 --> 01:44:16,060 And flight was a variable that I passed in from Python. 2203 01:44:16,060 --> 01:44:19,280 Then down here, I have another heading that says passengers. 2204 01:44:19,280 --> 01:44:21,030 I have another unordered list. 2205 01:44:21,030 --> 01:44:23,830 And for each passenger in my list of passengers, 2206 01:44:23,830 --> 01:44:27,880 I'm going to create a list item that is just that passenger's name. 2207 01:44:27,880 --> 01:44:30,650 I'm going to plug-in to a bulleted list. 2208 01:44:30,650 --> 01:44:32,920 And this else condition in gender two just says, 2209 01:44:32,920 --> 01:44:35,950 the else condition specifically in this templating language 2210 01:44:35,950 --> 01:44:40,280 means that if the passenger list was empty, if the for loop never ran, 2211 01:44:40,280 --> 01:44:41,470 run this code. 2212 01:44:41,470 --> 01:44:44,710 In this case, just saying there were no passengers. 2213 01:44:44,710 --> 01:44:48,740 So what happens if I cd into airline one and flask run this code. 2214 01:44:48,740 --> 01:44:50,740 Actually it's already in use because I'm already 2215 01:44:50,740 --> 01:44:52,600 running a flask application over here. 2216 01:44:52,600 --> 01:44:55,330 I run flask run again. 2217 01:44:55,330 --> 01:44:58,280 We're running on this URL. 2218 01:44:58,280 --> 01:44:59,200 So I go here. 2219 01:44:59,200 --> 01:45:00,580 This is the book page. 2220 01:45:00,580 --> 01:45:03,490 But recall, if I want to go to where I can see all the flights, 2221 01:45:03,490 --> 01:45:06,340 I go to the slash flights route. 2222 01:45:06,340 --> 01:45:10,900 So if I now go to slash flights, what I get is a heading that says all flights. 2223 01:45:10,900 --> 01:45:12,700 And then in this bulleted list, one flight 2224 01:45:12,700 --> 01:45:17,310 per line, where each one is going to be a link to that detail page. 2225 01:45:17,310 --> 01:45:22,150 So right now I'm at this URL slash flights. 2226 01:45:22,150 --> 01:45:27,530 But if I were to click on New York to London, for example, and go there, 2227 01:45:27,530 --> 01:45:29,040 now notice my URLs changed. 2228 01:45:29,040 --> 01:45:31,690 Now I'm at slash flights slash one. 2229 01:45:31,690 --> 01:45:37,190 Because clicking on that link took me to this route. 2230 01:45:37,190 --> 01:45:40,510 Slash flights slash the integer flight ID. 2231 01:45:40,510 --> 01:45:43,390 And I passed in which flight ID I actually wanted. 2232 01:45:43,390 --> 01:45:46,880 so going to slash flights slash one, tells me here is the flight, 2233 01:45:46,880 --> 01:45:50,530 its origin is New York, its destination is London, is lasts 415 minutes, 2234 01:45:50,530 --> 01:45:53,950 and Alice and Bob are the passengers that are registered for that right now. 2235 01:45:53,950 --> 01:45:56,080 If I instead just go to the URL and change flight 2236 01:45:56,080 --> 01:45:59,482 slash one to flight slash two, for instance. 2237 01:45:59,482 --> 01:46:00,940 Now I'm looking at this next fight. 2238 01:46:00,940 --> 01:46:04,570 Origin Shanghai, destination Paris, 760 minutes, the passengers 2239 01:46:04,570 --> 01:46:06,450 are Charlie and Dave. 2240 01:46:06,450 --> 01:46:08,620 And if I try going to a flight that doesn't exist, 2241 01:46:08,620 --> 01:46:12,010 you know flight 28 for example, when I don't have a flight number 28. 2242 01:46:12,010 --> 01:46:15,260 Now I get an error that just says no such flight. 2243 01:46:15,260 --> 01:46:18,730 And so I've been able to create arbitrarily many of these URLs 2244 01:46:18,730 --> 01:46:22,750 just by using this placeholder syntax, wherein I can substitute any arbitrary 2245 01:46:22,750 --> 01:46:25,540 flight ID into this particular route. 2246 01:46:25,540 --> 01:46:27,760 So questions about how that worked and how 2247 01:46:27,760 --> 01:46:31,930 I was able to create detail pages for all of these individual flights 2248 01:46:31,930 --> 01:46:39,387 just by using those routes and querying for the right data from the database? 2249 01:46:39,387 --> 01:46:41,220 So all of this and more is stuff that you're 2250 01:46:41,220 --> 01:46:43,530 going to get to try as you begin to approach project one, which 2251 01:46:43,530 --> 01:46:44,738 will be released later today. 2252 01:46:44,738 --> 01:46:47,310 And project one is all about building a flask application 2253 01:46:47,310 --> 01:46:50,160 and using SQL commands in order to query databases, 2254 01:46:50,160 --> 01:46:53,610 in order to read data and write to data and interact with data. 2255 01:46:53,610 --> 01:46:57,600 Building pages sort of like this that have detailed views for certain items 2256 01:46:57,600 --> 01:46:58,740 and so on and so forth. 2257 01:46:58,740 --> 01:47:01,350 And so more information on that to come with project one. 2258 01:47:01,350 --> 01:47:02,385 But for now that's SQL. 2259 01:47:02,385 --> 01:47:04,260 And next week we'll dive into even more depth 2260 01:47:04,260 --> 01:47:06,000 about how to interact with databases. 2261 01:47:06,000 --> 01:47:08,910 And in particular, how to go about using and creating 2262 01:47:08,910 --> 01:47:11,370 APIs, or application programming interfaces, 2263 01:47:11,370 --> 01:47:15,390 that let us interact with data in even more useful and programmatic way. 2264 01:47:15,390 --> 01:47:16,390 So more on that to come. 2265 01:47:16,390 --> 01:47:19,140 But for now thank you all. 2266 01:47:19,140 --> 01:47:20,541