[MUSIC PLAYING] BRIAN YU: Welcome back, everyone to CS50 Beyond. We'll just do a bit of a roadmap of where we are now and where we're going. This morning we took a look at SQL, some of which was hopefully review from CS50, and some of which was new. We introduced PostgreSQL, a way of using a different sort of database that doesn't exist just in a file the way SQLite did. Then we learned about how we can access that database programmatically using SQLAlchemy in order to add and select rows from the table. Then we gave you a morning project. I realize you might still be working on that, which is totally fine. The project consists of importing the data from a database of books into your database. You can continue working on that later this afternoon, as well. What we're going to do this afternoon is pick up from where we left off in the morning and continue diving more into the world of databases. In particular, we will be linking it to our discussion from a couple of days ago about object oriented programming in Python. We'll see how we can use this idea of objects and classes to allow ourselves to be very expressive with the way that we're working with databases in Python and with SQL. We'll do that with the rest of this afternoon. Tomorrow, we'll do a bit of a context switch and spend two days taking a look at React, a brand new framework for building user interfaces. It wasn't introduced in CS50, so it'll be new for almost all of you. It'll be an opportunity to show how we can use some more modern language features and some more modern libraries to make it easier to solve some of the problems that we've looked at so far in web applications over the course of this week. Let's go ahead and get started by diving back into the idea of classes. We introduced classes as a way of describing objects in Python. You might imagine that as we think about the databases that we've created-- for example, a table that we're going to store flights in-- that we could represent that using a Python class. Just to show an example of what that might look like, I'll go ahead and open up classes0.py. We'll make that bigger. Here's a Python class much like the one that we created a couple of days ago to represent a point in Python. This is a class that's just going to represent a flight. It's written in Python. There's no SQL just yet. We'll come back to SQL in just a moment. Here, the __init__ function, as you might recall from before, is the function or method that we use to create a new object, or in this case a new flight. When we create a new flight, we're going to give it three properties, an origin, a destination, and a duration. We're going to store all of those properties inside of the object. The object itself is just called "self," so we're going to store inside of self.origin whatever the origin of the flight is. We'll store the destination in self.destination and the duration self.duration. This is basically the same code that we saw with points. The only difference is that now we're calling it a "flight," and we're giving it the properties that a flight has. Once we have this class, we can begin to do interesting things with it. Take a look at classes1.py, for example. All the source code is available online, if you want to take a look at it. We define the flight at the top. Then in the main function, you might imagine that we could have code that looks a little something like this, whereby first, we create a brand new flight. And to create a flight, we use that __init__ method implicitly just by saying, "I would like to create a flight whose origin is New York, destination is Paris, and duration is some number of minutes." Line 12 is creating a new flight, where I'm providing arguments that define what the different properties of the flight are going to be. Once we have it, we can make changes to that object. On line 15, I was able to say, "OK, take the duration of the flight, then go ahead and add 10 to it," or, "I want to take the duration and increase it by some number." Later on, I can print out the origin of the flight, the destination, and the duration. This is just the basics of object oriented programming. When I create a flight object, I'm able to manipulate its properties, or the values stored inside the object. Then I can do something with those values like, for example, print them out. I type python classes1.pi, for example, and it prints out the origin, New York; the destination, Paris; and 550, which is the duration after you add 10 to it. These are all things that we saw a couple of days ago, but I'm showing it to you again just as a refresher. Are there questions about any of that? All right. Once we have this Flight class, we can also add methods to it, which is code that we can use to operate on flights. I'll show you classes2.py, for example. You might have a print_info function, for example, which prints out information out about that flight. When I run the print_info function, or method, it's going to print out whatever the flight's origin is. In order to access the flight's origin, which is stored inside of the object, or inside of self, we say self.origin. We print out the destination of the flight via self.destination, and we print out the duration of the flight via self.duration. In the main function, we create two flights, flight F1 and F2. F1 is a flight from New York to Paris with a duration of 540 minutes. Then I can just run the print_info function on that flight to print out information about F1. I can create a separate flight, F2, which has its own origin, its own destination, and its own duration. These variables are what we would call "instanced variables." Every time we create a new flight object, that instance of the object has its own set of the variables origin, destination, and duration. The two flights can have distinct variables that are stored there. If I type "python classes2.py," I get the first flight, New York to Paris, and then I get the second flight to print out, which is Tokyo to Shanghai, for example. Questions about any of that? Yeah? AUDIENCE: If you go into the code, is it necessary to say that origin = "New York" and destination = "Paris", or can you just leave it as [INAUDIBLE]? BRIAN YU: Good question. On line 16, do I need to say orgin = "New York", destination = "Paris", and duration = 540? No. In this particular case, I'm giving the arguments in their exact order. I can just use those names without needing to specify the "duration =" or "destination =". It will be useful to label them once we start diving back into the world of SQL, which we will in a couple of moments. For now, at least, it's not necessary. Yeah? AUDIENCE: For the left print_info function, [INAUDIBLE]. BRIAN YU: We only need "self" for print_info because in order to print information about the flight, the only information the flight needs is the object itself. We already provided it with an origin, destination, and duration when we created the object. When we create the object on line 16, for example, we have to provide all the information about the origin of the flight, the destination of the flight, and so forth. But line 17, in order to print out information about that flight, I don't need to additionally tell it, "Oh, this is a flight from New York," because F1, the object that represents the flight, is already storing information about its own origin and destination. That information is contained in the object. Yeah? AUDIENCE: [INAUDIBLE] dot notation for the purposes of print_info, because [INAUDIBLE]? BRIAN YU: How does this dot notation work? F1, in this case, is an object. When I say F1.print_info, I am calling the print_info method, or function, which is located here. Whatever object I'm calling the print_info function with gets passed in as "self" automatically by Python. If I say F1.print_info, then "self" is going to be F1 the object, so I'm going to get F1's origin, destination, and duration. Whereas down here on line 20, when I print out information about flight two, F2 is going to be "self." So self.origin will be flight two's origin, which in this case is Tokyo. Yeah? AUDIENCE: Is there a function [INAUDIBLE] BRIAN YU: Yes, you could define a function that takes a flight as its arguments. That's certainly something you could do. AUDIENCE: [INAUDIBLE] BRIAN YU: The actual print function? Yes. There is a special function in Python called __repr__. It stands for "representation." It will determine how an object will be represented when you try to print it out using the print function, for example. You could do that, too. Other things? All right. Now what we'd like to do is introduce a new concept, namely the concept of ORMs, or Object Relational Mappings. The idea of an ORM is to be a tool-- one of which is SQLAlchemy, which we looked at this morning, but there are others as well-- that basically lets you treat rows and tables inside of a database the same way you would treat objects and classes in an object oriented language like Python. We are able to take advantage of this object syntax and still manipulate a database. I'll show you what that would look like in an example like our flights and passengers that we were looking at earlier this morning. I'll go ahead and open up models.py, which is a file that's going to store all of our classes that are going to represent database tables. I'm importing SQLAlchemy, this time from a package called Flask-SQLAlchemy, which is a separate package that uses SQLAlchemy and adds some convenience features that make it easier to use with Flask. You may need to install this in order to get it to work. We create a variable called db that's going to represent our database. Now we have a very similar syntax to before, although it's a little more complex. We're going to define one class for every table that we have, so we define a class called Flight that's going to represent our Flights table. Here we're giving it a special property called __tablename__. This is basically telling the ORM, SQLAlchemy, the name of the table in the database that corresponds to this class. Because I'm going to use these classes to be able to interact with my data, SQLAlchemy needs to know what the name of the table is that corresponds to the class. I'm telling it, "All right, this is going to correspond to the table called Flights," for example. Then I'm going to give the Flight class a property for every column that I want to have inside of my database. It has an id column that is an integer, for example. Primary key is set to true because the id is the primary key. I'm going to say that the origin is also going to be a column. It's going to be a string. nullable=false just means that every flight needs to have an origin. There's no way to have a null origin. Likewise, every flight needs to have a destination. That destination is also going to be a string. It's also also not nullable, meaning it needs to have a destination. Finally, the duration is also a column. That column is an integer. It also needs to have a duration. We can't have an empty duration. There's a lot of new syntax here. Since there is a lot of new syntax, I'll show you some slides that will introduce a lot of the syntax to you. There's no need to feel like you need to memorize all this. Feel free to refer back to the slides with the examples if you're looking for the specifics of the syntax. The idea to get across here is that I'm now using a Python class to represent what is a SQL table. I can do the same thing with passengers. I'm defining a class called Passenger that's going to stand in for a passenger inside of my Passengers table. I'll give it this __tablename__ property that says, "OK, the name of this table is called Passengers. Every passenger has an id, every passenger has a name, and every passenger also has a flight_id. Here, the flight_id is the one that looks a little bit different. The flight_id is an integer, but I'm also going to specify that the flight_id is a foreign key. In particular, the flight_id is going to reference some key of another table. That's what it means for something to be a foreign key. The flight_id is going to reference a table called Flights, and namely a column called id. The id column of the Flights table is what this db.foreignkey is referencing. Thus far, we haven't quite seen why we would want to do something like this, but hopefully we can at least now see that these classes are modeled or shaped the shapes of the tables that we created earlier this morning. Question? AUDIENCE: What's [INAUDIBLE]? BRIAN YU: What is a Python class? You can think of a Python class as a blueprint for what it is that an object looks like. In other words, what is it that a flight looks like? Well, a flight has an id, an origin, a destination, and a duration. The class is going to be a blueprint that lets me create many of these things. I can create many flights, each of which has an id, an origin, a destination, and a duration. That would be one way to think of it. Other things so far? All right. The nice thing about defining classes this way is that we can begin to use these classes to interact with the database. I'll show you an example in create.py. In order to create tables when we were writing raw SQL, we either had to go into Adminer and say, "OK, let me create a new table. Give it these columns and choose things from a dropdown." Or you might have had to say something like, "All right, go to the raw SQL and type in a CREATE TABLE command." All you have to do now in Python is say, from models import *. In other words, from the models.py file that defines the Flight class and the Passenger class, go ahead and import *, or import everything into this file called create.py. Inside of main(), the only useful line here that we care about is db.create_all(). That is a function built into SQLAlchemy that basically says, take all of these models and create tables for them. Even if I didn't run any CREATE TABLE syntax at all, I could just run create.py and that would result in creating all of the tables inside of my database. A couple of you earlier this morning were asking, if I don't want to go into Adminer to create tables, is there a way in Python to programmatically create those tables? This would be how you would do it. You just define the classes that define the structure of what it is that your tables need, or what columns they should have. Then you call db.create_all(), and that will create all of your tables inside of your database. Again, we're pulling that database from an environment variable called DATABASE_URL. That's the same as what you were doing this morning. All you have to do is, on a Mac or on Linux, type "export DATABASE_URL =" followed by the URL of the database. On Windows, you would use "set" instead of "export" to set the database URL such that you can access it later. Questions so far? Yeah? AUDIENCE: If you go back to the actual class, to the logo, it says db.string. What kind of string is that in the [INAUDIBLE]? BRIAN YU: In this case, db.string is just going to represent some text-bassed input. We leave it up to SQLAlchemy to decide, based on the specific database, what is the exact SQL query that needs to run in order to make that work. All we need to care about is that it is a string, or some sort of text. This is what we might consider an "abstraction." You don't need to worry about the low level details of what exact SQL query is being run. We just have to worry about the fact that it is, in fact, a string. That's going to be one of the themes of ORMs, in general. We are no longer actually going to need to write any SQL queries at all. No more typing INSERT or UPDATE in all capital letters and using the specifics of SQL syntax. We can just interact only with Python objects and Python classes to be able to achieve entirely the same things. We're going to slowly move away from that syntax, in general. BRIAN YU: Yeah. AUDIENCE: In the passenger's class, the foreign key, is that [INAUDIBLE] or that column doesn't exist yet, because you're [INAUDIBLE] that table yet, will this still run? BRIAN YU: Will this still-- you'll need to-- AUDIENCE: You need to create like, those-- BRIAN YU: If you try to query the passenger table but there is no flights table, you'll probably run into issues, if you try and run any of the code that we're about to run, when this doesn't exist. Yeah. AUDIENCE: I don't know if you're [INAUDIBLE]. Does this take a lot more time like when it's running than [INAUDIBLE]? BRIAN YU: Does this take more time? There is probably some overhead for the ORM, but ultimately in the scheme of like larger queries, it's not going to be particularly noticeable. And you'll find that from a programmatic perspective, it's actually going to be a lot easier most likely to look at that syntax and reason about the syntax. And it'll be ultimately a net benefit usually, that most people don't want to be writing the raw SQL queries. And it makes much more sense to them to be able to interact with the objects, for instance. And I'll show you examples of that in a moment that'll help motivate why it is that we're doing this. So OK, what did importing data into a database look like before? Before we had something that looks like this. This is import 0.pi, which is the same as what we were doing this morning in order to import data, where we were opening up a file, reading it into a CSV file, and then looping over that CSV file and saying, OK, for every row, every row has an origin, destination, and duration. And we were running this query, db.execute, and then executing a SQL query, insert into the flights table these columns and these values using these placeholder symbols so that we could avoid SQL injection attacks, and passing it in this Python dictionary of what the origin is, what the destination is, what the duration is. Here's what that same thing is going to look like if we use an ORM instead. So I make the small change of, OK, from models import star. I'm going to import all of my models, those classes, into this application. And then for every row in this CSV file, these first couple lines are exactly the same. To create the flight, rather than type insert into something, I'm just going to create a new flight object the same way that I would in Python object oriented programming more generally, with a line that looks a little something like line 16 here, where I say, OK, let's create a new variable called flight. And that's going to be equal to capital F Flight, create a brand new flight. Origin equals whatever the origin was. The destination equals whatever the destination was. The duration equals whatever the duration was. And I've now created a new flight object. And in creating the new flight object, with SQLAlchemy is going to effectively treat that as is, I've created a new flight inside of my flights table. And to add it to the database it's as simple as saying db.session.add, followed by the name of this variable. Name of the variable happens to be flight. So I've created the object. And then I've said, add it to the database. And at the very end I do db.session.commit to say, OK, commit these changes. These are the changes that I actually want to make. And so slight difference. Now I'm inserting data into the database but I'm never using any SQL queries that are raw SQL queries. I'm just using these objects and saying create a new flight object and add it to the database. Questions about that? This is new. We're using Python object oriented syntax. And so it'll look a little bit different. But we're moving to higher levels of abstraction that are going to let us reason about larger problems as we work with SQL. Yeah. AUDIENCE: For line 16, you say origin, destination, duration. Do those have to show up in like a subsequent loop or do you just specify those as titles for each of your categories [INAUDIBLE]? BRIAN YU: So when I loop over the reader, I'm looping over one row at a time. And each row of the CSV file has three parts. And all I'm doing here is assigning a variable name to each of those three parts. I'm saying let Origin be the variable name for the first column. Let Destination be the variable name for the second column and Duration be the third. AUDIENCE: But do you have to [INAUDIBLE]? BRIAN YU: I'm just naming them so that I can reference them later when I actually add the flights. So I could say this flight has this origin and destination. If I wanted to, I don't have to separate it out. I could say for every row in the reader and just read one row at a time. And then I'll know, OK, the origin that's stored in row square brackets 0, it's the first thing in the row. The destination is row square bracket 1. And the duration is row square bracket 2. And so long as I make some changes to line 18 to avoid this variable name issue, this will work just as well. You can just take the row and access the different parts of the row. I'm just giving them names to make it a little bit clearer. Yeah. AUDIENCE: Why is line 22 added at the footer? BRIAN YU: Oh why is line 22 added at the footer? app.up context, this has to do with the Flask-SQLAlchemy library that we're using in order to run this. And the idea here is that what Flask-SQLAlchemy is going to help to do is help to make sure that we're able to run database queries in response to web requests when they're incoming web requests. So this is looking forward a little bit to when we're going to put this into a web application. And we want different users to be able to interact with the database simultaneously. So it's a nuance of Flask, basically, that Flask can only run certain types of code when it's in what's called an app context. But I wouldn't worry about that too much for the purposes of these simpler examples. Yeah. AUDIENCE: It looks like in this code there's a new session thing, or db.session, whereas before it was db.commit without session [INAUDIBLE]? BRIAN YU: Yes, so this is, again, a difference is coming about because we're using a slightly different package, Flask-SQLAlchemy, which just makes it easier to do this sort of ORM-type syntax. Although you can do it without Flask-SQLAlchemy. And the idea here is that our session is going to be our interaction with this database, that multiple different people can have multiple different sessions interacting with the database, adding their own objects to it, committing their own new objects to the database. And so this db.session represents our current interaction with the database, is what that's going to stand in for. OK, so what about selecting data from the database? What did that look like before? List 0.pi was what we were doing before. We would say db.execute. And we would say, OK, select the origin, destination, and duration from the flight and get all of those flights. And then we would say for every flight in our list of flights, go ahead and print out Origin to Destination lasting this number of minutes. If we want to do the same thing using ORMs now, we would first from Models import star, import all these classes. And then the line is as simple as flights=flight.query.all. Flight is the name of the class. And I want to take that class in order or run a query on it. And the query I want to run is all. In other words, get me everything from this particular class, all of the flight objects that exist inside of the database. And then for each one of those flights I'm going to loop through, going through every flight one at a time, printing out details about each one, the Origin, the Destination, and the Duration of that flight. So we've gotten rid of any select syntax. The word select doesn't show up inside the code. But by doing flight.query.all we've effectively replicated that idea of that syntax just by using objects. Questions about that? OK, so we'll take a look at some of the syntax that we can use with regards to ORMs. And I know a lot of this is new. I'm going to show you a lot of syntax in a very short amount of time. Please do not feel like you need to memorize this all. I don't have all this memorized. This is just showing you the types of things that you can do, so that if you have a question later on about oh, how do I do this type of query with an ORM, you can refer back to these slides and take a look at it. But the expectation is not that you're memorizing all of this by any means. So once we have a class that allows us to define all of the fields, we were able to say db.create_all in order to create everything, just as a review. Instead of something like insert into the flights, these origin, destination, duration, these particular values, what we would instead do is say OK, flight is equal to a new flight object. We would specify, OK, the origin of New York. The destination of Paris. Duration is 540. And then we would say db.session.add flight in order to say add this flight to the database. What did a select query look like? We saw a select star from flights was the way of getting everything from our list of flights. We would instead say flight.query.all to mean all right, take all my flights. Go ahead and run a query on them. And get me everything back. If instead I wanted to limit myself, run a filter to make sure that I'm only getting back certain results instead of all the results, whereas before I would have said something like select star from flights where origin equals Paris, the syntax in Python is going to look something like this. Flight.query, but not flight.query.all, I want to filter it first. I want to say flight.query.filter_by origin equals Paris. And this .filter_by means, OK, take this query and run a filter through it, namely filtering out only those rows where the origin of the flight is Paris, and then .all again meaning get me back all of the results from that. And you'll notice once again we see what we saw in JavaScript yesterday, this idea of chaining things together, something.somethingelse.somethingelse.somethingelse. And you can imagine programmatically, you can change these filter bys together, filter by something and then .filter_by something else. So if you really wanted to filter down a large data set into something a bit smaller you can run these subsequent filters in order to filter your data down to only what it is that you want. And this programmatic way of trying to use classes to be able to describe SQL queries is going to give us that flexibility that would have been a little bit tougher if we were just writing raw SQL commands. Questions on anything so far? All right, .all at the end of an ORM query basically means get me back all of the results. If I only want the first result, only one thing, whereas in SQL you would have just said limit one, for example, in order to do that, the Python code for doing that in an ORM, in particular in SQLAlchemy looks very similar. It's just that instead of .all at the end, we just say .first. So .first will just get you the first result coming back from a list of possible results. Yeah. AUDIENCE: How do you put like a variable into one of these things? So like for that example, if you have origin Paris, let's say you want to, if you have a [INAUDIBLE]? BRIAN YU: Yeah, how would you put a variable into it? This string here, Paris, could be any Python value. So this could be a variable that you just substitute in here. Because this is just a Python expression. And so you could put a variable into origin equals Foo, or whatever your variable name happens to be. And that will filter by that particular variable. Then you don't have to worry about escaping anything. You don't have to worry about SQL injection. The ORM handles all of that. Question? Another thing. AUDIENCE: That was my question. BRIAN YU: Oh, OK, got it. So .all returns all of the results. .first returns only the first result. If you were trying to count how many things are in a particular table, so how many flights are from Paris, for example, which you would do via syntax like this, SELECT COUNT star from flights, where the origin equals Paris. The equivalent of that in the ORM is the same thing, flight.query.filter_by origin equals Paris. And then instead of .all or .first, would just say .count. And .count is just going to return to you an integer, some number representing how many row has got returned from this particular query. Select star from flights where id equals 28, this is something we've already seen before. We're only selecting one particular thing. So we could say flight.query.filter_by id=28.first meaning, OK, take all the flights and find the one that has this particular id by filtering down to it. And then just get the first result. But this type of query is one that happens very, very frequently. It's a type of query where very commonly I'll have an id. And I'll want to get the flight with this particular id, or the passenger with that particular id. And so rather than do this more complicated syntax, SQLAlchemy has a bit of an easier way of getting it the same idea and introduces this syntax, just flight.query.get, followed by a number. And that's just going to be the id number. So if you run flight.query.get 28 you will get back what effectively you would have gotten from this query, just returning to you the flight with an id of 28, or whatever the idea happens to be. If there's no flight with id 28 what you'll get back is just the none value in Python. So you can check. If it's equal to none, then there is no flight with that id, for example. Yeah, question? AUDIENCE: Is the get just [INAUDIBLE] for an id, like [INAUDIBLE]? BRIAN YU: The get is going to get the primary key of the table. And so so long as your id is your primary key, we're going to try and get the idea of that table. Yep. AUDIENCE: So if you just use filter_by, shouldn't you have [INAUDIBLE] id? So like shouldn't that just return [INAUDIBLE]? BRIAN YU: Yeah, why do you still need first here? The ORM doesn't know necessarily that there will only be one result even if you know. So if you did filter_by id=28.all what you would get back is a list. And that list would probably only have one thing in it. But it's still a list and you would have to do that square bracket 0 in order to then get at the first object. And this might be something you might be familiar with if you remember working on CS50 finance, that even if you were selecting a row and you knew there was only one row that was going to come back, you still had to index into it and say, OK, get me the first row. And then let me get a property of this. Whereas this .first syntax will just get you back the object. It's not going to return to you a list of possible values. Other things, before we keep going? All right, how would you do an update? Update the flight, set the duration equal to some number where the id equals some other number. Well, the way I might do it looks a little something like this. First let me go ahead and get the flight, flight id number 6. So flight.query.get, get flight number 6. And just save that in a variable called flight. And then to update something you update it the same way you would update any other Python object. I want to update the duration of the flight. I just say flight.duration= whatever I want the new duration to be. And so I'm able to use the Python class and object syntax to be able to create these same general ideas. To delete a flight, delete from flights where id is a particular id, again, I would query for the flight by flight.query.get, get that particular flight, save it in flight. And in the same way that I could say db.session.add to add something to the database I can likewise say db.session.delete this flight to delete that same flight from the database as well, so with updates and deletes, yeah. AUDIENCE: So once you start having multiple flights inside the database, you probably [INAUDIBLE] objects. Instead of flight would you put like F1, or would you put like your own thing? BRIAN YU: Yeah, this flight variable could be any variable. It could be F1. It could be-- it doesn't even need to necessarily be just a single variable. You could, if you wanted to, store a whole bunch of flights in a list and then loop over them and do some operation with all of them. And that would be OK too. All right, and much like this morning when we were talking about transactions, meaning that we were grouping all of our transactions together, and when we were done making a whole bunch of SQL queries we would commit them. We're going to do the same thing here whereas before and SQL if you were typing raw SQL you would just type the word commit. If you type db.session.commit and run that commit function, that will take all of your changes, your inserts, your updates, and deletes, and actually save them to the database. OK, I'll show you a couple other examples of SQL syntax. These are going to start to get a little bit more involved, but just showing them to use that you know that they're out there. If I wanted to SELECT star FROM flights order by origin, in other words sort all the flights in alphabetical order by where they're coming from, whereas I had filter by to say I want to filter a particular class, we also have order by to say I want to order things by a particular class. So I could say flight.query.order_ by flight.origin, and then .all to say get me everything. So adding a .order_by by allows me to sort things. And you could imagine chaining these together. I could do a filter_by and then an order_by, or an order_by and then a filter_by or multiple different filter_bys in order to filter and order data however I want to and then return to me all of the resulting information. Questions about anything so far? Couple other quick examples, if I wanted to sort something in descending order instead of ascending order, instead of ordering by flight.origin, you just order by flight.origin.descending to put that in descending order, for example. And if you want to sort select where the origin is not equal to something, have like arbitrary logic or arbitrary expressions, then you'll have to do something a little bit more fancy. Rather than filter by, you're just going to use filter. And inside a filter that takes any arbitrary Boolean expression, where I could say filter where flight.origin is not equal to Paris. And you can add additional logic to that as well. This comes up a little bit less frequently, but just showing to you in case it were to come up. Yeah? AUDIENCE: So are these [INAUDIBLE] the SQLAlchemy? Or [INAUDIBLE]? BRIAN YU: These are unique to SQLAlchemy, but different, all ORMs usually have a similar set of functions that are along these lines. But these at least are specific to SQLAlchemy. OK, so one thing I want to talk about now is relationships between tables. So in the past, if we had a book, for example-- and this is where our ORMs really start to become useful. Because right now you might be thinking, OK, this is neat an interesting, but so far things are taking about the same number of steps. The relationships that ORMs allow for is where things really start to become a little bit more powerful. Imagine we had our passengers and flights again. And every passenger knew which flight it was associated with, because our passengers table kept track of a column called flight id, for example, that referenced the flights table and said this passenger is on that flight. But going the other way around with a little bit tougher. If I had a flight, a row in the flight's table doesn't have any notion of what passengers is associated with. I would have to separately take that flight, look it up in the passenger's table in order to figure out which passengers it's associated with. And so what relationships are going to allow us to do is instead of saying SELECT star FROM passengers where flight id equals 1, where I wanted to say, OK, get me all the passengers on flight number 1, I could really just say something like, and what I want to be able to say, is something like this. Flight.query.get 1, meaning get flight number 1. And then I want to just be able to say .passengers. And .passengers is interesting because .passengers is not a row in the flights table. The flight's table only has origin and destination and duration. But using relationships that ORMs are going to give us the ability to use, we can now just take a flight and say .passengers to get at all of the passengers that happen to be on that particular flight. I'll show you an example of what that looks like in just a moment as well. And one final example, if we wanted to join tables together, if we wanted to say SELECT star FROM flights, JOIN passengers on flights.id=passengers.flight_id, where passengers.name=Alice, what exactly am I trying to select here? Long complicated SQL query, let's try and tease it apart and figure out what it is that I'm querying for. Yeah? AUDIENCE: Flight Alice is on? BRIAN YU: Yeah, the flight that Alice is on. Right now we have a situation where each passenger is only on one flight. You could imagine generalizing this to allow passengers to be on multiple flights. And so in order to be able to turn this into an ORM query, it might look something a little like this. Passenger.query.filter_by name=Alice and then .first just to say, just get the first passenger whose name is Alice, even if there might be multiple, and then .flight will get me that passenger's flight, for example. So a lot of new syntax. I'll show you some examples to hopefully make this a little bit clearer. And then we'll give you time to play with this on your own as well that will hopefully help with this as well. And so let's take a look at airline 2. So airline 2 is going to be a Flask application that basically looks the same as the airline application we were doing before, whereby I can take a flight, say a Shanghai to Paris flight and say, OK, Josh is going to go on the Shanghai to Paris flight, book the flight, and then OK, success. You've successfully booked the flight. And if I now go to /flights for example, and click on the Shanghai to Paris flight, OK, Josh is now a passenger on that flight. And this is something that I can do here. And so what is the code that makes that work? Inside of models.pi, I have exactly what I had before. I've defined classes now. A flight class and a passenger class, each of which is going to represent the flight's table and the passenger's table respectively. And now let me go to application.pi. What does the index route look like? Well the index route, I just want to be able to display a dropdown of all the possible flights. So I first need to query for all the flights. So flights=flight.query.all, get all the flights. And go ahead and pass those in to index.html, just after all the flights, provide them to the index.html template. In order to book a flight, what do I do? The first part of this code is the same, just getting information from the forum. The interesting part is here. Let's make sure the flight they're booking actually exists. So flight.query.get_flight_id is saying go ahead and try and get that flight from the database, the one with id flight id. If the flight is none, well, OK, the flight doesn't exist. And so I return an error saying, sorry, there is no flight with that particular id. And now assuming the flight does exist, we can go ahead and add the passenger to the flight. And to do that it's as simple as creating a new passenger object, saying, OK, here is the name of the passenger. And here is the flight id of the passenger, saving it inside of a variable that we're going to call passenger, though it could be called anything. Adding the passenger to the database, and then committing the results, saying actually want to run these queries on the database and then returning a success message. How do we display all the flights? Again, all we do is query for all the flights, flight.query.all gets all the flights. And then we render the flights.html template, providing in that data about all of the flights. And then finally if we want to see one particular flight, well here's how we do it. We would say flight=flight.query.get_flight_id, in other words, query for the flight that has id flight id, just get that one flight. If there is no flight with that id, display some error message. And then we care about knowing, OK, who are the passengers that are on that flight? And so to do that, and we'll find simpler ways of doing this in just a moment, I could say passenger.query.filter_by, so I want to get passengers but not all the passengers, only those that meet a certain condition, namely the ones that are on this flight, filter by flight_id= whatever this flight id is, and get all of the results. And then we're going to render a template, passing it information about the flight and also information about the passengers who were on that flight. So ultimately not too much code, but a lot of new syntax. So I'll pause here for questions about why particular lines are the way they are, or why we're doing things in a particular way. Yeah. AUDIENCE: What are the triple quotes? BRIAN YU: What are the triple quotes? These are what we call in Python doc strings. They're basically a way of describing what it is that-- they're effectively a comment. It's a different way of having comments in Python. And it's common for functions to have a doc string, just triple quotation marks, that just describe what it is that the function does. And so for readability sake, we'll often add doc strings to functions just for clarity. Oh, yeah. So Athena wants me to show you-- if I hover over a function like this flights function, you'll see information about the function and you'll also see whatever the doc string happens to be. So it lists that as well. And so oftentimes more complex functions are documented in the doc string in terms of how they're used. And so you can hover over the function and some text editors like VSCode will provide you with information about how they work, for example. Other things, yeah? AUDIENCE: [INAUDIBLE] BRIAN YU: Yep. AUDIENCE: [INAUDIBLE] BRIAN YU: Yes. So this is-- I'm getting information from request.form. And the form that the user is filling out is this-- I need to run the application, flask run-- is this form, just on the default route, this dropdown box and this input field together are going to comprise the form. And when someone submits this form, that data is going to be sent to the server and request.form.getName will get the name that the user typed in and request.form.getFlightID is going to get which dropdown option they selected. Yeah? AUDIENCE: [INAUDIBLE] BRIAN YU: Yes, it is through post. So if we look at index.html, we see that this form uses the post method. And we'll talk a bit more about request methods in just a moment. And in particular, this route specifies method post, meaning post is one of the allowed request methods for this particular route. AUDIENCE: Are you [INAUDIBLE] request.post.get for-- BRIAN YU: In flask, at least, the way that you get at form data is through request.form for post requests that are submitting form data. Other things? OK. I'll show you a couple other examples, just to show you what it is that ORMs can do. So inside of the code now, we are adding passengers like this. We create a new passenger object, add it to the database, and then commit the results, saying actually, we want to save this information. But you might imagine that in a larger application, there might be multiple places in the code where I'm adding passengers. Maybe there's a form to add passengers and maybe whoever manages the airline also has an interface via which they can add passengers to flights. And so I'll show you airline3.html, or airline3 source code. And it's going to behave basically the exact same way. But here's the difference. If I go to models.py, I've defined a class called flight. And most of it looks exactly the same. But I've also added a method to flight. Instead of just leaving it as just the columns, I've added a method called add passenger, just a function that I can use on a flight. And so a flight now has the ability to add a passenger to itself. This is, again, getting at the idea of object oriented programming, taking a flight and adding a passenger to it. And how exactly does a flight, which we're going to call self, add a passenger called name to the database? Well, it's going to create a passenger object whose name is whatever the name of the passenger is. And the flight ID, well, OK, self is the flight. And so self.id is going to be the flight's ID. And so if a flight wants to add a passenger to itself, it's going to use self.id as the id of the flight. We add that passenger to the database via DD.session.add to say add this passenger to the database, and then finally commit the results, same as before. So the only change here-- none of this is going to functionally change the way the program behaves. But I've changed the way my program is organized. I've added to the flight class now the ability to allow flights to add passengers. And now in application.py, if I look down at how to add a passenger to a flight, all I need to do is get the name of the passenger, make sure the flight exists by doing flight.query.getFlightID to try and get that flight. And then to add the passenger to the flight, it's as simple as this line of code. Flight.addPassenger then passing in the name. I want to take this flight, this flight object, run the addPassenger method or the addPassenger function, and the passenger's name is whatever it is that the user typed in into the form. So I've been able to simplify application.py a little bit, adding this addPassenger function, by extending my models a little bit, by adding some code there, as well. Questions about how I did that? Yeah. AUDIENCE: Yeah, just in models, when you did p equals passenger and then parentheses name, that passenger, that looks like a function. Did you define that-- OK. BRIAN YU: It's defined here. AUDIENCE: So that's a separate class [INAUDIBLE]. BRIAN YU: Yes. Passenger is the name of a class. So I'm creating a new passenger by using passenger here. And just in standard Python convention, if it starts with a lowercase letter, then it's generally a function. If it starts with an uppercase letter, generally it's a class and I'm constructing a new class. OK. I'll show you one last example of ORMs. And then we'll move into something a little bit different. Now inside of models I'm going to add what I was referring to before, which is a relationship. So before, passenger had access to a flight ID, namely this is how a passenger gets at what flight ID it's associated with, what flight is that passenger on? But in the past, we haven't had a way for a flight to be able to access what passengers are on it. So we had sort of a one-way relationship happening here. And so inside of flight we can add a new relationship to the flight, information that we want the flight to know about. The flight has an ID. The flight has an origin. The flight has a destination. And the flight has a duration. And ID, origin, destination, and duration, those are all columns. And so we see they're all equal to DB.column. But a flight semantically also has passengers, passengers that are on it. Now, those passengers are not a column. There is no column inside of the flight's table called passengers because passengers are stored in a separate table. But there is a relationship between flights and passengers that is not just a column in the flights table. And so what we're saying here is that passengers is going to be a relationship, namely with the passenger class. So in quotation marks I'm defining the name of the class. The class is passenger. And what I'm saying here is that if I try and access the passenger's property of a particular flight, what I'm going to get is all of the passenger objects that the flight is related to-- in other words, all of the passengers that are on the flight. You don't need to worry about the rest of this as much. Back graph equals flight, it just gives me a way of going the other way around, if I have a passenger getting at the flight, though this is optional. And lazy equals true has to do with something called lazy evaluation, the idea that I don't want to load this information unless I actually need it. More on lazy evaluation if you go on to take classes like CS51, for example. We'll dive a little more deeply into what that means. The core of what you need to understand here is just this first half, the idea that the flight has passengers, but those passengers are not a column of the flights table, they're just something that the flight is related to. Questions about that idea? So in application.py now, when I wanted to take a flight and get access to all of its passengers, I would first just get the flight on line 49 here. Flight.query.getFlightID means I want to get a particular flight, namely the one that has this flight ID, save that in a variable called flight. If there is no flight, render some error. And then to get the passengers on the flight, that's as simple as just saying flight.passengers. I can take the flight, get at all the passengers on the flight by accessing the passenger's property of the flight and then pass that into the template. So moral of this story is that there are a lot of different ways to do the same thing with ORMs. You can use more queries. You can add relationships. You can add methods on all the various different classes. I'd encourage you to look through some of these examples. Try them out. Tinker with them. But definitely no need to feel like you can do all of that syntax right away, because there is a lot of syntax in SQLAlchemy. Their documentation is huge and extensive. And it takes quite a while to get familiar with, get comfortable with. So totally OK if a lot of this feels a little bit unfamiliar. But questions about any of this so far? Yeah. AUDIENCE: Sorry. Just on the db.relationship, [INAUDIBLE] how is that linked to the passengers database? Or can you describe once again? BRIAN YU: Yep. So db.relationship is just indicating the fact that there is some relationship between the flight and the passengers. And I would like to store any of the passengers that are related to this flight inside of this variable called passengers. And what SQLAlchemy is going to do ultimately is try and figure out what that relationship is. And it's going to find that this passenger has a flight ID column that references the flight's table. This foreign key is how SQLAlchemy knows that there is this relationship between flights and passengers. And here we're just giving that relationship a name, calling it passengers, such that from a flight I can access any of the passengers that are on that flight. OK. So ORMs, a lot of new syntax there. Last thing I wanted to talk about today are APIs, or Application Programming Interfaces, which are ways that we can imagine that applications can interact with each other or that we can try and get programmatic information from some sort of service. And we saw an API yesterday, actually, when we were looking at the currency converter where we wanted to be able to look up what the conversion rate was between, like, US dollars and euros, for example. And we went to some API in order to get what the current exchange rates are. And so APIs, at least more modern APIs, will often return data in a format called JSON, or JavaScript Object Notation. And that looks something like this, generally speaking. It's a combination of key value pairs. And so this might be what an API for our flight's application would return if it's returning information about a flight. It's going to say, OK, what is the origin of the flight? What is the destination of the flight? And what is the duration of the flight? And this resembles a JavaScript object. If we add some application that got this JavaScript object, it could look at the origin value, the destination value, the duration value, and do something interesting with this information. JSON allows for more than just strings and integers to be keys and value pairs. It allows arrays, as well. So if I wanted my API to return information about which passengers are on the flight, for example, I might extend the JSON object to look a little something like this, adding in a passenger's key that is equal to an array of all the passengers that happen to be on that flight. And you can nest JSON objects within each other, as well. So if I wanted to add more information about the origin the flight or the destination of the flight, I might have syntax that looks like this, whereby the origin of the flight is itself a JavaScript object that contains, OK, what is the name of the city, and also what is the code of the airport? And likewise do the same thing for the destination, as well. So that's JSON syntax, and the type of syntax that you'll see quite frequently if you begin interacting with APIs and with databases more generally. Yeah? AUDIENCE: [INAUDIBLE] BRIAN YU: Python has support for what are Python dictionaries, which are effectively equivalent to JSON objects. And you can convert between the-- AUDIENCE: --dictionaries. BRIAN YU: Repeat that? AUDIENCE: Can you treat them as dictionaries? BRIAN YU: You can treat them in Python as dictionaries, yes. Yeah. Excellent question. And APIs also will generally obey various different HTTP methods. And so this is common of what's called a REST API, whereby if I want to get information from an API, I want to get information about what the flights are, for example, I might use the get method, which we've seen before in flask already, to mean I want to get information about something. If I want to send information, I want to create a new resource, add a new row to a table, like I want to book a flight, for example, or add myself as a passenger to a flight, that will generally be done via the post HTTP method, where the post HTTP method is understood in the REST world to mean I want to add some new data. I want to register myself for a flight, for instance. And there are other HTTP methods that you'll often see, as well, for replacing data, for updating data, for deleting data, much in the same way that we can update rows of a SQL database or delete rows from a SQL database. Those both use different HTTP verbs, as well. Yeah? AUDIENCE: [INAUDIBLE] BRIAN YU: Patch is generally used if I want to, like, update a field of a resource, if I want update one particular column. Put is generally used for replacing an entire resource. And so in order to be able to interact with APIs one common library you'll often see used in Python is the requests library. It is generally used for making any kind of HTTP request to somewhere else. And so I'll show you an example of that. If I open up Python and import the requests library, I'll do request.get. And then I can type in basically any URL. I'll say, you know, Google.com. And I'll save the results of this in a variable that I'll call the response. So I'm going to make an HTTP request and I'm going to get back some sort of response. And OK, what is the response's status code? Status code is 200, meaning everything went OK with this request. It got back some OK response. If it was 404 that would mean that whatever my request was wasn't found and there are other status codes as well for different types of errors that you might recall from CS50. And if I want to see the contents of this response I can say something like response.text. See OK. Here is the HTML that corresponds to Google's website. Doesn't look particularly pretty, but at least I see something that says Google search. So something in there allows me to search for something on Google's web page. And so this is the request library. And I show you this mainly so that we can begin to use it in order to make API requests of your own should you wish to. So I'll show you an example inside of currency. I'll show currency1.py, where what I'm doing here is saying request.get and requesting data from the exchange rates API, that same API that we were using yesterday, to say I would like to get some information about the exchange rates between US dollars and euros, for example, where here I'm saying base equals US dollars and symbol equals euro. And I only know this from looking at the documentation for this particular API. But this is how I get information about what the exchange rate is between those two currencies. If I were to go directly to that URL, type it in, here's what I get back-- a JavaScript object that has a key called rates, and then inside of that, a key called EUR, for euros, and inside of that, there is the exchange rate between US dollars and euros. And so programmatically, if I want to get at that idea, what I'll first do when I get a response is check to make sure that the status code is 200. If the status code is not 200, something went wrong. And then I'll go ahead and say, response.json to mean take the HTTP response, convert it into a JSON object for me, a Python dictionary, effectively. And then in order to get what the exchange rate is, well if you remember, here's the data. It's inside of the rates keyword, and then inside of that in a key called EUR. So I'll say data, square bracket rates, square bracket EUR. Save that inside of a variable called rate. And then I can print something out. One US dollar is equal to that number of euros. Or by now if I run Python currency1.py what I get is one US dollar is equal to that number of euros, for example. Currency2.py, if you want to take a look at it, basically does the same thing, but it just prompts me to type in currencies first, a base currency and another currency. And then it performs that request, passing in those currencies that I wanted to provide, as opposed to just whatever ones were fixed. And so if I do Python currency2.py type in euros and Japanese yen, for example, I get one euro is equal to 124.25 Japanese yen. And so I can write Python scripts that interact with APIs in this way. And so you'll often find that APIs can be very powerful in giving you the ability to get access to information that you wouldn't ordinarily have access to, information about currency exchange rates, for instance. There are APIs for dealing with the weather, for example, that allow you to get what the current weather is, if that's something you care about displaying on your web page. And there are all sorts of different other APIs that you can use for financial transactions and getting stock market data and whatnot, all that you can use just by making web requests to some other endpoint and then taking that data and doing something interesting with that data. Yeah, question? AUDIENCE: How does params work? BRIAN YU: How does prams work? Good question. So in currency1 what you notice is I put in some hard coded HTTP parameters where I said the base is going to be US dollars and the symbol is going to be euros, meaning I want to convert from US dollars to euros. The request library in Python has a way of generating those URLs for me. And all I have to do is specify what the parameters are. So I say the base is this variable called base and the symbols are whatever the other variable is. And based on that, the request library is going to construct a URL that's going to look like that, turning it into base equals something and symbols equals something else. It's just a bit of an abstraction so I don't have to type the ampersand and the question mark and the equal signs on my own. I just let the library handle it for me so that I don't make a mistake, for example. AUDIENCE: [INAUDIBLE] BRIAN YU: You could have used a format string just to insert something in there, yes. But if you've imagined starting to deal with situations where there might be spaces in the text that you then need to escape, it becomes a lot easier just to let the library handle it for you instead of having to reinvent the wheel. Yeah? AUDIENCE: [INAUDIBLE] BRIAN YU: So there are differences between APIs, but oftentimes there is a standard set of conventions. And generally, it's conventional that when you have a get request, the parameters to the get request are going to be the arguments that are going to determine what sort of information is going to come back. Every API is a little bit different, so you'll want to look to that particular API documentation. But looking at the API documentation will usually tell you how it works. So for instance, GitHub has an API that you can use in order to request information about commits on a repository or who are the collaborators on a repository. So if I just go to GitHub API, I can find GitHub's API documentation, whereby I can say, all right, if I want to get information about a repository and I want to get information about the commits on a repository, I can say, OK, great, if I run a get request, remember get to get information to this particular URL slash repo slash whoever the owner of the repo is slash the name of the repo slash commits, then I'll get back all of the commits. And then most APIs will also give you information about, OK, here are the parameters you can provide. I can provide a parameter of since to say-- if I say and since equal some date, it will only get me commits since a particular date, for example. And so every API is a little bit different. But if you refer to the documentation, it will usually tell you what are the types of parameters that you can use in any particular API request. Other things? All right, so there was a lot of information today. And I know that. So we'll give you plenty of time to work on practicing this. Tomorrow, though, we're going to do basically a complete context switch, just to give some context for where we're going in the latter half of the week. We're done with flask. We're done with databases after the end of today. We're going to go back to the world of just HTML pages and JavaScript and look in particular about how to build dynamic web applications using JavaScript, creating interactive user interfaces, and starting to solve a lot of the problems that we've been seeing over the course of these past couple of days where we've had to write a lot of code to get fairly simple things to work inside of our user interfaces. So this is going to be our last project that has to do with flask and SQL before we move away from this, at least for the rest of CS50 Beyond. And the project that I'll have you work on is going to be an extension of the books that you were working on this morning. So if you haven't yet gotten all the books imported into your Heroku postgres database, feel free to continue working on that. And we're happy to help you as you go about taking that CSV file of books and importing it into your database. My recommendation would be take it a little bit at a time. Try and see if you can get one row imported into the database, one book added to the database. And then if that works, try doing a couple books. And only then maybe consider trying all the 5,000 books that we gave you inside of the file. But then-- and you can use ORMs for this next part if you would like to, or you don't have to if you would rather not-- try creating a web application around the work that you've been doing. So I'll show you books. A simple web application that you might want to build might look a little something like this. We open up the web page and it's just going to be a place where I can find a book by a title or by an author. So if I type in something like Joe, for example, and press Submit, well, what I'll get is a returning a listing of all the books that have the word Joe in it, all the authors that have Joe as the name or as a part of the name, for example. And if I click on a book, I might see details about that book, for example. And so you can imagine creating a web application. You can model it off of the flights application that's just querying flights that's instead querying your book database. And so think about the possibilities that you might want to do with that. You can also feel free to continue working on the projects you've been working on earlier in this week. I know some of you are still working on adding features to you tic-tac-toe program or your drawing application or any of the other applications that you've been working on over the course of CS50 beyond so far. And so we'll go ahead and break now into our afternoon project. Feel free to continue working on importing books. Feel free to start working on this web application if that's something that's of interest to you. What we'll have is we'll have this group and this group go ahead and stay in the auditorium. The back half of the middle group we'll have go to room 136. And the front half will go up to room 212. And tomorrow morning at 10:00 AM, we'll continue diving into React.