[MUSIC PLAYING]
SPEAKER: All right.
This is CS50, and this is week nine.
You'll recall that last time, we took a look back at such things as Scratch.
Because recall that when we introduced Scratch,
we introduced a whole number of programming
constructs-- loops and conditions and functions and variables and more.
And then just one week later did we transition from that world of Scratch
to C, where the syntax was much more cryptic looking, certainly
at first glance, and perhaps a little bit still.
But the ideas were ultimately the same.
And now in week nine as we transition from the world of C to Python,
you'll find that that same finding is the case, whereby
we are using the same ideas.
We're leveraging the same concepts.
But we have to translate it now to a slightly different domain
and a slightly different syntax.
But things really start to get interesting this week
and beyond, especially as we build on our ability
to write Python code, our ability to serve up web applications.
Because now we can begin to leverage so much more functionality
than comes with C, than comes with the CS50 library,
because there's such a large community of software developers
who have created some really amazing things that we can try out.
So let's look further today at Python, and let's look further
at the applications for a language like this.
You'll recall that perhaps the simplest program we could have written last time
was actually just one line-- print Hello World.
But you'll recall as you began to dive into some of this past week's
challenges, you might have needed or wanted
to actually start wrapping code like that in a method or function like main,
and then calling it by default with this magical incantation here.
But this was the building block toward which we were starting
to develop more interesting programs.
But now we're going to really context switch from a command line environment
over to a web-based environment.
And the world has been writing web-based applications for quite some time.
Even I 20 years ago recall made that Frosh IMs-- freshman intramural sports
website.
And even since then have the languages changed and the paradigms changed,
and we humans have learned a lot about programming for web-based applications.
And this, for instance, is one design pattern or one architecture
that has arisen, whereby MVC refers again
to this pattern, whereby you put your intelligence, your so-called business
logic in your controllers.
This is one or more files that has all the conditions,
a lot of the functionality calls, and actually does something
with your program.
Then you have the views, which are often templates or files that
render information that you might have dynamically generated
or input from users.
And then model.
And today, we start to dwell on the M in MVC, model, because last week,
we didn't really leverage much, if any, of a model.
But this week we're finally going to demand of ourselves the ability
to save data, retrieve data, search data, delete data, and more.
And we really haven't had this capability, other than very simple CSV
files, for instance back in the day of C,
and even last week when we dabbled with those in Python.
And you'll recall, last time we introduced this.
Flask is what's called a micro framework.
So a bunch of files, a bunch of code that a community of people
have written that just make it easier to make web-based applications.
It's absolutely not required.
In fact, one of the sample programs among last week's distribution code
if you like to go back and play was a program called serve.py
that doesn't use any of this.
It just uses built-in Python functionality.
But you'll find that it's pretty cryptic.
It's pretty heavyweight in order just to do something simple.
And so things like Flask have come around that just make it easy
and dare say more pleasurable to write web-based applications,
because people have realized in writing web app after web app after web app
that they're just repeating themselves, or borrowing code
they've written in the past.
And so in frameworks, you have solutions typically to very common problems.
So we'll use this set of solutions to the development
of a web-based application.
And for instance, the simplest Flask application
that's also available from last week's source code
might be something like this, whereby the top of your file,
you don't import like the CS50 library, you instead import someone else's
library, specifically from the Flask framework import-- what's
called the class, recall, called Flask-- capital F.
And then also a function or method called Render Template.
And we used both of those as follows.
Last week, we instantiated a Flask-based application
by essentially passing in this special reference
to the current file, the name of the current file,
and then allowing the framework to do its thing
and give us back this very special, very powerful object called app--
though we could have called it anything-- that gives us
access to some useful functionality.
For instance, the most useful functionality
initially was just this-- @app.
And this is just syntax for what's called a decorator, and more
and that some other time, or more on that in Flask's own documentation.
But essentially, this line here-- @app.route,
says that hey server, any time you see an HTTP request for slash, the default
web page typically of a website, go ahead and call the following function
that's immediately below it.
That function I've called Index, mostly by convention.
But I could've called it anything I want.
And all it did last week in this example was render a template.
In this case, index.html.
Now, that could be raw HTML recall.
But oftentimes, you use something called a templating language.
And indeed, we introduced a little bit of Ginga last time, which
is a Python-based templating language that we'll see just
makes it easier to generate HTML without having
to write HTML inside of our actual Python
code, which tends to be frowned upon.
So let's take a look back at one of those examples, which
I've renamed from Frosh IMs last week to Frosh IMs zero.
And recall that we had the following files.
We had a templates directory, inside of which
was failure, index, layout, and success.
Kind of a lot of complexity for a pretty simple program,
but we'll see what each of those does again.
And then application.
And this was the so-called controller code that actually did something
interesting last week.
Now, what was that?
Well, it's a pretty small program.
As before, I've imported from the Flask framework
a whole bunch of symbols here.
I'm instantiating my application here.
This is copy-paste from our simplest of examples,
whereby if the user just visits Slash, I want to show him or her index.html.
But then it got interesting.
This really was the first time we had the capability
in a web-based environment to respond dynamically to user's inputs
based on whatever they typed into a web form.
In fact, if you think back a few weeks in week six
when we first introduced the web and HTTP and TCP/IP
and making HTML-based web pages, you recall that all we did
was implement Google's front end, and an ugly one at that.
But just the HTML form, that if you click the Submit button,
it submits to Slash search on google.com,
because we pretty much deferred completely to them,
lacking at the time a backend and lacking at the time
even a language in which we could implement our own backend,
a web server that actually responds to those requests.
But in Frosh IMs Zero, we have the ability
to have our own route, in this case called
Slash Register that I've specified isn't even
going to respond to HTTP Get requests, but rather Post requests, which
typically mean a form submission is sending one or more fields
that you don't necessarily want to end up in the browser's URL or history.
I'm calling my function Register, and this would be a good convention.
Just make sure your function here lines up with what the route is there.
And then I'm checking a couple of conditions.
If that request's form-- that is, all of the parameters
that were submitted via HTTP Post-- has a name field--
so a text field, for instance, called Name--
and that equals nothing-- quote unquote, the so-called empty string--
or, that request's form's dorm key has a value of quote unquote, which
is to say that if the user, myself last week, did not give my name or my dorm,
then go ahead and return the template called Failure.html.
And we'll take a look back at that in a moment.
Otherwise, render template Success.html if all in fact goes well.
Now, if we take a look at Failure.html, it didn't do all that much.
It extended Layout.html.
It declared a title of Failure.
And then it declared a body of You must provide your name and dorm
as sort of an admonishment to the user.
If I look at Success, meanwhile, it's pretty similar,
but the text is different.
But this is the problem we address this week.
You are registered, well not really.
Because recall-- and you could have seen a moment ago-- we did nothing
with the user's name or dorm or any other information,
we just pretended to actually register them.
And then what did the form itself look like?
Well, this page here is mostly HTML.
But again, notice that even this page at the top
defines a body block and a title block, because it's extending Layout.html.
So this is the Ginga stuff that I referred
to earlier, the templating language.
And if we finally go into Layout.html, now you
see the basic framework for every page in this web-based application.
It's a pretty small application to be fair, but it does
have at least three distinct pages-- Index, Failure, Success-- all of which
are identical to the file, except that they each have a title and a custom
body dynamically embedded thanks to how we're using templates.
So this is a nice way of not having to copy and paste all of that code
into every file, thereby making it a pain to update anything,
to add CSS or JavaScript files, or generally any
of the overall structure of the page.
We can factor that all out to Layout.html.
Of course we just threw all this information away.
that name, that dorm, every student who's been registering by this app,
we're forgetting about.
And so at the tail end of last week, we introduced this solution recall,
whereby in Frosh IMs One now, I'll call it--
or rather today let's redress that by borrowing an idea from last week
that we didn't incorporate into Frosh IMs as follows.
In Application.py for this next version called Frosh IMs One,
what do I seem to be doing differently?
File's almost identical to before.
It's a little longer this route, because what I've grabbed
is some of the code from last time, whereby we wrote out to a CSV
file-- Comma Separated Value-- those student structures.
We did this way back when in C. We then ported the student struct to a student
class last week, and now I've borrowed that same code
for saving those students to disk, so to speak, by embedding it into this route.
So what's going on?
So if again the name or the dorm are blank,
I go ahead and just return the template called Failure.html and we bail out.
Otherwise, I declare a variable called File.
I open a la C's fopen function, registrants.csv,
which I'm just calling this just because it's
going to contain my registrants in a comma separated values file.
And then quote unquote a.
And you might not recall this or might not have seen this, but quote unquote a
is for appending.
If I instead and accidentally did w for writing,
you might think that's correct.
But every time you use quote unquote w, you're going to write out a new file.
Which is to say, if 100 students registered
for some intramural sport via this web app, and I was using quote unquote w,
I would actually keep clobbering or overwriting
the file, such that only the most recently registered student
would appear in this file.
So we want quote unquote a for append.
Then I declare a variable called writer, and I'm using this CSV module
that we get for free from Python that's going
to allow me to create what we'll a writer, passing in that file as input.
Then I'm going to call this special method Write Row, whose purpose in life
is to take a tuple, which in this case has
two elements, the name that was passed in via the form as well as the dorm.
And then I'm going to go ahead and close the file.
So Write Row is now the function that takes
care of all the complexity-- it's not all that much complexity-- of printing
out name, comma, dorm, name, comma, dorm.
And just in case anything has a quote in it, like a someone O'Leary,
or a name that has an apostrophe or some other punctuation symbol that
might otherwise confuse a program reading this text file,
which itself might contain quotes, this kind of function Write Row
will take care of those kind of details, as well as
commas that might correctly or incorrectly be in the values like name
and dorm that I'm providing.
And then we print out the success page.
But this isn't all that useful of a file format.
A CSV file is nice in that you can download it,
you can open it in Excel or Apple Numbers or similar programs.
You can import it into Google Spreadsheets.
But it's not really a database.
You can store data in it, but if you want to read any of that data,
or change any of that data, you pretty much
have to do what we've been doing in C, which is open it with Open or fopen
or whatever, iterate over the lines, maybe parse them or read them
into separate variables or into an array or a list or whatever,
and then you can use binary search or linear search
or whatever you want to actually find data,
maybe to then change data, and then save it all back out.
But this is just tedious.
To have to do all that work simply to save data
isn't all that much fun for programming, and it also doesn't scale very well.
As soon as you have some good success with some web-based application or even
some mobile application, it'd be nice if your code were
as efficient and as fast as possible.
And wouldn't it be nice if we could stand
on the shoulders of others who have had similar problems of storing data
efficiently so that we could learn from them as well and leverage
some of their work?
So thus was born SQL-- Structured Query Language--
which can be used in any number of environments.
So SQL is just a language that we will now introduce.
And it's a programming language, though it's not
going to be as-- we're not going to use it as richly as we have C or Python.
We're going to use it for a number of fairly basic, but nonetheless very
powerful operations.
And you can store data using this world of SQL in any number of ways.
You can use things like MySQL-- which is a very popular database
server that Facebook started with and still uses for some of its purposes--
PostgreSQL, Microsoft Access, or Oracle, or any number of third party products
that you might have heard of that are super
popular for web-based and business applications.
And then there's also a format that's even simpler
but gives us all of the same capabilities called SQLite.
Whereas MySQL and Postgres and Oracle and Microsoft Access and the like
typically require that you run some special program, a server, that's
listening for requests and responding to requests
and often has usernames and passwords, if you want something
simpler because you're making a fairly small scale website that might only
have hundreds or thousands or tens of thousands of users,
and you're OK with all of your data living on the same server
that your code lives on, maybe in the same folder,
you can use something called SQLite, which allows us to use the language
SQL that we're about to see,
but it doesn't require the complexity of configuring
a whole server or a whole set of tools.
You can just store your data right there in a .db or .sqlite or whatever file
right in your same directory.
But how are we going to store that data?
Well, many of you are probably familiar with tools like this-- Google
Spreadsheets or Excel or Numbers, which allow you to store data
in generally rows and columns.
And if you're looking at a spreadsheet like this
and you're storing people's names and dorms and email addresses and phone
numbers, generally we humans use the top row for headers,
and we'll put quote unquote name, quote unquote dorm, quote unquote email
or phone or whatever.
And then every row below that first row represents in this case a student,
a record in our spreadsheet.
So if I were to do this in reality-- let me go ahead and do just that.
Name and dorm and maybe email and phone, and any number of other fields.
And I'm going to go ahead and really be tidy here.
And you can do these silly aesthetics in something
like Google Spreadsheets and Excel and Numbers.
But here's where I would do something like David and Matthews and Rob
and Thayer, and dot dot dot, and we can fill in the rest of these rows,
and we can just keep growing and growing and growing.
And frankly, most of these programs are smart enough
that even though I think Google Spreadsheets now
goes to a default of 1,000, when you export that file or save it,
they're only going to save rows presumably that actually
have data up until that point.
And then you can have any number of columns.
You get A through I think Z probably by default. So 26 columns
even if you need fewer, but you can create more as well.
So this is nice.
This itself is structured data.
You have meta data, like name, dorm, email, phone, keys, if you will.
And we've used that expression before in various CS contexts.
And then you have values, and those values line up with those keys.
And in fact, now if you think back to some
of the features we've seen already in Python,
a dictionary is a very popular and very useful data structure.
In Python, a list or an array is another such one.
And so really, if you think about what a spreadsheet is, it's kind of really
just a list of rows, each of which is a dictionary.
And what do I mean by that?
Well, within every row, whether it's two or three or 1,000, you have columns.
And those columns have keys or names to them.
Name, dorm, email, phone.
And recall that a dict or dictionary in Python
is just a collection of key value pairs.
So if this is just a list of values, and each of those values
is organized horizontally essentially as dictionaries, key value pairs,
where row three's name is Rob, row three's dorm is Thayer, and so forth.
Well it seems that we could map this kind of format
very cleanly to something like Python.
Or C for that matter, but Python would be a lot more convenient.
And sometimes, one sheet is not enough.
So sometimes you might have gone down here to the bottom corner.
You might have created something like sheet two.
And maybe here, instead of just storing students, you might store professor.
So the professor's name and office and course
and any number of other pieces of data that you
want to store about him or her.
And we could store professors.
And I'll even rename this from sheet two to Professors.
And in sheet one, I'm going to go here and rename this to Students.
And so we can really kind of organize our data cleanly.
But that's about it.
Even though Google Spreadsheets and Excel
do have some programming functionality built
in with macros or similar functions, it's
not all that easy to query the data.
It's not all that easy to integrate this kind of file
into a program you're writing.
And it's definitely not necessarily efficient.
Because you've not really told Google Spreadsheets anything
about how much data you're going to be putting in here,
what the type of that data is beyond maybe the formatting thereof.
And so there are some opportunities for better design.
And thus came along SQL databases that give us
not only the ability to store things in rows and columns,
but also the ability to create data or create structures in memory,
and insert data, select data, and update data, and delete data.
In fact, a SQL database is really a specific instance
of what's generally known as a relational database, a database that
allows you to maintain relations among pieces of data,
generally spanning sheets-- or as we're going to start calling them,
tables-- that adheres to a silly acronym,
CRUD, the ability to Create, Read, Update and Delete data.
And those verbs map to such keywords or commands as these
as we're about to see.
So what does this actually mean?
Well, let me go ahead and do this.
Let me go ahead and within CS50 IDE, I'm going
to open up a special web-based program that's
simply going to give me the ability to create databases and create fields
therein.
I'm going to go ahead and create a database called Lecture.db.
And I'm going to go ahead and open now a web-based tool via which
I can administer this database called Lecture.
And you're going to see that I have a tab called Structure,
which is where I'm going to be able to define sheets,
or tables as we'll call them.
I can execute raw SQL commands, with which we'll now start to get familiar.
And later on, I can even export or import data as well.
But let's focus on these fields down here.
So this tool is one of any number of dozens
of tools that might exist that allow you to create and administer databases
using SQL.
I'm going to go ahead here and call my table Registrants.
So this is like creating a new sheet in Google Spreadsheets.
And the number fields-- for now, I'm just
going to go ahead and have two, Name and Dorm.
And now notice I get a little HTML form here, whose purpose in life
is to make things a little easier for me.
So I'm going to type in Name for one field, Dorm for another.
And you'll notice that SQL databases now allow
me to choose any number of data types.
So we're sort of going back and forth here
in between the world of strongly typed languages, weakly typed or loosely
typed languages.
Here it can actually matter for performance,
and I'm going to indeed tell my database what kind of data
I'm generally trying to store in its columns and each of its rows.
So my options with SQLite, which is one incarnation of the SQL language,
is Integer-- which means what it says-- Real--
which is like a real or floating point number
as we've seen that generally can store up to 15 or so digits of precision.
But even then, if it can't, it's going to use a text field automatically
instead, because text is really just a string, otherwise known
in some environments as var char or variable length characters.
Blob is just going to be binary data.
And this means we can store things that are just zeros and ones that
aren't ASCII or Unicode text.
Numeric is a little more flexible.
If I want to store a dollar amount with dollars and cents.
If I want to store maybe an integer, maybe a real number,
I can use numeric, and just let the database-- SQLite in this case-- figure
out what actual data type or affinity, so to speak, to use.
Booleans is just going to be a zero or one.
But that's really just a convenience.
It's actually still going to use a full byte or 8 bits just to store a zero
or to store a one, really just an integer.
And then date/time, which underneath the hood can use any number of formats,
whether it's text or integers or reals.
I'll defer to the documentation.
But that's going to allow me to in a standard way
store things like dates and times so that we can actually
record when stuff is happening, when someone
registered for freshman intramural sports,
and even do useful arithmetic operations on dates and times.
So what should Name be?
Given all that, there aren't really too many options.
Indeed, my instincts are to go with text.
So I'm going to do that for Name and for Dorm.
And then I'm being prompted by this web form for a few different questions.
So primary key.
It turns out that among the features of a SQL database
is the ability to specify that this field-- this column
shall be my primary key, the field whose values uniquely identify
all of the rows in my database.
Now, it's not going to be relevant just yet for the following reason.
Right now, it's quite possible that two Davids live in Matthews,
or two Robs live in Thayer.
And so if I said that Name or Dorm were my primary key,
that would mean I can have only one David or one Rob or one Matthews
or one Thayer in my database.
So for now, we're going to leave this alone.
That would seem to be a bad thing to do.
Auto increment isn't going to be applicable, because you can't auto
incremented a string or a text field.
That's going to be germane to integers.
And not null, which is my problem that this field cannot or shall not be null.
So I will go ahead and do this and just promise that every student in Frosh IMs
is going to have a name and a dorm.
Meanwhile, you can specify a default value.
I'm not going to bother doing that here, but you can see from the drop down
that you can specify a certain value, like null,
or you can specify the current date or time, if that's actually germane.
But I don't want to give people generic names.
And I don't want to just assume that people live in some default dorm.
So I'm going to leave that alone and expect
that the user of this database table is always
going to give me a name and a dorm.
So let me go ahead and click Create.
And what you'll see is this.
phpLiteAdmin is really just a handy web-based tool
for executing SQL statements.
So I don't necessarily have to remember all of the syntax up front.
I can kind of learn from this actual tool.
And what phpLiteAdmin has really done for me is this.
It has in that file called Lecture.db executed this statement,
Create Table quote unquote registrants, Name is text not null,
Dorm is text not null, and that's it.
So phpLiteAdmin is just saving me, at least for today's purposes,
from having to remember exactly all that syntax.
But after practice, it becomes pretty familiar.
And certainly with Google, you can fill in any blanks.
But what does this now mean?
If I click on Return here and go back to not just
the lecture where I was, but I click now on Registrants, my actual table,
you'll see a bunch more tabs.
The top one of which is Browse-- but this table
is empty, because I've not inserted me or Rob or anyone else for that matter.
But if I click on Structure, you can now see in a web-based environment
a little reminder as to what this table looks like underneath the hood.
It's more esoteric looking than something like Google Spreadsheets,
where you just have columns and rows.
Here we're being more precise.
I have two fields, Name and Dorm.
Each is of type text.
Neither can be null.
But neither has a default value, and neither is a primary key.
But via the Edit link here and Delete, I can actually change those definitions,
and I can even add more fields if I think of things later.
And so this is allowing me to create really
a table of information whereby these are my columns or fields Name and Dorm.
So let's go ahead and insert for instance me in here.
So I'm going to go ahead and insert David, who is in from Matthews,
and click Insert.
And notice what phpLiteAdmin did.
It simply executed this SQL command.
Insert into Registrants, Name,Dorm.
So it's specifying what the two fields are that I want to insert into.
And what values do I want to put?
David,Matthews, not to be confused with Dave Matthews.
So now, if I click Return and go back to the Browse tab,
notice that we see Davids and Matthews.
Meanwhile, if I click Insert Here, and I type something like Rob and Thayer,
click Insert, a very similar SQL statement was executed.
But this one customized for Rob.
And now Return and see Browse, both of us are in there.
But now let me start to take those training wheels off.
It's not all that interesting just to click around in a web GUI.
Let me actually learn something from this.
And no, let me go ahead and type my own SQL now.
Insert into Registrants.
And if I really want to be proper, I can quote it.
But so long as you don't have any special characters,
quoting symbols is not strictly necessary in this case.
What fields do I want to insert into?
Name and Dorm.
And what values do I want to insert?
Raw values.
Textual values that are not special keywords or field names.
I do need to quote with single quotes here in SQLite.
Let's go ahead and put [INAUDIBLE] and Currier for Dorm or House there.
I can put a semicolon.
It's OK if I omit it, but if I want to execute multiple SQL
statements at once, I will need the semicolon again.
Let me go ahead and click Go.
And nicely enough, notice what happened.
One row was affected.
It took only 0.01 seconds, so it's pretty darn fast.
This is just a reminder of what it is I executed.
And if I go back now to Browse, I can actually see three rows in the table.
All right, so now things are getting a little more interesting.
What if I want to search for things?
I'm not even going to use this training wheel here.
I could use the Search tab and type in some names or some dorms
that I want to search on.
But that's not the goal here.
The goal here is not to teach phpLiteAdmin or this web-based tool.
It's just to use pretty quickly this kind of web-based environment
to create the schema for our database, the design of the database,
our choices of tables and columns and perhaps some of the initial data,
and then use that database in actual code.
So that's where we're going.
We're going to move away from this web-based environment
and use actual Python code, but first let's
see some of those other instructions.
Turns out, per this little summary here, that we have the ability
to create-- which we already did using Create Table-- to instert--
which I just did.
But what about select, update, and delete?
Let's not use the web GUI per se.
Let's just go ahead and start typing some raw SQL.
I'm going to go ahead and do exactly this.
Select let's say star from Registrants.
And now as an aside, stylistically it's not strictly
necessary to write select or from or all of the SQL key words in all caps.
Tends to make code I think a little more readable.
But I could still just say select star from Registrants.
But I find it nice to distinguish what are built in keywords from like what
are my field names and table names.
So select star-- star meaning everything.
Let's click Go.
And now what I see here is this web-based representation
of two columns with three rows that have come back.
We're going to start calling this a result set.
A result set containing three rows have come back.
And that's going to map to code pretty nicely.
Now, let's go ahead and do this.
Suppose that Zamyla moves to a different dormitory.
And let's go ahead and type this-- update registrants,
set Dorm equal to let's say Grays where Name equals quote unquote Zamyla.
And this is what's particularly nice about SQL.
Even though the syntax is a bit new and some of the key words
are certainly new, you kind of just say what you mean.
So update the Registrants table, setting the Dorm field or column
equal to Grays.
Not for everyone, only where the name field or column equals Zamyla.
So let me go ahead and click Go here.
All right, one row is affected up here.
And notice, a reminder of what I just executed.
Now if I click on Browse just to see what the data looks like,
indeed Zamyla has relocated to a different dorm or building altogether.
If I screwed up, though, notice what could quickly happen.
Suppose I did this, Update, Registrants, set Dorm equal to Grays,
and I left off the so-called predicate, the
Where clause that I did a moment ago and click Go.
Now three rows were affected as indicated here.
And if I click Browse now, notice that oops, all three of us
now live in Grays.
So I can fix this manually if I really wanted to
without executing another SQL query.
But this is just meant to be a user-friendly way
of quickly and dirtily editing or creating some data.
Really, we're going to start using those SQL commands only.
What about Delete?
Suppose that Rob has graduated, and he's moving out.
Let me go ahead then and do Delete from registrants, where Name equals Rob.
Go ahead and click Go.
One row effected.
And if I go and click Browse now, only Zamyla and I remain.
All right, so those are just a few such keywords.
But what's nice is that again, it's pretty expressive.
So to recap here, how did I create the table?
I literally said Create Table Registrants.
Though I didn't type this at the time, but this was just the
SQL that that web tool generated for me.
Spoiler alert, don't look at that just yet.
We're going to come back to that in a moment.
And I specified that the Name field is going to be of type text
and Dorm is going to be of type text as well.
Down here I then inserted something like David from Matthews,
specifying name and dorm.
And notice, these lines wrapped onto two lines,
but in general, this could just be a really long query as well.
Select Star from Registrants, Update registrants,
Delete from registrants where-- I get another little spoiler.
And it's a spoiler because there is actually
a flaw in my database table's design.
My Registrants table was not so well thought through.
I indeed only care about storing names and dorms, at least right now.
Maybe later, emails and phone numbers.
But what could go wrong?
When I deleted Rob, something could have gone wrong a moment ago.
When I updated Zamyla, something could have gone wrong.
What if there are two Zamylas at Harvard?
What if there are two Robs at Harvard?
Well, each of those queries didn't seem to specify which Rob or which Zamyla.
It just said where the name is Rob or the name is Zamyla.
So how do we distinguish the two?
Well, maybe I should have forced undergrads or students
to register with their full names, Zamyla Chan.
OK, so that would decrease the probability
that we're going to have multiples Zamyla Chans at Harvard.
But still there could be that probability.
Same with Rob Bowden, or really any name, whether common or uncommon.
So that doesn't feel very robust.
We could look for a Zamyla that specifically lived in Korea,
but maybe on the off chance there's two of them
there, that's going to create problems.
And we might update or delete more rows than we intend.
So this is why we humans in the US for instance
have unique identifiers, for better or for worse.
Things like Social Security numbers.
Or here at Harvard we have Harvard University IDs-- HUID numbers-- or at
Yale MIT IDs.
Or where you are in the world, odds are you have in your wallet or at home
or somewhere in your life, a unique identifier.
And you might not even know what those identifiers are.
In fact, anytime you've registered for an account
on some website like Facebook or Google or any number of others,
underneath the hood, even if those companies aren't telling you
what your unique ID is, they know you by more than just your name and dorm
or email address or phone number.
Odds are, they have assigned you some unique but very simple
piece of data, like an integer, so that Zamyla might be user number three.
Rob might be two.
David might be one.
Simply because that's the order in reverse in which I inserted them
into the table.
So let me go ahead actually and fix this.
Let me go back into my database.
And you know what?
I'm going to be pretty bold here.
I'm going to go ahead and in my table drop the whole thing.
So beware ever executing the drop command,
because it will literally drop all the data in your table.
But I'm going to confirm that, I'm going to start over as follows.
I'm going to create a new table called Registrants,
this time with three fields.
And in addition this time to Name which will
be text and Dorm which will be text, this time I'm going to think ahead.
And I'm going to give everyone a unique ID.
Convention is to call it literally id in all lower case.
And indeed, field name should generally not have spaces, not
have funky punctuation.
Whether you use so-called camel case with alternating capital letters
or snake case with underscores, it's generally up to you.
But using underscores tends to be the convention here,
although it's not yet to remain.
But I'm going to make this id field an integer,
and I'm also going to make it my primary key.
So that there shall be no two registrants in this Frosh IMs program
that have the same primary key, the same number uniquely identifying them.
And you know, a better yet-- and this is one
of the features you get for free with many relational databases--
auto increment.
I the programmer don't care what number Zamyla is.
I don't care what number David or Rob is.
I just want them to have a number, whatever it is.
But I don't want them to be the same.
So let me just let my database auto increment this ID.
Give the first user one, the second user two, the third user three,
and so forth.
And by nature, this cannot be null.
They need to be uniquely identified.
And default value here doesn't matter, because the database
is going to take care of that.
Let me go ahead now and click Create.
And now you'll see the create table that I just
spoiled a moment ago on the slide.
This now creates a table again called Registrants,
because I dropped or deleted the old one.
But notice how much detail we have specified what id is.
It is not only an integer, it is also a primary key that supports auto
increment and it cannot be null.
Meanwhile, Name, Dorm, I don't particularly care this time.
Though I could specify like before, I don't want those to be null either.
So again, that's just going to be a design decision.
But let me see how this affects things.
Now let me go into MySQL tab, where I can just execute some raw SQL.
And soon we're going to be executing SQL from within Python code.
Let me go ahead and insert into Registrants a name and a dorm.
And notice, I don't care about specifying the ID.
That's not interesting to me.
All I care about is the values like David and Matthews.
And if I now click Go, notice that's all that the database executes.
It doesn't go in and secretly change my query,
but it does notice that I've omitted explicit mention of that id field.
And if I click Browse, now notice, David for Matthews has an id of one.
Let's go ahead and do two other inserts, again via raw SQL.
Let me go ahead here and do insert into Registrants.
And you'll notice there's a nice little history in this web tool of all
the previous queries I've executed.
So in case you want to click and save yourself some typing, you can do that.
But for now I'm just going to go ahead and insert Rob as before from Thayer.
And for good measure at the same time, Registrants,
Name Dorm values Zamyla from Currier.
And notice the semicolon, so that hopefully both of these
should go through as separate queries.
Indeed they did.
One row affected.
And if I click Browse, now Rob is number two, Zamyla is number three.
And this is just going to continue on until we maybe run out of integers.
But SQLite will support as many as 8 byte integers, or 64-bit values.
So that's not going to happen for quite some time.
So now notice what I'm protected against.
There might be a whole bunch of Robs at Harvard it's fair to say.
But now if I do a Delete from Registrants
where name equals Rob and id equals two--
and I don't have to quote the two, because it is indeed a number--
this is actually more information than I need.
Indeed, because id is a primary key, I don't need to mention Rob's name.
If I already know his id, I can just say Delete from Registrants
where id equals two, click Go.
One row affected.
And goodbye to Rob.
And what's nice now about SQLite is suppose I do one other insert.
Let me go ahead and Insert into Registrants a name and a dorm
with values of Jason and Kirkland.
And click Go.
Is Jason going to now be the new number two, or is he going to be number four?
Let's take a look.
If we click Browse here, he's indeed four.
So one of the features you get by using the auto increment field
is that the database is going to ensure that just in case
you have remnants of Rob somewhere else, or really
his id number maybe in some other table or in your logs or some piece of data
you care about, we're going to make sure that Jason has not only a unique id
now, but a unique id in perpetuity.
If you don't use unique id-- or rather auto increments,
SQLite will actually try to reuse numbers just
for efficiency, which can actually break things down the road later.
All right, so things are getting interesting.
We have the ability now to insert data, delete data, update data,
and then select it.
What more can we actually do?
Well, it turns out that SQL tends to come with not only various data types,
but also various functions.
It turns out to save ourselves trouble, there are functions like date functions
and times and date find times.
So for instance, I want to execute a query like select star
from registrants.
But no, I don't want to select all the registrants.
Maybe I want to select all of the registrants this year.
I could say something like, select star from Registrants
where date of a particular field is greater than or equal to January 1st,
2016 or 2017, or whatever the year happens to be.
So these are functions that can do that kind of thinking for me.
But SQL also supports a number of even more compelling features.
We saw a moment ago primary key, which again specifies
that this one and only field shall uniquely identify all of the rows
in my table, I guarantee it.
There's other types of indexes you can use.
So this is the special type of index.
And essentially what you're getting is underneath the hood the database,
whether it's SQLite or MySQL or Postgres or Oracle or whatever,
is generally leveraging some of that week five, week four, week six material
from CS50 in the fancier data structures, especially the tree data
structures.
And if we tell the database in advance, hey, this field's
going to be my primary key, or this field
is going to be an index more generally, whoever implemented the database
software itself probably built into it the ability to store that field using
a fancy tree or some other data structure that ultimately
is going to expedite selecting and updating
and inserting and deleting data.
And indeed, we can say exactly that.
If you know in advance that you're going to be searching on a certain field all
the time-- not just the id field.
Maybe your table has an email field or maybe a zip code field or some other
field that users might want to search on, rather than just store it
in a column by defining a field in your database,
and then really just letting the database use linear search to find
everyone in 02138 or 90210 or whatever zip code,
you can actually tell your database in advance, index this zip code field,
index this email field.
Because I know I'm going to be searching on that specifically using
that special Where keyword in SQL.
And let the database give you better performance than something
like linear search alone.
Meanwhile, you can also specify, I'm not going to just search on this field.
I don't care just about it's performance.
You know what?
I can promise you right now this field shall be unique.
It might be an integer like a primary key.
But if there's some other field in a database that
in advance is going to be unique by design,
you can tell the database as much.
And the database too will optimize queries involving that field as well.
So among the fields we discussed today-- name, dorm, phone, email, zip code,
you can probably think of bunches of others--
which of those might be candidate to be unique
and to be indexed as unique by your database?
Name already feels like a bad idea.
We want to be able to have multiple Zamylas, multiple Robs, multiple
of any name in the world.
Probably want multiple people to live in the same dorm,
so that there can't be just one person from Currier, Matthews, or Thayer,
wherever.
What about email?
Email's kind of an interesting candidate, right?
Assuming you're OK with banning users-- maybe a couple of people in your life
who happen to use the same email address for whatever reason.
So long as you're comfortable assuming that every human in the world
is going to have or must have his or her own email address,
you could proactively say to your database,
OK, the email field or the email column shall be unique.
I don't want a user with the same email address to accidentally register twice,
let alone three times or more.
And even if two people think they have the same email address maybe because
of a typo, I don't want him or her to be able to register
if someone with that address has already been registered.
Phone number maybe works OK in the world of mobiles,
but if people still have landline phones,
you probably don't want to enforce uniqueness there.
But what else might you want to impose?
Not null.
If you want to ensure that everyone in your database has a name,
there's nothing stopping them inherently from typing like ASDF or whatever
into your database.
You might have to protect against bogus data.
But you can at least ensure that the user
has to give you some kind of value.
And then foreign key.
And we'll come back to this, but there's a way
of specifying that the data in one spreadsheet or in our world now,
one table, is somehow related to or identical to the data
in another spreadsheet or again a table.
But let's come back to some of these features in a moment.
What else do we get from SQL?
We also get the ability to join tables together.
Now what does this mean?
Well, let's go ahead and let's go back to the spreadsheet for just a moment.
And let's not worry about students and professors anymore, but let's go ahead
and think of this as, for instance, users.
I'm going to rename my quick and dirty spreadsheet here Users.
And what do I want every user in my world to have?
Well, I want everyone to have some unique ID,
and I'll let my database ultimately assign that.
I want everyone to have a name.
Maybe let's say a mailing address, a phone number, an email address,
and there could be bunches of other fields as well.
What else might someone have?
That's probably enough for a customer database.
And let's go ahead now and consider what these values might look like.
So my very first customer, who buys my first widget or whatever,
might be someone like Alice.
And she lives at 1 Oxford Street, Cambridge, Mass, 02138, USA.
Let's go ahead and make this column wider just so we can see it.
Her phone number shall be 617-495-5000, and her email address
shall be alice@example.com.
Let me make this field a little wider as well.
Then Bob comes along, and he buys something too.
He happens to live down the street at 33 Oxford Street, Cambridge, Mass
82138, USA.
And his number is 617-495-9000.
And he is just bob@example.com.
Then there's someone new altogether, Charlie.
He lives at 51 Prospect Street in New Haven, Connecticut,
and his zip code is 06511, USA.
And his number will be-- he doesn't want to cooperate,
so he's just going to give us some bogus number there.
But he'll be also charlie@example.com.
And this table now-- if you think of the spreadsheet
really as just a database table of users,
could be dozens or hundreds or even thousands of rows long.
So let's now consider what data types I should
be using if I want to migrate this spreadsheet into an actual SQL
database.
So the id field, which frankly you usually get sort of for free
in a spreadsheet program, because it just numbers all the rows for you.
But if you resort your data, those numbers
do not followed the original rows.
And so giving our data, ultimately we'll see their own numbers as good.
Because those should remain with the data.
Alice should always now be one, Bob two, and Charlie three.
So name.
Let me go ahead and consider what should the fields here be?
Well, just for the sake of discussion, let me go ahead
and annotate right on the screen here.
It stands to reason that this should be an integer.
Name should probably be text.
Address should probably be text.
Phone, I mean it could be an integer.
It's definitely not a real.
Numeric is not right, because it could then become real somehow.
But is integer right?
I'm a little worried here.
Like especially if it's an international customer,
I'm kind of biased at the moment toward US users.
But suppose someone typed in their actual calling code or country code
and they did something like 011 or 001 for the US and then 617.
If I called this field an integer, and then let
the user type in their actual number like this, if it's an integer,
those leading zeros mean nothing mathematically.
So my database is probably going to throw them away.
And I might not want that to happen.
We get lucky in that this still could work as a phone number,
but I really shouldn't be throwing away user's data.
So you know what, I'm going to go ahead and just call this text.
Whatever the user gives me, I shall store from him or her.
And then finally email can be text.
And now let's consider how we might index these columns.
We'll come back to id in a moment, because SQL's
going to give us that automatically in our database
we'll see by using auto increment again.
But it will be an integer.
Name, I could-- I don't want to make it unique,
because I want to have multiple Alices and multiple Bobs.
I could make it an index.
If for whatever reason the website or the application I'm making
wants to make it easy for users to search on names,
I could proactively say, OK, database index the name column,
because it will speed up my searches to be something better than linear search.
Address, same thing.
If I want to use auto complete or some kind of search feature.
So I can search over address.
Maybe I want to do that.
Phone number, maybe that could be unique, if I'm expecting only mobiles.
But that could be risky.
Email is the only one I might claim a unique constraint is pretty reasonable,
especially if your website-- as is commonly
the case these days-- is going to expect users to log in,
certainly not with their id.
And I see no mention of username.
If they're going to use their email address as their login,
then you better only allow any email address
to be registered once and only once.
But you know what I'm not liking?
And let me make this column even wider.
What's kind of dumb about this design at the moment?
You know, before we even get ahead of ourselves
and start moving this into our SQL database, what's dumb here?
I feel like some redundancy, right?
Like 1 Oxford Street, that's special and unique.
33 Oxford Street, that's special and unique,
even though there could be multiple people living or working there.
51 Prospect Street, same thing there.
But like Cambridge, Mass 02138 USA.
Cambridge, Mass 02138 USA.
Like why for every resident of Cambridge,
Massachusetts am I storing Cambridge, Mass 08138 USA.
Cambridge, Mass 02138 USA.
This would seem redundant.
I mean it's not likely that Cambridge is going to change its name.
I mean, once upon a time it was Newtown, now it's Cambridge.
But that's not likely to happen again anytime soon.
So it's not so much anticipating change, but just look at all these darn bytes
that I'm storing redundantly.
Which of those bytes though should hopefully
be sufficient to identify where someone lives or works?
At least in the US, we tend to use our postal codes pretty readily.
So you know what?
Let me go ahead and create a field called zip code.
Why don't I just store 02138 here.
And oh, stupid Google Spreadsheets, notice what it did.
It is assuming, because something looks like an integer, that it is an integer.
So this is a stupid artifact of using a spreadsheet program.
Let me change that to plaintext and retype it.
Now Google will respect my input.
But the point is all the more clear, we had better in our SQL database
call that text and not an integer.
And now you know what?
I can make my address really just a street address.
And I can get rid of this, and oh, I shouldn't get rid of this.
But I should in this column do 06511, and then here, get rid of all of that.
Damn Google again.
Let's go ahead and change that to plaintext.
05611, and change this to 02138.
So it's a little cleaner now.
There's a little bit of duplication.
I now have 02138 twice in my database-- or in my spreadsheet in this case.
But at least it's a lot, lot, lot less redundant.
But I need to recover that information.
This is just my Users spreadsheet-- my Users table.
You know what?
I'm going to go ahead and create another sheet with Google Spreadsheets here.
I'm going to go ahead and call this Zip Code.
I'm going to call this City, State, Country.
Although Country makes things a little more complicated,
because zip codes aren't going to be the same format everywhere.
But for now, 02138 is going to be-- dammit.
Let's change this whole column this time to plain text.
02137, Cambridge, Massachusetts, USA.
And now for New Haven, let's do the same thing.
Paste that, New Haven, Connecticut, USA.
And now rather than just call the Sheet Three,
let me more intelligently call this like Zip Codes.
And you know what?
I'm not loving how many bytes I'm using to store Zip Code.
Why don't I learn from lessons past and give this spreadsheet
or this column or rather this table its own ID,
and just arbitrarily assume that someone will number those for me.
So now I can whittle this down to just numbers where
this should be an integer now.
And in my Zip Codes table, let me make room for just these little annotations.
This shall be an integer.
Zip code we've already learned had better be text.
This should be text.
This should be text as well.
All right, so I have taken a very simple idea, spreadsheet
with all of my customers, and I seem to have really over-engineered it.
I've made something simple more complex.
But why?
I mean, it's not compelling when I have just three customers.
It took me more time to describe what I was
doing than just do it the original way, albeit with the redundancy.
But if you do start to have tens of rows or hundreds of rows or thousands
or tens of thousands, having all of that duplicated
data just doesn't make sense.
You're throwing away disk space.
You're throwing away potential performance and running time.
You're throwing away money, if you have to buy more disk space for your server.
And so what I've done here is what we would call normalizing my database.
Factoring out the commonalities that can be uniquely identified more simply,
with this case a zip code, or even more simply,
via some arbitrary but some consistent number that I impose.
So let's go ahead now and port this over to my database.
I'm going to go ahead and use phpLiteAdmin, just because it
makes things nice and easy here.
I'm going to go ahead and drop my Registrants
table, because that story is over.
And now let me go ahead and create a couple of tables.
The first of which I'm going to go ahead and call my-- what did I call it?
Users.
And number of fields, I'm going to need one, two, three, four, five,
six fields.
So let's go ahead and do that.
So six fields, and those are going to be id, name, address, and what did we say?
Zip code, and phone, and email.
And this shall be an integer.
This shall be text.
But id shall be a primary key.
I want it to auto increment.
And you know what?
I don't want names to be null.
I'm OK with addresses being null if they don't
want to cooperate or zip code being null if they don't want to cooperate.
Phone, email should not be null.
So I'm going to go ahead now and click Create.
This is the query-- somewhat longer now-- that was executed.
And now let me go ahead and create another table
by clicking on the name of my database again, creating a new table called
Zip Codes.
Number of fields here is going to be one, two, three, four, five.
So five fields, one of which is id, one of which is the zip code.
Then city, then state, then country.
Text, text, text, text, integer.
This shall be primary key.
This also shall be auto incremented.
Zip code cannot be null.
And everything else I'm OK with being null.
So I can also not spell country.
Create.
And so now, let's toss in some data.
Let me go ahead and insert really quickly, I'm going to use the web form,
zip code of 02138, Cambridge, Massachusetts, USA.
Insert.
Let me go ahead and insert New Haven as well.
So 06511.
05611, yep.
05611.
New Haven, Connecticut, USA.
And now let me just give myself a few customers.
Let me go in to my Users table, insert David-- or no, who did we have?
We'll copy and paste our actual users.
Alice, so we had Alice from 1 Oxford Street in zip code now.
But this zip code-- oh, interesting.
I goofed.
I went a little too fast.
So let me abort.
Let me go to Structure.
Zip code I said was text, but you know what?
I'm going to change that to integer.
All right, and now let me go over to the Insert tab again.
Let's put Alice from 1 Oxford Street at location 1,
with her phone number, which was for 495-5000.
And you can do this.
Even though this looks even more like an integer,
a common mistake I think in a database is
to just blindly throw in whatever the user typed in,
even if he or she used parentheses or pluses or dashes
or any number of other punctuation symbols.
There's no reason for us to store all of that.
In fact, if we want to pretty things up and throw away
some of the inconsistencies, I could just
use code-- Python, ultimately-- throw away all punctuation symbols,
and then just store 10 characters in the case of a US number,
or whatever pattern I care about.
Or I could proactively with Python even pretty this
up by just putting the dashes where I want them to be,
not where the user necessarily put them.
And then Alice was alice@example.com.
And now let me really quickly go ahead and insert Bob, who was from 33
Oxford Street.
He's also in zip code 1 now.
His phone number was 617-495-9000.
And I'll go ahead and pretend like I'm formatting this in code.
And then he was bob@example.com.
And then lastly, let's go ahead and insert Charlie
from 51 Prospect Street in New Haven, whose id I now know to be 2.
617-000.
He was the one that didn't cooperate.
And charlie@example.com.
All right, so what does this now mean?
In my database called Lecture, I now have two tables--
Users and Zip Codes, each of which has some number of fields inside of them.
But this is not all that useful to me anymore,
because now if I go ahead and select my users with a query like this,
select star from users, go, I get back this.
But what the heck is zip code 1 or zip code 2?
Now OK, I can figure this out.
If I know that I'm looking for Alice, and I see that she's in zip code 1,
I could do another query.
All right, let me go back to that SQL tab.
Let me go ahead and do select star from Zip Codes where id equals 1.
Because I saw that Alice's zip code was uniquely identified by 1.
Oh, all right.
So Alice lives in zip code 1.
Yeah, that's 02138 Cambridge, Mass, USA.
OK, got that.
But now I've used two SQL queries.
The data is still like-- some of it's over here, some of it's over here.
Now I've got to somehow combine it in code.
And you can do that, but SQL is much more powerful than that.
It has other keywords like this, where I can have
the database do the thinking for me.
Let me do this.
Select star from users, join Zip Codes on users.zipCode equals zipCodes.id.
Take a moment to think, and ignore the red squigglies.
That just my browser thinking I'm being grammatically bad, verbally too.
Select star from users, join zip code.
So I'm telling the database, go ahead and join these two tables.
How?
Well if you think of one table as this hand and one table
as this hand, what's nice is that each of them
has a field that's inside of the other.
This table might have the id field.
This table has the zip code field.
Wouldn't it be nice if I could somehow stitch those together,
lining up those zip codes, whereby in this table, Users--
I called it zip code-- and then in my Zip Codes table I called it id.
Let's see what happens.
Let me go ahead and click Go.
And amazingly, look at what I get back.
My result set this time contains everything.
I get Alice, Bob, and Charlie, each of whom lives at these addresses.
But look at their zip codes now.
They've been filled in with the actual values,
plus the cities and the states and the country.
So this is where you really now start to scratch
the surface of the capabilities of something
like SQL, because it can actually combine data in this way.
And this was a pretty simple query.
But now I can sort of exercise good design.
I can keep my data very cleanly structured and normalized, whereby
I factored out all of the redundancies.
And yet I can programmatically reconstruct that data
and ensure that I can get back everything I care about.
Moreover, I can further optimize things.
If I go into Users for instance and I go under Structure,
notice down here there's another field.
Create index on one column or more.
Let me go ahead and create that index on my Users table.
I'm going to call it Arbitrarily Email, and I'm
going to say duplicate values are not allowed.
I'm not going to bother with a where clause here,
but I'm going to put this constraint-- this unique index on my email field,
and now click Create.
And notice what got executed.
Earlier we used it to create a table.
Here I'm using it to create a unique index called Emait--
though I could've called it whatever-- on the Users table
using specifically this field.
So this now is my way of ensuring that the following can't happen.
Notice that in my Users table I've got Alice with alice@example.com.
suppose that someone else named Alice, also with that same email address--
or who thinks her email address is the same,
because of whatever typographical error-- comes along,
and I try to register them for my site as follows.
Insert into Users.
Let's see, we have name, what are the other fields now?
Name, address, zip code, phone, and email.
The values as follows Alice number two, so her name's not distinct.
Her address is going to be 1 Main Street, so her address is not the same.
Zip code, she'll go ahead and live in New Haven, for instance, so 2.
Phone we don't need to worry about, so she'll be another fake phone
number for today's purposes.
But suppose that she also thinks her address is alice@example.com
Or heck, maybe this is the same Alice who
forgot she has an account on our website-- maybe more likely--
and is trying to reregister with the same address.
What's going to happen?
If I didn't make any typos here, let's click Go.
Interesting.
The database stopped me from doing this.
Error, unique constraint failed, users.email.
And this is common syntax in the SQL world.
tableName.fieldName.
So insert into Users, whatever I just typed
failed, because the unique constraint.
And indeed, we can see this.
Let me go to Browse and look.
There is no second Alice.
So we could do this in code, as we'll soon
see even more in the world of Python.
You could check by writing your Python code to see, wait a minute,
someone's trying to register as alice@example.com.
Let me quick select all my users from my database, look for Alice,
and if I already have Alice at example.com,
I'll just say, no, you can't register.
Much like I said no you can't register for Frash IMs
if you don't have a name or a dorm inputed.
But my database can do that for me.
And this is a nice wall between me and my database,
a nice wall between the software developers
and even the database administrators so that you
don't have to worry about data accidentally
getting into your website or your database that shouldn't actually
be there.
All right, we have all of these building blocks now.
We have Select and Insert and Delete and Update and now Join.
What more is there?
Well, there are some more keywords.
But let's first use the ones we have.
But this time, not just to play around via the web-based tool.
Let me actually show you one other thing.
So let me go in to CS50 IDE and take a look at my source 9 directory,
and you'll see these four folders, two of which
you're about to look at in a moment.
And you'll also see lecture.db, a file that I've
been using to store all of my SQLite data, including those tables.
Well, it turns out that we can actually use a command line client in order
to see the same data.
Let me go ahead and do that by typing SQLite 3 for version 3.
And then let me go ahead and type in the name of this file, lecture.db Enter.
And now you'll see a very simple and perhaps a little cryptic command line
interface.
But thankfully if you type as it says .help,
you'll see everything that you can possibly do with this command line
program.
But we're going to go ahead and keep it simple.
I'm going to go ahead and just say .tables,
which is SQLite's way of saying show me what tables are inside of this file
called lecture.db.
Now here I have users.
Let me go ahead and see what is the schema of these tables.
And you'll see the SQL commands with which the Users table and the Zip Code
tables were created.
But more interesting than that is this.
At the command line, this is just a SQL client.
I've been using the web-based GUI, phpLiteAdmin for the past few examples.
But we can just do this at the command line too.
Let me go ahead and select star from users semicolon,
and now you see in purely textual form the exact same data.
Here's the ids, my users name addresses, zip codes, phone, emails.
I can see the same thing from my zip code fields.
Let me go ahead and do select star from Zip Codes.
And there we see the same data.
So using the command line too, you don't have to even use phpLiteAdmin.
It just tends to be a little more user friendly.
You can also see the same data in the same file.
And now that I've done this purely manually,
let's now transition to doing this in Python code.
But first, let me make mention of one detail in the tables
that we had created.
Back here in users, you'll recall that we
had the structure of having an id and a name
and the address, zip code, phone, and email.
What you'll actually find is that the world has generally
standardized how you define what are called foreign keys in these tables.
So specifically, which of the fields in this table
are kind of foreign to this table?
That one of zip code.
Originally, that zip code was a text field, 02138,
and any number of other zip codes as well.
But then I changed it to an integer, because it really in this table
is a foreign key.
Because those same numbers 1, 2, and onward
are primary keys in that other table called Zip Codes.
And so what would actually be more typical here
is that we would often name this not Zip Code, which is a little ambiguous.
But just as a human convention, let me propose
that we clean up this design a little bit
and call this Zip Code ID to make super clear that this is indeed an ID.
It's not this table's ID, it's an ID from the Zip Codes table.
Plural would be the convention, and singular would be the convention here.
And indeed, we can now use the same kind of statements in code.
But it turns out with one of our concluding examples today,
we'll see while it's advantageous to adhere to certain conventions,
and frankly even this might even be typically higher in the table as an id
and not just buried there in the middle.
But that's a more minor detail.
All right, now let's return to CS50 IDE and build upon those previous Frosh IMs
example.
But now weave in this new feature that we have,
which is that ability to store data and retrieve data from a database.
So recall that this was Frosh IMs 1, which
was an improvement upon Frosh IM 0 in that at least Frosh IMs 1 actually
kind of sort of registered students.
It used registrants.csv, and it wrote out the data to a simple text file.
But with text files, CSV files, even though you could double
click them and open them again in Numbers, Excel, and port
them into Google Spreadsheets, we don't have the same expressive capabilities
as we do with a language like SQL to select, insert, update, delete.
They're just text files.
With SQLite though, a binary file, and the SQLite 3 program,
and we'll soon see Python code, we can actually
execute more sophisticated queries than the CSV format allowed.
So how are we going to do this?
Let me propose now in Frosh IMs 2, we create a new application.py
that's going to behave as follows.
Let's go ahead and propose that I'm going
to import as before some of the Flask functionality.
So from Flask import capital Flask, which is the class that we're using
is the application itself.
Render template, so I can render HTML.
Redirect, so that if I want to redirect the user,
I can do this by an HTTP location redirect.
Request, so that I can actually get at form data.
And we'll see URL4, which you might recall from past problems.
But you know what?
I'm also going to import from CS50 our SQL wrapper.
So inside of the CS50 library recall for Python
is getInt and getString and getChar and getFloat.
But inside there also is a whole library called SQL.
So the CS50 SQL library that doesn't do all that much,
but it does offer us an execute function that's
going to allow us more simply than might otherwise
be possible to execute SQL statements inside
of our Python code against Lecture.db on FroshIMs.db
or whatever the file may be.
And I'm going to do that as follows.
First I'm going to instantiate my Flask application, as always.
And now I'm going to instantiate a database connection essentially
as follows.
SQL quote unquote SQLite colon, slash, slash, slash.
So notice the third slash in this case.
FroshIMs2.db.
So this will be Frosh IMs version 2.
And that's it.
So I'm telling the CS50 library to use SQLite and to open up ultimately
the Frosh IMs 2 database.
Now I'm going to go ahead and have a simple route as before for just slash.
I'm going to define a function called index as before,
and this one's super simple.
Just go ahead and return render template of index.html.
And now let's reimplement register as follows.
So app, route, slash register.
But in this case, you know what?
I want to support post.
I don't want students' information ending up in the URL
and like the computer lab's history or the roommate's browser or whatever.
So let's go ahead and specify to Flasks that you know what?
Only support this list of methods, specifically just post.
Let me go ahead now and define a method called Register.
That's going to map to this route.
And as before, if request.form quote unquote name equals equals nothing,
or request.form quote unquote dorm equals equals nothing,
then go ahead and return failure, and return render template of quote unquote
failure.html.
So the website is almost exactly the same
now, except we have preemptively added this database capability to our code
that we're now going to use.
Otherwise, if we're not in failure, let me
go ahead and execute the following SQL.
Inserts into registrants a name and a dorm with the values of-- we'll
come back to this in a moment.
Colon name colon dorm.
I could call these anything, but a convention
in a lot of SQL libraries-- a lot of code,
whether it's Python or PHP or Ruby or other languages--
is to have these placeholder values with a colon and then a word that's probably
identical to the field name, but much like in C,
percent s has been placeholders, much like in Python open curly
brace close curly brace has been placeholders.
We've got one more placeholder convention, which is colon, and then
the name of a symbol.
But for the most part, they can almost always
be identical to what the field names are.
And now, I need to plug these values in.
So I'm going to go ahead and plug in for the first name equals request.form
name.
And dorm is going to equal request.form dorm.
So again, this is a Python thing meets SQL thing.
In our SQL library, as implemented by the CS50 execute function,
we have this SQL statement.
Insert into Registrants name, dorm, values, colon name colon dorm.
CS50's execute method, like many libraries out there,
will recognize any words that you have written in a SQL statement that
begin with a colon, and will then proceed
to substitute in any of the named parameters you provide thereafter.
So I'm saying, hey, library, give plugin a name of request.form name.
So whatever came from the user's HTTP request.
And for dorm, plug in whatever came in the dorm field as well.
So those were from the HTML forms that the user submitted in order
to register for this sport.
After they've done that, let's go ahead and do this.
Return, render, template, success.
And this time I'm going to change the success message to actually be,
yes, you really did indeed register, or the spirit thereof.
So let me do this.
Let me go into this Frosh IMs 2 directory.
And as you might have done for recent problems,
let me go ahead and run Flask run.
And we have since tweaked the configuration of the IDE
so that you no longer have to specify a host of 0.0.0.0 or port of 8080.
Those will just be assumed by default. You can override them if need be.
Now, I believe my application is running.
So let me go over to my web server.
And indeed, this is the form we saw last week.
Super simple, super ugly, super HTML.
Looks like HTML1, actually HTML5.
But let me go ahead now and prepare a database for this.
In another terminal window, I'm going to go ahead.
And in my source 9 directory as well, open up FroshIMs2.db.
Which if I open it up in phpLiteAdmin already looks like this, Frosh IMs 2.
But there's no table in there yet.
So I care only for now about names and dorms.
But I've learned my lesson.
I'm going to care about IDs too.
I'm going to go ahead and create registrants with three fields,
much like we conjectured earlier.
An ID, a name, and a dorm, first of which
shall be an integer, primary key, auto increment.
Next of which will be text, dorm of which will be text.
I don't want any of those to be null.
They don't need to be auto incremented or primary keys.
So let me go ahead and create.
If I return now to my Browse Structure, the table is empty.
Now let's go to the web-based application.
Here we go.
Let me go ahead and have a new name altogether.
Maria wants to register.
And Maria is from Stoughton.
Register.
You are registered.
Really?
So we've not invested heavily in the aesthetics of this website just yet,
but really I have been registered.
If I go back to phpLiteAdmin, click on Browse now, notice Maria from Stoughton
is actually now in my database.
Wait a minute, maybe I'm kind of cheating.
Maybe she was already there somehow.
Let's do this again.
Let me go ahead and hit back.
Let me go ahead here now and say Andy.
And let's pretend Andy is visiting someone in Weld.
And click Register.
You're registered really.
Let's go back to phpLiteAdmin.
Click Browse.
And now Andy is in there as well.
So now we're not just storing things on disk so to speak with CSV files.
Now we're actually interacting with the SQL database.
And we did so by using CS50's execute method inside of its SQL database
library.
But notice how relatively simple it was.
We simply used the same syntax with which
we've been playing around, either the command line or phpLiteAdmin,
but now doing it an actual Python code.
So now we literally have the ability programmatically to create data,
to update data, delete data, or select data, ultimately, from a database.
So let's do exactly that.
Let's take things up a notch and do even more than we did last time.
You know what?
Let me go ahead now and create another route for slash registrants.
Suppose I want to make available a web page that shows
me everyone who has in fact registered.
So this is a feature we haven't even had yet, though we could have.
At least with CSVs, we would have had to open up the CSV and iterate over it.
Now we'll do that even more simply with the registrants method here
that has rows gets db.execute, select star from registrants.
So I know from our earlier experimentation,
select star means get everything.
From registrants means from that table.
db.execute is just the CS50 method that's going to execute the SQL
and return to you-- what?
I didn't care about a return value earlier, although technically, I
could have gotten back a value, as you'll see in the documentation.
But selecting star from registrants.
What do I want back?
Well earlier, I proposed that a database table, like a spreadsheet,
really is just a list of dictionaries.
And indeed, that's exactly what the CS50 execute method gives you back.
It will return to you if you've used a select a Python list each
of whose elements is a Python dict object, which
means you have access, which each of those
rows to the field name or column name, the so-called key in a dictionary,
and the value, the cell in that table.
So now that I have these rows, what can I actually do with them?
Well, I'm going to go ahead and render a new template, registrants.html,
and I'm going to pass in as you might have
for a past problem all of my registrants by passing in all of these rows.
So it turns out, templates can be parametrized such
that I don't just have to spit out some hard-coded registrants.html file.
I can pass in a key of registrants or call whatever I want,
and the value that I just got back from the database.
So that I'm now handing to my templating language, Ginga all of these rows.
So that suggests that in my template, my so-called view more generally in MVC,
my view can iterate over those rows and spit out every one of my registrants.
Let's go ahead and do this.
Let me go ahead into registrants.html, which will be a new file.
So let me create a new file here called registrants.html.
And let me go ahead and make sure that as are others
extends layout.html so that it looks just like everything else.
And then let me go ahead and just give it a block title up here,
so that its 2 is consistent.
This will be called registrants.
And now I do nblock.
So again, this is the Ginga templating language.
This is not a Python per se, not SQL per se.
It's just really for rendering a viewer or the aesthetics of my site's.
Block body.
And now in here is going to go nblock.
So the question is, how in this new template file
do I spit out like a list of registered students?
Well, list.
I know from some HTML back from week six,
I can give myself an unordered list.
So just a bulleted list like that.
And now I know I can spit out list items to put values
next to those bulleted lists.
But I don't know how many list items to output yet.
But wait a minute, passed into this template
was a key called registrants whose value is rows,
the list I got back from my database.
So you know what?
It turns out that in Ginga, you can execute
essentially Python code that looks like this for registrant and registrants.
And then down here, let me do N 4.
So slightly new syntax here.
So no colon here we're doing 4, and the opposite of it I N4.
I can now do list item.
And next to that list item, you know what?
I'm going to do it and we've seen this syntax before.
Registrant.name from registrant.dorm.
OK, this looks very strange so what's going on?
First line just means we're inheriting from layout.html.
So the whole page is going to just plug in values into that template, namely
a title and a body.
Title's uninteresting.
It's just going to be registrants.
Body gets interesting, but notice it's not hard-coded HTML anymore.
We've got an open UL tag and a close UL.
So this means hey browser, here comes the start of unordered,
the end of an unordered list.
But notice that my template or more generally the view in my software
is now going to use a Python-like loop here.
But again, no colon.
You have these special tags with the curly braces and the percent signs.
And four is the opposite and now notice what
I want to do inside of this loop I want to output literally
open bracket LI close bracket.
And then eventually, open bracket slash LI closed bracket.
And then dynamically, on each iteration of this loop,
I want to output a registrant's name and a registrant's dorm
and just grammatically say from in-between, so it's David from Matthews
and some Zamyla from Currior and Rob from Thayer and so.
And the only thing that's passed in is this thing here.
Remember the named arguments, the named parameters that I
passed into my render template method.
It was registrants equals rows.
This is really equivalent to iterating over the rows that
came back from my database.
In fact, if you want to be even more l I don't
have to call registrants I could just say rows equals rows.
So I'm literally passing in my database rows, and then in my template here,
I could do for row in rows row.name, registrant, row.dorm.
Just semantically, I thought it would be a little more intuitive if I actually
say what these things are and not just generically refer to them
as rows from my table.
All l so let's see this now.
Here that web form.
Let me now go to slash registrants, enter.
And oh my god, a bulleted list.
This may be-- I want to prove that this is working.
Let me go back here.
Let me go ahead and register Stelios now,
who is currently living in Canaday, register.
OK, Let me go to slash registrants again.
Reload.
Stelios from Canaday.
Very interesting.
But this isn't where we need to stop.
Let me add one other feature.
Lets get that Stelios out of there.
I suppose that it wasn't quite making it on the team
here so you know what, we want to have one other route.
Let's have an unregisterred function if Stelios wants to bow out after all
and focus on something else.
So apt out route slash unregister.
You know what?
This is going to support multiple methods.
So by default, it's just get.
But I want it to be not just get but get and post.
And then in here I'm going to have def unregister as the name of my method.
Again, it could be anything.
But you should be consistent, I would say.
And now I'm going to have two conditions.
So this slash unregister page is visited via gets.
You just go to this URL l.
Then I just want to see a same list of registrants
but with some kind of form by which I can delete them from my database,
by which they or I can unregister them.
Meanwhile, I want to go ahead and if post,
I want to acually do the deletion.
So let me try this.
If request.method equals equals gets, then Rose get's d.b.
Execute, select star from registrants.
And then I'm going to go ahead and return the templates, render template,
unregister.hteml.
And pass in those registrants as those rows.
Actually, c with.
Lith requests that method equals equals post.
What I want to do now, I want to do if request.form, quote unquote "ID."
So if there is, in fact, an ID passed in-- more on that in a moment--
I want to go ahead and execute-- hmm, where is this going?
Let me go ahead and see the template first.
I think we need to see this.
Let me go into froshims2.
And I whipped this one up in advance.
unregister.html-- let's go ahead and do this.
So on registrants.html, I just spit out an unordered list
of all the registrants.
In unregister.html, I'm going to add a little bit more.
Inside of each of those list items, I'm going
to have a forms input field-- input element-- whose name is going to be ID.
I've just hard-coded that, because this is
going to represent the ID of the student I want to unregister.
The type of this input is going to be radio.
So these are mutually exclusive circles that you can essentially toggle.
Value of this is going to be whatever this current registrant's ID is.
And then I'm going to go ahead and put this registrant's name
and dorm just as before.
So we'll see what this looks like in a moment.
But notice I've also added two other lines up here.
I've specified I'm inside of a form, the URL for which is unregister.
So we've seen this before. url_for() is just a function that comes with Flask
that helps you dynamically figure out what the actual URL should be
for the method called unregister().
So that's why, if you adopt some nice naming conventions,
you can use tricks like this, and Flask will just figure out to what URL
this form should be submitted.
And the method I'm going to use is going to be POST.
And then down here, notice I have a Submit button.
Type equals submit.
Value equals unregister.
So what does this look like?
Well, let's see that.
If I go ahead and visit /unregister, I see an ordered list, just like before.
And if I played with CSS, I could even get rid of these bullets
altogether, each of which has a radio button next to it, one of which
I can click in order to delete this user from my database.
But how does this work?
Let me go ahead in Chrome now, and inspect, and look
under elements at one such row.
And notice the HTML that Python, and in turn, my Jinja template
has dynamically output it for me.
Here's my page's HTML.
If I expand this list item, I see input name equals ID, and I hard-coded that.
Type equals radio, I hard-coded that.
Value equals-- this was registrant.id.
So in this Jinja for loop where I'm spitting out one registrant at a time,
this is where I was dynamically spinning out
Maria, and Andi, and now Stelios' ID.
So if I expand, the LIs above will see that, ahh, Andi has a value of 2,
because her ID is 2.
And Maria has an ID of 1, because her ID was 1.
Meanwhile, Stelios from Canada is just text.
So if I select him and then click this button,
we need to be able to handle that.
So what do I want to do?
Let me go back into CS50 IDE, into application.py.
And here we go.
If the form was submitted via POST, as by clicking that Submit button,
and if there is indeed an ID in the form--
so the user actually did select one of those radio buttons,
and therefore, there's some actual work to do-- DELETE FROM registrants
WHERE ID equals colon ID, where, again, colon ID is just my placeholder.
The value I want to plug in for ID is request.form quote unquote "ID."
Where did that come from?
Again, if we go back to my HTML, notice that these radio buttons are all
called "ID."
And because they are mutually exclusive by definition of a radio button,
only one ID will be submitted if one of these boxes is checked,
one of these circles is checked.
And that value submitted will be 1, 2, or 3.
So to confirm, here's our page with unregister,
and all three students are still in there.
Here is /registrants.
All three students are there.
Although this is uneditable, let's go ahead
and unregister Stelios by clicking on Register.
And it would seem that he's indeed gone from the bulleted list.
Let's go ahead and check phpLiteAdmin.
Let's go ahead and click Browse.
And Stelios is now gone.
So this is where everything's finally starting to come together, right?
In week 6, we talked about HTTP, and parameters,
and how HTML and CSS worked.
But it was largely static and hard-coded,
and we were just playing around with fake Google
and implementing our own front end.
But now I'm using forms again, and using them not only
to create an interactive UI-- user interface-- for users,
I'm also now implementing the back end, the server,
the routes that are capable of getting those HTTP parameters'
values as with request.form-- or other mechanisms if they come in via GET,
a different syntax altogether.
I can get those values and then do something with them
by combining those values with SQL code.
And so the last line here that I actually
should have included for good measure-- I simply
ran the code that I had pre-written in advance so that I didn't mess up.
Let me go ahead and do this.
This is the one line that was technically there
when I just ran that code even though you didn't see it until just now.
After all this, I have decided, just to keep the UI pretty simple,
after trying to delete someone, just go ahead and redirect,
not to the unregister page again, but to the registrants.
And this is why, after deleting Stelios, I immediately
saw a new bulleted list with just Marie and Andi,
because I redirected the user to the route for registrants, a.k.a.
/registrants.
So again, this is where everything's coming together.
And it's a lot to absorb all at once.
Because, my god, we had HTML and CSS.
Then we introduced Python.
Now we introduced SQL.
Then, we have Jinja and the templating language.
And now all of this comes together.
But again, if you go back in diving into all
of this, first principles and the definitions of each of these, HTML,
it's just the markup language that lets us lay out and format a web page.
HTTP is just the language-- or the protocol,
technically-- via which web browsers and servers intercommunicate.
Python, of course, is a higher-level language.
It's an alternative to C.
And it seems to come with a whole bunch of useful functionality
that, thanks to frameworks, or micro-frameworks like Flask,
make it relatively easy to get real work done with relatively few lines of code.
There's a learning curve, to be sure.
But you know, this is kind of impressive, that with just a dozen
or two lines of code, I've implemented the beginnings
of a web-based application by which students
can register for sports, unregister for sports,
see who's registered for sports.
You know, I might just need to add some logins, and a few other features,
and definitely, some prettier aesthetics.
But that's a lot of functionality packed into just a few lines.
And now that we have SQL today and we have a function like db.execute() that
allows me to execute SQL inside of my Python code,
now we have the ability to store data long-term, to access it, search it.
And we're just using small data sets.
I could store thousands, tens of thousands of rows
and use these same principles, especially
for data science applications, analytics, analyzing corpuses of text.
So many possible applications now.
And you know what, if we will go just one level deeper,
it turns out that while having programming chops with SQL,
and knowing SELECT, and INSERT, and DELETE, and JOIN, and CREATE,
and all of the various keywords we've started to play with today
and scratched the surface of-- super useful and super powerful
when you want to analyze your own data, or your own company's
data, or your own thesis' data, or the like--
it turns out that eventually, you even outgrow that level of interest
typically.
And an additional layer of abstraction is often helpful.
And so another feature you get with frameworks
like Flask is what are called models-- literally, models.
So you recall that we've been talking about,
in general, MVC, whereby we have models, and views, and controllers.
Well, in this model, we have been interacting
with our data via low-level SQL.
It's new, for sure, today.
But it turns out that once you get comfortable with SQL,
and so long as you adopt certain conventions of giving
all of your tables an ID field-- and if you have foreign keys,
it's something, underscore, ID-- where you generally
adhere to certain conventions and adhere to a framework's requirements,
it turns out you can do things like this.
In froshims3, we have essentially the same files,
except that we've changed the application.py
to be a little bit different.
We're not using any of CS50's package or module now,
so we're not using db.execute().
And in fact, you don't technically need to use that to access SQL.
Our single-function execute just makes it much easier
to get back lists of dictionaries as opposed
to executing multiple lines of code as you could do with a library called
SQLAlchemy or Postgres' own-- or rather, SQLite's own-- driver
in the world of Python.
But I'm going to add a few lines here using another library called
Flask-SQLAlchemy, which was pre-installed, or will
be pre-installed, for you in CS50 IDE.
There's a few lines that I had to copy and paste earlier,
from the documentation, to get it to work right.
But notice, this is, perhaps, the familiar line.
Instead of froshims2, it's now forshims3.db.
But notice, at the end, I get another db object.
It's not CS50's.
It now belongs to the author-- or it was created
by the author-- of this library.
But it turns out you can do some pretty cool things as follows.
Recall, from last week, that Python supports classes much like C
supports structures.
And inside of classes can go properties, or pieces of data,
as well as methods, or functions.
So it turns out that we can define, using Flask
and using libraries or frameworks like it, what's called an Object Relational
Mapper, or ORM.
And this is just a fancy way of saying, if you
don't want to think about your data as rows and columns,
which we have been all of today, ultimately,
whether in spreadsheet form or database form,
you'd really like to think of a registrant for a freshman intramural
sport as an entity, as an object of some class, well, you can do that.
You can declare a class called registrant
and have it extend the db.Model.
So this is another class that comes with Flask that we are now inheriting from,
so to speak.
So this is truly now object oriented programming.
We are specifying, via __tablename, that the SQL table to which this class
should map is going to be called registrants.
And ultimately, this class, registrants, when it is instantiated
is going to give me an object that represents a row in that table.
And this object is going to have an ID, a name, and a dorm,
as we've been doing.
And notice here, using SQLAlchemy-- so using this library from Python--
I am declaring a column called ID that's going to be of type integer.
And yes, it's true that it's the primary key, whereas name and dorm are just
going to be text.
Because what you can do with SQLAlchemy, and with ORMs,
more generally, is you can specify, in Python code, or whatever language,
what your database looks like and what your data therein
looks like without actually writing raw SQL queries so to speak.
So even though we've just introduced SQL syntax,
you can eventually take off that layer altogether and build
on top of it using objects like this.
And now, as an aside, this is a constructor or an initialization method
that you might recall from past problems.
But let's focus, a little later in the code,
on why this is actually compelling.
If we scroll down later in the code, like, to my register route,
there is no SQL in this implementation of froshims3.
3
The first few lines are the same.
Indeed, only once we get down to this line here
do we have registrant as a variable, registrant as the class name.
And we're passing in, apparently, the name and the dorm
that came from the HTTP request.
And we're passing those into the registrant class.
If you recall how classes worked, if they have an init() method,
you can pass in some default values, name and dorm in this case.
And that's how we are creating a registrant object of type registrant.
And now notice what we can do here, db.sessios.add().
So this adds to my database sessions, so to speak,
another feature you get from this particular library.
We're going to add that registrant.
And then wonderfully, we're going to commit that registrant.
In other words, we have created a variable in memory-- specifically,
called registrant-- who's type, who's data type, is Registrant, capital R.
And that's simply a class, inside of which is name and dorm.
There's no ID yet.
But what's really cool about this ORM is that when you call add and then commit,
that's like putting it in a database and then hitting Save.
And the database, because of the code we find up here,
is going to automatically insert that ID for us.
And meanwhile, this object now, registrant, is actually going to have,
inside of it, the ID that was stored in the database.
So I don't have to worry about insertions.
I don't have to worry about any updates or deletes.
I can interact with my data now, completely at a higher level, in Python
alone and leave it to the ORM-- SQLAlchemy
here-- to actually do the creation of the SQL statements.
And if you look later in here, if you'd like to play around,
you'll see that we've rewritten registrants and unregister as well
to use SQLAlchemy as opposed to raw SQL queries.
And for instance, here is how you can get all the registrants
in your database if using an ORM.
Instead of doing SELECT* FROM registrants, you can just say,
hey registrant class, give me a query for all of my data.
And what you get back is a whole bunch of rows, which, as before,
we can pass into our template.
Down here, meanwhile, we can request all of those rows
again and pass them into that template.
Or here, notice what we can do.
If we've been passed the ID of a registrant
like Stelios', we can say, hey registrant class, give me a query,
but filter that query so that the registrant ID I care about equals
the one I was passed via HTTP.
Oh, and by the way, once you find that in my database,
call the dot delete method to just get rid of Stelios from the database.
So again, we might not necessarily be solving a problem
or scratching an itch just yet, especially since SQL itself
is, odds are, quite new to you.
And so we've already solved the problem in one way.
Here's another way to solve it.
But realize that, eventually, it's fair to say that you find
writing SQL queries sometimes tedious.
Though frankly, you'll get a lot more control, and potentially,
more performance out of them if writing them yourself.
And so having the best of both worlds is perhaps the best takeaway
here-- actually understanding what's going on underneath the hood,
as was the entire point of our spending so much time in C,
and understanding how you can execute SQL queries,
but realizing, down the road, especially if you find that, wow, it's really
getting a little slow to write all these low level SQL queries,
wouldn't it be nice to just create my database schema
as I did earlier with phpLiteAdmin or at the command line
and then let my library code figure out how
to get data in and out for me, albeit perhaps at a performance
penalty, that's a nice place to get to.
So again, even now that we're in week 9, and we've
abstracted so far away from week 0s and 1s,
is there still this progression and this onward march of abstraction
as the world gets more and more familiar with solving problems and starts
to realize best designs for doing so?
But it's not all fine, and good, and safe.
In fact, let's make note of perhaps one of the most tragically common mistakes
people make when using SQL.
And indeed, one of the reasons to use things
like libraries like CS50's library or even higher-level, fancier
libraries like SQLAlchemy, is to avoid these kinds of threats.
And yet many people and many sites still suffer
from what are called SQL injection attacks, for instance.
So what does this mean?
Well, probably, a few times a week, you log in with your Yale NetID
or with your HarvardKey, which gives you forms like this or like this.
And ultimately, you're providing.
simply, a username and a password.
But suppose that, for the sake of discussion,
the HarvardKey system were implemented on the back end with Python using SQL.
And how, then, do we implement logins?
Well, when I give Harvard or Yale my login name or my NetID
and then my password, well, what are they doing?
They probably have, each of them, a users
table if they're using SQL, whether it's a SQLite, or Oracle, or MySQL,
or Postgres, or whatever.
And they probably have written code somewhere in that login site that says
SELECT* FROM users WHERE username equals whatever they typed in AND password
equals whatever they typed in.
And if that gives you back a row representing David, for instance,
then you know that he or she has logged in correctly,
because you wouldn't have found the row if the username and password weren't
in the database.
Now, it turns out fancier things are done with the password.
You probably don't want to store users' passwords in plain text,
so to speak, borrowing language from week 2.
Rather, you want to store ciphertext or some kind of hashed value
so that even if, in the worst case, your database is compromised or stolen,
no one in the real world actually sees your users' passwords but only
some cryptic-looking hashes thereof, which at least raises
the bar to exploiting your account.
But if they're using SQL-- this is what worries me-- they are, at some point,
taking what I, a human, typed in-- hopefully a good guy,
but could be a bad guy, typed in-- to their website
and plugging it into a SQL query.
Because they're not just going to necessarily do SELECT* FROM users.
They might actually say, SELECT* FROM users WHERE username equals such
and such AND password equals such and such,
or at least one of those predicates.
So what if you've done things poorly in code?
And suppose that Harvard had implemented it in such a way
that this simple-looking, stupid-looking query is actually a really big threat?
So I've temporarily turned off the bullets
that you would normally see in a password form.
This is not hard.
It's just an HTML thing.
And suppose that my email addresses is me@examplemailprovider.com.
And suppose, for my password, I don't type in 12345,
or whatever my actual password is.
I type in, cryptically, ' OR ''1='1.
Why this?
And there's an infinite number of things I
could type if I am aggressively trying to hack into Harvard or Yale's website.
But notice this feels like it's part of a logical query.
It turns out SQL has OR.
We haven't seen that before, but like Python, it literally
has the keyword "or."
And I am assuming, in this case, per some of the examples,
that maybe the programmer at Harvard or Yale
has single quotes in his or her code.
And maybe they are just foolishly, and very riskily, plugging in what I type
in between those quotes.
So notice, this is kind of the end of a quote.
This is the beginning of a quote, but I've not finished the thought there.
So let's see what happens.
Suppose that the code on the back end at Harvard or Yale--
we're in Python-- this.
So somewhere in their files, username variable gets request.form username.
Password gets request.form password.
So just two variables called username and password,
just so I have them handy.
Maybe they're using CS50's library function.
db.execute( SELECT* FROM users-- and this happens to wrap on two lines,
but ignore the extra space-- WHERE username equals {} AND password equals
{}).
So just like we did last week when printing things in a formatted fashion,
this was like Python's equivalent of print diff
using the format method of the string class passing in username and password.
But this worries me.
Because if the programmer at Harvard or Yale has literally given me these
placeholders of '{}', whatever I typed in as my username and my password is
literally going to go there and there.
But what if-- oop, oop, oop, oop, typo.
The whole story breaks without this.
And there-- but what if the user types in that cryptic-looking string?
Well, then, what ends up happening is this.
And it's red because red is bad.
SELECT* star FROM users WHERE username name equals quote, unquote,
me@examplemailprovider.com-- no big deal-- AND password equals--
this is interesting.
Previously, there was a single quote and a single quote,
and then the curly braces in between.
But underlined here is what I, the adversary, typed in.
Cryptically, 'OR'1'=1', that's it.
Notice where the underlining starts and ends.
But I seem to have maliciously finished that programmer's
thought at Harvard or Yale.
I've not finished it in the way they intended.
But this is syntactically and semantically correct, if a bit strange.
I am essentially saying, if the password equals nothing or 1 equals 1,
because I've plugged in those characters in such a way
that they still make a WHERE clause syntactically accurate.
1 equals 1 always.
So this is like saying, SELECT* FROM users WHERE username equals
me@examplemailprovider and whatever else is the case.
Like, that is always true, that 1 equals 1.
So it doesn't even matter what the user's password is
or that I didn't even type it in.
I just did quote, unquote.
All right, well, what if maybe-- OK, so format may be bad.
Curly braces, like we've been doing in Python for string formatting,
maybe that's bad.
Python also has, like Java and JavaScript,
the ability to concatenate things together, in this case,
using the plus operator.
So what if I just go old school and concatenate my strings together
like this?
Maybe that's better, noticing single quote here, single quote here.
And the double quotes are just stopping the string
while we do this concatenation there and there.
It's the exact same problem.
So those instincts don't serve us well at all.
In the end, we still get, username equals
whatever I typed in AND password equals this WHERE 1, indeed, again, equals 1.
So no matter what, this query is going to return a row if that email
address is in there, probably resulting in the Harvard or Yale code logging
this malicious user in.
So there's got to be a better way.
And indeed, the reason to use libraries in general,
whether it's CS50's for the next couple of weeks,
or in the real world, any of dozens of SQL libraries,
is that other people before you have thought through these threats,
have written the requisite code, which isn't all that much,
to notice when there's dangerous queries being injected
and escape them properly.
So here's how we would do this with the CS50 db.execute() method.
SELECT* FROM users WHERE username equals colon username AND password equals
colon password-- sorry for the type there.
I'll fix that.
Bad with spacing today-- AND password equals
colon password, unquote, passing in username, passing in password.
And again, you don't repeat the colons here.
But the key and the key in those named parameters lines up with the key
and the key that do have the colons.
And the way the CS50 library works-- and there's not much
going on in the CS50 library.
There's not much of a training wheel there--
we essentially are simply wrapping that other library I referred to,
SQLAlchemy, which gives you not only the ORM feature, the Object Relational
Mapper feature, where you can create your own classes like registrant,
they also let you, in that library, execute raw SQL.
And they also take care of all of this escaping.
So we, the CS50 library, are really just taking your queries,
passing them to the SQLAlchemy library via raw SQL, getting back the results,
and just neatening them up, and returning to you only
a list with dictionaries inside without expecting
you to execute multiple lines of code.
And now, in green, is the expected solution here.
Because of the way the CS50 library works, and in turn, the SQLAlchemy
library works, even if an adversary types in funky syntax
like those single quotes trying to trick your database or your Python code
into executing something malicious, notice what the library has done.
These backslashes were not in the user's adversarial input.
They were not in the previous red problematic examples.
What the CS50 execute() method does for you,
what the SQLAlchemy library does for you is looks at user input that's been
plugged in for named parameters and says, whoa-ho, wait a minute.
If there's a single quote in there, let me escape it with a backslash
so that the only actual single quotes are the outermost ones.
And only if the user's password is that thing there that's underlined will
they actually get in.
And most likely, it's not going to be something as crazy as that.
But in light of all this, do you perhaps now appreciate
what this particular person, perhaps with a little too much free time,
was trying to do?
So parked in a parking lot here is this fellow's plate here.
And this person had taken the time to print out something a little curious.
Let's enhance.
What the heck is going on there?
Well, it turns out that while we've been focusing on the logic of 1 equaling 1,
the real takeaway of SQL injection attacks
is that if you can somehow trick a database into executing a line of code
that you have written-- previously, the only line was, quote, unquote,
1 equals 1 or what not.
But suppose that that adversary-- suppose
I had included a semi-colon in my username
or a semi-colon in my password.
And you, the programmer, naively trusted what I was typing in.
And you simply executed whatever I typed into my username or my password field,
allowing me to have a semi-colon in there.
And that semi-colon, as you know, ends one SQL statement and begins a new one.
Suppose, god forbid, like this person here tried
to finish his or her license plate in a SQL-like way,
with a quote here, and commas, whatever those mean, and a semi-colon,
but then also included a valid SQL command like DROP DATABASE TABLE, which
is the only thing I cautioned about, earlier,
being especially bad-- this person was apparently trying to use those traffic
cameras, which, he or she surmised, might
have been using SQL as the back end and storing people's license
plates in that back end and, correctly or incorrectly,
was hoping that, upon a camera seeing this,
using Optical Character Recognition, OCR, converting this into text,
passing that text into a database that might not be scrubbing
or sanitizing its inputs as we've proposed
with CS50's library or SQLAlchemy-- was hoping-- that maybe that back end
database was poorly implemented enough to trust
what was passed in so that after logging this person for speeding
or whatever, actually dropped the entire database, covering
his or her tracks entirely.
So all this and more is ahead of us as we continue to build, and build,
and build on top of lessons past.
And next week, when we introduce one final language, JavaScript, a language
that you can not only use on the server side
but also, and especially, on the client side
to create all the more of an interactive experience
and all the more of a compelling user experience
for users using a bit of Python, and SQL, and HTML, and CSS,
and soon, now, JavaScript, we'll see you then.
[MUSIC PLAYING]
SPEAKER 1: Rosebud-- yeah, actually, now that you mention it, one time, I went
into his office to look for some forms.
[KNOCKING ON DOOR]
SPEAKER 2: Bud?
David always said "pal."