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.
