SQL BASICS - CS50 on Twitch, EP.6 (SQL BASICS - CS50 on Twitch, EP. 6)

字幕表動画を再生する

[MUSIC PLAYING]
COLTON OGDEN: All right, and we're live.
Hello, world.
This is CS50 on Twitch.
And I'm joined by CS50's own David Malan.
DAVID MALAN: Nice to see everyone again.
Hello to the viewers in the chat room right now.
We've got a few people in there.
We got to see [? Bavick ?] [? Knights. ?]
Scotty529 says, I don't have SQL experience but I'm excited to learn.
Excited to teach some SQL today.
Or I'm going to be learning some SQL today.
Yeah, Colton is going to be pretending not to know much about SQL.
So--
COLTON OGDEN: Pretend.
DAVID MALAN: --you can experience vicariously
what it's like to learn a little bit of structured query language today.
COLTON OGDEN: Sure.
Andre's here in the chat, says hello.
Hello, Andre.
Good to see you.
Good to see [? Bavick ?] [? Knight, ?] Zodiac.
And [? Nuwanda, ?] that's [INAUDIBLE] from last when
we did the concentration game.
DAVID MALAN: Yes, good to see some familiar usernames here.
Some of which are a little hard to pronounce,
since they're not exactly words.
But I hope you'll forgive just a little wave from us here.
COLTON OGDEN: All right, and so what are we going to be talking about today?
DAVID MALAN: So why don't we just talk about databases?
We actually, in CS50, of course every semester talk about SQL
and databases more generally.
But we tend to move through it really quickly.
We don't necessarily get too much into the weeds of doing things hands on.
And in fact, even when we do, we tend to use CS50 IDE.
But for the more comfortable audience that might be tuning in via Twitch,
thought we could play a little more hands on the terminal window
on my own Mac, which you can certainly do on your own PC
as well, so that it feels a little more real-world representative and less
classroom like.
COLTON OGDEN: Cool.
Yeah, sounds great to me.
Do you want me to move on into your laptop on the screen?
DAVID MALAN: Yeah, if you don't mind.
I pulled up a little throwback from 1999.
COLTON OGDEN: Oh, are we--
DAVID MALAN: Let's see if we're plugged in here.
Give us just one moment.
COLTON OGDEN: Should be plugged in.
DAVID MALAN: So what you're about to see is a screenshot from a website
that I think I first created back in, like, 1997.
I was a sophomore or junior in college at the time.
And I was part of the freshman intramural sports program,
or Frosh IMS program.
This--
COLTON OGDEN: I think you might need to-- are you on--
[INTERPOSING VOICES]?
COLTON OGDEN: Are you on 720p?
DAVID MALAN: Ah, let's take a look.
Pardon me.
We're just going to go into my system preferences here on Mac OS
and make sure I'm in the right resolution.
SPEAKER 1: Or you can just mirror it.
DAVID MALAN: Let me go ahead and mirror my display here.
All right.
COLTON OGDEN: Appreciate it.
DAVID MALAN: Now that we have that, let's go ahead and give you 720p.
COLTON OGDEN: Awesome.
DAVID MALAN: There we go.
Cool.
COLTON OGDEN: There we go.
DAVID MALAN: No movie magic here.
Everything is happening incorrectly here in Cambridge, Massachusetts,
as you see it.
No post-production.
So what you're seeing on the screen now behind me and Colton is Frosh IMS,
the freshman intramural website.
COLTON OGDEN: Who designed that great background?
DAVID MALAN: Well, obviously I did.
Though to be fair, it was probably something we found on Google images
before there was even Google images back in the day.
COLTON OGDEN: How did you find images back then, if it--
were there like user groups, or?
[LAUGHTER]
DAVID MALAN: I think I had to call someone on the phone
to get an image back then.
COLTON OGDEN: Send a floppy over the--
DAVID MALAN: Yeah, pretty much.
The [INAUDIBLE] too.
Someone would deliver it to you in the mail.
Yeah, no, I don't remember where the image came from.
But clearly it's all in its 1990s glory here.
And at the time, like, so Harvard for the students
had an internal sports program, which just
means sports for those of us like myself who
couldn't compete at the varsity or junior varsity or really any level.
And was really just for fun.
But at the time, if you wanted to sign up for sports,
you would literally fill out a piece of paper, walk across campus,
and then hand it to someone physically or slide it under their door.
And then you were registered for the sport.
So nowadays, you would clearly think that you could at least use a Google
form for this.
But back in the day, there was no Google.
There were no Google forms.
And even then, there was no website even for the intramural program.
So my first foray into web programming was just to learn it on my own.
I mean, I did not learn how to do web programming in CS50 or another class
here called CS51.
So I was completely self-taught.
And frankly, to this day I think the best way to learn something new
is just force yourself to do it and pick it up--
COLTON OGDEN: Almost feels like the--
DAVID MALAN: --along the way.
COLTON OGDEN: --Wild West of the web in a way.
DAVID MALAN: Yeah.
You were alive at the time, I think.
It wasn't all that long ago.
COLTON OGDEN: Ashley says thanks for remembering my name, Colton.
Thanks, Ashley, for joining us today.
DAVID MALAN: Yeah, indeed.
Good to see everyone.
And interject at any point over the chat with questions
about this or really any other topic.
So we always talk about Frosh IMS, not just so
that I can relive my glory days, but so that we
can use it as a point of departure for talking about where the data actually
goes.
So the whole point of the Frosh IMS website,
besides being informational and telling people about the website,
about the sports that were upcoming, was to allow
students to click a link, type in their name, their dorm, maybe their phone
number, although no one really had cell phones back then, their email address,
and then click submit so as to register.
And at the time, I had no idea what to do with the data
once someone actually clicked submit.
So I pretty much found a library, which did exist at the time,
and I just sent an email to the person who was running the sports program so
that he or she could just have in their inbox
a list of all the kids who had registered.
So this is clearly an opportunity for a database.
And like, what would you have done maybe back in the day
if you needed to store something elsewhere than someone's inbox?
COLTON OGDEN: Probably written it down on a piece of notebook paper.
But that's probably not a sustainable solution.
DAVID MALAN: No, that's essentially what people did there.
And let's see, actually, Andre's noting back
in the 1990s we used smoke signals for internet transfer.
The big breakthrough happened when Morse code was invented.
Wow, that's very, very, very well--
very appreciated comment, Andre.
I'm pretty sure that's not entirely true, for those of you who
were born in the 90s or after.
COLTON OGDEN: JP Guy's rating with a party of five.
So I believe that means, and I'm somewhat new to Twitch myself,
but JP has actually brought his channel's chat into our chat.
DAVID MALAN: Oh, nice.
COLTON OGDEN: That's what that-- the connotation of that.
So thanks so much, JP, for that.
Dr. [? Profedo ?] says raid, raid.
Heard you had cool hair, says [? Tangora. ?] Thanks.
Appreciate that.
JP Guy has his emoji there.
DAVID MALAN: No.
In fact, we'll be having an upcoming Livestream about Colton's hair.
So be sure to tune in later this week on that.
COLTON OGDEN: Oh man, oh man.
[INAUDIBLE] you want to read-- maybe read some of the other ones
below Andre's there too?
DAVID MALAN: Yup.
Whip Streak, nice to see you as well.
So what of course might you do with something like this?
So back in the day, what I would do is actually store
the data, not just in someone's email inbox, but in a CSV file,
or comma separated values.
Which is kind of a poor man's database, so to speak, whereby all you need to do
is to be able to do F open and then F write,
and then actually write the data to a file.
And that's it.
And it's kind of a lightweight database.
Wouldn't you agree?
Yeah, that's right.
So he's not quite on screen, so it just seems like I'm talking to no one there.
So but of course, the problem with CSV files might be what?
Like where does the limitation come to mind?
COLTON OGDEN: A little bit less than human readable, I would probably say.
DAVID MALAN: Yeah, it gets a little annoying,
especially when you've got more and more words and lines.
COLTON OGDEN: I was going to say if I were to do it back in the day,
probably would have opened something like a text file
and just written them out line by line.
But that's effectively what a CSV is.
DAVID MALAN: Yeah.
You're just using commas to sort of imply that there's a column there.
COLTON OGDEN: Sure.
DAVID MALAN: Right?
COLTON OGDEN: Yeah, that's the only thing I guess I could think of,
would be that it's a little bit hard.
Hard to do.
Hard to maybe find things you're looking for, I guess, in a programmatic way.
But I guess we'll get into a little bit of that.
DAVID MALAN: Yeah, no, I mean, Colton's right.
Because if you think about CS50, or if you have any other prior programming
experience, you might have thought about running time or performance.
And a CSV file is a file.
And files are slow, partly because you can only read them top to bottom.
Like you have to start at the first byte and read all the way to the end.
And then you have to do sort of big o of n or linear search
on anything, which can grow-- it can become tedious, certainly,
once the data set gets large.
So Andre, you're about the same age as me, I believe.
Just pointing out that talking about the olden days of the internet
makes me feel, well, you are old.
I think you're much older than me, if I may say.
So I'm just reminiscing about things I've heard about, of course, Andre.
But thank you.
COLTON OGDEN: We missed [? Gossen's ?] note there. [? Gossen119 ?] says,
is this a part of the Harvard CS50 course?
DAVID MALAN: It is.
Well, it's sort of ancillary.
So Colton has been taking the charge on leading this and other sessions
with Kareem, Bryant.
Nick you'll meet soon too.
Really doing something beyond the scope of the course
to have a more casual chat with folks interactively, albeit with us
on video and you on chat, so we can explore more topics hands
on especially.
COLTON OGDEN: Yeah, sort of shift a little bit of maybe the load on lecture
from coding to doing that here, given time limitations.
DAVID MALAN: Yeah.
So David asks, what's the difference between SQL and NoSQL?
So in a moment we'll dive into more detail on what SQL itself is.
But SQL, you'll find, is a language for manipulating relational databases.
And the simplest explanation of a relational database
is something that's based in rows and columns.
Really it can do spreadsheets, but running into computers memory
not just visually laid out for the human.
NoSQL is not that.
It instead tends to store things as documents, so to speak,
or as objects where there is actually more hierarchy and even nested
structure.
[? Gossen119, ?] I wouldn't say SQL lacks security.
I'm not quite sure what you mean by that.
In fact, you're welcome to elaborate here so that we can respond.
They are really different data models, so to speak, and different mindsets.
And NoSQL has come into vogue in recent years,
but SQL still very much has a lot of momentum still.
And in fact, it allows you to model your data a little more, let's say,
with a little more rigor, perhaps, even than a lot of object storers do.
But more on that in a bit.
COLTON OGDEN: So NoSQL would be a little bit more flexible, but maybe not
as efficient?
DAVID MALAN: It's definitely more flexible.
It eliminates some of the pain involved in actually thinking
through your schema and factoring everything out.
It can be more performant, in that you don't necessarily
have to rejoin data in ways that we'll talk about in a bit.
But really to each his or her own.
They can both be used for similar problems.
So in MySQL is a very specific implementation of SQL,
also called Maria DB these days.
MySQL was just one of the most popular versions of software
via which you can host a SQL database.
COLTON OGDEN: I didn't realize those were the same.
DAVID MALAN: Which one?
COLTON OGDEN: Maria DB and MySQL.
DAVID MALAN: I think Maria DB is like the open source spinoff or fork of what
has been historically MySQL.
But I'd have to dig back into the details in Wikipedia or the like.
It coexists though alongside things like Postgres,
which you might have heard of.
Heroku uses that.
Oracle, certainly one of the bigger fish in the space, Microsoft Access SQL
Server.
So it's just an alternative, but a free and open source alternative.
COLTON OGDEN: Is it the case that CS50 is potentially
going to look more into Postgres for future stuff?
Is that something you were mentioning in lecture?
DAVID MALAN: A little bit.
Only in so-- so back in the day, we used MySQL in CS50.
And honestly, it was just a pain in the neck
for students to run not only their own web server on their Mac or PC
or in the virtual machine or now in CS50 ID, but also a database server as well.
It's another process.
It's another port.
It's just another thing to manage.
So we actually transitioned to SQL Lite instead, which we'll use today as well.
Postgres is more analogous to MySQL, competitor to it if you will.
And we have introduced students to the data types used by Postgres this year.
Because Heroku, frankly, which is this wonderfully free, very easy to use,
cloud-based service for hosting a web app, it's what they offer.
And it was a nice way to sort of prepare students to graduate from CS50
and go off into the real world with one such popular service.
COLTON OGDEN: OK, cool.
Sounds good.
Makes sense.
DAVID MALAN: Indeed, yeah.
So let's see.
Perhaps this has been asked before, but how
do you handle big data in terms of SQL or other database structures?
OK, so hang in there for a look at what SQL actually is.
But for those a little bit familiar, you can use SQL as well as NoSQL
for big data.
It really depends on what your definition of big data is.
But you'll see that within a SQL database
there's a lot of design decisions that you can make along
the way to improve performance.
Indexing certain fields, deciding how to or not to normalize your database
can be a big question there.
NoSQL can certainly be used as well.
It kind of depends on your data set.
It depends on your comfort.
And honestly, it depends on if and when you bump up
against some actual limitations.
Starting with what you know, starting with what
you're comfortable with is always a reasonable design decision, I think.
And when you actually have a problem to solve,
does it make perfect sense too to rethink that whole architect?
COLTON OGDEN: And big data--
DAVID MALAN: Architecture.
COLTON OGDEN: --normally you hear like Python,
and are big languages and big data.
So I'm assuming you can use MySQL with other programming languages too, right?
DAVID MALAN: Oh yeah.
I mean, those languages are fairly agnostic to big or little data.
But the database can certainly be finetuned to the size of the data
that you actually care about.
And this is a can of worms of topics too.
You can introduce discussions of replication of databases,
caching of databases, views that you can impose on databases.
There's so many ways to make more efficient sense of the data
that you have.
And we'll only scratch the surface surely today.
But keep the questions coming.
COLTON OGDEN: Cool.
Makes sense.
DAVID MALAN: All right.
So shall we talk about what SQL, is for those less familiar, perhaps?
COLTON OGDEN: Let's do it.
Let's take a look.
DAVID MALAN: All right.
So let's go ahead and formulate things like this.
Let me go ahead and open up a Google spreadsheet here, untitled spreadsheet.
It's got nothing in it by default.
But what's nice about a spreadsheet, whether it's Google Spreadsheets
or Excel or Apple Numbers if you're more familiar,
is that it lays out your data in rows and columns.
And of course whenever you're using a spreadsheet,
like, how do you use these rows and columns personally?
COLTON OGDEN: I usually categorize my--
I mean, I guess you could do it both ways.
But I'll usually have a category of information along the top.
DAVID MALAN: OK.
COLTON OGDEN: So that first row, maybe it's there would be a name field,
and like an address field.
And then each-- I guess it would be shifted over,
and then each on the left, that column there,
will be the individual people or the individual entries in the database pf
my document.
DAVID MALAN: OK.
COLTON OGDEN: So David on the first one, Colton on the second one.
And probably the name is duplicated in this instance,
if we were to just put their name there, but the same idea applies.
DAVID MALAN: Yeah.
So the columns each represent something like an entity, so to speak,
or a field.
And then the rows represent a piece of data broken out
into these individual cells within those columns.
So you could do it completely the opposite.
Like what Colton just said could be completely flipped with rows
and columns and columns and rows.
But frankly, this just happens to be a little more user friendly in
that you can scroll down, which is just more pleasant generally, when
you want to look through your data.
And you only scroll left to right when you
want to see additional fields if they don't all fit on the screen.
Yeah, so this is exactly the mental model
you should have when it comes to a database.
But the problem when it comes to a spreadsheet
is when it gets large, like thousands of rows or tens of thousands of rows.
Like Google Spreadsheets and Excel and Numbers,
they're not meant to handle tens of thousands of rows.
Like you could still hit control F or command F to search for something,
but you might get the stupid spinning beach ball or hourglass
because they're just not designed to search over huge data sets efficiently.
And you can certainly edit them by moving your cursor up,
down, left, right and typing, but there's
no easy way other than find and replace to do things programmatically.
And so very quickly, or eventually rather, especially with big
data to your point earlier, does it become the wrong tool for the job.
COLTON OGDEN: Sure.
DAVID MALAN: So SQL Lite, or SQL more generally,
is a language via which you can read and write and update and delete data.
But using actual commands, not just using your keyboard or your mouse,
but actually writing code in a language called SQL.
So let's go ahead and do this.
Let's actually flesh out what we might want to store in this database.
You proposed name and address.
What else might a human have?
COLTON OGDEN: Sure.
So maybe a phone number, because now people do have cell phone numbers.
DAVID MALAN: OK, thank you.
Maybe an email address like we said earlier.
COLTON OGDEN: Yeah, email address.
And I guess it depends on what this would be used for,
but we probably could suffice with four or five fields for now.
DAVID MALAN: OK, yeah.
Let me just boldface these in the spreadsheet,
even though this has no semantic meaning.
Just to make clear that those are our columns or our fields.
And then a few of-- a couple of us will be like the data set here.
So I might live at, say, 33 Oxford Street in Cambridge, Massachusetts,
02138.
And the number here might be 617-495-9000, and Malan@Harvard.edu.
Whoops.
E-D-U.
Let's go ahead and make a little more room here by blowing these out.
And then you, meanwhile, might live down the street at 1 Oxford Street,
maybe Cambridge, Massachusetts, 02138.
617, we can all go through the same switchboard, 495-9000.
And then Cogden@CS50.Harvard.edu.
All right, so that might be two representative pieces of data.
And those of you following along online might
notice that there's already a little bit of redundancy in here.
And odds are you can spot this too.
Whip Streak chimes in here.
I know this is completely unrelevant, but Colton said to make suggestions.
And I would suggest the tic-tac-toe in [INAUDIBLE] and love.
COLTON OGDEN: Duly noted, Whip Streak.
Thank you.
That sounds like something we could pretty easily do, so yeah.
We'll take a look at that.
DAVID MALAN: Yeah.
Let's do it.
Tic-tac-toe in [INAUDIBLE] and love.
Go for it.
I'll do that maybe in another stream.
I will prepare for a little bit of time there.
So, OK-- so [? bobak, ?] you propose an ID number.
So yeah, that's actually a good idea, too.
The spreadsheet kind of gives us that notion--
with the column all the way on the left, the one, two, three-- but of course,
those numbers are going to change if we sort things.
So absolutely, I like this idea.
Let me go ahead and insert another column
and just arbitrarily assign each of us a number-- generally,
by convention, starting at one-- and then just auto
incrementing it's way on up to 2 billion, 4 billion-- however high
you want to go.
And this is going to be advantageous because SQL is indeed
a relational language whereby the whole purpose is to relate data in one sheet,
if you will, to data in another.
And so we're going to find that this number is actually
a nice way of relating data in this sheet to another data set all together.
COLTON OGDEN: Maybe another database where we have courses that we teach,
for example.
And then you teach a SQL course, I'll teach a games course--
and maybe the other database can map the IDs to those courses?
DAVID MALAN: Yeah, so let's actually do that.
Let me go ahead and rename this spreadsheet
for the moment to say Instructors.
And that kind of implies that we'll have some other tables
here in just a moment, too.
There is some redundancy though, here.
What would you point out as being redundant?
COLTON OGDEN: Probably-- well, the phone number, for one thing.
It's duplicated amongst both of our--
DAVID MALAN: --OK, that's fair--
COLTON OGDEN: --tables.
And the addresses both contain Cambridge, Massachusetts,
and the 02138.
DAVID MALAN: Yeah.
COLTON OGDEN: So there's probably an ability for us
to factor that out somehow.
DAVID MALAN: Yeah, and so there's a buzz word in database design called
normalization, where as soon as there's an opportunity to factor out
common text that appears again and again and again, odds
are you'll benefit space wise by just removing it, putting it in one place
once, and then somehow linking the data.
Now, this is a bit of a mislead, because it turns out
in the US that zip codes do not necessarily uniquely identify cities.
But for the sake of discussion, let's assume as much.
And let me go ahead and create another sheet here called, let's say-- cities.
And maybe that city is going to have a city name, a state, and a zip
code associated with it.
But because zip code is going to be a little more important here,
I'm going to go ahead and--
well, what you want to put in this table, exactly?
What do you think?
COLTON OGDEN: In the zip code?
DAVID MALAN: Yeah.
And what do you want to factor out here, exactly?
COLTON OGDEN: Probably this--
well, I'm trying to think how it would be best to store--
because the table itself wouldn't semantically
know that a state belongs to it--
I guess we could have a states table--
DAVID MALAN: --hello from Cambridge--
COLTON OGDEN: --so we could have states that maybe get an ID.
DAVID MALAN: OK.
COLTON OGDEN: And then cities that--
I guess they could also get an ID.
DAVID MALAN: Yeah, we could kind of continue this pretty deep.
So let's keep it simple, maybe.
And let me propose that why don't we call this not cities, but zips--
or postal codes, for those more familiar with that term.
Let's just move this column over here, just because it kind of
is most important.
And to your point earlier, let's go ahead
and create an ID column as well, just so that we can number these things
arbitrarily, but with simple numbers.
And let's go ahead and put in one of these zips--
02138.
And you already see a potential problem with databases.
COLTON OGDEN: You had this issue a long time ago doing something, didn't you?
DAVID MALAN: I did.
So-- stupid, stupid feature, sometimes.
Right?
The leading 0 disappeared.
But here in Cambridge, Massachusetts, we need to have that zero.
The fix, of course, is to reformat your data--
which is an issue we'll come back to in a minute-- as plain text,
and now tell Google not to ignore it.
And indeed, yeah.
When I used to use Microsoft Outlook for email years ago,
at some point I transitioned to Gmail and I exported all of my contacts
from Outlook--
some of whom lived in Cambridge, Massachusetts, 02138.
And damn it if I didn't make the mistake of opening
the export, which was a CSV file-- so comma separated value file.
Accidentally must have hit Command-S or Control-S to save the file,
and stupid Excel got rid of all of those leading zeros on all
of my friends' and contacts' zip codes.
And so to this day-- years later--
I'm living with friends who still have 2138 as their zip code,
because I haven't fixed them all manually.
COLTON OGDEN: It's an unfortunate consequence
of technology trying to work in your favor and screwing it up.
DAVID MALAN: Indeed.
So, [? Bavek, ?] if we are indeed moving right now.
Maybe try a reload if you're only seeing the chat and not the video.
We are doing a bit of typing in this spreadsheet,
and I think our human bodies are moving a little bit, as well.
So the database automatically generates--
OK, so David, you're mixing technologies here.
So MongoDB is an example of a NoSQL database,
and indeed it can create unique identifiers for you,
but frankly, so can SQL.
And right now-- we've not even looked at SQL, per se.
We're literally just using Google Spreadsheets
and manually adding these IDs.
But most database engines in SQL can also
add these unique identifiers for you.
So you do not need to create one manually--
as I'm currently doing for the sake of discussion.
COLTON OGDEN: And JP, in solidarity, says "thanks Excel.
Face palm."
DAVID MALAN: Yes.
Indeed, indeed.
So let's just finish this up.
So if you know that 02138--
for the sake of discussion-- always maps to Cambridge, Massachusetts,
we can go ahead and create a table-- a row-- just like that.
And let's do another one I know from childhood--
Andre, maybe you're in on this with me, even though different country.
90210 is one of the few other zip codes I know, and that's Beverly--
Beverly Hills, California.
So we might have another row in this table, there.
And let's just give these unique IDs of 1 and 2 as well.
So what you can do now is notice that Cambridge, Mass,
02138 has a unique identifier of 1.
So what we could do is go over to the instructors
and actually change the address to be something like this--
not just be an address, but we could have it have a city column.
And we can clarify this as just the street address,
so that now this is 33 Oxford Street, and then city ID equals 1.
And then meanwhile, you--
Colton-- live at 1 Oxford Street in this story,
so your city ID is also going to be 1.
Let me go ahead and just left align everything for consistency.
And so now you can see that we're storing the same information
but across multiple tables.
And so we've made a relationship between the instructors' table and the zips'
table so that, if you do somehow combine these two,
you can reconstruct the original data set.
COLTON OGDEN: And it's not worth necessarily storing all of the street
addresses because there are just so many of them,
but there is a smaller but still sizable number of cities and-- well,
I guess countries, as well, you could have--
but zips.
DAVID MALAN: Exactly.
We could certainly continue this, and you could certainly factor out
Massachusetts and have a state ID.
You could factor out country, if we'd included that, and have a country ID.
But at some point you're just creating work for yourself,
and you have to decide for yourself-- design-wise--
where that inflection point is.
So you mentioned before that we teach some courses.
So let's add one more table.
Let me go ahead and create a sheet called Courses.
And one will be called, for instance, CS50,
and that's Introduction to Computer Science.
The other one is nicknamed GD50, which we
call Introduction to Game Development.
And now these things, of course, should have some columns.
So what would you title the columns here, would you say?
COLTON OGDEN: So, course abbreviation is not the greatest one,
but maybe course handle?
DAVID MALAN: Handle?
OK.
COLTON OGDEN: And then, course title.
DAVID MALAN: Title.
OK.
I'll boldface these just to make clear that those are the field names.
Let me go ahead and-- again, just for consistency-- create an ID column.
And it's OK that I'm reusing 1 and 2 and 3 and 4 and 5
again and again, because they're in different tables in this case.
So now we need to somehow relate who teaches a course to the course itself.
So let me propose, as a straw man, this.
Let me go ahead and say the instructor ID for this course is-- well let's see.
If I go back to the instructors table, so David is ID one.
Colton is ID two.
So if I teach CS50, we'll put a one here.
And if you teach GD50, I'll put a one here.
Let me line everything on the left again.
Oh, sorry.
I took over the course.
Two.
So now we've created another relation but this time between the courses
table and the instructors table.
But it's kind of a leading question.
I've kind of messed something up here.
Like, who actually teaches GD?
Well who technically teaches GD50?
COLTON OGDEN: Technically, both of us do.
You're the leading instructor.
I'm the secondary instructor.
So there should be two instructors, in that case.
DAVID MALAN: Yeah.
And whoever is who, it feels like we need two instructors.
So let's assume that the columns need to be uniquely named.
So maybe this is, like, instructor 1 ID, and this is instructor 2 ID.
And that's OK because then I could come in as a co-instructor.
And maybe no one teaches CS50 with me, officially.
So that can just be null, and that's OK.
But suppose that we bring on a third colleague.
You know, him or her would need to go in--
well, OK, instructor three ID.
And if, God forbid, we have a fourth person, we'd need another column.
And so this tends to be a bit of a slippery slope.
If you know only that there's going to be one instructor for any course,
super clean.
If you know there's only going to be two instructors, OK, fine.
Just have two columns.
But if you have a variable number of instructors,
you could just be defensive and be like, all right, well let's just
go all the way up to instructor 10 ID.
But for most courses, most of those columns are going to be blank.
So at some point, it just gets a little stupid and inefficient.
You're just wasting space and adding messiness to the table.
So you would actually then use a join table instead.
COLTON OGDEN: OK.
DAVID MALAN: So what I'm actually going to do is get rid of all of this.
Let's not actually put the instructor in the table because of this variability.
Let's give ourselves a little more flexibility
and create another table here that, by convention, is typically
called something like course underscore instructors
where you have one word from each of the tables with an underscore
by convention.
But you can call it anything you want.
And then, you could call it teaching assignments or whatever.
So in here, I'm going to have a couple of columns.
One is going to be called, say, course ID.
The other can be instructor ID.
Let's go ahead and boldface those to make clear that there are columns.
And now the courses have IDs 1 and 2 for CS50 and GD50.
So let's just put that in here.
Course 1.
Course 2.
The instructors are, again, David is 1.
Colton is 2.
So we can 1 and 2.
But if I'm also going to be a co-instructor, technically, of GD50,
that's another row for course 2 with another instructor
ID, which happens to be mine.
And now we have unlimited number of potential colleagues.
COLTON OGDEN: So we don't put the burden of actually storing
those relations in the data.
It's more like we have a separate database that keeps
track of all the possible relations.
DAVID MALAN: Exactly.
And this now allows for a many to many relationship as opposed
to a many to one relationship.
Like, multiple courses could be taught by the same human.
Or one human could teach multiple courses or both.
And that's the reality here.
Each of us is, potentially, involved in multiple courses.
So by having this intermediate join table, so to speak,
that literally joins the two tables conceptually
can you avoid this problem altogether.
And so just to be clear because a lot of CS50 students
make this mistake on campus, you should never
be in the habit of adding columns to your database dynamically.
Once you've decided on your database tables and schema, so to speak,
that's it.
You should only be changing them when you want to add features.
You should not be changing your schema when you want to add data.
COLTON OGDEN: Sure.
Makes sense.
DAVID MALAN: So we've had a few comments here.
So yes, this stream will be available later today on YouTube.
COLTON OGDEN: Yep.
DAVID MALAN: Take a look at.
COLTON OGDEN: [? One Bad Panda ?] is hosting us
on his channel, which means that people looking at his channel
are seeing our video.
DAVID MALAN:
COLTON OGDEN: So thanks, [? One ?] [? Bad ?] [? Panda, ?] very much
for hosting us on your channel.
Very much appreciate it.
DAVID MALAN: Not a sentence most people have probably said before.
COLTON OGDEN: Yeah, that's true.
Let's see.
DAVID MALAN: [INAUDIBLE] said, hey David,
I've sent you an invitation on Facebook.
Except it.
I will do my best.
I'm afraid we get lots of kind invitations and notes.
And we try our best to keep up.
So I'll try to catch up soon.
COLTON OGDEN: You're the friend cap, aren't you?
You have 5,000 friends?
DAVID MALAN: That's also the thing too.
Yeah, you're limited in the number of requests you can actually get.
So I have to wait till a few of my friends
unfriend me sometimes before I can say hi to new people.
COLTON OGDEN: Cool.
DAVID MALAN: All right.
So none of this thus far has actually been about databases.
But what we have done is create relations among data.
The problem is with something like Google Spreadsheets,
or Excel, or [? Numbers, ?] it's kind of hard now to answer simple questions,
like, Colton, what courses do you teach?
Or what courses are taught by David and Colton?
Or what courses don't have an instructor?
Like, for that, you really want a query language.
Like, some way of typing a command in the form of a question
and getting back an answer.
And yes, you could write macros in these spreadsheets.
You could do Find, and Search, and of Copy Paste to find the data.
But at some point, it just becomes the wrong tool for the job.
And thus, were born actual databases.
COLTON OGDEN: And you can't integrate Excel necessarily easily
with a web app.
DAVID MALAN: No, exactly, especially when
you're writing code in some actual programming language.
Python, Java, PHP, [? Roby, ?] whatever.
It is indeed hard to then interface it with a spreadsheet,
unless you just dump the data as a CSV, which is totally fine.
But then too, you lack some of the dynamism,
and it devolves back into linear search and linear time operations.
COLTON OGDEN: [? One ?] [? Bad ?] [? Panda. ?] You guys helped me write
a game mod that helped my stream.
I appreciate you guys.
That's awesome.
Out of curiosity, which mod?
I'd like to know.
DAVID MALAN: Yeah, and I'm sure all thanks go to Colton on the gaming front
there.
All right.
So let's actually transition the conversation to an actual database.
So what do you need to run a database?
Well you either need money to pay for something like Oracle,
or SQL Server, or Microsoft Access.
Or there's so many open source and free options these days.
My SQL.
I DB.
Postgres and also SQLite.
And SQLite is, perhaps, the easiest, lightest weight way to get
started because it's light.
It's nice and simple.
Has fewer features, but it's just really easy to get started.
And it also happens to be what iOS and Android are using on phones.
So it's also wonderfully applicable.
It's not even just a little toy environment.
COLTON OGDEN: Cool.
DAVID MALAN: So how to do this?
So typically, you can install a command on your own Mac, or PC,
or Linux box called SQLite or SQLite 3, which is the latest version.
And if I go ahead and do that, I can specify the name of a database
that I want to create.
So this one might be called, say, Harvard dot DB
if we're making a database for Harvard courses,
and instructors, and all of that.
So now you just got a simple prompt to like
this where you have SQL Light as your command
prompt where you can actually start typing commands.
Now if we wanted to actually create a table,
we're going to have to create a command with which to create that table.
And I'm just bringing up my little notes because, frankly, I never remember
the precise syntax for creating tables.
After that, it gets a little easier.
But I do recall that it's create-- whoops.
Strike one already.
Create table, literally.
I can type in uppercase or lowercase.
But I would say convention tends to be to use uppercase for the SQL commands.
And then, lowercase for your own choice of words.
So if I want to create a table that has, for instance, an ID column, and a name
column and, say, an email column--
and let's keep it simple with just those initially--
called instructors, I can actually do exactly that.
So let's create a table called--
what did we call it before?
Instructors that has an ID, name, and email.
But with SQL and SQLite in particular, you have to be a little more specific.
You can't just specify the column names.
You have to specify the types.
So turns out SQLite has a few well-defined types.
One of which is integer, which I'm going to write as here.
Another of which is--
actually, let's do it in all caps for a moment-- integer.
Another of which is text.
And then, we'll use text again for these, as well.
COLTON OGDEN: To clarify, is everything being uppercase or lowercase optional?
Is it case insensitive?
DAVID MALAN: It is optional for the SQL keywords.
What you type inside single quotes, or double quotes, or even back ticks
is going to be case sensitive.
COLTON OGDEN: OK.
DAVID MALAN: Good question.
So it turns out this is where SQLite does
differ from fancier databases like MySQL on Oracle in that SQLite only
has a few data types.
It only has integers, it has reals, which are like floating point values,
text which are, of course, strings.
It has numeric, which is a bit of a catch all for other data types,
like dates or times.
And then, it has blob for binary large objects,
which is when you want to store just raw zeros and ones in the database.
But I think for the sake of discussion, let's actually
use some more conventional data types that are more useful in the real world.
One of which is integer, say in Postgres.
By convention, its lower case.
But it does not matter in this case.
Text is a little ill-defined.
So I'm actually going to say something called varchar.
A variable number of chars.
And this is where you need to make your first database decision,
like how many characters maximally might the person's name be.
Like, how long.
C-O-L-T-O-N. So we could go with six, maybe.
COLTON OGDEN: Right.
For our first name.
DAVID MALAN: First name.
So last name, Ogden.
You know, at some point, you have to decide.
And we could argue all day long what the possible longest name of a human
is going to be.
But I Googled in advance.
COLTON OGDEN: You know the actual [INAUDIBLE] in lecture, didn't you?
DAVID MALAN: Yeah.
According to Google, it's 225 for a crazy, long name
that this guy named Nick had.
But let's just round up to 255, which was just a historical convention.
Eight byte value starting zero indexed.
COLTON OGDEN: One Bad Panda says, Terraria connected my StreamElements
points system to reward in-game items.
That's really cool.
It's C#, right, if I'm not mistaken?
We'll be covering C# on Friday, actually, this week with Unity,
so if you're interested in that, and if it is indeed C#,
definitely come check that out.
DAVID MALAN: Nice.
An awesome panda.
I think that is an awesome username too.
Someone should scoop that one up too--
One Bad Panda.
How can we do varchar if we use phpLiteAdmin,
as in mashup pset requirements?
So Bob, it's funny you ask.
So we are actually in the process now of transitioning to these newer data
types, so students who are working on last year's CS50 materials
will now see in phpLiteAdmin these new data types.
Not just text, you will now see varchar as of today.
You might need to do an update 50, which is
the command in CS50's IDE for updating itself,
but you should now see these more precise types.
Yeah, really nicely done on the C# side.
That's a pretty cool combination there.
So for email too, I don't really know what the longest
email might be in the world.
Something tells me that no one really has email addresses longer than 255.
COLTON OGDEN: Except Nick.
DAVID MALAN: Because no one's going to email you.
So let's just assume 255, but this is a real decision you have to make.
And if you've ever been to a website where
you try typing in your address, your name, or whatever,
and you suddenly can't type anymore, that's
quite likely because the database designer has decided
that's enough characters for you.
You often see this on customer support forums.
If a company wants to be able to have customer service,
but they don't want you to be too wordy in your complaints or whatnot,
you can just tap the number of chars altogether.
COLTON OGDEN: Sounds like you might have some personal experience with this.
DAVID MALAN: Yeah, I'm a little annoyed because I've been trying
to do this recently, and someone--
I'd like to provide a lot of detail, but they didn't want to hear it.
Why does the SQL implementation differ between platforms and servers?
I think this kind of boils down to reasonable people disagree.
There have been different computer scientists, different developers,
different companies who've decided we can do that better,
or we would like to do that differently, or we have different problems
that we want to solve.
And while there is this common subset of SQL
that tends to persist across all of these various dialects,
as they're called, different vendors and different humans
have just different implementations.
COLTON OGDEN: Kind of why Ruby and Python exist.
And why C# and Java exist.
DAVID MALAN: Yeah, exactly.
Why are there so many languages?
Not everyone agrees on what a language should do and how it should do it.
So same thing here.
It is a little annoying with SQL, though, in that it's all called SQL,
but it is platform specific.
And this can create problems if you actually
want to transition from one type of database to another.
Now, frankly, that's probably not that commonly done,
but it's certainly one implication of that reality.
So let me cross my fingers, and hit Enter, and see
if we have actually created a table.
Here we go.
Nothing seems to have happened, which, generally,
in a Linux environment or Mac environment is a good thing.
Let me go ahead now and type schema with a dot in front of it, which means this
is a SQL-like command and not a SQL command,
and you'll see just a regurgitation of the command
with which to create that table.
Now, how do I go about adding data to the database?
Let me go ahead and do that.
Insert into instructors the following fields--
an ID, a name, and an email, and the values I want to insert will be one,
say, David and Malan at Harvard.edu.
You'll notice I got a little lazy here, and I did not
put quotes around absolutely everything, and sometimes that's OK.
If the words you have chosen for your columns or whatever
don't coincide with reserved SQL words, you don't need to bother quoting them,
so you can save a few keystrokes.
Enter-- nothing seems to have happened, but let's do this once more.
Insert into instructors-- ID, name, email, values two--
Colton and then Cogden@cs50.harvard.edu--
semicolon, which is important--
Enter.
And now-- and someone proposed this year, select from where?
Indeed, spoiler, so if we want to actually select
the users in the database, select star, which
means select everything from instructors should select all of the instructors
from this table.
And indeed, you see it's kind of like a CSV, Comma Separated Value,
but it's using a vertical bar or pipe, and you can kind of sort of see
a graphical version of that same table.
COLTON OGDEN: OK, so values is like a function that has multiple parameters,
and that unpacks those to put them into the table?
DAVID MALAN: Yeah, I don't know if it's technically a function,
but it's definitely a keyword that needs to be present in order
to specify here come the values to distinguish them from the key names
there.
A bit of chatter here, so let's rewind here.
Why is JavaScript still a thing?
OK, so a little off topic.
That's kind of a can of worms.
Certainly useful on the client side, and lots of people-- and increasingly,
people are finding it useful on the server side via Node.js.
Let's see, what else here.
COLTON OGDEN: I think Web Streak and Swarm
Logic were making a joke about a SQL injection attack, possibly.
DAVID MALAN: Yeah, you could certainly do that.
COLTON OGDEN: So you can send a message in Twitch chat.
DAVID MALAN: Let's see.
You completed mashup, [? Bave, ?] but I had to make tables locally in my PC.
Ah, so yes, that was by design. phpLiteAdmin
was configured to support only SQLite, and therefore,
only its five or so data types.
And we have just recently, as of a week ago,
enhanced it to actually support Postgres data types as well,
but a subset of them only.
How can you disable HTML?
Now I think you're hopefully trolling us,
because that's not really going to happen if you're using the web.
What's an integer in the context of SQLite--
a 32-bit, or 64-bit, or is it dynamic signed or unsigned?
Good question, Andre.
I actually don't know offhand.
I know that in Postgres, this is well-defined behavior.
A small int is two byte, an integer is four bytes,
and a big int is eight bytes, but when in doubt, let's
consult the documentation.
So SQLite, integer-- you'll find that the official documentation
is at SQLite.org.
Version three is the latest.
Let's go ahead in here.
Yeah, look at this.
So Andre, in answer to your question, SQLite's a little dynamic.
It'll use one, two, three, four, six, or eight bytes,
depending on how big the number actually is, thereby, dynamically
figuring itself out for efficiency.
COLTON OGDEN: That's cool.
DAVID MALAN: I believe it is only signed by default,
but let me search for signed.
Signed, signed, oops-- let's go back up.
I saw one hit there.
Yeah, it looks like unsigned big int is mapped to integer two,
so something tells me that it's only signed integers from negative something
to positive something if it's using a byte value, which
I don't know how to pronounce offhand.
What else do we have here?
Can we do a table name like insert into table name?
Table name-- so, [? Bavik, ?] do you mean can you name your table,
quote, unquote, insert into table name?
In brackets-- oh, I see what you mean.
Yes, SQLite does support bracket notation
where you can put the table names in brackets instead of using quotes.
I think that's what you mean.
Yes, that is indeed the case too.
Quotes tend to be a little more conventional,
but for SQLite, you'll see that as well.
All right, so why is this all actually useful?
And honestly, this is getting a little tedious.
I don't really want to type out all these commands in black and white.
So [? Bavik ?] mentions phpLiteAdmin, which is this free web-based tool.
It's written in PHP, but that's an implementation detail
that just makes it more pleasant to interact
with the database on Macs and PCs.
Let me show everyone a really helpful free tool.
If you search for DB Browser for SQLite, the first hit
is hopefully SQLitebrowser.org.
If you go and visit that, you'll see a website that looks like this,
and you will see a Windows version, a Mac version, and even the tar ball
if you want to install it on Linux for yourself.
I've already done this in advance and installed it on my Mac,
and once I've done that, you'll see that I have this program--
DB Browser for SQLite.
And it's actually pretty nice, pretty simple GUI, Graphical User Interface,
for manipulating databases.
So let me do this.
Let me exit out of SQLite 3, which is the black and white command line
version.
And let's go ahead and open that same database, which we called Harvard.db.
Enter-- and in here, now you'll see exactly the same information,
but formatted into GUI, and so notice, if we actually
look at the instructors table and browse the data, we'll see a couple of columns
here.
I can zoom in here.
COLTON OGDEN: Looks awfully familiar.
DAVID MALAN: Yeah, we've used it-- what's familiar about it?
COLTON OGDEN: It looks like Excel all over again.
DAVID MALAN: It is.
So it's a GUI that actually looks like Excel, but we're going to have,
in just a little bit with Python, the ability programmatically
to talk to this data.
So let's go ahead and actually start over with this tool,
so folks have seen how to do it here.
So let's see.
COLTON OGDEN: It changed the colors of the windows, says One Bad Panda.
Is that a reference to-- oh, that's--
DAVID MALAN: Yes, I know it doesn't have to be black and white.
It's just meant to convey the simplicity of the environment.
Green and black is very popular as well.
So let me go ahead and just blow this away and delete this table as unneeded.
And let's start over, so we can actually see how you can create the same thing.
And notice-- zero, zero, zero, zero-- there's nothing in this database now.
So let's create a table.
Let's call it instructors, and what's really cool about DB Browser is that
it's sort of a teaching tool. %%%
It will show you what code you could type out manually
to execute the same functionality.
COLTON OGDEN: Does phpLiteAdmin also do that?
DAVID MALAN: It does after the fact.
It doesn't do it in real time.
When you click Submit.
So let's go ahead and click Add Field, and let's go ahead and call this ID.
We're going to keep it an integer, and you'll
see that these are the finite number of types we talked about before,
but you can override it.
If you want to proactively prepare to migrate to Postgres,
you could type in something like big int here,
and it would accept it, but let's go ahead and go with the defaults.
Notice that there's these columns here--
PK for Primary Key, AI for Auto Increment,
U for Unique, and Not for not null.
So primary key is, by definition, the value
that uniquely identifies each row, which hopefully is indeed
these unique numbers.
Auto increment is a fancy feature, because now I
don't have to think about whether you're one and I'm two or you're two
and I'm one.
We just let the database give us IDs.
COLTON OGDEN: If you don't do auto increment,
will it just or store whatever data you give it?
It won't perform any automatic--
DAVID MALAN: Correct, and you can still manually provide a value.
And what SQLite will do is take the next highest value the next time
you do an insert without specifying.
COLTON OGDEN: So if there's two people and we give them the ID one,
and if we try to grab those, it'll just give us both those rows in that case?
DAVID MALAN: Exactly, yeah.
That's what we want to avoid.
So we see a lot of sarcasm going on here in the chat window.
Thank you so much.
Hello, Sergio from Spain.
What code editor?
I tend to use Atom these days.
Colton uses--
COLTON OGDEN: VS Code, typically.
DAVID MALAN: Those are two of the more popular ones these days.
COLTON OGDEN: They're pretty much identical.
DAVID MALAN: Every phrase has a meaning--
OK, so there's a whole conversation going on.
We're going to talk about SQL here on our end.
So we've got ID.
Let's go ahead and create an instructor's name now.
COLTON OGDEN: [INAUDIBLE] did ask one more question at the very bottom.
DAVID MALAN: Let's see, what have we got here.
What's the difference between unique and primary key?
Good question.
Primary key is a superset of unique.
And by that, I mean primary key means the value has to be unique,
but it's also a commitment by you to use that field as the unique identifier
for that specific table, and it is a value that can therefore
appear in other tables as a foreign key, which is just what we were doing
earlier with our table that we called--
let's go over to that one--
course instructors-- where a number from another table
was appearing in that table.
Good question.
Keep them coming.
So let's just bang out the rest of this table.
Name is going to be text, although we could type in var
chart to be more precise with a number, like 255,
but we'll go with SQLite's types now.
Do I want to make this a primary key?
No, because we already have one in our number.
COLTON OGDEN: You should only usually have one primary key
throughout your entire database, right?
DAVID MALAN: Yeah, and it can span multiple columns.
You can say that together these two columns define my primary key,
but you shouldn't have one primary key, another primary key, and another.
That violates the definition.
What about unique?
Should we make name unique in this table?
COLTON OGDEN: I don't think so, because like John Smith,
for example, very common name.
Those might exist, right?
DAVID MALAN: Exactly.
And god forbid we--
we have two Coltons.
We should at least allow for it.
So name might be in there, and then let's do one more, email.
We'll keep it simple as text as well.
That could maybe be unique if you assume people can't share email addresses.
So sure, we'll make that unique, but not the primary key,
but that invites a good question.
You could use email as your primary key if it's already unique,
but if your email address is like cogden@cs--
it's going to be 10 or more characters, whereas an int is
going to be two bytes or four bytes, so it's a lot more efficient, typically,
to use integers.
So that's another reason to make the primary key an integer.
So primary key is how we tell SQL what we intend to use the ID for?
Yes.
That's a good way of thinking about it.
Are primary keys auto indexed on SQLite?
Yes.
Primary keys and unique keys are, by definition,
indexed on SQLite, and more generally on SQL databases.
What's the difference-- OK, I'm going to pluck those off as well.
Keep them coming.
Let me go ahead and before I click OK just draw our attention
to the SQL that was generated for me dynamically.
Notice it's using back ticks, which is technically
how you should escape table names and field
names to distinguish them from keywords, but sometimes single quotes
are fine as well.
So a bit more syntax than before.
Partly because I don't remember it when I type it out manually,
which is why tools like this are nice.
So let's go ahead and click OK there.
So we could continue this logic and actually
create more and more tables here just by walking through those steps.
If you want to play around with DB Browser,
I would encourage you to do so, but why don't we actually transition now
to maybe some actual code.
See how you can talk to this?
COLTON OGDEN: Lets take a look at it.
DAVID MALAN: Hey, love the haircut there.
COLTON OGDEN: David says, love your haircut,
Col. Thanks, David, appreciate it.
DAVID MALAN: Keep the hair compliments coming for Colton.
All right, so those of you who are familiar with a bit of Python
might know about Flask, which is a popular framework for web programming.
It's similar in spirit to Express on [? NOJS, ?]
and it's similar to Laravel and PHP.
It's similar to Rails for Ruby, although there are certainly
differences among those.
But we can go about implementing a program in Python,
and with it, Flask, that actually uses a database.
But let's start by making, not a web app first, but just a little command line
application.
COLTON OGDEN: Let's do it.
DAVID MALAN: All right, so I'm going to go ahead and open up VIM here
in my black and white terminal window.
COLTON OGDEN: The other editor that you primarily use, right?
DAVID MALAN: Ah uh, yes, no indeed.
When I'm in a Linux command line environment I just pull up VIM.
Let me go ahead and open up harvard.py as a script.
And now let's go ahead and write a little script that maybe just iterates
over the instructors and print them out on the command line,
just because so that we can show how to do it.
So we need a way of talking to the SQL database.
And there's a few, there's any number of ways to do this.
In CS50, the undergraduate class, we actually
have our own fancy library that creates a nice little abstraction that
does this for us.
In the Python world, SQLAlchemy is, frankly, quite popular, perhaps
too popular for its own good.
I find it to be a pain in the neck to actually use.
It tends to provide not just an abstraction layer over SQL, also
a whole ORM, or Object Relational Mapper, which
is convenient in some cases.
But it's kind of a whole technology stack unto itself to learn.
So in CS50 we use a simpler API that just has one function, called execute,
which I'll use today just so we can keep the focus on SQL and not
on a rabbit hole of Python frameworks.
COLTON OGDEN: Does the CS50 library wrap SQLAlchemy at all?
DAVID MALAN: It does.
So we figured out how to use SQLAlchemy.
And we just abstract that away for students.
But we're not abstracting any of the SQL stuff.
This is just a Python thing.
So let me go ahead and import the CS50 library as follows.
So from CS50, import SQL.
And after that, let me go ahead and connect to the database.
So I'm going to declare a variable called db.
But I could call it anything I want.
I'm going to use this SQL class that I've just
imported from the CS50 library.
And I'm going to go ahead and import the SQL file that we called Harvard.db.
Not a typo, I'm indeed using three slashes there
specifying a protocol of SQL [? flights. ?]
Now let's go ahead and execute a query.
To execute a query we can literally type db.execute.
And then let me go ahead and select all of the instructors.
So SELECT star FROM instructors, and let's just leave it
at that, so just the instructors.
And actually I don't really care about all of our information.
Let's go ahead and select the name of the instructors from instructors.
So again I'm capitalizing SQL keywords and lower-casing field names
that we defined ourself.
COLTON OGDEN: So if you select a keyword, or I guess a column,
it will default to all, the basically the same thing as star?
DAVID MALAN: Say that again?
COLTON OGDEN: So this select name from instructors
will give you all of the rows and all of their names, just like a star,
basically but more specific?
DAVID MALAN: Star selects all of the columns.
Name selects one of the columns.
But you will always get back all of the matching rows.
COLTON OGDEN: OK.
DAVID MALAN: This function will always return a Python list,
even if it's empty.
You will get back zero or more rows by definition.
COLTON OGDEN: OK.
DAVID MALAN: I'd really like to see some good Vim RC file and plugins for VIM
someday.
That is a good, actually, topic for Jordan [? Hiyoshi, ?]
who teaches our mobile app development class using React native.
If you look at that course online you'll actually
find that he uses VIM quite a bit and has his own plugins.
If you send me or Colton an email we can point you at that after too.
We just have to dig up his URL.
Is it better to start with Flask or Django, regarding the learning curve?
So for my part, Flask, hands down.
Django is a much bigger beast than Flask.
Flask solves a few problems very well.
Django solves a lot of problems all at once.
And so frankly I would hands down start with Flask
and then work your way up to Django.
COLTON OGDEN: And then Brian's course on CS video [INAUDIBLE] [? .org/web ?]
goes over both, right?
DAVID MALAN: Indeed, yeah, Brian's class,
we start with Flask and then transition to Django.
So you can go to that URL that Colton just rattled off
if you'd like to see that as well.
Semicolon missing, this is Python.
So we-- oh, good question.
Within the SQL commands, the way the execute function
works, and this tends to be true of a lot of SQL APIs in Python
and any language-- you don't need the semicolon there.
The library will take care of that for us.
What do you think about Postgres SQL versus regular SQL MySQL?
So there isn't really--
you can't really compare Postgres against regular SQL
because SQL is the language, whereas Postgres and MySQL are
implementations of the language, or specifically of a server.
Frankly, I find MySQL a little easier to use.
It's a little simpler in parts.
But frankly Postgres has been gaining steam, I would say.
MySQL has probably been losing some steam in recent years.
We tend to encourage our own students to use [? Heroku ?] just
because it's so simple.
They've got a free tier of service.
They've got great documentation.
And so on those bases alone, the fact that they support Postgres
is why we encourage students to pick up a bit of Postgres.
But if they had used something else, we would probably
encourage something else.
Oh sure, we can repeat the URL.
You're about to type in the chat room, perhaps?
And we'll type in the URL of Brian's web class here.
All right so if we turn our attention back to the code here,
this line of SQL, SELECT name from instructor
should give us back a list of all of the instructors.
So let's actually store that list in a variable that I'll call rows.
And then the purpose of this program is just to print out these names.
So let's go ahead and do this.
So for row in rows, let me just go ahead and print out row itself.
And that's it.
And, whoops, no semicolon, wrong language.
But what do I want to print about the row?
Well it turns out that each of these rows in CS50's library,
and commonly in these SQL libraries is to return a dictionary, or Python dict,
as each of the rows.
So I'm specifically going to print out someone's name in parentheses there.
All right, so I think I've made no mistakes here.
Let me go ahead and save this here.
And now let me do a few things.
This is using, of course, the CS50 library.
If you've never done this before on your Mac or PC, your Linux box,
you're going to want to do PIP 3 install CS50, which will install CS50's
library, if you have PIP installed.
If not, Google how to install PIP.
It varies based on platform.
PIP is just a package manager that will install that on your machine.
I happened to install it already so it's already there.
So I'm just going to wave my hands at the text there.
And now I'm going to go ahead and run Python 3
to make clear we're using the latest version of Python of harvard.py, Enter.
And voila, you see on that put all of our names.
Now you also see a debug line that's built
into the library for pedagogical purposes,
just to show you what command in green was sent to the database.
And all indeed seems well.
COLTON OGDEN: That's handy.
I like that.
DAVID MALAN: So what we can do now is make this a little more programmatic.
If I also, for instance, import the sys library, thereby
giving myself access to like [INAUDIBLE] [? arg ?] v,
I could even do something like this.
I could add a WHERE clause, which someone alluded to earlier,
WHERE name equals, and then I can put a placeholder.
Now normally in Python you'd put a placeholder
with like curly braces or whatnot.
But in SQL a convention tends to be to use named place holders like this.
And then the library is going to have me do this, pass
in a value for that named place holder.
You don't repeat the colon here.
And let me go ahead and say sys.rgv 1 where
I'm going to presume that the user has typed in a word.
And then hopefully that's going to filter down
the number of results I get.
So if I save that and do Python 3 of harvard.py.
And now let's go ahead and search for just David instructors.
Hopefully I'll get back just David.
And if I do this again I can just get back Colton.
But of course I don't support prefix matching,
like Dav, I'm going to get no one because there's no Dav in there.
So we can actually show another command.
Instead of using equal sign, we can actually say, like, and then go ahead
and plug-in a value here.
So instead of just doing this here we can go ahead
and concatenate onto this, for instance, using a plus, a percent sign, thereby
saying the name should start with [? whatever ?] the human typed in,
but it can end with any number of characters as well.
COLTON OGDEN: I think somebody even mentioned that in the chat.
DAVID MALAN: Yeah, I think we saw a percent sign a moment ago.
COLTON OGDEN: JP guy, fourth one down, third one down.
DAVID MALAN: Indeed, yeah, no, very good addition there.
So let's try this.
So now let me run, let me go ahead and run this again, searching for David.
OK, that's still works.
Searching for Dav, and voila.
That now works as well.
So we the ability to search on a field too.
COLTON OGDEN: It's kind of like the primitive version of the Google,
what's it called?
What's their technology called where it knows what you're typing?
DAVID MALAN: Autocomplete.
COLTON OGDEN: Autocomplete.
Didn't it have a different name to it, I thought?
DAVID MALAN: Autocomplete, I don't know.
COLTON OGDEN: I thought Google had like a specific--
DAVID MALAN: Oh, like intelli-sense or something?
COLTON OGDEN: Something like that.
DAVID MALAN: I don't know.
All right, let's catch up here.
So SQL [INAUDIBLE],, you can use the percent sign.
Yep, OK thank you JP guy.
Semicolon, yep, not necessary.
OK, can I have a job in Silicon Valley although I'm in Tunisia?
I think by definition no.
You would need to be in Silicon Valley.
But if you mean can you apply, absolutely.
I certainly think you should be able to find
contact information on any number of tech companies websites in California.
See if there's a link to internships or jobs, and by all means,
reach out to it with an email to someone you see online.
Or if you have access to LinkedIn.com and have a profile,
look for, search for Facebook recruiter, or other such search phrases
and see if you can connect with someone, by all means.
The world's a increasingly small place.
And talent is everywhere.
So I wouldn't hesitate.
What is SQL best used for?
So it's a good question.
I think it tends to be best used when you have lots of data to store,
or data that you want to be able to search conveniently or efficiently.
And that, [? may be ?] a relatively small data set.
Frankly, even we, for purposes on campus,
find it much more useful to be able to type
SQL queries to answer questions for ourselves
about the students, about grades, and any number of administrative things,
than it is to write a program and a script to analyze data,
or to poke around Excel, or the like.
So I think it really depends.
But the fact that you get the ability to create data sets, select from them,
delete from them, update, and delete, is a pretty powerful tool kit.
Can you use SQL with other languages?
Absolutely, pretty much every language comes with the--
certainly any popular language these days comes with support for SQL
in the form of a library, including C. So you can use it from old languages
to new.
COLTON OGDEN: CS50 [INAUDIBLE] the docs at iO, got a plug there.
DAVID MALAN: Well, thank you.
Thank you.
So what happens behind the scenes when you search--
so is this what happens behind the scenes when you search a website?
Possibly, yes, if that website is using SQL--
which is very possible because it's very popular technique these days--
then yes, there is one or more queries going on probably using the verb SELECT
to find that data.
You could, as someone noted earlier, by using a NoSQL database,
but the spirit is the same.
The data is stored differently.
But you can actually search it in the same way.
So both of these paradigms are actually quite popular.
COLTON OGDEN: We'll be covering actually a little bit of that, aren't we?
You said possibly a mini example, like a [INAUDIBLE] example?
DAVID MALAN: Well possibly.
We actually ended up talking about names and addresses, which is fine too.
We started the conversation there.
But I don't think we need to import it just to [? sports ?] next.
COLTON OGDEN: [INAUDIBLE] oh, co-completion, right.
That's right, yeah.
DAVID MALAN: Is that what you were thinking about?
COLTON OGDEN: No, it's got a different word.
I forget.
There was a specific phrase I thought Google
used for their autocomplete feature.
Autocomplete would be like the general term for it.
But Google had a specific--
I'm gonna look it up.
We'll look it up.
DAVID MALAN: In the meantime, does regular expressions in SQL
helped me to find data?
Yes, but they're not exactly regular expressions.
The expressiveness of SQL tends to be a little less.
You can use a percent sign, as I think the JP guy mentioned earlier,
which represents 0 or more characters.
You can use a question mark, which is 1, or 1 or 0 characters, I believe,
or possibly just 1.
I forget offhand.
And that's mostly it.
You can do other little trickery but it's not
nearly as expressive as using something like Python or JavaScript
or Ruby to actually use regular expressions on the language.
Good question.
Keep them coming.
COLTON OGDEN: It might be Autocomplete.
But I swore that it had a different name to it.
DAVID MALAN: OK, I believe you.
So in any case, where does that leave us?
So we've only just scratched the surface of this.
Let's put together a simple web application just
to tie it together to some generation of html.
But it's really not a hard leap.
Because notice that after all, what we've just done with harvard.py
is generate text dynamically by using a for loop in print.
But you could certainly imagine using the same kind of code
to generate like an unordered list, or an ordered list, or a table tag, again
and again and again.
So let's actually do that with a bit of Flask
code, which is Python code that just happens to use a micro framework, so
to speak, called Flask.
And what have we got here?
Aren't regular expressions mostly--
I'm not sure I'd say that.
They're used in bunches of languages.
I learned them back in the day in Perl, one of the earliest incarnations.
They're in PHP.
They're in Python.
They're in Ruby.
They're in, did I say Python?
They're in all modern languages nowadays, certainly
higher level languages as well.
0 or 1, thank you, [? Bavek, ?] for looking that up as well.
Yeah, regular expressions are super popular and definitely something
worth picking up.
I can say it's really easy at first to like not quite
understand how to use them.
The syntax can get a little scary.
And honestly just as we preach in CS50, the best way
to start building up regular expressions is baby steps,
like just write the simplest one you can, then enhance it,
enhance it, enhance it until you have something crazy complicated
that you might not even recognize anymore.
But if you document it and you remember those steps,
odds are it'll solve your problem.
Google suggest?
Is that super effects?
COLTON OGDEN: Yeah, maybe.
I honestly don't remember.
I might be trippin.
But I'm pretty sure that it was--
it had a specific name.
DAVID MALAN: All right, let's keep belaboring that point.
OK so let's go ahead and make a relatively simple web app just
to demonstrate how all this works.
I'm going to go ahead now and do this, PIP 3 install Flask, which will install
this framework on your own Mac or PC.
Nothing really happens because I did this earlier as well.
Then let me go ahead and open up a file called application.py,
which initially is blank.
And I'm going to go ahead and do a few imports.
So I'm going to import from the Flask module,
something called FLASK in all uppercase, a function called render template,
in lowercase, and request variable as well.
And from the CS50 library, I'm going to import our SQL library again.
But you can use SQLAlchemy or the like.
We just want to use SQL today.
We don't really care what the library is.
And then we go ahead and do the following.
Let me create a web app using Flask by saying,
OK, Python, this file with this name should
be initialized with the Flask app, the Flask micro framework, capital F.
And I'm going to store that in a variable called app.
And let's do the same thing as before with our database.
Let's go ahead and use the SQL constructor to say go ahead and connect
to [? sqlite///harvard.db. ?]
All right so we got two global variables.
Now in Flask, let's do the simplest thing possible, define a route.
So for those unfamiliar, a web-based framework typically models incoming
requests by way of routes, like slash, or /login, or /register,
some path with slashes in it that represents what URL the user might
visit.
And the way to express this in flask is to say, hey application, go ahead
and define a route like slash, which is typically
the default. And then associate a function with any name--
I'll choose index because that tends to mean the index of a site below it.
And that function now should do something.
I'm going to do something super simple.
I'm just going to return hello world.
So this is not html yet.
It's literally just a string.
But I think it's enough to get the job done.
Because what I'm going to do next is this.
I'm going to go ahead and type python3-m and run the Flask module--
the software for which I installed a moment ago.
And I'm going to specify a global environment variable called FLASK_APP,
that the name of the file I want to use for my Flask application
is called application.py.
I'm going to go ahead and hit Enter.
And you'll see that I screwed up because I forgot the key word run.
So I need to actually type in Flask run.
So let me bring this up to a line of its own and go ahead and hit Enter.
So you'll see a bunch of diagnostic output, the most useful of which
is this URL at the bottom.
I'm going to highlight and copy that, open up a new tab, paste it, and voila,
there's hello world.
COLTON OGDEN: Beautiful.
DAVID MALAN: If I view the source of this it's not technically a web page.
It's just a string.
But we're one step closer.
COLTON OGDEN: Cool.
DAVID MALAN: Now for those a little less familiar with command lines,
this is a little tedious, honestly.
To run your web server it's a little annoying
to have to type this whole thing again and again.
So there's a few changes we can make here.
One, you can export an environment variable semi-permanently.
And I can say go ahead and export a variable called Flask app,
setting it equal to application.py, Enter.
And now until I close this window I never need to type that again.
And I'm also going to set an alias in Mac OS or Linux
called Flask, and set that equal to python 3-m flask,
so that I never again need to remember that crazy incantation.
So now if I want to do the exact same thing as before,
I can just very simply say Flask run and voila, I'm good and going.
COLTON OGDEN: CLI magic.
DAVID MALAN: Indeed, now depending on the platform,
like Linux sets all this up for you.
Mac OS, I think have to do a little bit more manually.
With Windows, open through sub [? shell, ?] you might--
subsystem.
You might need to do it manually too.
I'm not sure.
But these are just little command line tricks.
COLTON OGDEN: XKCD 208 for on point [? rejects ?]
truth, are you gonna pull that up for [INAUDIBLE]??
DAVID MALAN: Oh yeah, sure, let's see.
COLTON OGDEN: XKCD 208
DAVID MALAN: XKCD 208, here we go.
COLTON OGDEN: Regular expressions, let's see.
Whenever I learn a new skill, I concoct elaborate fantasy scenarios
where it lets me save the day.
Oh no, the killer must've followed her on vacation.
But to find them, we'd have to search through 200 megabytes of emails
looking for something formatted like an address.
It's hopeless.
Everybody stand back, I know regular expressions.
Perl, boom boom, and he's done.
Nice.
DAVID MALAN: Thank you for that.
There's an XKCD for almost anything in CS these days.
COLTON OGDEN: There is some truth there, though.
DAVID MALAN: Another question, what is the -m for?
So -m means run a module in Python speak.
So somewhere in the file system now is installed a module called Python.
And that got installed when I ran PIP 3, the package manager.
So Python 3-m Flask means run the module called Flask.
And when I then created an alias, I was able to just remove that prefix
because an alias sort of secretly types the same command for you
magically behind the scenes.
Which language, asks [? amano, ?] would you
recommend for getting data out of machines, mostly PLCs and REST,
in addition to SQL?
I started to pick up Python, want to get the data into a database
and work with them in a statistical way.
So I'm a little less familiar with that particular use case.
But to be honest, if you've started to pick up Python, that is certainly fine.
You can write a command line script that does kind of the opposite of what
we're been doing.
Instead of writing SELECT statements, you
can instead run INSERT statements like I did earlier for Colton's and my data,
and do that script once, import all your data into a database,
whether it's SQLite, or Postgres, or MySQL and be on your way.
And then you can use R, or any number of other environments
to actually analyze that data the way you'd like.
Is there a visualizer to cut and paste, like Excel, data around in SQL?
Kind of sort of, db browser light might let you copy and paste some data.
Let's see, if I go ahead and let's go into browse data.
Let's go ahead and create a new record.
So David, and then here [? Malan@harvard.edu. ?] Whoops,
I didn't apply it before, one sec.
David, apply, let's see, can I copy this?
Copy-- so short answer, no.
You can't really copy paste easily it seems in this GUI tool.
I'm sure there is.
And frankly the only reason-- well, the primary reason
we use DB browser in the courses we teach here is one, it's free.
Two, it's very easy to use.
Three, it exists for Macs and PCs and Linux machines as well.
There's also far fancier versions, like SQLpro, SQL [? lies, ?]
I think, or something like that for Mac OS.
And I'm sure there are others for PCs.
So I'm sure there are.
I just don't know any, honestly.
Visualizer will let you run SQL.
So you can get all this by downloading Docker.
OK, so true, you can get all this by downloading Docker.
We're not using Docker here.
I'm just using a little old Mac OS.
And you can use the Ubuntu system on Windows these days, or Windows proper.
You can certainly use Linux or Unix.
But Docker, yes, would allow you to do this as well.
But there's no need, strictly speaking.
All right, so let's pick up where we left off.
I've got a server running.
It doesn't actually render any html.
But for that we need one other feature Flask.
So Flask supports templating.
It uses a language called Jinja, J-I-N-J-A,
which can be used not just with Flask but with other frameworks.
And it just assumes that you have a templates directory.
And in that directory it assumes you have one or more files.
Let's go ahead and create an index.html file that's just
a super simple web page, like HTML.
Let's close that tag.
Let's have a head of the web page, close the head of the page.
Let's have the title, like let's just call this my first app title.
Let's have a body of the page here, and close body.
And just, let's go ahead and say for now,
hello world, but surrounding it with html.
If now in my application.py I want to output
that file and not just a hardcoded string, I can use that function
we imported earlier, render template, and render index.html.
Let me go ahead and save that, go back to my browser,
hit reload, and of course, can't be reached, refused to connect.
But that's because I'm not running Flask.
So sometimes two windows are helpful.
So I'm going to go ahead and close this, do Flask run to kick off the web server
again.
Let me go ahead though and go open another tab.
So we have access to two windows here, application.py.
And now let me go ahead and reload.
And now it's working.
And now if I view the source you'll see that I have a full-fledged web page.
But the cool stuff you can do pretty easily with Flask is like this.
You might recall from the internet more generally,
you can often have URLs that have question marks in them to provide user
input, like name equals David, Enter.
Now that doesn't change the behavior of my app yet.
But it could if I do this.
If I go in here and I use my request variable that I imported earlier,
I can do something like this.
Name equals requests.orgs get, quote unquote name.
And if the user gave me his or her name, I can now do something like this,
pass in a name equal name, where name is a key and name is a value here
that came from the variable.
If I go into my template now I can do something like this, not hello world,
but I can do curly curly brace, or mustaches, and say name,
and have a placeholder holder in this template.
Now if I go and reload it's not going to quite work as I expect yet.
See nothing seems to have happened.
But that's because Flask, like a lot of frameworks,
is caching my output from before for performance.
So I can solve this in a couple of ways.
The simplest is just to stop the server with control C, rerun it
with Flask run.
Now go ahead and reload, and indeed you see David.
If we now change this to Colton up here, you can see Colton and any other name
too.
COLTON OGDEN: So I might see where you're going with this.
I think we have another iteration potentially.
DAVID MALAN: Oh, where are we going with this?
COLTON OGDEN: Maybe some SQL integration?
DAVID MALAN: Oh, a SQL integration, yeah let's do that.
So let's go ahead now and go into application.py,
which is the controller in my application,
and just add a bit of SQL logic.
But let's take a look here at a few questions first.
So scrolling back into town, we I think we left off with the lol
before, and then the Docker question.
Where can I download Docker for Windows?
So honestly, I would just Google Docker for Windows
and the first hit should give you there, should come up on Docker.com.
Where can you find contacts on the internet
for having a direct job interview for like Skype?
Honestly, check, as we mentioned earlier, LinkedIn, looking
for contacts, recruiters especially.
I'm sure we'd welcome reach-outs, as they're
trying to identify talent as well.
Thank you, [? swarm ?] logic, for the Docker reference there.
[? LinkedIn ?] stuff for everything, thank you.
COLTON OGDEN: Oh right, yeah.
DAVID MALAN: Why do we use Python and not PHP?
You want to take that one?
COLTON OGDEN: Well, it's a bit of an opinionated answer, I think.
I mean PHP is a bit of an older language now,
though it's not safe to say that it's not used.
Because I think Facebook does still use PHP.
DAVID MALAN: Yeah, they technically use Hack I think,
which is their own variation of PHP.
COLTON OGDEN: But I think a while back we just
altogether agreed that Python was just a bit more elegant, better pedagogically.
PHP kind of has verbose sort of function names.
And it's kind of, it feels a little bit more of a hackish language than Python.
Python's a bit more I think engineered, I would say.
DAVID MALAN: It's true.
PHP has some messiness.
But by the time they got up to version 5 point
whatever, a lot of that had gotten cleaned up I think.
There were remnants, but it was teaching better design principles,
or more modern ones.
COLTON OGDEN: I think you expressed that you found it more or less pleasurable
using frameworks like Laravel, or I think Symphony was another?
DAVID MALAN: No, I never liked Symphony.
But Laravel I liked, even though we never actually rolled it out.
Yeah, I mean, honestly the way I summed this up in a Quora answer was,
it was time.
Like the reality was there's just increasing amount of momentum
these days around Python and also Node.js.
But that I think is a little too complex for the pedagogical purposes we have,
especially given the asynchronicity of it all.
Python is useful for not just web applications but also command line
scripts.
And yes, you could use PHP for command line scripts.
And indeed, I did myself for years.
It was never really the right language for that, even though you could.
It was designed for the web.
And I think too that there's just a practicality of Python.
It's just kind of increasingly omnipresent.
And it's not a bad thing for students to have some programming chops in Python.
So I do think that it's documentation is inferior.
I think PHP's documentation is fantastic, especially for newbies.
And so we did give that up, which I think is an unfortunate price to pay.
Python's documentation is not nearly as accessible or thorough, I think.
But I don't regret the decision, I would say.
COLTON OGDEN: I think you made the right call.
DAVID MALAN: Thanks.
All right, let's see.
Let's see.
Hello to [? Sirez, ?] if I'm pronouncing that right.
Welcome to Working On Music.
Having some issues with the isRest function.
Probably a little tricky for us to answer that in real time here.
But by all means, reach out on--
This course's subreddit-- r/cs50.
Or Facebook, or Twitter--
If you go to cs50.edx.org/communities, if you don't mind maybe typing that
out.
On the chat, cs50.edx.org/commuities--
That should give you a link to any number of places
you can asynchronously ask questions.
Let's see.
Julius, Thank you, appreciate you providing valuable content
for everyone, national treasure.
Oh, thank you, Julius.
COLTON OGDEN: I just said, we saw it here.
DAVID MALAN: Just for the web and data analysis--
OK.
Come on to asynchronous by a--
OK.
Swarm logic.
I don't disagree, but I think for students
who have only two months prior learned what a for loop is, and a while loop,
and the like--
I do think it's a non-trivial step.
And so while we do teach it actually in a follow-on class on campus here
at least, called CS100, Software Engineering in the Arts
and Humanities--
I'm comfortable saying it's a little too much too
soon for most of CS50 students.
Who, again, have just learned a few weeks
prior, procedural programming itself.
Python is cool for non-CS majors also.
It's quite popular in STEM, I suppose.
Yep.
Absolutely, agree there Andre.
Sure, Ann, as well.
Suggested something-- How did you actually
get into computer science and programming, both of you?
COLTON OGDEN: You first.
DAVID MALAN: Slowly, I would say.
The story I always tell in CS50 that as a freshman in college,
I was a little too intimidated by CS50 because I went here when
the course was already offered here.
And I didn't take it freshman year because it seemed only
for kids who'd been programming since they were 6 years old.
And I was scared away.
Finally got up the nerve to shop the class or sit-in on it, sophomore year.
And honestly, I just fell in love with it.
And legitimately, it was the first time ever
in 18-plus years of going to school that homework was fun.
I used to look forward to going home on Friday nights
and writing computer programs for CS50's homework.
And that just felt like a sign for me.
COLTON OGDEN: For me, I was interested in game development.
I actually had no idea what programming was.
And I bought a bunch of books and saw this weird syntax
for all these home-brewed programming languages that various game
engines in the mid-2000s used.
And was turned off by it, but eventually gave it another chance.
And then I found that I actually really enjoyed C and C++,
and then other languages in years thereafter.
It's been an interesting ride.
DAVID MALAN: Feel free to share your stories too,
especially if you're just starting off in programming-- how you actually
came to find your way here.
Minter27, currently during the final project for CS50
and having a bit of a problem.
Can I iterate over two arrays that have the same length, I presume you mean.
If so, how?
So, short answer, yes.
Depends on the language, of course.
But if they are the same length, you can just find the length of one of them.
Then use a for loop, for instance, over that specific length and index
into both of them.
But if I'm inferring here that you have two arrays that by design have
the same length, and you're just trying to line up elements in each of them--
odds are, that's not a good design.
You should generally never have parallel arrays, so to speak, each of which
represents something else--
but between which there is a relationship.
You should probably, instead, have one array, each of whose members
is a struct in C, or an object in JavaScript,
or a dictionary or a tuple in Python so that you're actually
keeping similar data together in just one bigger array, if you will.
How do you get into Harvard, asks [? Gassen. ?] Love
to be there for just one day.
OK.
So getting into Harvard for one day is very easy.
You just come to Cambridge, Massachusetts, 02138.
In fact, folks online are welcome to come
to see CS50's lectures, which are on Friday mornings.
It might be a little too short notice because we only
have two left this semester.
And those are coming up the next couple of weeks.
But by all means, next fall-- next September--
if you'd like to join us on campus in an actual CS50 lecture,
you're welcome to join.
Keep an eye on our social media channels for the invitations
and the free tickets for that.
You can also take free tours of Harvard, walk around Harvard Square.
You're welcome to come through and just see the university itself.
And if you go to Harvard.edu, there should
be a link somewhere to visitors or guests
that should provide you with all information.
Everyone I met told you that I looked like a programmer.
Don't know what that means, so when life gives you lemons, eh?
OK, so that's fair.
COLTON OGDEN: Fitting the mold, so to speak.
Oh that's great.
I forgot about indexing.
Thanks so much.
DAVID MALAN: Swarmlogic-- OK I don't know if this is going to be sarcastic
or not but here we go.
I knew I was getting out of the Army soon-- a few years ago-- took
CS50x, kept going and hacking.
Now a software engineer.
Wow.
That's awesome on multiple fronts.
COLTON OGDEN: He was here the last couple streams I did.
And he was providing suggestions to the implementation.
DAVID MALAN: That's awesome.
Glad that went so well.
And thank you, of course, for everything you did there.
And of all the things to find your way to--
computer science.
That's fantastic.
COLTON OGDEN: And we know I needed all the help I could get.
So my story-- I'm a translator and had no idea
how programming worked whatsoever.
I wanted to learn a bit and get out of my comfort zone.
And a friend suggested Khan Academy.
It was so easy that I could actually get into it.
And much like you, I fell in love with it from the beginning.
For Free Code Camp at CS50 afterwards.
Thank you both so much for the wonderful work.
DAVID MALAN: Now I feel a little bad.
We're not actually fishing for CS50 stories here.
You're welcome to have your own independent discoveries.
But that's really sweet to hear.
COLTON OGDEN: And let's [INAUDIBLE] who provided the concentration game
suggestion from last week.
DAVID MALAN: Yeah, keep the suggestions coming.
Colton loves to prepare large projects for these streams.
So I think we should just possibly finish this thought
because we're running low on time.
And we can go ahead and get this little app working.
So I have access here to my DB variable after having
connected to the SQLite database.
And so really, I can select anything I want from this.
And so rather than get the argument just directly from the URL,
why don't we go ahead and generalize this-- maybe
Q for query, just like Google does.
And then let's use this variable Q in a SQL query.
So let's go ahead and say, for instance, rows gets db.execute.
Let's say, select name FROM instructors WHERE name LIKE colon q.
And then we can go ahead and plug in the value like this.
We can go ahead and say q should equal request.args.get.
Its wrapping because the response is a little big here.
q, and then I can go ahead and just append to that a percent sign.
And I could clean this up and actually use kind of a templating approach
to make this a little cleaner.
But for now, this gets the job done.
But what's most important and we go into more detail in this
in CS50's lectures--
Always, always, always use some kind of placeholder
that's built into the SQL library itself.
Do not just use Python's f strings or format strings.
Do not just concatenate a value onto that first argument.
You will make yourselves vulnerable to a SQL injection attack, which means--
as someone alluded to earlier--
you can accidentally let a bad guy slip some of their own code
into your database, potentially compromising,
deleting, updating your data in ways you do not attend.
So now that I've done this, let me go ahead and save this.
And now pass in, not the name, but let me go ahead
and pass in all of these rows.
Let me then go into my template, index.html.
And let's go ahead and get ready for a list of 0 or more results--
this time creating an unordered list in HTML.
And then in there, let's go ahead and do this-- for row in rows.
And now I'm using this templating language
I alluded to earlier called Jinja--
syntax of which is almost identical to Python, but a little different.
Like endfor is not a Python thing, it's a Jinja thing.
And now let me go ahead and say hello and then curly brace
row, name, plugging in the 0, the first, the second, and so name that's
coming back from the database.
So now you see the power.
Even though it's just scratching the surface of what
you can do with a templating language using
Python to get the data from a SQL database using Jinja-- a templating
language to render the data.
So we have all these technologies now talking together.
So if you've ever heard the term technology stack,
that's kind of what we're talking about.
All this stuff stacked together to produce one final result.
So let's go ahead and search for just Colton first, reload.
And that does look as we expect, but let's go ahead and restart the server.
But you can also do this.
With flask, if you pass in reload, it will reload it on every request.
But that won't make a difference on your templates.
You might have to still restart then or add some code to make that work.
Let's reload.
Uh-oh, dammit, I thought we almost made it through the stream perfectly.
But internal server error means I screwed up somewhere.
It's kind of overwhelming at first type.
But it looks like unsupported operands type for plus.
So I seem to be getting back none from the database.
And I'm trying to concatenate a stir onto that.
So let's see if we can diagnose this.
So that's definitely on my DB line.
So let's go into application.py.
So let me go ahead and do where name like q [? equals request.rxget. ?] Oh,
you know what I did?
COLTON OGDEN: I do know what you did.
DAVID MALAN: I know what I did too.
I never changed my variable.
So now in a better application where we're not just whipping it up
on the fly, we are going to have some more actual error checking.
But recall that I changed the parameter to q.
So let's change that in my browser to q.
Now, I get Colton.
It's formatted a little differently because even
though you don't see the bullet here--
I actually forgot the tag.
That's why you don't see the bullet.
So let me actually fix this.
Let me go back to my template.
And let me give you your very own list item tag.
Let me restart flask.
And now let me reload, and voila.
Now we see the li tag.
Now we see the bullet.
And if we go ahead and search for, say, nothing here, now we get everyone.
Because the empty string matches everyone.
And so if we reload here, we see that we indeed now
have a bulleted list of two people.
COLTON OGDEN: Did you end up using the like thing
in the lecture demo for the dictionary?
DAVID MALAN: Most recently, I think, very briefly.
Someone in the class asked about it.
And I changed my equal sign to a like.
So let's see.
I think we've got to scroll up a little bit.
We got a little distracted here.
COLTON OGDEN: I think this was the last where we left off on.
DAVID MALAN: So either of you participate in Hacktoberfest 2018?
No, I did not, I'm afraid.
Helped build a boot camp.
I spent some time in college, but I couldn't find what I liked.
And eventually realized that the answer was right in front of my face
on my computer.
So I decided I wanted to contribute to the world of programs and apps
that made my life easier.
That's awesome, very compelling.
And the lives of others too.
Nice to slip that in there too.
[INAUDIBLE],, OK, thank you for the qualification there.
I think that's on you.
COLTON OGDEN: This is called [INAUDIBLE]
DAVID MALAN: Swarm logic.
OK.
Nice little connection here.
It is live, right?
Yes.
[INAUDIBLE] It is live, because you're hearing this live right now,
probably a few seconds after you type that.
All right, Colton, things coming up on this Friday?
COLTON OGDEN: Yeah, on Friday.
And we'll be doing a 3D [INAUDIBLE] example in Unity and C#.
DAVID MALAN: [INAUDIBLE],, why not q equals q?
That's because I'm stupid and I forgot that.
So yes, that would in fact make this line a little shorter.
Thank you.
Very well said.
I missed that myself.
Let's see.
Live on Friday.
Yep.
Everything's live and we also post it on demand after.
And what else have we got here?
Uh-oh.
[INAUDIBLE] just wanted to say this.
CS50 is my most favorite course, especially because of David and Colton.
You put in so much work and can actually see you sweating in the lectures.
Yes, unfortunately there's not very good air conditioning in there.
I want to watch lectures when I don't have to and I love you
guys because of that.
Your not so secret admirer, Nate.
Thanks so much, Nate.
That's really quite sweet.
I guess keep the comments coming today.
We do appreciate it.
But you're all too kind.
COLTON OGDEN: Seems to be a good temperature in here, though.
You're not sweating too much.
DAVID MALAN: Yeah, no sweating here.
It's when we're moving around more and it's
under the hot lights of the theater that it gets a little warm.
Here we have all LED lights.
So I think we're winding down.
Feel free to chime in with some final questions here.
But just to recap what some of the goals were--
For those unfamiliar with SQL, hopefully you
have a little bit of a sense of what's possible with it.
Actually, do you mind going ahead and pasting
a link to this past week's lecture for folks who
would like an even more in-depth look?
Go to CS50.harvard.edu.
If you go to CS50.harvard.edu and click on--
do slash weeks.
Actually 2018/fall/weeks/8 enter.
That should lead you to this past week's lecture video,
which offers more in-depth discussion of SQL and what it's for.
It will recap a little bit of what we did, but using different examples.
By all means, tune in there.
Those of you who are following along with CS50 on edX, the final one or two
problem sets will introduce you to Flask, and Python, and SQL, as well.
So feel free to tune in there if you want some hands-on experience.
And then of course in the coming days, do
you want to recap the upcoming Livestreams that we've got going on?
COLTON OGDEN: Yes.
So tomorrow we'll be joined by Nick Wong, who'll
be giving us a tour of a basic binary classifier from Scratch
using Keras and TensorFlow--
a couple of open source machine learning libraries in Python.
On Wednesday, we'll be joined by Brian Yu, who
taught the web course with CS50.
And he's also a head teaching fellow.
And he will be talking about some React basics.
And then on Friday, it'll be just me and we'll be talking about Unity in C#
making 3D Pong.
DAVID MALAN: So all this is thanks to Colton.
He's been spearheading this whole initiative,
building the audience on Twitch, getting everyone lined up
to chat about various fun technologies.
So props to Colton for that.
COLTON OGDEN: And my guests, CS50's own David Malan.
DAVID MALAN: Thank you.
[? Bavik Knight-- ?] any examples of left inner join?
If you don't mind, let me defer to the lecture
that Colton just pasted the URL of where we go into a little
more detail on joining data.
We kind of set the stage today when talking about instructors and courses.
And in that lecture, we go into more detail
on how you can join those data sets back together in a way
that SQL really empowers you to.
And thank you, [? Gassen, ?] for tuning in today.
We're indeed here live from Cambridge, Massachusetts.
You're very welcome, [? WebStreak23. ?] Thank you
for the kind shout-outs, literally.
Any final questions?
COLTON OGDEN: Some people just lost their hearing on stream.
DAVID MALAN: Give folks a few more seconds here.
COLTON OGDEN: [INAUDIBLE] Colton, thank you.
Studying to understand Friday's session better.
Thanks [INAUDIBLE],, looking forward to having you there on Friday.
DAVID MALAN: Can we do a shout-out actually to this [INAUDIBLE]..
You want to paste the URL @CS50.edx.org/games?
So if you're interested in learning more about gaming from Colton specifically,
Instructor ID 2, you can go to that URL there-- @CS50.edx.org/games.
It's freely available.
Just click the audit option and you can follow along with all the projects
there.
Nice to see you, David, from Tel Aviv.
Hello from Cambridge here.
No children here.
Thanks for this very much.
Is it a major game-changer for web?
Is it a major web--
What is a major game changer?
I'm not sure I follow, [? JPGuy. ?]
COLTON OGDEN: Thanks, Bella, for tuning in today.
Appreciated.
DAVID MALAN: Very welcome [? Vahib. ?]
COLTON OGDEN: First course, wow.
DAVID MALAN: Nice.
Let's see what other final comments come in here.
Can't quite see the blue text there from far away here.
But you're very welcome, London.
Oh, web assembly.
I guess we missed that there.
Yes so web assembly-- maybe.
So we in CS50 are actually pretty excited about the potential
of web assembly.
One of CS50's former TFs-- or Teaching Fellows--
is actually at Google now working wonderfully on that team.
What we are hoping it enables ultimately is
to do much more browser-based development in languages
besides JavaScript and Python.
In fact, we're hoping before long, we'll be
able to have students coding C in the browser
without even needing a server-side IDE or cloud environment.
Hopefully, it'll be possible even offline.
It's probably a few years away, but we shall see.
So yes, [? JPGuy, ?] I think that's all very, very promising.
And also for performance, I think, of future applications
where as single-page applications get more popular,
they can be theoretically even more performant if they can be compiled
down to something more native, locally.
COLTON OGDEN: I can't read this name on here.
Oh, gymnasium.
DAVID MALAN: Gymnasium88.
Thank you, London, as well.
[? Bavik, ?] we'll tell Jordan that you've requested his presence.
He works nearby at a startup.
So we'll see if he can join us here some time.
Thank you Liberianwarlord.
Also a sentence I've not had to say very often Very welcome,
[? Munich. ?] Thank you, [? Gassen. ?]
When will David be back?
I don't know.
I don't think I've been booked yet.
So Colton has the schedule packed, but hopefully in a week or two.
COLTON OGDEN: We don't want to spoil what
we're going to do in a couple of weeks.
DAVID MALAN: I don't know what are we going to do in a couple weeks?
COLTON OGDEN: Thanksgiving week.
DAVID MALAN: Oh no, let's keep that to ourselves for now.
Stay tuned for an exciting day of relaxation
once it's Thanksgiving here in the United States, which is the holiday.
Anything else?
COLTON OGDEN: We'll see you soon.
DAVID MALAN: Nope, no spoilers.
COLTON OGDEN: We'll keep that one on the DL.
DAVID MALAN: All right, I think we're going to have
to log out here in a few seconds.
No spoilers, there you go.
Apparently Unity noticed something like 30% performance increase
by compiling to Web Assembly rather than the JS Web Assembly Emulator.
I'm not surprised.
I think that's the kind of potential that lies ahead.
COLTON OGDEN: Yeah, I've heard similar.
Spoiler, spoiler.
DAVID MALAN: How about this?
Do you know with what button can we stop the stream?
Don't hit it just yet.
COLTON OGDEN: I have to go over here and click this.
DAVID MALAN: OK.
So how about this?
In just a couple of weeks time, what Colton and I are going to be doing is--