Placeholder Image

字幕表 動画を再生する

  • [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.

  • 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.

  • 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.

  • This shall be text.

  • This shall be text.

  • This shall be text.

  • 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."

[MUSIC PLAYING]

字幕と単語

ワンタップで英和辞典検索 単語をクリックすると、意味が表示されます

B1 中級

CS50 2016 - 第9週 - SQL (CS50 2016 - Week 9 - SQL)

  • 15 6
    小克 に公開 2021 年 01 月 14 日
動画の中の単語