BRIAN YU: Now that we have the ability to create dynamic web applications using Flask and to connect those applications to a SQLite database, let's put these ideas into practice by creating CS50 Finance. Ultimately what CS50 Finance is going to be is it's going to be a website where users can register and log in and buy and sell stocks. Let's take a look at what the finished product might look like. 

Once you've completed the application, what users should be able to do is they should be able to register for an account. So I could click here, Register, and type in my name along with a password and a confirmation of that password. And I'll register for an account. And all right. Here's what I see when I log in to CS50 Finance. I can first go to Quote, which lets me look up a stock quote. I could say, all right, how much does one share of Apple cost right now, for example, and click on the Quote button to see that, all right, a share of Apple right now costs $235.48, for example. 

If I wanted to buy some of Apple stock, for example, I could go into the Buy tab, type in Apple, and say let's go ahead and buy five shares of Apple and click Buy. And now I've bought that number of shares of Apple. And you'll see that on this index page, the main page when I log in to CS50 Finance, I'll see all of the stocks that I currently own organized in a table along with the number of shares of that stock that I own, the current price of that stock, which may have changed from when I bought that stock, the current value of all my stock holdings, how much cash I have on hand, which by default is going to be $10,000, and then the total value of all of my holdings plus my cash as well. 

Once I bought some stock, I should also be able to sell some stock. So I can go to the Sell tab here to say that, you know what? I would like to take my Apple stock and so three shares of it, for example, and click Sell, and all right. Now I have two shares of Apple left. And it looks like the value of the stock of Apple had gone up in the couple of seconds between when I bought the stock and when I sold it. So I now have a total value of $10,000.25 right now. 

And I should also be able to access my history where if I go to the History tab, I can see a history of all of the actions or the transactions that I've made while logged into my account. So I've bought five shares of Apple here, and then I sold three shares of Apple at a slightly different price at this particular time stamp. 

Ultimately, that's what CS50 Finance is going to be, and it's going to be different for every user that logs in. Every user that logs in will have their own portfolio of stocks, their own history of transactions, and the ability to buy and sell the stocks that they want to buy and sell using the cash that they have on hand inside of their CS50 Finance account. 

So that's ultimately what we're going to be creating in CS50 Finance. And let's now walk through the distribution code, the code that we're going to give you such that you can begin to add to this in order to implement the features of this web application. 

So I'll go ahead and go into CS50 IDE. And let's take a look at the distribution code for CS50 Finance. Inside of the finance directory, the first file we'll take a look at is this file called application.py, the main file that Flask is going to run when it's running your web application. 

Now, application.py is pretty long, but we'll try and distill the most important parts of it. At the beginning, we're just importing a number of different modules that we're going to prove useful as we go about working on this web application. And then we're configuring a number of different settings. No need to worry too much about the settings. The interesting part of the application comes around here, where first we're giving ourselves access to a database, a SQLite database, stored in a file called finance.db. 

Ultimately, this web application, CS50 Finance, is going to use paradigm that's very common in web applications called Model View Controller or MVC where we separate the three parts of our web application, the model, which has to do with our data of the tables and the rows that are inside of our database that are keeping track of all the users, how much cash they have, what stocks they currently own, for example. And that's the model. 

Then there's the V, the View, which determines what it is the user actually sees. These are the templates, the HTML files that display forms for the user to fill out, the display tables that show all the stocks the user currently owns. 

And then finally, C stands for the Controller, which is what application.py is. The logic that connects the model and the view together. Your controller, in other words, your application.py code, is going to make database queries to finance.db by running SQL queries, whether those be selects or updates or inserts. You're going to use that data then in order to pass that information to a template, to the view, in order to determine what it is the user is actually going to see when they try to buy a stock or sell a stock, for example. 

And so we'll see in a moment how this application uses this MVC paradigm, separating the model and the view and the controller to make sure everything is very cleanly organized into a number of different files. So db here, this just represents the database that we're going to use. 

We're also going to need an API key. So we're going to take advantage of an API that is going to allow us to make queries to another website in order to request real time stock data. So you're ultimately going to need to request your own API key in order to be able to use that API key when you're making requests to get access to that stock information. 

Beneath all of that begin the routes. And this is what you're probably more familiar with from Flask where we say when you go to this particular route as by typing in your website slash or slash buy or slash sell that this function is the function that should run. So what we see here is the index function, which is going to be the function that gets run when the user tries to load the main page of the website after logging in. 

Notice that this login required lineup here is what we call a decorator. And by putting login required above the index function, we're saying that the user must be logged in in order to actually see this index page. More on what it means to be logged in in just a moment. 

So when a user visits the slash route and they're logged in, the index page is going to run, which should ultimately, as this comment indicates, show a portfolio of all the stocks the user currently owns, like that table that shows which stocks the user currently owns and how much each of those stocks is worth. 

Of course, so far we haven't implemented that yet, and that's going to be up to you, so we've for now just returned an apology, which is just going to display a to do message to the user if they ever try to visit this page. And you can use the apology function in order to return some sort of error message in general. If something goes wrong in your web application, feel free to use apology, and we'll see how we might use that in a moment. 

After the index route, if we scroll down a little bit, we see the buy route, also left as a to do for you to do. But this is going to be the function that handles displaying a form for the user to type in what stock they want to buy and also handling the logic of actually purchasing a stock. After that is history. This is where you're going to display the history of all the transactions. Again, left up to you to do. 

Then is log in. And log in is one that we've actually implemented for you. So if you're looking for some logic as to how you might go about actually writing some Flask code, take a moment to look at the log in function to see how it is that it works. In particular, notice that it accepts two different request methods, GET and POST. Recall the GET is generally used when I just want to get a web page, and POST is more commonly used if I want to submit data via a form, for example. 

So if I request the log in route using the GET request method, what I'm saying is I would just like to get the page where I can type in my username and password to be able to log in. Meanwhile, once I actually type in my username and password and click the Log In button, then I'm going to hit the log in route again but this time using a request method of POST, meaning I'm sending data, my username and password, to the log in route. And then you should actually handle the logic of logging the user in. 

So how does this log in function work? Well, first we say if the request method is POST, in other words, if the user was actually trying to submit some data via this forum to the log in route, well, let's try and log the user in. The first thing that we're probably going to want to do is check for error conditions. So you see that here we first check if not request.form.get username. Request.form is the form the user submitted. 

And if we try and get the input field that had a name of a username and we say if not that, we're saying if there was no user name typed in, well then, we're going to return an apology that you must provide a username, for example, because we need to make sure the user types in a username to be able to log in. Likewise, if the user didn't type in a password, then we're going to return an apology that says you must provide a password in order to be able to log in. 

So after we've confirmed that the user actually submitted a username and submitted a password, the next step is making sure that these are actually valid credentials. And in order to do that, now is where we're going to need to connect our controller to the model. We'll need application.py talk to the database, finance.db, to say, let's see if there's a user that has this username. And if there is a user with that username, let's make sure their credentials are valid, make sure the password is actually correct. 

So we'll look at the code to do that. Here we're using db.execute to say execute a particular line of SQL. In other words, execute a SQL query. The query that we're going to execute is this. Select star from users where username equals and then colon user name means this is a placeholder for a value that we're going to plug in dynamically, because we don't know in advance what the username is. It's going to be whatever the user typed into the form, for example. 

So we're selecting from users. The user that has this particular user in it. What username? User name here corresponds to that placeholder. Well, it's whatever the user submitted using the form. Request.form.get username means take the form, get what they typed into the username field. And that's the row we're going to try and get back. 

What we get back whenever we run a select query is a list of all of the matching rows. And so if the length of the rows is not one, well, that means no user came back. Because in theory, the username is unique. So either 0 rows are going to come back, meaning there is no user with that username, or one row will come back, meaning there is a user with that username, and that's the row that was selected. So if the length of rows is not one, that means the username doesn't exist. 

And what else could go wrong? Well, there's a function called check password hash that is going to check to make sure that this password corresponds to this particular hash value that stored in the database. And what exactly is that hash value? Well, it would be insecure if we stored in our database the actual passwords of all of the users. Because if our database were ever compromised, then suddenly an adversary would have access to all of the passwords of all of the users that are using our website. 

So instead what most websites will do nowadays is rather than store the password, they'll store a hash of that password. They'll run a hash function on the password and get a hash value. And then when someone tries to log in, the application will again try to hash the password that was typed in and check to make sure that the password hashes match up. 

If the password hashes match up, it's pretty likely that the user typed in the correct password, assuming the hash function is good enough, and as a result, we can sign the user in. So your database should not store actual passwords. It should only store the hash of the user's password instead. So if we check the password hash and the password hashes don't match, then we're going to apologize and say, sorry, invalid username or password. 

So all right, the user has now typed in their username and password, and we've confirmed that the username exists and that the password is correct. So what next? Well, now we log the user in. And to log the user in, we're going to take advantage again of Flask sessions that allow us to store data associated with the user's current interaction with this website. 

And so we are going to store inside of session square bracket user ID a particular value. We're going to store the current user's ID. In other words, the ID of the user that has just logged in. How do we get that? Well, recall that rows was that list of rows that came back from our database representing all of the users. If only one row came back, well, the first row represents row square bracket 0. So rows square brackets 0 will get us the first and only row representing that user. 

And once I have a row, if I want to access a particular column in that row, I'll need to use square brackets again and say, all right, take the first row and get the value of the ID column for that row. So that's what rows square brackets 0 square bracket ID means. And you'll see this syntax a fair bit in CS50 Finance where we say take a list of rows, get me the first row, and get me the value of the ID column in that first row. 

Finally after the user is logged in, we're going to go ahead and redirect them back to slash, which is just that indexed route as before. So long story short, that's what happens when the user tries to log in to this website. We're going to basically check to make sure their username and password are correct and then redirect them back to the index page. That's what happens if the user submits via POST. But what happens if they just try to get the login page? 

Remember that all of this logic was under this if condition. If request.method equals post, that means the user submitted the log in form. But otherwise, in the else case, well, the user should just try to get the log in form. So we should just display the log in form to the user such that they can type in their username and password and then submit the form. So here we're just going to render a template called login.html. And here's the third part of that model view controller idea, the view, the HTML page the user actually sees. 

If we go into templates, you'll see that we have a basic layout page that just defines what the website looks like. But what we're ultimately loading is login.html, which extends layout.html, says that the title of this page should be Log In, and then in the main section of the page, we have this form where when you submit the form you're taken to the slash login route using the request method POST. 

And inside of this, we have a couple of different input fields. Here's an input field that has a name of username that asks the user to type in a username. And here's an input field with the name of password that asks the user to type in their password. And then finally, at the bottom is a button that says log in that ultimately lets the user log in. 

So that's how all these pieces work together. You first get this page, login.html. Then when you click the Log In button, you're taken back to the slash log in route using the POST request method and then that's when the logic of logging the user in gets handled, and the user is finally redirected back to a different route. 

After log in incomes log out, where we just clear the session. And after that comes quote, which again, you're going to implement, that lets the user look up a stock quote. And then register, which you'll also implement to let the user register for a page. And then sell, which will let a user sell stocks as well. 

At the bottom, we have some error handling conditions. No need to worry too much about that. But that's what application.py contains. A number of different routes that accept various different request methods that you can ultimately use in order to handle logging in and registering and buying and selling of various different stocks. 

So let's now look at the pieces of this application that you are going to implement for CS50 Finance. You'll first implement register to let users register. Then you'll implement quote to let users look up a stock quote and look up the current value of a stock. Then you'll implement buy to let users buy stocks. Once users are able to buy stocks, they should be able to see all of the stocks that they've bought. 

So you'll next implement the index function, which will display that table of all the stocks the user currently owns. Next is sell, which will let users sell any number of the stock that they currently own. Then finally, give them the ability to look at the history of all the transactions they've made. And finally, add a personal touch of your own, some additional feature of your choosing to add to the CS50 Finance application. 

So let's look at these steps one by one, beginning with register, which will allow users to register for a new account on your application. How is this going to work? Well, when requested via GET, in other words, if the request method is GET, you should just display a form that lets the user register for a new account. 

And it's probably going to look pretty similar to login.html, that form that users can use in order to log in to their account, where they just typed in the username and password and clicked the Log In button. Your register form is going to look very similar, except in addition to just a username and password field, you should probably also add a password confirmation field, someplace where the user types in their password again to make sure the passwords match up before you actually let the user register. 

If the request method is POST, in other words, if the user submitted the register form, then you should actually register the user as by inserting the new user into the users table. Of course, you'll want to do some error checking here first. If the user didn't type in a username or if the username they typed in is already the username of another user in your database or if the password confirmation field doesn't match the password field, then rather than registering the user, you should just display an apology and say, sorry, the username is already taken or, sorry, your passwords didn't match. So be sure to check for those invalid inputs as well. 

And then be sure to hash that user's password. Remember that we don't want to store the actual password of the user inside of the database. We should instead hash the user's password and just store that hash in the database instead. Once you do that, users should be able to register for an account. 

And because we have already implemented log in for you, you should be able to log into an account and see an index page that for now will just say to do, but log in and log out have already been implemented for you. So you should be able to test register right away after you've implemented these two features, the ability to get the page and display a form and when the form is submitted actually take care of the logic of registering the user. 

After register, you're going to implement quote, which is going to let a user look up a particular stock quote. And just like register and just like log in, quote should accept two different request methods. If I tried to get the quote route, you should display a form that lets me request a stock quote that I would like to look at. 

For example, this is the form where I typed in before Apple to look up the current price of a share of Apple stock, for example. But when I submit that form and the form is therefore submitted by a POST, you should look up that stock symbol as by calling the lookup function. So inside of the finance distribution code that we've given you, inside of a file called helpers.py, we have implemented for you a function called lookup. Lookup takes as its argument a stock symbol. 

So if you call lookup and then in parentheses Apple, for example, A-A-P-L, that will take care of the logic of querying the API, looking up the current value of Apple stock, and giving you back that data in the form of a Python dictionary. So you might want to print out that dictionary to see what data actually comes back to you after you've looked up the value of a stock so that you know how to actually use that information to display the current value of the stock. 

Of course, if the user types in an invalid symbol that doesn't actually represent a valid stock, lookup is not going to be able to determine what stock you're looking for. So lookup in this case is just going to return the Python value none, meaning no results came back. So you'll probably want to handle that case as well. In the event that lookup returns none, you'll likely want to display some sort of error message to indicate that the stock that was attempted to be looked up did not actually exist. 

After users are able to look up the value of a stock, the next step is to let users actually buy some shares of that stock. So how might you go about doing that? Well, again, you're going to display some sort of form that will let users type in the stock they would like to buy and the number of shares of that stock they would like to buy. This is going to be very similar to quote except in addition to just specifying the name of the stock, they'll also specify how many shares of that stock they would like to purchase. 

When the user submits that form, there's some logic that you'll need to do. You'll probably need to query the database for the current user to make sure the user can actually afford the current stock, checking the current amount of cash that they have, which is stored in the user's table, and comparing that against the price of the stock, which you can look up using that lookup function, times the number of shares of that stock they would like to buy. 

And if the user is able to afford the stock and the stock actually exists, then you should actually take care of the logic of purchasing that stock for them. And to do that, you'll likely need to add one or more tables to the finance database. We've given you inside the finance database a table called users, but you'll likely want to be able to use other information inside of that table as well, storing not just users, but other information about the stocks that the user has purchased. 

Let's take a look at this in a little more detail by going back into CS50 IDE. Recall that inside a finance directory we had this file application.py, but we also had this file finance.db, which represents a SQLite database. So I'll go ahead and close these template files and make my terminal window a little bigger. If I typed SQLite3 followed by finance.db, what I'll be taken to is SQLite prompt that lets me actually run SQL queries on this database. 

So for example, I could say select star from users to select all of the users that are currently inside of my database. Of course, nothing comes back right now, because no users are registered. But after you've attempted to register a new user using your implementation of register, you should be able to go into the SQLite prompt, type select star from users, and actually see the user and their password hash and also how much cash they have. Because if I run a command like dot schema users, this will show me how the users table was actually constructed. 

So here was the create table query that was run in order to generate that users table. So we created a table called Users and gave it a couple of fields. We give it an ID column, which is an integer, a username column, which is just some text, a hash of their password, which again, is some text, and then cash, which is some numeric value, which means it could be a decimal, with a default value of $10,000, for example. And so this was the create table query that we already ran to give you access to a users table. 

But you might want to create a new table of your own. And to do that, you can literally in the SQLite prompt type create table followed by the name of the table followed by in parentheses all of the columns that you would like to add to that table along with their types. So you should probably create one or more tables to represent all of the stocks that were created. But what those tables are and what the columns and what the types of those columns are are ultimately up to you. 

But you'll likely want to keep track of at least what the stock that was bought is, how many shares of that stock were bought, and who bought that stock. Because if a different user logs in at a different time, you'll likely want to make sure that every user only sees the stocks that they themselves have actually bought and not some other user's stock, for example. 

So that's buy that will allow users to be able to type in what stock they would like to purchase and actually purchase that stock. And once they do, you'll display the results by implementing index. Unlike several of the other functions, no need to worry about a request method of POST here. There is no form involved with index. All index is going to do is query data from the database and display data about all of the current stocks that the user that's currently logged in owns. 

So you'll display a table that shows the values of all the current user's stocks, how many shares of each of the stocks they have, as well as the total value of each of the holdings, which is just the current price of each stock multiplied by the number of shares of each stock. How are you going to get access to this data? Well, in the buy step, you likely already created one or more tables that represent all of the stocks that users own inside of your database. 

So you'll likely want to run some sort of query selecting from that table, all of the stocks that the currently logged in user owns. And recall that you can get access to the currently logged in user by taking a look at the value of session square bracket user ID to get who is currently logged in. Once you have all those stocks, you'll want to use the lookup function to look up the current price of each of those stocks, and then ultimately, you're going to want to display all of that data inside of some sort of template that you might call index.html, for example, but it could be called anything you want. 

After users have the ability to buy stocks and see what stocks they own, next up is sell, which is really just the opposite of buy. It'll, again, have two parts. When requested by a GET, you should just display a form that lets users indicate what stock they would like to sell and how many shares of that stock they would like to sell. And when the form is submitted via POST, you should sell that specified number of shares of stock. 

Again, there is some error conditioning you'll want to do here. You'll want to check to make sure the user actually owns that stock and they're not trying to sell more shares of stock than they currently own, for example. But if they are able to sell that stock, you should update the user's cash in order to add to it whatever number of shares of stock they're selling multiplied by the current value of the stock. 

And you can find out the current value of the stock, again, using that lookup function. And then you'll want to make sure to update any of your tables that are keeping track of how many shares of stock the user owns in order to indicate that they have sold some number of shares of that stock. 

After all of that, you can implement history, which will give you the ability to view all the history of transactions of all the times that stocks have been bought or sold. Depending on how you've constructed your tables, you might be able to get access to this information just by querying the table or tables you already have or you might need to create a new table that's keeping track of this information as well, such that when users are buying and selling stocks, you're keeping track of when that happened, you're keeping track of how many stocks were bought and sold, and what particular stock was bought or sold. 

But you'll display all of that just inside of a table, similar in spirit to the index table, but while the index table shows all of the stocks the user currently has, your history table will show all of the transactions that have ever happened for the user, row by row, every time the user has ever bought or sold a stock. 

Once you've done that, you'll have a working CS50 Finance. And the last step is just to add a little bit of a personal touch, some additional feature of your choosing. What you choose for the personal touch is entirely up to you. But possibilities include letting the user change their password or add more cash to their account or buy or sell stocks directly from the index page rather than needing to go to the Buy page or the Sell page, or adding password complexity requirements by making sure the passwords are a certain length or have a certain number of numbers or symbols or other characters, for example. 

And you can feel free to be creative here. Come up with any other personal touch or some combination of these various different features just to add a little something personal to your web application as well. After that, that will complete CS50 Finance and complete your creation of your very first web application that uses Flask and SQL and HTML in order to combine models and views and controllers to create a working web application that users can log in to, buy and sell stocks from, and interact with.