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.