1 00:00:00,000 --> 00:00:00,140 2 00:00:00,140 --> 00:00:02,640 BRIAN YU: Now that we have the ability to create dynamic web 3 00:00:02,640 --> 00:00:06,480 applications using Flask and to connect those applications to a SQLite 4 00:00:06,480 --> 00:00:11,280 database, let's put these ideas into practice by creating CS50 Finance. 5 00:00:11,280 --> 00:00:13,870 Ultimately what CS50 Finance is going to be is 6 00:00:13,870 --> 00:00:16,530 it's going to be a website where users can register and log 7 00:00:16,530 --> 00:00:18,840 in and buy and sell stocks. 8 00:00:18,840 --> 00:00:22,313 Let's take a look at what the finished product might look like. 9 00:00:22,313 --> 00:00:25,230 Once you've completed the application, what users should be able to do 10 00:00:25,230 --> 00:00:27,313 is they should be able to register for an account. 11 00:00:27,313 --> 00:00:30,300 So I could click here, Register, and type in my name 12 00:00:30,300 --> 00:00:33,470 along with a password and a confirmation of that password. 13 00:00:33,470 --> 00:00:35,057 And I'll register for an account. 14 00:00:35,057 --> 00:00:35,640 And all right. 15 00:00:35,640 --> 00:00:38,460 Here's what I see when I log in to CS50 Finance. 16 00:00:38,460 --> 00:00:42,480 I can first go to Quote, which lets me look up a stock quote. 17 00:00:42,480 --> 00:00:45,780 I could say, all right, how much does one share of Apple cost right now, 18 00:00:45,780 --> 00:00:48,960 for example, and click on the Quote button to see that, all right, 19 00:00:48,960 --> 00:00:53,530 a share of Apple right now costs $235.48, for example. 20 00:00:53,530 --> 00:00:56,510 If I wanted to buy some of Apple stock, for example, I 21 00:00:56,510 --> 00:01:00,750 could go into the Buy tab, type in Apple, and say let's go ahead 22 00:01:00,750 --> 00:01:04,660 and buy five shares of Apple and click Buy. 23 00:01:04,660 --> 00:01:06,840 And now I've bought that number of shares of Apple. 24 00:01:06,840 --> 00:01:09,660 And you'll see that on this index page, the main page when 25 00:01:09,660 --> 00:01:12,480 I log in to CS50 Finance, I'll see all of the stocks 26 00:01:12,480 --> 00:01:15,900 that I currently own organized in a table along with the number of shares 27 00:01:15,900 --> 00:01:18,780 of that stock that I own, the current price of that stock, which 28 00:01:18,780 --> 00:01:21,960 may have changed from when I bought that stock, the current value 29 00:01:21,960 --> 00:01:25,500 of all my stock holdings, how much cash I have on hand, which by default is 30 00:01:25,500 --> 00:01:29,160 going to be $10,000, and then the total value of all 31 00:01:29,160 --> 00:01:32,280 of my holdings plus my cash as well. 32 00:01:32,280 --> 00:01:35,220 Once I bought some stock, I should also be able to sell some stock. 33 00:01:35,220 --> 00:01:37,950 So I can go to the Sell tab here to say that, you know what? 34 00:01:37,950 --> 00:01:42,430 I would like to take my Apple stock and so three shares of it, for example, 35 00:01:42,430 --> 00:01:43,950 and click Sell, and all right. 36 00:01:43,950 --> 00:01:45,958 Now I have two shares of Apple left. 37 00:01:45,958 --> 00:01:48,000 And it looks like the value of the stock of Apple 38 00:01:48,000 --> 00:01:50,860 had gone up in the couple of seconds between when I bought the stock 39 00:01:50,860 --> 00:01:51,720 and when I sold it. 40 00:01:51,720 --> 00:01:56,010 So I now have a total value of $10,000.25 right now. 41 00:01:56,010 --> 00:01:58,470 And I should also be able to access my history where 42 00:01:58,470 --> 00:02:01,200 if I go to the History tab, I can see a history of all 43 00:02:01,200 --> 00:02:05,490 of the actions or the transactions that I've made while logged into my account. 44 00:02:05,490 --> 00:02:07,500 So I've bought five shares of Apple here, 45 00:02:07,500 --> 00:02:11,070 and then I sold three shares of Apple at a slightly different price 46 00:02:11,070 --> 00:02:13,320 at this particular time stamp. 47 00:02:13,320 --> 00:02:15,840 Ultimately, that's what CS50 Finance is going to be, 48 00:02:15,840 --> 00:02:18,300 and it's going to be different for every user that logs in. 49 00:02:18,300 --> 00:02:21,630 Every user that logs in will have their own portfolio of stocks, 50 00:02:21,630 --> 00:02:24,150 their own history of transactions, and the ability 51 00:02:24,150 --> 00:02:27,810 to buy and sell the stocks that they want to buy and sell using the cash 52 00:02:27,810 --> 00:02:31,920 that they have on hand inside of their CS50 Finance account. 53 00:02:31,920 --> 00:02:35,310 So that's ultimately what we're going to be creating in CS50 Finance. 54 00:02:35,310 --> 00:02:37,910 And let's now walk through the distribution code, the code 55 00:02:37,910 --> 00:02:39,660 that we're going to give you such that you 56 00:02:39,660 --> 00:02:42,990 can begin to add to this in order to implement the features of this web 57 00:02:42,990 --> 00:02:45,570 application. 58 00:02:45,570 --> 00:02:47,970 So I'll go ahead and go into CS50 IDE. 59 00:02:47,970 --> 00:02:51,420 And let's take a look at the distribution code for CS50 Finance. 60 00:02:51,420 --> 00:02:54,620 Inside of the finance directory, the first file we'll take a look at 61 00:02:54,620 --> 00:02:57,300 is this file called application.py, the main file 62 00:02:57,300 --> 00:03:00,570 that Flask is going to run when it's running your web application. 63 00:03:00,570 --> 00:03:03,180 Now, application.py is pretty long, but we'll try and distill 64 00:03:03,180 --> 00:03:04,740 the most important parts of it. 65 00:03:04,740 --> 00:03:07,682 At the beginning, we're just importing a number of different modules 66 00:03:07,682 --> 00:03:09,390 that we're going to prove useful as we go 67 00:03:09,390 --> 00:03:10,973 about working on this web application. 68 00:03:10,973 --> 00:03:13,390 And then we're configuring a number of different settings. 69 00:03:13,390 --> 00:03:15,300 No need to worry too much about the settings. 70 00:03:15,300 --> 00:03:18,480 The interesting part of the application comes around here, 71 00:03:18,480 --> 00:03:20,850 where first we're giving ourselves access 72 00:03:20,850 --> 00:03:26,670 to a database, a SQLite database, stored in a file called finance.db. 73 00:03:26,670 --> 00:03:29,460 Ultimately, this web application, CS50 Finance, 74 00:03:29,460 --> 00:03:32,850 is going to use paradigm that's very common in web applications called 75 00:03:32,850 --> 00:03:37,380 Model View Controller or MVC where we separate the three parts of our web 76 00:03:37,380 --> 00:03:40,987 application, the model, which has to do with our data of the tables 77 00:03:40,987 --> 00:03:44,070 and the rows that are inside of our database that are keeping track of all 78 00:03:44,070 --> 00:03:48,340 the users, how much cash they have, what stocks they currently own, for example. 79 00:03:48,340 --> 00:03:49,440 And that's the model. 80 00:03:49,440 --> 00:03:51,810 Then there's the V, the View, which determines 81 00:03:51,810 --> 00:03:53,640 what it is the user actually sees. 82 00:03:53,640 --> 00:03:57,300 These are the templates, the HTML files that display forms for the user 83 00:03:57,300 --> 00:04:00,240 to fill out, the display tables that show all the stocks the user 84 00:04:00,240 --> 00:04:01,380 currently owns. 85 00:04:01,380 --> 00:04:04,120 And then finally, C stands for the Controller, 86 00:04:04,120 --> 00:04:05,850 which is what application.py is. 87 00:04:05,850 --> 00:04:09,300 The logic that connects the model and the view together. 88 00:04:09,300 --> 00:04:12,480 Your controller, in other words, your application.py code, 89 00:04:12,480 --> 00:04:17,070 is going to make database queries to finance.db by running SQL queries, 90 00:04:17,070 --> 00:04:19,940 whether those be selects or updates or inserts. 91 00:04:19,940 --> 00:04:21,779 You're going to use that data then in order 92 00:04:21,779 --> 00:04:24,690 to pass that information to a template, to the view, 93 00:04:24,690 --> 00:04:26,970 in order to determine what it is the user is actually 94 00:04:26,970 --> 00:04:31,630 going to see when they try to buy a stock or sell a stock, for example. 95 00:04:31,630 --> 00:04:35,370 And so we'll see in a moment how this application uses this MVC paradigm, 96 00:04:35,370 --> 00:04:39,150 separating the model and the view and the controller to make sure everything 97 00:04:39,150 --> 00:04:42,130 is very cleanly organized into a number of different files. 98 00:04:42,130 --> 00:04:46,510 So db here, this just represents the database that we're going to use. 99 00:04:46,510 --> 00:04:48,640 We're also going to need an API key. 100 00:04:48,640 --> 00:04:50,400 So we're going to take advantage of an API 101 00:04:50,400 --> 00:04:53,490 that is going to allow us to make queries to another website 102 00:04:53,490 --> 00:04:56,070 in order to request real time stock data. 103 00:04:56,070 --> 00:04:59,550 So you're ultimately going to need to request your own API key in order 104 00:04:59,550 --> 00:05:03,660 to be able to use that API key when you're making requests to get access 105 00:05:03,660 --> 00:05:05,940 to that stock information. 106 00:05:05,940 --> 00:05:08,247 Beneath all of that begin the routes. 107 00:05:08,247 --> 00:05:10,830 And this is what you're probably more familiar with from Flask 108 00:05:10,830 --> 00:05:13,290 where we say when you go to this particular route 109 00:05:13,290 --> 00:05:17,900 as by typing in your website slash or slash buy or slash sell 110 00:05:17,900 --> 00:05:21,150 that this function is the function that should run. 111 00:05:21,150 --> 00:05:23,558 So what we see here is the index function, 112 00:05:23,558 --> 00:05:25,350 which is going to be the function that gets 113 00:05:25,350 --> 00:05:30,990 run when the user tries to load the main page of the website after logging in. 114 00:05:30,990 --> 00:05:35,310 Notice that this login required lineup here is what we call a decorator. 115 00:05:35,310 --> 00:05:38,340 And by putting login required above the index function, 116 00:05:38,340 --> 00:05:41,460 we're saying that the user must be logged in in order 117 00:05:41,460 --> 00:05:44,250 to actually see this index page. 118 00:05:44,250 --> 00:05:47,500 More on what it means to be logged in in just a moment. 119 00:05:47,500 --> 00:05:50,850 So when a user visits the slash route and they're logged in, 120 00:05:50,850 --> 00:05:53,250 the index page is going to run, which should ultimately, 121 00:05:53,250 --> 00:05:56,490 as this comment indicates, show a portfolio of all the stocks 122 00:05:56,490 --> 00:05:58,410 the user currently owns, like that table that 123 00:05:58,410 --> 00:06:02,040 shows which stocks the user currently owns and how much each of those stocks 124 00:06:02,040 --> 00:06:02,957 is worth. 125 00:06:02,957 --> 00:06:05,040 Of course, so far we haven't implemented that yet, 126 00:06:05,040 --> 00:06:07,770 and that's going to be up to you, so we've for now just returned 127 00:06:07,770 --> 00:06:11,910 an apology, which is just going to display a to do message to the user 128 00:06:11,910 --> 00:06:13,800 if they ever try to visit this page. 129 00:06:13,800 --> 00:06:16,110 And you can use the apology function in order 130 00:06:16,110 --> 00:06:18,630 to return some sort of error message in general. 131 00:06:18,630 --> 00:06:22,140 If something goes wrong in your web application, feel free to use apology, 132 00:06:22,140 --> 00:06:24,600 and we'll see how we might use that in a moment. 133 00:06:24,600 --> 00:06:27,430 After the index route, if we scroll down a little bit, 134 00:06:27,430 --> 00:06:30,630 we see the buy route, also left as a to do for you to do. 135 00:06:30,630 --> 00:06:32,340 But this is going to be the function that 136 00:06:32,340 --> 00:06:36,150 handles displaying a form for the user to type in what stock they want to buy 137 00:06:36,150 --> 00:06:39,800 and also handling the logic of actually purchasing a stock. 138 00:06:39,800 --> 00:06:40,737 After that is history. 139 00:06:40,737 --> 00:06:43,820 This is where you're going to display the history of all the transactions. 140 00:06:43,820 --> 00:06:45,830 Again, left up to you to do. 141 00:06:45,830 --> 00:06:47,360 Then is log in. 142 00:06:47,360 --> 00:06:50,075 And log in is one that we've actually implemented for you. 143 00:06:50,075 --> 00:06:51,950 So if you're looking for some logic as to how 144 00:06:51,950 --> 00:06:54,410 you might go about actually writing some Flask code, 145 00:06:54,410 --> 00:06:56,690 take a moment to look at the log in function 146 00:06:56,690 --> 00:06:58,550 to see how it is that it works. 147 00:06:58,550 --> 00:07:02,450 In particular, notice that it accepts two different request methods, 148 00:07:02,450 --> 00:07:04,040 GET and POST. 149 00:07:04,040 --> 00:07:07,970 Recall the GET is generally used when I just want to get a web page, 150 00:07:07,970 --> 00:07:12,930 and POST is more commonly used if I want to submit data via a form, for example. 151 00:07:12,930 --> 00:07:17,120 So if I request the log in route using the GET request method, what I'm saying 152 00:07:17,120 --> 00:07:20,240 is I would just like to get the page where I can type in my username 153 00:07:20,240 --> 00:07:22,310 and password to be able to log in. 154 00:07:22,310 --> 00:07:24,920 Meanwhile, once I actually type in my username and password 155 00:07:24,920 --> 00:07:29,030 and click the Log In button, then I'm going to hit the log in route again 156 00:07:29,030 --> 00:07:32,270 but this time using a request method of POST, meaning I'm 157 00:07:32,270 --> 00:07:35,870 sending data, my username and password, to the log in route. 158 00:07:35,870 --> 00:07:40,020 And then you should actually handle the logic of logging the user in. 159 00:07:40,020 --> 00:07:41,750 So how does this log in function work? 160 00:07:41,750 --> 00:07:45,750 Well, first we say if the request method is POST, in other words, 161 00:07:45,750 --> 00:07:49,550 if the user was actually trying to submit some data via this forum 162 00:07:49,550 --> 00:07:51,968 to the log in route, well, let's try and log the user in. 163 00:07:51,968 --> 00:07:54,260 The first thing that we're probably going to want to do 164 00:07:54,260 --> 00:07:56,000 is check for error conditions. 165 00:07:56,000 --> 00:08:01,940 So you see that here we first check if not request.form.get username. 166 00:08:01,940 --> 00:08:04,800 Request.form is the form the user submitted. 167 00:08:04,800 --> 00:08:09,230 And if we try and get the input field that had a name of a username 168 00:08:09,230 --> 00:08:13,648 and we say if not that, we're saying if there was no user name typed in, 169 00:08:13,648 --> 00:08:15,440 well then, we're going to return an apology 170 00:08:15,440 --> 00:08:18,290 that you must provide a username, for example, because we 171 00:08:18,290 --> 00:08:22,110 need to make sure the user types in a username to be able to log in. 172 00:08:22,110 --> 00:08:24,270 Likewise, if the user didn't type in a password, 173 00:08:24,270 --> 00:08:26,020 then we're going to return an apology that 174 00:08:26,020 --> 00:08:29,020 says you must provide a password in order to be able to log in. 175 00:08:29,020 --> 00:08:32,309 So after we've confirmed that the user actually submitted a username 176 00:08:32,309 --> 00:08:34,830 and submitted a password, the next step is making sure 177 00:08:34,830 --> 00:08:37,058 that these are actually valid credentials. 178 00:08:37,058 --> 00:08:38,850 And in order to do that, now is where we're 179 00:08:38,850 --> 00:08:41,549 going to need to connect our controller to the model. 180 00:08:41,549 --> 00:08:46,350 We'll need application.py talk to the database, finance.db, to say, 181 00:08:46,350 --> 00:08:48,690 let's see if there's a user that has this username. 182 00:08:48,690 --> 00:08:50,490 And if there is a user with that username, 183 00:08:50,490 --> 00:08:52,323 let's make sure their credentials are valid, 184 00:08:52,323 --> 00:08:54,820 make sure the password is actually correct. 185 00:08:54,820 --> 00:08:56,580 So we'll look at the code to do that. 186 00:08:56,580 --> 00:09:01,380 Here we're using db.execute to say execute a particular line of SQL. 187 00:09:01,380 --> 00:09:03,510 In other words, execute a SQL query. 188 00:09:03,510 --> 00:09:06,030 The query that we're going to execute is this. 189 00:09:06,030 --> 00:09:09,390 Select star from users where username equals 190 00:09:09,390 --> 00:09:12,960 and then colon user name means this is a placeholder for a value 191 00:09:12,960 --> 00:09:15,630 that we're going to plug in dynamically, because we don't 192 00:09:15,630 --> 00:09:17,550 know in advance what the username is. 193 00:09:17,550 --> 00:09:21,280 It's going to be whatever the user typed into the form, for example. 194 00:09:21,280 --> 00:09:22,800 So we're selecting from users. 195 00:09:22,800 --> 00:09:25,500 The user that has this particular user in it. 196 00:09:25,500 --> 00:09:26,430 What username? 197 00:09:26,430 --> 00:09:28,860 User name here corresponds to that placeholder. 198 00:09:28,860 --> 00:09:31,470 Well, it's whatever the user submitted using the form. 199 00:09:31,470 --> 00:09:34,740 Request.form.get username means take the form, 200 00:09:34,740 --> 00:09:37,140 get what they typed into the username field. 201 00:09:37,140 --> 00:09:39,690 And that's the row we're going to try and get back. 202 00:09:39,690 --> 00:09:42,150 What we get back whenever we run a select query 203 00:09:42,150 --> 00:09:45,120 is a list of all of the matching rows. 204 00:09:45,120 --> 00:09:49,200 And so if the length of the rows is not one, 205 00:09:49,200 --> 00:09:51,300 well, that means no user came back. 206 00:09:51,300 --> 00:09:53,310 Because in theory, the username is unique. 207 00:09:53,310 --> 00:09:55,470 So either 0 rows are going to come back, meaning 208 00:09:55,470 --> 00:09:59,190 there is no user with that username, or one row will come back, 209 00:09:59,190 --> 00:10:01,080 meaning there is a user with that username, 210 00:10:01,080 --> 00:10:03,040 and that's the row that was selected. 211 00:10:03,040 --> 00:10:07,780 So if the length of rows is not one, that means the username doesn't exist. 212 00:10:07,780 --> 00:10:09,123 And what else could go wrong? 213 00:10:09,123 --> 00:10:11,040 Well, there's a function called check password 214 00:10:11,040 --> 00:10:15,840 hash that is going to check to make sure that this password corresponds 215 00:10:15,840 --> 00:10:20,080 to this particular hash value that stored in the database. 216 00:10:20,080 --> 00:10:22,030 And what exactly is that hash value? 217 00:10:22,030 --> 00:10:25,380 Well, it would be insecure if we stored in our database 218 00:10:25,380 --> 00:10:27,990 the actual passwords of all of the users. 219 00:10:27,990 --> 00:10:30,720 Because if our database were ever compromised, then 220 00:10:30,720 --> 00:10:32,940 suddenly an adversary would have access to all 221 00:10:32,940 --> 00:10:37,050 of the passwords of all of the users that are using our website. 222 00:10:37,050 --> 00:10:39,480 So instead what most websites will do nowadays is 223 00:10:39,480 --> 00:10:43,410 rather than store the password, they'll store a hash of that password. 224 00:10:43,410 --> 00:10:47,070 They'll run a hash function on the password and get a hash value. 225 00:10:47,070 --> 00:10:50,460 And then when someone tries to log in, the application 226 00:10:50,460 --> 00:10:53,100 will again try to hash the password that was typed in 227 00:10:53,100 --> 00:10:56,910 and check to make sure that the password hashes match up. 228 00:10:56,910 --> 00:10:59,112 If the password hashes match up, it's pretty likely 229 00:10:59,112 --> 00:11:02,070 that the user typed in the correct password, assuming the hash function 230 00:11:02,070 --> 00:11:05,180 is good enough, and as a result, we can sign the user in. 231 00:11:05,180 --> 00:11:07,950 So your database should not store actual passwords. 232 00:11:07,950 --> 00:11:11,880 It should only store the hash of the user's password instead. 233 00:11:11,880 --> 00:11:15,310 So if we check the password hash and the password hashes don't match, 234 00:11:15,310 --> 00:11:20,000 then we're going to apologize and say, sorry, invalid username or password. 235 00:11:20,000 --> 00:11:23,130 So all right, the user has now typed in their username and password, 236 00:11:23,130 --> 00:11:25,620 and we've confirmed that the username exists 237 00:11:25,620 --> 00:11:27,670 and that the password is correct. 238 00:11:27,670 --> 00:11:28,690 So what next? 239 00:11:28,690 --> 00:11:30,180 Well, now we log the user in. 240 00:11:30,180 --> 00:11:34,890 And to log the user in, we're going to take advantage again of Flask sessions 241 00:11:34,890 --> 00:11:39,030 that allow us to store data associated with the user's current interaction 242 00:11:39,030 --> 00:11:40,510 with this website. 243 00:11:40,510 --> 00:11:43,170 And so we are going to store inside of session 244 00:11:43,170 --> 00:11:46,380 square bracket user ID a particular value. 245 00:11:46,380 --> 00:11:48,870 We're going to store the current user's ID. 246 00:11:48,870 --> 00:11:52,240 In other words, the ID of the user that has just logged in. 247 00:11:52,240 --> 00:11:53,360 How do we get that? 248 00:11:53,360 --> 00:11:58,260 Well, recall that rows was that list of rows that came back from our database 249 00:11:58,260 --> 00:12:00,270 representing all of the users. 250 00:12:00,270 --> 00:12:03,510 If only one row came back, well, the first row 251 00:12:03,510 --> 00:12:06,390 represents row square bracket 0. 252 00:12:06,390 --> 00:12:10,710 So rows square brackets 0 will get us the first and only row representing 253 00:12:10,710 --> 00:12:11,710 that user. 254 00:12:11,710 --> 00:12:16,200 And once I have a row, if I want to access a particular column in that row, 255 00:12:16,200 --> 00:12:20,220 I'll need to use square brackets again and say, all right, take the first row 256 00:12:20,220 --> 00:12:24,490 and get the value of the ID column for that row. 257 00:12:24,490 --> 00:12:27,780 So that's what rows square brackets 0 square bracket ID means. 258 00:12:27,780 --> 00:12:30,420 And you'll see this syntax a fair bit in CS50 Finance where 259 00:12:30,420 --> 00:12:33,540 we say take a list of rows, get me the first row, 260 00:12:33,540 --> 00:12:37,690 and get me the value of the ID column in that first row. 261 00:12:37,690 --> 00:12:40,130 Finally after the user is logged in, we're 262 00:12:40,130 --> 00:12:43,170 going to go ahead and redirect them back to slash, which 263 00:12:43,170 --> 00:12:45,900 is just that indexed route as before. 264 00:12:45,900 --> 00:12:48,720 So long story short, that's what happens when the user tries 265 00:12:48,720 --> 00:12:50,700 to log in to this website. 266 00:12:50,700 --> 00:12:53,640 We're going to basically check to make sure their username 267 00:12:53,640 --> 00:12:57,720 and password are correct and then redirect them back to the index page. 268 00:12:57,720 --> 00:13:00,710 That's what happens if the user submits via POST. 269 00:13:00,710 --> 00:13:03,630 But what happens if they just try to get the login page? 270 00:13:03,630 --> 00:13:07,290 Remember that all of this logic was under this if condition. 271 00:13:07,290 --> 00:13:10,350 If request.method equals post, that means 272 00:13:10,350 --> 00:13:12,330 the user submitted the log in form. 273 00:13:12,330 --> 00:13:15,270 But otherwise, in the else case, well, the user 274 00:13:15,270 --> 00:13:17,790 should just try to get the log in form. 275 00:13:17,790 --> 00:13:20,610 So we should just display the log in form to the user such 276 00:13:20,610 --> 00:13:24,120 that they can type in their username and password and then submit the form. 277 00:13:24,120 --> 00:13:27,747 So here we're just going to render a template called login.html. 278 00:13:27,747 --> 00:13:29,580 And here's the third part of that model view 279 00:13:29,580 --> 00:13:34,320 controller idea, the view, the HTML page the user actually sees. 280 00:13:34,320 --> 00:13:37,810 If we go into templates, you'll see that we have a basic layout page that just 281 00:13:37,810 --> 00:13:39,810 defines what the website looks like. 282 00:13:39,810 --> 00:13:45,270 But what we're ultimately loading is login.html, which extends layout.html, 283 00:13:45,270 --> 00:13:48,990 says that the title of this page should be Log In, and then 284 00:13:48,990 --> 00:13:51,750 in the main section of the page, we have this form 285 00:13:51,750 --> 00:13:55,320 where when you submit the form you're taken to the slash login route 286 00:13:55,320 --> 00:13:57,660 using the request method POST. 287 00:13:57,660 --> 00:14:00,310 And inside of this, we have a couple of different input fields. 288 00:14:00,310 --> 00:14:03,120 Here's an input field that has a name of username that 289 00:14:03,120 --> 00:14:05,380 asks the user to type in a username. 290 00:14:05,380 --> 00:14:07,590 And here's an input field with the name of password 291 00:14:07,590 --> 00:14:09,810 that asks the user to type in their password. 292 00:14:09,810 --> 00:14:12,060 And then finally, at the bottom is a button 293 00:14:12,060 --> 00:14:16,060 that says log in that ultimately lets the user log in. 294 00:14:16,060 --> 00:14:17,970 So that's how all these pieces work together. 295 00:14:17,970 --> 00:14:20,910 You first get this page, login.html. 296 00:14:20,910 --> 00:14:24,075 Then when you click the Log In button, you're taken back to the slash log 297 00:14:24,075 --> 00:14:27,210 in route using the POST request method and then 298 00:14:27,210 --> 00:14:29,730 that's when the logic of logging the user in gets handled, 299 00:14:29,730 --> 00:14:34,180 and the user is finally redirected back to a different route. 300 00:14:34,180 --> 00:14:37,838 After log in incomes log out, where we just clear the session. 301 00:14:37,838 --> 00:14:40,630 And after that comes quote, which again, you're going to implement, 302 00:14:40,630 --> 00:14:42,910 that lets the user look up a stock quote. 303 00:14:42,910 --> 00:14:44,920 And then register, which you'll also implement 304 00:14:44,920 --> 00:14:47,230 to let the user register for a page. 305 00:14:47,230 --> 00:14:50,830 And then sell, which will let a user sell stocks as well. 306 00:14:50,830 --> 00:14:53,080 At the bottom, we have some error handling conditions. 307 00:14:53,080 --> 00:14:54,700 No need to worry too much about that. 308 00:14:54,700 --> 00:14:56,860 But that's what application.py contains. 309 00:14:56,860 --> 00:14:59,740 A number of different routes that accept various different request 310 00:14:59,740 --> 00:15:03,460 methods that you can ultimately use in order to handle logging in 311 00:15:03,460 --> 00:15:07,280 and registering and buying and selling of various different stocks. 312 00:15:07,280 --> 00:15:09,820 So let's now look at the pieces of this application 313 00:15:09,820 --> 00:15:12,550 that you are going to implement for CS50 Finance. 314 00:15:12,550 --> 00:15:15,970 You'll first implement register to let users register. 315 00:15:15,970 --> 00:15:19,030 Then you'll implement quote to let users look up a stock quote 316 00:15:19,030 --> 00:15:21,220 and look up the current value of a stock. 317 00:15:21,220 --> 00:15:24,100 Then you'll implement buy to let users buy stocks. 318 00:15:24,100 --> 00:15:27,610 Once users are able to buy stocks, they should be able to see all of the stocks 319 00:15:27,610 --> 00:15:28,540 that they've bought. 320 00:15:28,540 --> 00:15:30,970 So you'll next implement the index function, 321 00:15:30,970 --> 00:15:34,630 which will display that table of all the stocks the user currently owns. 322 00:15:34,630 --> 00:15:38,050 Next is sell, which will let users sell any number of the stock 323 00:15:38,050 --> 00:15:39,108 that they currently own. 324 00:15:39,108 --> 00:15:40,900 Then finally, give them the ability to look 325 00:15:40,900 --> 00:15:43,270 at the history of all the transactions they've made. 326 00:15:43,270 --> 00:15:45,730 And finally, add a personal touch of your own, 327 00:15:45,730 --> 00:15:50,800 some additional feature of your choosing to add to the CS50 Finance application. 328 00:15:50,800 --> 00:15:53,380 So let's look at these steps one by one, beginning 329 00:15:53,380 --> 00:15:56,410 with register, which will allow users to register 330 00:15:56,410 --> 00:15:58,990 for a new account on your application. 331 00:15:58,990 --> 00:16:00,350 How is this going to work? 332 00:16:00,350 --> 00:16:04,570 Well, when requested via GET, in other words, if the request method is GET, 333 00:16:04,570 --> 00:16:09,000 you should just display a form that lets the user register for a new account. 334 00:16:09,000 --> 00:16:12,802 And it's probably going to look pretty similar to login.html, 335 00:16:12,802 --> 00:16:15,760 that form that users can use in order to log in to their account, where 336 00:16:15,760 --> 00:16:19,360 they just typed in the username and password and clicked the Log In button. 337 00:16:19,360 --> 00:16:21,520 Your register form is going to look very similar, 338 00:16:21,520 --> 00:16:24,340 except in addition to just a username and password field, 339 00:16:24,340 --> 00:16:27,970 you should probably also add a password confirmation field, someplace 340 00:16:27,970 --> 00:16:30,040 where the user types in their password again 341 00:16:30,040 --> 00:16:32,560 to make sure the passwords match up before you actually 342 00:16:32,560 --> 00:16:34,660 let the user register. 343 00:16:34,660 --> 00:16:37,190 If the request method is POST, in other words, 344 00:16:37,190 --> 00:16:40,180 if the user submitted the register form, then you 345 00:16:40,180 --> 00:16:44,380 should actually register the user as by inserting the new user into the users 346 00:16:44,380 --> 00:16:45,370 table. 347 00:16:45,370 --> 00:16:48,100 Of course, you'll want to do some error checking here first. 348 00:16:48,100 --> 00:16:52,150 If the user didn't type in a username or if the username they typed in 349 00:16:52,150 --> 00:16:55,660 is already the username of another user in your database 350 00:16:55,660 --> 00:16:59,740 or if the password confirmation field doesn't match the password field, then 351 00:16:59,740 --> 00:17:03,190 rather than registering the user, you should just display an apology and say, 352 00:17:03,190 --> 00:17:07,869 sorry, the username is already taken or, sorry, your passwords didn't match. 353 00:17:07,869 --> 00:17:11,200 So be sure to check for those invalid inputs as well. 354 00:17:11,200 --> 00:17:14,140 And then be sure to hash that user's password. 355 00:17:14,140 --> 00:17:17,109 Remember that we don't want to store the actual password of the user 356 00:17:17,109 --> 00:17:18,440 inside of the database. 357 00:17:18,440 --> 00:17:20,500 We should instead hash the user's password 358 00:17:20,500 --> 00:17:24,040 and just store that hash in the database instead. 359 00:17:24,040 --> 00:17:27,440 Once you do that, users should be able to register for an account. 360 00:17:27,440 --> 00:17:29,990 And because we have already implemented log in for you, 361 00:17:29,990 --> 00:17:31,870 you should be able to log into an account 362 00:17:31,870 --> 00:17:36,310 and see an index page that for now will just say to do, but log in and log out 363 00:17:36,310 --> 00:17:37,930 have already been implemented for you. 364 00:17:37,930 --> 00:17:41,470 So you should be able to test register right away after you've implemented 365 00:17:41,470 --> 00:17:44,890 these two features, the ability to get the page and display a form 366 00:17:44,890 --> 00:17:47,200 and when the form is submitted actually take care 367 00:17:47,200 --> 00:17:51,160 of the logic of registering the user. 368 00:17:51,160 --> 00:17:53,140 After register, you're going to implement 369 00:17:53,140 --> 00:17:57,310 quote, which is going to let a user look up a particular stock quote. 370 00:17:57,310 --> 00:17:59,800 And just like register and just like log in, 371 00:17:59,800 --> 00:18:02,650 quote should accept two different request methods. 372 00:18:02,650 --> 00:18:05,260 If I tried to get the quote route, you should 373 00:18:05,260 --> 00:18:07,872 display a form that lets me request a stock quote 374 00:18:07,872 --> 00:18:09,080 that I would like to look at. 375 00:18:09,080 --> 00:18:10,997 For example, this is the form where I typed in 376 00:18:10,997 --> 00:18:14,410 before Apple to look up the current price of a share of Apple stock, 377 00:18:14,410 --> 00:18:15,700 for example. 378 00:18:15,700 --> 00:18:19,540 But when I submit that form and the form is therefore submitted by a POST, 379 00:18:19,540 --> 00:18:24,470 you should look up that stock symbol as by calling the lookup function. 380 00:18:24,470 --> 00:18:26,920 So inside of the finance distribution code 381 00:18:26,920 --> 00:18:30,160 that we've given you, inside of a file called helpers.py, 382 00:18:30,160 --> 00:18:33,430 we have implemented for you a function called lookup. 383 00:18:33,430 --> 00:18:36,020 Lookup takes as its argument a stock symbol. 384 00:18:36,020 --> 00:18:39,190 So if you call lookup and then in parentheses Apple, for example, 385 00:18:39,190 --> 00:18:43,450 A-A-P-L, that will take care of the logic of querying the API, 386 00:18:43,450 --> 00:18:45,550 looking up the current value of Apple stock, 387 00:18:45,550 --> 00:18:49,033 and giving you back that data in the form of a Python dictionary. 388 00:18:49,033 --> 00:18:50,950 So you might want to print out that dictionary 389 00:18:50,950 --> 00:18:53,680 to see what data actually comes back to you 390 00:18:53,680 --> 00:18:55,600 after you've looked up the value of a stock 391 00:18:55,600 --> 00:18:58,900 so that you know how to actually use that information to display 392 00:18:58,900 --> 00:19:00,880 the current value of the stock. 393 00:19:00,880 --> 00:19:04,600 Of course, if the user types in an invalid symbol that doesn't actually 394 00:19:04,600 --> 00:19:07,390 represent a valid stock, lookup is not going 395 00:19:07,390 --> 00:19:10,180 to be able to determine what stock you're looking for. 396 00:19:10,180 --> 00:19:13,990 So lookup in this case is just going to return the Python value none, 397 00:19:13,990 --> 00:19:15,880 meaning no results came back. 398 00:19:15,880 --> 00:19:18,520 So you'll probably want to handle that case as well. 399 00:19:18,520 --> 00:19:20,530 In the event that lookup returns none, you'll 400 00:19:20,530 --> 00:19:22,900 likely want to display some sort of error message 401 00:19:22,900 --> 00:19:25,600 to indicate that the stock that was attempted to be looked up 402 00:19:25,600 --> 00:19:28,570 did not actually exist. 403 00:19:28,570 --> 00:19:31,630 After users are able to look up the value of a stock, 404 00:19:31,630 --> 00:19:35,720 the next step is to let users actually buy some shares of that stock. 405 00:19:35,720 --> 00:19:37,790 So how might you go about doing that? 406 00:19:37,790 --> 00:19:40,390 Well, again, you're going to display some sort of form that 407 00:19:40,390 --> 00:19:43,120 will let users type in the stock they would like to buy 408 00:19:43,120 --> 00:19:46,300 and the number of shares of that stock they would like to buy. 409 00:19:46,300 --> 00:19:49,150 This is going to be very similar to quote except in addition 410 00:19:49,150 --> 00:19:51,250 to just specifying the name of the stock, 411 00:19:51,250 --> 00:19:56,010 they'll also specify how many shares of that stock they would like to purchase. 412 00:19:56,010 --> 00:19:59,460 When the user submits that form, there's some logic that you'll need to do. 413 00:19:59,460 --> 00:20:02,580 You'll probably need to query the database for the current user 414 00:20:02,580 --> 00:20:05,400 to make sure the user can actually afford the current stock, 415 00:20:05,400 --> 00:20:07,920 checking the current amount of cash that they have, 416 00:20:07,920 --> 00:20:10,380 which is stored in the user's table, and comparing 417 00:20:10,380 --> 00:20:14,010 that against the price of the stock, which you can look up using that lookup 418 00:20:14,010 --> 00:20:18,030 function, times the number of shares of that stock they would like to buy. 419 00:20:18,030 --> 00:20:21,592 And if the user is able to afford the stock and the stock actually exists, 420 00:20:21,592 --> 00:20:23,550 then you should actually take care of the logic 421 00:20:23,550 --> 00:20:26,130 of purchasing that stock for them. 422 00:20:26,130 --> 00:20:30,690 And to do that, you'll likely need to add one or more tables to the finance 423 00:20:30,690 --> 00:20:31,590 database. 424 00:20:31,590 --> 00:20:35,100 We've given you inside the finance database a table called users, 425 00:20:35,100 --> 00:20:38,640 but you'll likely want to be able to use other information inside of that table 426 00:20:38,640 --> 00:20:42,960 as well, storing not just users, but other information about the stocks 427 00:20:42,960 --> 00:20:44,730 that the user has purchased. 428 00:20:44,730 --> 00:20:46,970 Let's take a look at this in a little more detail 429 00:20:46,970 --> 00:20:49,530 by going back into CS50 IDE. 430 00:20:49,530 --> 00:20:54,030 Recall that inside a finance directory we had this file application.py, 431 00:20:54,030 --> 00:20:59,452 but we also had this file finance.db, which represents a SQLite database. 432 00:20:59,452 --> 00:21:01,410 So I'll go ahead and close these template files 433 00:21:01,410 --> 00:21:03,420 and make my terminal window a little bigger. 434 00:21:03,420 --> 00:21:09,390 If I typed SQLite3 followed by finance.db, what I'll be taken to 435 00:21:09,390 --> 00:21:14,860 is SQLite prompt that lets me actually run SQL queries on this database. 436 00:21:14,860 --> 00:21:19,230 So for example, I could say select star from users 437 00:21:19,230 --> 00:21:22,660 to select all of the users that are currently inside of my database. 438 00:21:22,660 --> 00:21:26,280 Of course, nothing comes back right now, because no users are registered. 439 00:21:26,280 --> 00:21:29,310 But after you've attempted to register a new user using 440 00:21:29,310 --> 00:21:31,350 your implementation of register, you should 441 00:21:31,350 --> 00:21:35,100 be able to go into the SQLite prompt, type select star from users, 442 00:21:35,100 --> 00:21:38,490 and actually see the user and their password hash 443 00:21:38,490 --> 00:21:40,260 and also how much cash they have. 444 00:21:40,260 --> 00:21:44,630 Because if I run a command like dot schema users, 445 00:21:44,630 --> 00:21:48,120 this will show me how the users table was actually constructed. 446 00:21:48,120 --> 00:21:51,060 So here was the create table query that was run 447 00:21:51,060 --> 00:21:53,130 in order to generate that users table. 448 00:21:53,130 --> 00:21:56,610 So we created a table called Users and gave it a couple of fields. 449 00:21:56,610 --> 00:22:00,750 We give it an ID column, which is an integer, a username column, which 450 00:22:00,750 --> 00:22:03,780 is just some text, a hash of their password, 451 00:22:03,780 --> 00:22:07,480 which again, is some text, and then cash, which is some numeric value, 452 00:22:07,480 --> 00:22:11,850 which means it could be a decimal, with a default value of $10,000, 453 00:22:11,850 --> 00:22:13,120 for example. 454 00:22:13,120 --> 00:22:16,290 And so this was the create table query that we already ran 455 00:22:16,290 --> 00:22:18,360 to give you access to a users table. 456 00:22:18,360 --> 00:22:20,820 But you might want to create a new table of your own. 457 00:22:20,820 --> 00:22:23,820 And to do that, you can literally in the SQLite prompt type 458 00:22:23,820 --> 00:22:26,940 create table followed by the name of the table 459 00:22:26,940 --> 00:22:28,920 followed by in parentheses all of the columns 460 00:22:28,920 --> 00:22:31,920 that you would like to add to that table along with their types. 461 00:22:31,920 --> 00:22:34,200 So you should probably create one or more tables 462 00:22:34,200 --> 00:22:36,870 to represent all of the stocks that were created. 463 00:22:36,870 --> 00:22:39,090 But what those tables are and what the columns 464 00:22:39,090 --> 00:22:42,158 and what the types of those columns are are ultimately up to you. 465 00:22:42,158 --> 00:22:45,450 But you'll likely want to keep track of at least what the stock that was bought 466 00:22:45,450 --> 00:22:49,530 is, how many shares of that stock were bought, and who bought that stock. 467 00:22:49,530 --> 00:22:52,260 Because if a different user logs in at a different time, 468 00:22:52,260 --> 00:22:54,900 you'll likely want to make sure that every user only 469 00:22:54,900 --> 00:22:57,960 sees the stocks that they themselves have actually bought 470 00:22:57,960 --> 00:23:02,100 and not some other user's stock, for example. 471 00:23:02,100 --> 00:23:05,360 So that's buy that will allow users to be able to type in what stock 472 00:23:05,360 --> 00:23:08,420 they would like to purchase and actually purchase that stock. 473 00:23:08,420 --> 00:23:12,710 And once they do, you'll display the results by implementing index. 474 00:23:12,710 --> 00:23:14,600 Unlike several of the other functions, no 475 00:23:14,600 --> 00:23:17,050 need to worry about a request method of POST here. 476 00:23:17,050 --> 00:23:19,220 There is no form involved with index. 477 00:23:19,220 --> 00:23:22,820 All index is going to do is query data from the database 478 00:23:22,820 --> 00:23:25,910 and display data about all of the current stocks 479 00:23:25,910 --> 00:23:28,600 that the user that's currently logged in owns. 480 00:23:28,600 --> 00:23:31,730 So you'll display a table that shows the values of all the current user's 481 00:23:31,730 --> 00:23:34,740 stocks, how many shares of each of the stocks they have, 482 00:23:34,740 --> 00:23:38,210 as well as the total value of each of the holdings, which is just 483 00:23:38,210 --> 00:23:40,520 the current price of each stock multiplied 484 00:23:40,520 --> 00:23:42,920 by the number of shares of each stock. 485 00:23:42,920 --> 00:23:45,150 How are you going to get access to this data? 486 00:23:45,150 --> 00:23:47,690 Well, in the buy step, you likely already created 487 00:23:47,690 --> 00:23:50,960 one or more tables that represent all of the stocks 488 00:23:50,960 --> 00:23:53,880 that users own inside of your database. 489 00:23:53,880 --> 00:23:57,320 So you'll likely want to run some sort of query selecting 490 00:23:57,320 --> 00:24:01,220 from that table, all of the stocks that the currently logged in user owns. 491 00:24:01,220 --> 00:24:04,520 And recall that you can get access to the currently logged in user by taking 492 00:24:04,520 --> 00:24:08,570 a look at the value of session square bracket user ID 493 00:24:08,570 --> 00:24:11,030 to get who is currently logged in. 494 00:24:11,030 --> 00:24:12,860 Once you have all those stocks, you'll want 495 00:24:12,860 --> 00:24:16,070 to use the lookup function to look up the current price of each 496 00:24:16,070 --> 00:24:18,470 of those stocks, and then ultimately, you're 497 00:24:18,470 --> 00:24:22,190 going to want to display all of that data inside of some sort of template 498 00:24:22,190 --> 00:24:24,950 that you might call index.html, for example, 499 00:24:24,950 --> 00:24:27,650 but it could be called anything you want. 500 00:24:27,650 --> 00:24:31,250 After users have the ability to buy stocks and see what stocks they own, 501 00:24:31,250 --> 00:24:34,400 next up is sell, which is really just the opposite of buy. 502 00:24:34,400 --> 00:24:36,140 It'll, again, have two parts. 503 00:24:36,140 --> 00:24:38,570 When requested by a GET, you should just display 504 00:24:38,570 --> 00:24:42,380 a form that lets users indicate what stock they would like to sell 505 00:24:42,380 --> 00:24:45,260 and how many shares of that stock they would like to sell. 506 00:24:45,260 --> 00:24:47,540 And when the form is submitted via POST, you 507 00:24:47,540 --> 00:24:50,330 should sell that specified number of shares of stock. 508 00:24:50,330 --> 00:24:53,030 Again, there is some error conditioning you'll want to do here. 509 00:24:53,030 --> 00:24:56,600 You'll want to check to make sure the user actually owns that stock 510 00:24:56,600 --> 00:24:58,850 and they're not trying to sell more shares of stock 511 00:24:58,850 --> 00:25:00,840 than they currently own, for example. 512 00:25:00,840 --> 00:25:02,900 But if they are able to sell that stock, you 513 00:25:02,900 --> 00:25:05,120 should update the user's cash in order to add 514 00:25:05,120 --> 00:25:08,660 to it whatever number of shares of stock they're selling multiplied 515 00:25:08,660 --> 00:25:10,243 by the current value of the stock. 516 00:25:10,243 --> 00:25:12,410 And you can find out the current value of the stock, 517 00:25:12,410 --> 00:25:14,480 again, using that lookup function. 518 00:25:14,480 --> 00:25:17,240 And then you'll want to make sure to update any of your tables 519 00:25:17,240 --> 00:25:19,850 that are keeping track of how many shares of stock 520 00:25:19,850 --> 00:25:24,350 the user owns in order to indicate that they have sold some number of shares 521 00:25:24,350 --> 00:25:26,660 of that stock. 522 00:25:26,660 --> 00:25:29,030 After all of that, you can implement history, 523 00:25:29,030 --> 00:25:32,330 which will give you the ability to view all the history of transactions 524 00:25:32,330 --> 00:25:35,240 of all the times that stocks have been bought or sold. 525 00:25:35,240 --> 00:25:37,400 Depending on how you've constructed your tables, 526 00:25:37,400 --> 00:25:39,680 you might be able to get access to this information 527 00:25:39,680 --> 00:25:43,220 just by querying the table or tables you already have or you might need 528 00:25:43,220 --> 00:25:47,000 to create a new table that's keeping track of this information as well, such 529 00:25:47,000 --> 00:25:49,190 that when users are buying and selling stocks, 530 00:25:49,190 --> 00:25:51,710 you're keeping track of when that happened, 531 00:25:51,710 --> 00:25:55,220 you're keeping track of how many stocks were bought and sold, 532 00:25:55,220 --> 00:25:57,770 and what particular stock was bought or sold. 533 00:25:57,770 --> 00:26:01,370 But you'll display all of that just inside of a table, similar in spirit 534 00:26:01,370 --> 00:26:05,390 to the index table, but while the index table shows all of the stocks 535 00:26:05,390 --> 00:26:08,360 the user currently has, your history table will 536 00:26:08,360 --> 00:26:12,710 show all of the transactions that have ever happened for the user, row by row, 537 00:26:12,710 --> 00:26:17,030 every time the user has ever bought or sold a stock. 538 00:26:17,030 --> 00:26:19,730 Once you've done that, you'll have a working CS50 Finance. 539 00:26:19,730 --> 00:26:21,770 And the last step is just to add a little bit 540 00:26:21,770 --> 00:26:24,980 of a personal touch, some additional feature of your choosing. 541 00:26:24,980 --> 00:26:27,990 What you choose for the personal touch is entirely up to you. 542 00:26:27,990 --> 00:26:31,430 But possibilities include letting the user change their password 543 00:26:31,430 --> 00:26:36,140 or add more cash to their account or buy or sell stocks directly from the index 544 00:26:36,140 --> 00:26:39,950 page rather than needing to go to the Buy page or the Sell page, 545 00:26:39,950 --> 00:26:42,280 or adding password complexity requirements 546 00:26:42,280 --> 00:26:44,693 by making sure the passwords are a certain length 547 00:26:44,693 --> 00:26:46,610 or have a certain number of numbers or symbols 548 00:26:46,610 --> 00:26:48,400 or other characters, for example. 549 00:26:48,400 --> 00:26:50,150 And you can feel free to be creative here. 550 00:26:50,150 --> 00:26:53,150 Come up with any other personal touch or some combination 551 00:26:53,150 --> 00:26:56,690 of these various different features just to add a little something personal 552 00:26:56,690 --> 00:26:59,070 to your web application as well. 553 00:26:59,070 --> 00:27:01,610 After that, that will complete CS50 Finance 554 00:27:01,610 --> 00:27:04,640 and complete your creation of your very first web application 555 00:27:04,640 --> 00:27:07,730 that uses Flask and SQL and HTML in order 556 00:27:07,730 --> 00:27:10,130 to combine models and views and controllers 557 00:27:10,130 --> 00:27:13,280 to create a working web application that users can log in to, 558 00:27:13,280 --> 00:27:16,500 buy and sell stocks from, and interact with. 559 00:27:16,500 --> 00:27:17,000