CS50 Video Player
    • 🧁

    • 🍦

    • 🍈

    • 🍿
    • 0:00:00Introduction
    • 0:00:15SQL
    • 0:08:03Tables
    • 0:14:23SQL Queries
    • 0:30:16Joining Tables
    • 0:49:14Django Models
    • 0:51:34Migrations
    • 0:53:50Shell
    • 1:11:16Django Admin
    • 1:19:09Many-to-Many Relationships
    • 1:40:20Users
    • 0:00:00[MUSIC PLAYING]
    • 0:00:17BRIAN YU: All right, welcome back everyone
    • 0:00:19to Web Programming with Python and JavaScript.
    • 0:00:21So last time we took a look at a brand new web framework
    • 0:00:24written in Python called Django.
    • 0:00:26And what Django allowed us to do was to build dynamic web applications.
    • 0:00:30Applications where we now had the ability
    • 0:00:32to not just display the same HTML and CSS to the user every single time,
    • 0:00:36but to dynamically generate HTML in order
    • 0:00:38to allow users to interact with a page that was a little bit more dynamic.
    • 0:00:42Doing things like rendering things on the page only if a certain condition
    • 0:00:45was true.
    • 0:00:46Or looping over a list of values and displaying one HTML element
    • 0:00:50for each of those possible values.
    • 0:00:52But where Django gets especially powerful
    • 0:00:54and we're web application certainly get much more interesting
    • 0:00:56is as we start to delve into the world of data
    • 0:00:59and trying to have web applications that store data inside of a database.
    • 0:01:03And to do that we're going to introduce a couple of topics today.
    • 0:01:06In particular, we're going to introduce the idea of SQL, models,
    • 0:01:09and migrations.
    • 0:01:10SQL is a database language that we can use to interact with databases
    • 0:01:14and Django is going to allow us to have an abstraction layer on top of SQL.
    • 0:01:18To interact not by writing direct SQL queries,
    • 0:01:21but by interacting with Python classes and objects
    • 0:01:24that we're going to refer to as models.
    • 0:01:26And migrations are going to be a technique that
    • 0:01:28are going to allow us to update our database in response to changes
    • 0:01:32that we make to our underlying models.
    • 0:01:34So before we get into things that are more Django specific,
    • 0:01:37let's begin with a discussion of SQL more generally.
    • 0:01:40And more broadly, just a discussion of data and the types of data
    • 0:01:43that we're going to want to store.
    • 0:01:44And now there are a number of ways that we could try to store data inside
    • 0:01:47of a computer system but oftentimes in databases,
    • 0:01:50in particular, in a type of database known as a relational database,
    • 0:01:54we're going to store data inside of a table where each table is
    • 0:01:57going to have rows and columns.
    • 0:02:00So for the sake of today, we're going to imagine
    • 0:02:02starting to construct a database and a web application
    • 0:02:04that an airline might use.
    • 0:02:05For example, for keeping track of various different flights
    • 0:02:08on that airline and keeping track of which passengers are on those flights.
    • 0:02:12And so what we have here is a sample table, for example,
    • 0:02:15of how you might represent a whole bunch of flight related data.
    • 0:02:19Here I have three columns for three pieces of information
    • 0:02:22that I might want to keep track of for any particular flight.
    • 0:02:25For any given flight I might want to know that flight's
    • 0:02:27origin, what city it's weaving from.
    • 0:02:29Its destination, what city it's arriving at.
    • 0:02:31And its duration, some value in minutes of how long
    • 0:02:34it's going to take the flight to get from the origin to the destination.
    • 0:02:38So each of those columns represents one field
    • 0:02:40that I might want to keep track of about my data.
    • 0:02:42And each row, meanwhile, is going to represent an individual flight.
    • 0:02:46So here, for example, is one row representing one flight from New York
    • 0:02:50to London and that flight just so happens to take 415 minutes.
    • 0:02:55And so what SQL is going to allow us to do
    • 0:02:57is it is going to be a database of language that
    • 0:02:59is designed to interact with relational database management systems.
    • 0:03:03Databases that organize data into tables where every table has rows and columns.
    • 0:03:08And it turns out, there are a number of different database management systems
    • 0:03:11that implement parts of the SQL standard of this type of language.
    • 0:03:15Some of the more popular are MySQL, PostgreSQL, and SQLite
    • 0:03:19but there are definitely others as well.
    • 0:03:20And each of these has various different features and different use cases
    • 0:03:23where some might be more appropriate.
    • 0:03:25MySQL and PostgreSQL, otherwise known as just Postgres,
    • 0:03:29are more heavier database management systems, so to speak.
    • 0:03:32They're generally running on servers elsewhere where a lot of big companies
    • 0:03:36will use servers like this in order to store data
    • 0:03:39and represent their data in a sort of a separate database where.
    • 0:03:42The idea of that is that you might want your web application running
    • 0:03:45on one server but you might want your database
    • 0:03:47running as an entirely separate process.
    • 0:03:48Such that they can behave independently, you can debug and test them
    • 0:03:51independently, and if one goes down, the other doesn't go down as well.
    • 0:03:56SQLite is a bit of a simpler lighter weight
    • 0:03:58implementation of the SQL standard.
    • 0:04:00And what SQLite is going to do is rather than be an entire server that
    • 0:04:03is going to be listening for requests and making responses,
    • 0:04:06SQLite is just going to store all of its data as a single file.
    • 0:04:10So we're going to store SQL data inside of a SQLite file
    • 0:04:12and that's going to make it a little bit easier for us
    • 0:04:14as we're just getting started to begin to write queries and add things
    • 0:04:18to our database and retrieve things from our database.
    • 0:04:20But know that many of the same types of SQL syntax that we'll see with SQLite,
    • 0:04:24which is Django's default, can also be applied
    • 0:04:26to other languages as well and other database management systems too.
    • 0:04:31So as we begin to store data inside of a SQL database,
    • 0:04:34ultimately each of those pieces of data has a type.
    • 0:04:37In the same way that in Python we have types
    • 0:04:39for various different types of data, we have integers and we have strings
    • 0:04:42and we have lists and tuples and so forth.
    • 0:04:44SQL too has types that represent various different categories of information
    • 0:04:49that you might want to store inside of a database.
    • 0:04:51And each database management system has its own different set of types.
    • 0:04:54SQLite for example, has a fairly short list of basic types that it supports.
    • 0:04:59It supports text for just like strings of text, for example.
    • 0:05:03Something like a city name, for instance, might be stored as text.
    • 0:05:06We have support for integers, which are just 0, 1, 2, 3 4
    • 0:05:09plus the negative numbers, negative 1, negative 2, negative 3, and so forth.
    • 0:05:14We have support for real numbers where real numbers don't
    • 0:05:16have to just be integers, they can have something after the decimal point.
    • 0:05:19Something like 2.8 or something else that might have a decimal in it.
    • 0:05:23And then numeric is another category for data that's just more generally
    • 0:05:26some sort of number that might number like data,
    • 0:05:29so something like a Boolean value or something like a date, for example,
    • 0:05:33might be stored using this numeric type.
    • 0:05:35Where it's not really an integer or a real number,
    • 0:05:37though you could represented at such, it's other numeric type of data.
    • 0:05:41And then finally blob, which stands for binary large object,
    • 0:05:44is any type of other binary data.
    • 0:05:46Just zeros and ones that you might want to store inside of your database.
    • 0:05:49So for example, if you're storing files, maybe they're
    • 0:05:52audio files or images or other types of files
    • 0:05:54that you want to keep track of inside of your database,
    • 0:05:57you can store just pure binary data inside of a SQL database as well.
    • 0:06:02So these now are the basic types of SQLite supports
    • 0:06:05but other database management systems have
    • 0:06:06other types that they support as well.
    • 0:06:08MySQL, you all for example, has a much longer list of other types
    • 0:06:11that it supports.
    • 0:06:12In addition to just supporting a text type for arbitrary length text,
    • 0:06:16MySQL also has a type called char that takes an argument of size.
    • 0:06:20For saying I want like size characters worth of data
    • 0:06:23that we're going to store inside the database.
    • 0:06:25And you might imagine that this can be advantageous in situations
    • 0:06:28where you know that your data is going to be
    • 0:06:30within a certain number of characters.
    • 0:06:32So if you're storing a zip code for example, in the United States.
    • 0:06:35A zip code in the United States might just be five characters
    • 0:06:38and so you can allocate a character length
    • 0:06:40of five characters worth of space for storing every single zip code
    • 0:06:44inside of your database table.
    • 0:06:45So for efficiency's sake, if you know the maximum length,
    • 0:06:48and you can fix that length inside your database,
    • 0:06:50that can be an efficient choice.
    • 0:06:52VARCHAR of a size is similar.
    • 0:06:54And this is a variable length character.
    • 0:06:56If something isn't going to be exactly a certain number of characters,
    • 0:06:59but maybe up to a certain number of characters,
    • 0:07:02you can use VARCHAR to say maybe sometimes it's
    • 0:07:04going to be fewer characters.
    • 0:07:05But it could be up to size, number of characters.
    • 0:07:08And there are tradeoffs there, too.
    • 0:07:11Then there are various different types of integers.
    • 0:07:13There's not just a single integer type.
    • 0:07:15But SMALLINT and INT and BIGINT, each of which uses a different number of
    • 0:07:18bytes in order to store an integer data.
    • 0:07:21So if you want to be able to store much larger or much smaller numbers,
    • 0:07:24you might need a bigger type, like an INT or a BIGINT.
    • 0:07:26But if your numbers are going to be pretty small,
    • 0:07:28maybe you're OK with just a SMALLINT.
    • 0:07:30And likewise there are similar types of trade-offs for floating point numbers.
    • 0:07:33Where, much as in programming languages like C, if you're familiar,
    • 0:07:36have both a float and a double type.
    • 0:07:38Where a double uses more bytes of memory in order to store information.
    • 0:07:42Here, too, we have FLOAT and DOUBLE, which allow
    • 0:07:44us to store floating point numbers.
    • 0:07:46Numbers that might be real numbered values, where a double just
    • 0:07:49allows us to express a number to a little bit more precision
    • 0:07:52than a floating point number might be able to.
    • 0:07:54And there are many other types as well, but the general idea
    • 0:07:57is that here even in SQL, much as in a language
    • 0:07:59like Python, we have types for each of these various different kinds of data.
    • 0:08:03So now that we understand that each type of data has types, what we'd like to do
    • 0:08:07is to be able to, inside of a database, actually create a table.
    • 0:08:11And there are various different SQL commands
    • 0:08:13that we can run on our database in order to perform
    • 0:08:15various different operations.
    • 0:08:17And the first we might want to execute is
    • 0:08:19a command that is just going to create a table, for example.
    • 0:08:22So how might we go about doing that?
    • 0:08:24Well, it turns out the syntax for creating
    • 0:08:25a table is going to look a little something like this.
    • 0:08:28This here is the syntax we would use to create a table in SQLite
    • 0:08:32for representing flights, for example.
    • 0:08:35So we begin with the keyword Create Table,
    • 0:08:38to say I would like to create a new table inside of this database.
    • 0:08:41Next I give the name of the table.
    • 0:08:43Every table has a name just for ease of reference.
    • 0:08:46And here the name and the table is just the word "flights."
    • 0:08:49Then what follows in parentheses are a comma-separated list
    • 0:08:53of all of the columns that should be present in the table.
    • 0:08:56I'm not yet adding any data to the table.
    • 0:08:59The Create Table query is just going to decide the structure of the table.
    • 0:09:02What are the columns and what are the columns' types.
    • 0:09:05And so here we see that in the beginning of each
    • 0:09:08of these clauses that are separated by commas, we have the name of the column.
    • 0:09:13So we have an id column because, though we didn't see this before, often in SQL
    • 0:09:18it's going to be helpful to be able to have
    • 0:09:19some way of uniquely referencing a particular element inside of the table.
    • 0:09:23Maybe there are multiple flights that are
    • 0:09:25going from New York to London, for example,
    • 0:09:28and I want some way of distinguishing between them.
    • 0:09:31And one easy way is to give every single entry inside of a table
    • 0:09:35a unique identifier, or a unique id, represented here by this id column.
    • 0:09:40And that's going to be a way of making sure that we can always
    • 0:09:42access a particular flight uniquely.
    • 0:09:45So we have an id column, an origin column, a destination column,
    • 0:09:48and a duration column.
    • 0:09:50Each of those columns has a type.
    • 0:09:53So the types here, id is an integer.
    • 0:09:55It's just going to be some number representing the flight, counting one,
    • 0:09:58two, three, four, and so on and so forth.
    • 0:10:00Origin and destination we'll label here as text.
    • 0:10:03Though you'll see that if we were using my SQL,
    • 0:10:06I might make these a variable length character that
    • 0:10:08could be up to a certain number of characters
    • 0:10:10if I know there's probably not going to be
    • 0:10:11a city that's longer than a certain number of characters, for instance.
    • 0:10:15Then I have a duration, which here is an integer.
    • 0:10:18Some number of minutes that is going to be
    • 0:10:19how long it takes for this flight to go from the origin to the destination.
    • 0:10:24After the types for each of the columns, I
    • 0:10:26have additional constraints that I might add to the columns as well.
    • 0:10:29So integer Primary Key.
    • 0:10:31The Primary Key here means that the id is
    • 0:10:34going to be the primary way via which I uniquely identify a flight.
    • 0:10:38There's going to be some optimizations here
    • 0:10:40to make it very quick to be able to look up a flight by its id.
    • 0:10:44Null is a constraint that I can place on columns to be able to say,
    • 0:10:48I don't want to allow for this particular column to ever be empty.
    • 0:10:51I don't want there to ever be a flight that doesn't have an origin
    • 0:10:54or doesn't have a destination or a duration.
    • 0:10:56So I can add constraints to a particular column to say,
    • 0:11:00the origin, destination, duration, columns
    • 0:11:02should not be allowed to be null.
    • 0:11:04I want to always have an origin and a destination and a duration.
    • 0:11:09Finally this Autoincrement after the Primary Key
    • 0:11:12is a cue into SQL to automatically update the id every time
    • 0:11:15I add a new row into the table.
    • 0:11:17So if I add a new flight, I can give the flight
    • 0:11:19an origin, destination, and duration.
    • 0:11:21But I don't have to worry about giving it an id.
    • 0:11:23SQL is going to handle that for me automatically.
    • 0:11:25Automatically giving that new row in that table the next available id
    • 0:11:32number.
    • 0:11:33Here, then, is the way we create a table by giving the name of the table,
    • 0:11:37each of the columns, the types for each of those columns,
    • 0:11:39and then any constraints we would want to place on the columns.
    • 0:11:44There are a number of different types of constraints
    • 0:11:46that I can add to a particular column.
    • 0:11:48Not null is one we've seen before.
    • 0:11:50You can add a default, which just adds a default value to a particular column.
    • 0:11:54Primary Key we've seen.
    • 0:11:56Unique guarantees that every value is going to be unique.
    • 0:11:59So if you don't want to allow for the same value
    • 0:12:01to appear twice for a particular column, you
    • 0:12:04can add a unique constraint to a particular column
    • 0:12:06to enforce that as well.
    • 0:12:08Check can be used to make sure that a value obeys a certain condition.
    • 0:12:12Like, a number falls within a certain range, for example.
    • 0:12:14If you have movie ratings, for example, and you're storing movie ratings inside
    • 0:12:18of a database, you might care to make sure
    • 0:12:19that those ratings are within the 1 to 5 range or the 1 to 10 range
    • 0:12:23or whatever range you deem valid for the database.
    • 0:12:27So via constraints you can begin to ensure
    • 0:12:29that as you add data to the table, that data is going to be valid in some way.
    • 0:12:33That it needs to obey and certain constraints,
    • 0:12:36otherwise, if you try to add it to the table,
    • 0:12:38it's not going to be accepted at all.
    • 0:12:40And so that leads into the natural next question, which
    • 0:12:43is, I now have all of these various different tables
    • 0:12:45that I can create using these create table commands,
    • 0:12:48but how do I actually add data into those tables?
    • 0:12:51The way we're going to do that is via an INSERT command, where we're
    • 0:12:55going to insert data into a SQL table.
    • 0:12:58So what might that command look like?
    • 0:13:00The command is generally going to look like this.
    • 0:13:02We're going to say, INSERT Into, to say I
    • 0:13:05would like to add a new row into a table.
    • 0:13:08Following that, we provide the name of the table.
    • 0:13:10Something like "flight" to say, I would like
    • 0:13:12to add a new row to the flights table.
    • 0:13:14And following the name of the table, we need
    • 0:13:16to provide in parentheses a comma-separated list
    • 0:13:19of all of the column names for which we are going to provide values.
    • 0:13:24So here I said I want to provide values for the origin, destination,
    • 0:13:28and duration of this flight.
    • 0:13:30Note that I'm leaving out the ID column.
    • 0:13:32I don't have to worry about adding a value for the ID,
    • 0:13:35because I said the id should autoincrement.
    • 0:13:36So it's going to automatically give the next available ID.
    • 0:13:40And what values am I going to provide?
    • 0:13:42Well, after the word "values", I provide, again,
    • 0:13:44in parentheses, a comma-separated list of all of the values
    • 0:13:48that are to be associated with those columns in the same order.
    • 0:13:51So origin was the first one here.
    • 0:13:53So origin will correspond to New York.
    • 0:13:55Destination will correspond to London.
    • 0:13:57Duration will correspond to 415.
    • 0:14:00So this query let's me take the flights table that I've already created
    • 0:14:04and add some data into that table.
    • 0:14:09So you can use these INSERT queries once you
    • 0:14:11have a table to being able to add new rows of data to that table.
    • 0:14:14Every time a new flight comes up for an airline,
    • 0:14:16the airline might, behind the scenes, be running an INSERT
    • 0:14:19into Flights command that takes data about that flight
    • 0:14:22and adds it to the table.
    • 0:14:24But, of course, once we've added data to a table,
    • 0:14:26we'd ideally like some way to get data out of that table, as well.
    • 0:14:30That once we've stored data inside of our database
    • 0:14:33we want to retrieve that data, too.
    • 0:14:35To do that, we're going to use a particular type of query
    • 0:14:38called a SELECT query.
    • 0:14:39What SELECT is going to do is take a table that already exists
    • 0:14:43and allow us to get data out of that table.
    • 0:14:46It's not going to modify the data that's there,
    • 0:14:48it's just going to retrieve data that might be inside a particular table.
    • 0:14:52So what do those queries look like?
    • 0:14:53Well, the simplest query might look a little something like this.
    • 0:14:56SELECT * from flights.
    • 0:14:59SELECT from flights means I want to select data from the flights table.
    • 0:15:03And this * is a wild card that here just means
    • 0:15:07select all of the possible columns that you can.
    • 0:15:10So if this is my table, my table of flights
    • 0:15:11where each flight has an id, an origin, a destination, and a duration,
    • 0:15:16then select * from flights will select all of that data.
    • 0:15:20All of the rows and all of the columns are
    • 0:15:22going to come back to me when I run a query like this.
    • 0:15:25Now it's possible that when I'm running these queries, when I'm accessing data,
    • 0:15:29that I don't actually care about all of these columns,
    • 0:15:31especially if I have a table with many more columns than just this.
    • 0:15:34Maybe I only care about particular columns.
    • 0:15:37And it would be wasteful to query the database
    • 0:15:39asking it to return to me a whole lot more data than I actually need.
    • 0:15:43So instead I could run query like this.
    • 0:15:45SELECT-- instead of * I'm saying select origin comma destination from flights.
    • 0:15:52So I'm again selecting from the flights table.
    • 0:15:54Selecting all of the rows still, but the difference
    • 0:15:57is that instead of select *, which means select all of the columns that
    • 0:16:01are present in the table, select origin comma destination means I'm only
    • 0:16:05going to select these two columns--
    • 0:16:07the origin column and the destination column.
    • 0:16:09So highlighted in blue here is the data that
    • 0:16:12would be returned if someone were to run this particular SQL query.
    • 0:16:16Now especially as tables begin to get larger and begin to have more and more
    • 0:16:20rows-- here I just have six rows, but you might imagine a table with
    • 0:16:23thousands or even millions of rows within it--
    • 0:16:25you probably don't want to be returning every single row every single time.
    • 0:16:29So just as you can constrain which columns come back to you,
    • 0:16:32likewise, you too can constrain which rows come back
    • 0:16:35to you when you perform a query.
    • 0:16:37So I could say something like select * from flights where id= "3."
    • 0:16:41Again, this reads as very English like.
    • 0:16:43I'm saying select all of the columns from the flights table,
    • 0:16:47but not all of the rows.
    • 0:16:49I only want to select the rows where--
    • 0:16:51this is a SQL keyword--
    • 0:16:53where id= "3."
    • 0:16:54ID is the name of a column, 3 is the value,
    • 0:16:57and it's going to look through this table, only find me the flights
    • 0:17:00where the id is equal to 3, and I'll just get back that one row as a result.
    • 0:17:05It finds the row with an ID of 3, and it gives that back to me.
    • 0:17:10If, instead, I said select * from flights where origin= "New York,"
    • 0:17:14I can see that I don't just need to filter on the primary key--
    • 0:17:17the id-- I can also filter on any other column.
    • 0:17:19I can say, give me all the flights where New York is the origin.
    • 0:17:22So all the flights leaving from New York.
    • 0:17:24You might imagine that in New York's airport,
    • 0:17:26for example they likely want some sort of query that's
    • 0:17:29going to find all the flights leaving from New York such
    • 0:17:32that they can display on a screen of some sort all of the departures
    • 0:17:35from that particular airport.
    • 0:17:36So you could run a query like this and get back
    • 0:17:39only the rows in this database, inside of this table, that happened to have
    • 0:17:43an origin of New York, for example.
    • 0:17:46So let's go ahead and take a look at how we might actually
    • 0:17:49run some of these queries inside of a SQL database.
    • 0:17:53So in order to create a SQLite database, which is really just a file,
    • 0:17:57I can start just by creating the file.
    • 0:17:59It turns out that in the terminal, there is a command called Touch that
    • 0:18:02will create a brand new file for me.
    • 0:18:04So I'm going to create a file called flights.sql.
    • 0:18:08It's going to be a SQLite database file that by default has nothing in it.
    • 0:18:12But then, assuming you have SQLite installed,
    • 0:18:15I can run SQLite 3 followed by flights.sql,
    • 0:18:20and now I'm inside of the SQLite prompt.
    • 0:18:22I can now begin to write commands that will be executed on this SQL database.
    • 0:18:28So one thing I might do is create a table called "flights."
    • 0:18:32In this table, I want to have an id column, which is an integer.
    • 0:18:36It should be the primary key, the main way via which I identify a flight.
    • 0:18:40I would like to autoincrement it.
    • 0:18:42I would also like for the flights table to have an origin, which
    • 0:18:45will be a text field that is not null.
    • 0:18:48I'll add a destination field, also a text field that is not null.
    • 0:18:52And then finally I'll add a duration field,
    • 0:18:54which is an integer that also shouldn't be null.
    • 0:18:57I'll end with an end parentheses and a semicolon, press Return,
    • 0:19:00and that executes that command on my database.
    • 0:19:03I have now created a table called "flights."
    • 0:19:06To verify this in SQLite, in the prompt I can type .tables,
    • 0:19:10and that will show me all of the tables that currently exist in my database.
    • 0:19:14It just so happens that I have a table called "flights."
    • 0:19:17Of course, there's nothing inside this table, which
    • 0:19:19I can verify by running SELECT * from flights,
    • 0:19:22meaning get all the data from the flights table.
    • 0:19:25If I press Return nothing happens.
    • 0:19:27I don't see anything.
    • 0:19:28There is no data that happens to be here.
    • 0:19:30But now I could say something like INSERT into Flights.
    • 0:19:34Then I'll provide in parentheses the names of the columns.
    • 0:19:36And on the slide a moment ago I showed this on multiple lines.
    • 0:19:39That was just for visuals sake.
    • 0:19:40These commands can be entirely on one line if we would like it to.
    • 0:19:43So I can say origin, destination, and duration and then provide values.
    • 0:19:48I can say let's add New York to London, and I'd
    • 0:19:52like for this flight to be duration of 415 minutes.
    • 0:19:56So I type in the command, press Return.
    • 0:19:58That adds a new row to my flights table.
    • 0:20:01I can verify this by running SELECT * from flights.
    • 0:20:06And I see that now I have this table whose id is one--
    • 0:20:09that's the id column from New York to London--
    • 0:20:12with a duration of 415 minutes.
    • 0:20:15I could do this again and again.
    • 0:20:16I, in advance, prepared a couple of INSERT
    • 0:20:18queries just to give us a little bit more data.
    • 0:20:20Each of this is just a different INSERT query
    • 0:20:23that's going to add one new flight into our database.
    • 0:20:26So I'll go ahead and copy those and then into the database
    • 0:20:29I'll go ahead and paste in all these INSERT commands
    • 0:20:31to insert a whole bunch of flights into this database.
    • 0:20:34Now if I run SELECT * from flights what I get is all of the flights.
    • 0:20:40This is not formatted particularly nicely.
    • 0:20:42SQLite has some additional tricks if you'd like to format it a little nicer.
    • 0:20:45So I can say, go ahead and put this into columns mode,
    • 0:20:48and it gives me some headers.
    • 0:20:51So .headers yes.
    • 0:20:54And now if I SELECT * from flights, the data
    • 0:20:56is organized a little more nicely where things are really into columns
    • 0:20:59and there are headers for each of the rows.
    • 0:21:01Where I can see that I kind have a text-based representation of what
    • 0:21:05I was displaying graphically a moment ago.
    • 0:21:07That inside of my flights table I have four columns-- an id column, origin,
    • 0:21:11destination, and duration.
    • 0:21:13Each flight is just one of the rows that's inside of this table.
    • 0:21:18Now I can begin to run additional SELECT queries.
    • 0:21:20If I want to do something like SELECT * from flights where origin= "New York,"
    • 0:21:28well then instead of getting back all of the flights,
    • 0:21:30I'm only going to get back the flights where the origin is New York.
    • 0:21:33So I can very quickly filter down on this data,
    • 0:21:36only getting the information that I actually care about taking a look at.
    • 0:21:39There are other ways of interacting with SQLite as well,
    • 0:21:42but this SQLite prompt is a very direct way
    • 0:21:44of just being able to write a query very quickly
    • 0:21:46and see the results of that query.
    • 0:21:49So it turns out, there are other types of queries we can run as well.
    • 0:21:52So we'll take a look at some others.
    • 0:21:53We don't just need to say where something equals something else.
    • 0:21:57Other types of Boolean expressions are allowed as well.
    • 0:21:59So I could say something like SELECT * from flights
    • 0:22:02where duration is greater than 500.
    • 0:22:05Where the idea here is let's look at the duration column.
    • 0:22:08It's an integer, so we can do arithmetic expressions on these values.
    • 0:22:12I can take a value and see if it's greater than 500 or not.
    • 0:22:15When I do that, what I get back is all of the rows that have
    • 0:22:18a duration that's greater than 500.
    • 0:22:20I can begin to query not just on whether one value equals
    • 0:22:23another value, but also other types of comparisons as well.
    • 0:22:27Much as in Python, as when we had Boolean expressions in Python,
    • 0:22:30I could join multiple Boolean expressions together
    • 0:22:33using words like AND and OR.
    • 0:22:35SQL has the same type of thing in its own query syntax, too.
    • 0:22:39I could say something like SELECT * from flights where duration is greater than
    • 0:22:43500 AND destination= "Paris."
    • 0:22:46As you might logically intuit this means,
    • 0:22:48this means get me all the flights that are going to Paris
    • 0:22:51and that take longer than 500 minutes.
    • 0:22:53It turns out there's only one such row inside this table that
    • 0:22:56satisfies both of those constraints.
    • 0:22:59Of course, instead of using AND I could've also
    • 0:23:01used OR to get a different query where now I'm looking for all of the flights
    • 0:23:08longer than 500 minutes or the destination is Paris.
    • 0:23:11So as long as either of those conditions are met, I'm going to get results back.
    • 0:23:15So what I get back are some of the rows where the destination is Paris,
    • 0:23:19some rows where the destination isn't Paris,
    • 0:23:21but the duration is more than 500 minutes,
    • 0:23:23and of course any row that satisfies both of these constraints.
    • 0:23:27In particular, flight id number 2.
    • 0:23:29That gets resulted as well when I run this particular query on this table.
    • 0:23:35There are other types of queries I can perform in addition to that.
    • 0:23:38So I can say something like SELECT * from flights
    • 0:23:41where origin is in-- and then in parentheses-- in "New York"
    • 0:23:44comma "Lima" to say, check if the origin is in this sequence of possible values.
    • 0:23:48The sequence of New York and Lima, as long as it is one of those two, then
    • 0:23:52I want for the results to come back.
    • 0:23:54So then you'll get back rows that have an origin of New York, rows
    • 0:23:57that have an origin of Lima, as well.
    • 0:23:59So you can begin to construct more sophisticated queries that
    • 0:24:02are able to check for whether an origin is in a list of possible values
    • 0:24:05or even if it matches a particular pattern.
    • 0:24:08That maybe you know that one of the columns looks a certain way
    • 0:24:11or is formatted according to a particular structure,
    • 0:24:13but you don't know exactly what the value is.
    • 0:24:16I could run a query like SELECT * from flights where origin--
    • 0:24:20not equals-- but where origin is like--
    • 0:24:23and then in quotation marks, I have a bit of a strange expression "%a%"
    • 0:24:29This percent stands for a wild card.
    • 0:24:32And this time the wild card is looking at the values of a particular column.
    • 0:24:35In this case, the value of the origin column, meaning the percent
    • 0:24:39stands for zero or more characters, no matter what those characters are.
    • 0:24:42We're looking for some number of characters-- maybe zero maybe more--
    • 0:24:46followed by an A, followed by some other number of characters--
    • 0:24:49maybe zero maybe more.
    • 0:24:51And ultimately what this query is going to do
    • 0:24:53is look inside of the flights table and get back to me
    • 0:24:56all of the results where there is an A the origin.
    • 0:25:00As long as there is an A in the origin column,
    • 0:25:02doesn't matter if there are characters before it or characters after it,
    • 0:25:05all of the rows that come back are going to be the rows that
    • 0:25:08just so happened to have an A in it.
    • 0:25:10So lots of ways we can begin to run SELECT queries.
    • 0:25:13And there are even additional functions you can add to your select queries
    • 0:25:16as well.
    • 0:25:17If instead of just selecting the values of columns,
    • 0:25:19I want to compute the average duration of a particular category of flights,
    • 0:25:23or I want to count how many flights that are coming out of New York, or I
    • 0:25:26want to find the longest flight that goes to London,
    • 0:25:28or the shortest flight that goes to London,
    • 0:25:30or I want to add up all of the durations for a whole bunch of flights.
    • 0:25:33Maybe there are flights that are connected to each other
    • 0:25:35and I want to add up the total travel time that might take.
    • 0:25:38SQL has a number of built-in functions that
    • 0:25:40allow us to perform these sorts of calculations, as well.
    • 0:25:44So a couple of different SQL commands we've now taken a look at.
    • 0:25:47We've taken a look at Create Table that allows us to create a new table.
    • 0:25:50INSERT that lets us add data to a table.
    • 0:25:53SELECT, which lets us take data inside of the table and retrieve it.
    • 0:25:57But, of course, often when we're dealing with databases,
    • 0:25:59the data doesn't just get added.
    • 0:26:01The data changes in some way.
    • 0:26:03So we also need some way of being able to update data after it's already
    • 0:26:06inside of our database.
    • 0:26:08There's another query for that, which is an Update command that we
    • 0:26:11can execute on our database.
    • 0:26:13And that command looks something like this.
    • 0:26:15Again displayed on multiple lines, but you could put this entirely just on one
    • 0:26:19line.
    • 0:26:19SQL doesn't really care if you add line breaks.
    • 0:26:22It just knows that when it sees a semicolon at the end of the command,
    • 0:26:24that is the end of this particular command.
    • 0:26:28But here what I've said is used the Update keyword
    • 0:26:30to say I would like to update a table.
    • 0:26:33What table would I like to update?
    • 0:26:34I'd like to update the flights table.
    • 0:26:36What would I like to do?
    • 0:26:38Well I'd like to set the duration equal to 430.
    • 0:26:42So whatever the value of duration happens to be right now,
    • 0:26:45change it to 430.
    • 0:26:47But of course I don't want to change it to 430 for every single flight.
    • 0:26:51Just as in a SELECT clause, I could say WHERE something = something or WHERE
    • 0:26:54and then some other clause to specify where
    • 0:26:57I want the rows to be selected from.
    • 0:26:59Likewise, to an Update I can say set duration equal to 430
    • 0:27:03WHERE a particular condition is true.
    • 0:27:06So here I'm going to look through the flights table,
    • 0:27:08find myself all of the flights where the origin is New York
    • 0:27:12and the destination is London.
    • 0:27:14For those rows I'm going to update the value of the duration column
    • 0:27:18setting the duration column equal to 430.
    • 0:27:20So using that syntax I can take data and update it,
    • 0:27:23changing one value to another value by pinpointing which row or rows I
    • 0:27:27would like to change.
    • 0:27:28If I only want to change one row, I might do Update flights SET duration=
    • 0:27:33something WHERE id= a particular id.
    • 0:27:36To be able to pinpoint one id and then take that row
    • 0:27:40and make some modification to it.
    • 0:27:42In addition to inserting data, selecting data, and updating data,
    • 0:27:46the last main command we'll concern ourselves with
    • 0:27:48is the ability to delete data.
    • 0:27:49The ability to take a row and say, I'd like to get rid of it.
    • 0:27:52Or take multiple rows and get rid of them.
    • 0:27:54And so a command like delete from flights, where destination= "Tokyo"
    • 0:27:59as you might imagine deletes from the flights table all of the rows that
    • 0:28:02satisfy this condition where the destination is equal to Tokyo.
    • 0:28:07So a number of different operations now that we have the ability to do.
    • 0:28:10The ability to delete from a particular table where a condition is true,
    • 0:28:13the ability to update a table based on particular conditions, the ability
    • 0:28:17to select data from a table, and insert data into a table as well.
    • 0:28:21There are a couple other clauses that can be used to add SQL queries as well.
    • 0:28:25Just to add additional functionality.
    • 0:28:27If I don't want all of the rows to come back from a particular SQL query,
    • 0:28:31I can limit the results that come back.
    • 0:28:33So normally SELECT * from flights would get me
    • 0:28:36all of the flights inside of the table.
    • 0:28:38But I could say SELECT * from flights, LIMIT 5 to just say,
    • 0:28:42I only want five results to come back from this table.
    • 0:28:46ORDER BY allows me to decide how the results are ordered inside the results
    • 0:28:50to come back.
    • 0:28:50So I can say SELECT * from flights, ORDER BY destination, or ORDER BY
    • 0:28:54duration to get all of the flights in order by how long they are.
    • 0:28:58GROUP BY allows me to group a whole bunch of rows together.
    • 0:29:02So if I wanted to group all of the flights by their origin
    • 0:29:05so I can get all of the flights leaving New York and all the flights leaving
    • 0:29:08London and so forth, I could do something like SELECT *
    • 0:29:11from flights GROUP BY origin to group flights by their origin as well.
    • 0:29:16HAVING is a constraint I can place on GROUP BY
    • 0:29:19to say that I would like to select all of the flights grouping them
    • 0:29:23by their origin, but they need to have a count of at least three.
    • 0:29:27Meaning there needs to be at least three flights that
    • 0:29:29are leaving from that particular city.
    • 0:29:32For all of these particular clauses, these
    • 0:29:34are helpful to know if you're going to be directly writing SQL.
    • 0:29:36We won't worry about them too much here, in particular
    • 0:29:39because fairly shortly we're not going to be writing SQL ourselves.
    • 0:29:42We're going to just be writing Python code
    • 0:29:44and Django is going to, under the hood, be manipulating the database
    • 0:29:47and creating the SQL commands that it is going
    • 0:29:49to run on the underlying database.
    • 0:29:52So we will see how we don't actually need to worry
    • 0:29:54about writing the specific syntax.
    • 0:29:56But Django is going to handle much of that for us.
    • 0:30:00So here now we have a flights table, a table
    • 0:30:02that keeps track of all of the flights that we have,
    • 0:30:04organizing them by their id and their origin and their destination
    • 0:30:08and their duration.
    • 0:30:09But oftentimes when we're dealing with data, especially in a larger database,
    • 0:30:13we don't just have one table of data.
    • 0:30:15We have multiple tables of data.
    • 0:30:17And those multiple tables might relate to each other in some way.
    • 0:30:21Let's take a look at an example of how that might come about.
    • 0:30:24We're going to introduce a concept that will call foreign keys,
    • 0:30:26and we'll see what that means in just a moment.
    • 0:30:29So here again is our flights table.
    • 0:30:31The flights table has four columns-- an id, origin, destination, and duration.
    • 0:30:37But of course in New York, there are multiple airports.
    • 0:30:41And so it might not make sense for me to just
    • 0:30:43label each origin or each destination just by the name of the city.
    • 0:30:46Maybe I also want to give the three letter airport
    • 0:30:49code that corresponds to the airport to which I'm referring in this case.
    • 0:30:54So how would I encode into this table, not only the origin, but also
    • 0:30:57that city's airport code?
    • 0:30:59And not only for the destination the name of the city,
    • 0:31:01but also the airport code for that airport as well?
    • 0:31:04Well I could just add more columns.
    • 0:31:06I could say something like, all right, now
    • 0:31:08we have this table that has an id, an origin, an origin code, a destination,
    • 0:31:14a destination code, and a duration.
    • 0:31:17But here now, the table is starting to get fairly wide.
    • 0:31:21There are a lot of columns here and in particular, there
    • 0:31:23is some duplicate data.
    • 0:31:27Paris is associated with this particular three letter
    • 0:31:30code, and the same thing for New York and other airports as well.
    • 0:31:33There is some messiness in the structure of this data.
    • 0:31:37Often what we'll want to do as we begin to deal
    • 0:31:39with data in larger and larger sets with more and more columns
    • 0:31:42is we'll want to normalize this data.
    • 0:31:44Separating things out into multiple different tables that
    • 0:31:47just reference one another in some way.
    • 0:31:50So instead of just having a single flights table what we might consider
    • 0:31:54doing is saying that flights are one type of object
    • 0:31:57but another type of object that I care about is an airport.
    • 0:32:01So I might just have a separate table just for airports
    • 0:32:04where this table has three columns.
    • 0:32:06A column for the ID of the airport, just some unique number
    • 0:32:09that can identify a particular airport.
    • 0:32:11One column for the three letter code for that airport,
    • 0:32:14and one letter for the city, or one column
    • 0:32:16for the city where that airport is in.
    • 0:32:19Now this is a much more straightforward, simpler representation
    • 0:32:22of all of the airports.
    • 0:32:23The question becomes what happens to my flights table?
    • 0:32:27My flights table that here had an id, an origin, destination, and duration where
    • 0:32:32the type of origin and destination were in this case just text.
    • 0:32:36Text-based data representing the name of the city
    • 0:32:39from which the flight is departing or to which the flight is arriving.
    • 0:32:43Well now that I have the separate airports
    • 0:32:45table where every row in the airports table
    • 0:32:47has its own unique id, then what I can do in this case is,
    • 0:32:51instead of storing an origin and a destination as text,
    • 0:32:54I can store what we'll call a foreign key.
    • 0:32:56A reference to a key in another table and rename these columns
    • 0:33:00to origin id and destination id.
    • 0:33:04That instead of storing text are going to store a number where
    • 0:33:08origin_id 1 means the origin of flight one
    • 0:33:11is whatever airport number 1 happens to be.
    • 0:33:16I could go to the airports table, look up which airport has an id of 1,
    • 0:33:21and that would tell me the origin of this flight.
    • 0:33:23And if I went to the airports table and looked up which airport
    • 0:33:26had an ID of 4 that would tell me the destination of this flight as well.
    • 0:33:30So by combining two different tables.
    • 0:33:32One table for representing airports, and one table for representing flights.
    • 0:33:37I'm able to connect these two different tables together
    • 0:33:40by way of a foreign key.
    • 0:33:41Some columns inside of my flights table, namely the origin id column
    • 0:33:45and the destination id column that together
    • 0:33:48allow me to reference information stored inside of another table as well.
    • 0:33:52As you imagine this sort of airlines database
    • 0:33:55growing and storing more different kinds of data,
    • 0:33:57the ability to relate tables to each other
    • 0:34:00is going to become incredibly powerful.
    • 0:34:02So one thing you might imagine is that in addition
    • 0:34:04to storing airports and storing flights, an airline probably
    • 0:34:07also needs to store information about its passengers,
    • 0:34:10like who on which flight.
    • 0:34:12So you could imagine constructing a passenger's table
    • 0:34:15that has an id column to uniquely identify every passenger, a first name
    • 0:34:19column that stores every passenger's first name,
    • 0:34:21a last name column for storing their last name,
    • 0:34:23and a flight id column for storing what flight that passenger happens to be on.
    • 0:34:28So in this case, I could say that Harry Potter is on flight number one.
    • 0:34:32I could look that up in the flights table
    • 0:34:34to find out inside the flights table, here
    • 0:34:36is where the flight is leaving from, where it's going to
    • 0:34:39and what its duration happens to be.
    • 0:34:42Now as we begin to design these tables, we
    • 0:34:44have to think about what the implications of that design
    • 0:34:47happen to be.
    • 0:34:48In the case of this passenger's table, it
    • 0:34:50does seem that there is a limitation on the table design that I have created.
    • 0:34:55Namely, if you think about it you'll see the limitation of this table design
    • 0:34:58is that any particular row can only have one flight id associated with it.
    • 0:35:04That Harry Potter has a single flight id column
    • 0:35:07that can only have one value stored inside of it.
    • 0:35:10This would seem to make it impossible to allow
    • 0:35:13for us to be able to represent a person that
    • 0:35:15could be on multiple different flights.
    • 0:35:18So this starts to get at the idea of different types of relationships
    • 0:35:22that rows in a table can have to one another.
    • 0:35:25One type of relationship is a many to one
    • 0:35:28relationship, or a one to many relationship,
    • 0:35:30where I express the idea that one flight can
    • 0:35:33be associated with many different passengers, for instance.
    • 0:35:36But we might also want is a many to many relationship,
    • 0:35:40where many different passengers can be associated with many different flights.
    • 0:35:43A passenger might have more than one flight.
    • 0:35:46A flight might have more than one passenger.
    • 0:35:48To do that we're going to need a slightly different structure
    • 0:35:52for this particular type of table.
    • 0:35:54One way we could approach it is by creating a separate table
    • 0:35:57for storing people.
    • 0:35:59I can have a people table where every person has an id, has a first name,
    • 0:36:03and has a last name same as before.
    • 0:36:05But I'm no longer storing flight information inside of the table.
    • 0:36:08I've cleaned my setup up.
    • 0:36:10I'm only storing people in this table and nothing about their flight
    • 0:36:13information.
    • 0:36:14I'll have a separate table for dealing with passengers on the flight
    • 0:36:18and mapping people to their flights.
    • 0:36:20We can think about what does that table need to look like?
    • 0:36:23Well I need some sort of table that is going to relate people to what flights
    • 0:36:27they happen to be on.
    • 0:36:28So odds are we're going to need one column that is a foreign key that
    • 0:36:31references this people table, and we'll need another column that
    • 0:36:35is a foreign key that references the flights table, such
    • 0:36:38that I can relate those two tables together.
    • 0:36:40So that table could look like this.
    • 0:36:43This now is a simplified passengers table that only has two columns.
    • 0:36:46It has a person id column and a flight id column.
    • 0:36:50The idea of this table now is it's known as an association
    • 0:36:53table, or a joined table that just associates one value from one table
    • 0:36:57with another value from another table.
    • 0:37:00This row here, one and one, means the person with an id of one
    • 0:37:04is on flight number one.
    • 0:37:06I could look up that person inside of the people table,
    • 0:37:09look up that flight inside of the flights table,
    • 0:37:11and figure out who the person is and what flight they're on.
    • 0:37:14Down here, two and four, means whoever the person with an ID of 2
    • 0:37:18is on whichever flight happens to have an ID of 4.
    • 0:37:24So this now has allowed us to be able to represent
    • 0:37:27the types of relationships we want.
    • 0:37:28We have a table for airports and a table for flights and any flight
    • 0:37:33is going to map to two different airports, one destination one origin.
    • 0:37:37And any airport might appear on multiple different flights.
    • 0:37:40It's sort of a one to many relationship.
    • 0:37:42Then over here, when it comes to passengers,
    • 0:37:45we've stored people inside of a separate table,
    • 0:37:47and then had a many to many mapping between people and flights
    • 0:37:50so that any person could be on multiple different flights.
    • 0:37:53Like here, for example, person number two is on both flights one and four.
    • 0:37:58Likewise, a flight could have multiple people.
    • 0:38:00So in this case flight number six has passengers five and six
    • 0:38:04that are on that flight as well.
    • 0:38:06We've been able to represent those relationships.
    • 0:38:09Of course a byproduct of doing this is that now our tables are a little bit
    • 0:38:13messier to look at.
    • 0:38:15Messy in the sense that it's not immediately obvious to me,
    • 0:38:17when I look at this table, what data I'm looking at.
    • 0:38:20I see these numbers, but I don't know what these numbers mean.
    • 0:38:23I've separated all these tables into different places.
    • 0:38:26Now it's a little harder for me to figure out who is on which flight.
    • 0:38:29I have to look at this data, look up people in the people table,
    • 0:38:31look up flights in the flights table, and somehow
    • 0:38:34associate all of that information back together in order
    • 0:38:37to draw any sort of conclusion.
    • 0:38:39But luckily, SQL makes it pretty easy for us
    • 0:38:42to be able to take data across multiple different tables
    • 0:38:45and join them all back together.
    • 0:38:47We can do this using a JOIN query that takes multiple tables
    • 0:38:51and joins them together.
    • 0:38:52So the syntax for a JOIN query might look something like this.
    • 0:38:56And here we'll go back to just the two-table setup where
    • 0:38:59I have flights and passengers, where every passenger is
    • 0:39:02associated with one flight.
    • 0:39:03But you could extend this and join multiple tables
    • 0:39:05to deal with our more complex example as well.
    • 0:39:08But here, I'd like to select every person's first name
    • 0:39:11and their origin and their destination.
    • 0:39:14I'm going to select that from the flights table,
    • 0:39:16but I need to join it with the passengers table.
    • 0:39:19Then I say ON to indicate how it is these two
    • 0:39:21tables are related to one another.
    • 0:39:23In this case, I'm saying the way these two tables are
    • 0:39:26related to one another is that the flight id column of the passengers
    • 0:39:30table is associated with the id column of the flights table.
    • 0:39:34The flights table has an id that uniquely identifies every flight,
    • 0:39:37and the passengers table has a flight id column
    • 0:39:40that uniquely identifies the flight that we're referring to
    • 0:39:43for this particular passenger.
    • 0:39:45And so the result I might get is a table that looks like this.
    • 0:39:48That gives me everyone's first name, but also
    • 0:39:50their origin and their destination.
    • 0:39:52Our origin and destination are going to be drawn from that table of flights
    • 0:39:58and the first name is going to be drawn from the table of passengers.
    • 0:40:01But by using a JOIN query, I've been able to take data
    • 0:40:04from two separate tables and join them both back together.
    • 0:40:08And there are a number of different types of JOIN queries that I can run.
    • 0:40:11What we saw here was just the default JOIN, which
    • 0:40:14is otherwise known as an INNER JOIN.
    • 0:40:16Effectively, an INNER JOIN will take the two tables,
    • 0:40:19it will cross compare them based on the condition that I specified
    • 0:40:22and only return back to me the results where there's a match on both sides.
    • 0:40:26Where we match a passenger's flight id with an id in the flights table.
    • 0:40:31There are various different kinds of outer
    • 0:40:33joins if I want to be OK with the idea that maybe
    • 0:40:35something on the left table that I'm joining
    • 0:40:37doesn't match with anything on the right,
    • 0:40:39or maybe something on the right table doesn't match with something
    • 0:40:42on the left.
    • 0:40:42But just know there are other types of JOIN queries that I can run as well.
    • 0:40:47Other strategies that can be helpful when dealing with SQL tables
    • 0:40:50are optimizations we can make to make queries more efficient.
    • 0:40:54One thing we can do with our tables is to create an index
    • 0:40:57on a particular table.
    • 0:40:59You can think of an index as kind of like the index in the back of a book,
    • 0:41:02for example, where if you wanted to be able to search for a topic in a text
    • 0:41:06book, you could open the textbook and just page by page look for every topic
    • 0:41:10and just try and find the topic you're looking for.
    • 0:41:12But often what you'll be able to do if the table has
    • 0:41:14an index is go to the index of the book, find the topic you're looking for,
    • 0:41:19and that will quickly give you a reference for how
    • 0:41:21to get to the right page in question.
    • 0:41:23An index on a table operates in much the same way.
    • 0:41:26It is an additional data structure that can be constructed,
    • 0:41:29and it does take time and memory to be able to construct this data structure
    • 0:41:32and to maintain it anytime you update the data inside the table.
    • 0:41:36But once it exists it makes querying on a particular column much more
    • 0:41:40efficient.
    • 0:41:41You can very quickly look something up in the index
    • 0:41:44and find the corresponding rows that go along with it.
    • 0:41:47So here we can have a command like create an index that we're
    • 0:41:50going to call name index on the passengers table,
    • 0:41:53and in particular on the last name column.
    • 0:41:57I expect that as I query this table, I'm pretty frequently
    • 0:42:00going to be looking up passengers by their last name.
    • 0:42:03So I would like to create an index on that table
    • 0:42:06to be able to more efficiently search for a passenger
    • 0:42:09based on their last name as well.
    • 0:42:12So that's just a general overview of what SQL syntax is all about.
    • 0:42:15A syntax that we can use to be able to create data tables inside of which
    • 0:42:19are storing rows of data where every row consists of some number of columns,
    • 0:42:24and every column has a type.
    • 0:42:25We have the ability to create tables, add data to them,
    • 0:42:28update, delete, and get data out of those tables as well.
    • 0:42:31But as we begin to introduce these new technologies,
    • 0:42:34there are always risks and potential threats
    • 0:42:36that are associated with those technologies as well.
    • 0:42:39In SQL, the key one to be aware of is what's known as a SQL injection attack.
    • 0:42:44A security vulnerability that can happen if you're not
    • 0:42:46careful about how it is you actually execute your SQL commands.
    • 0:42:50So where might this come about?
    • 0:42:52You might imagine for instance, that if a database has some number of users,
    • 0:42:56you might be storing those users inside of a database.
    • 0:42:59For instance, in a users table where there's a user name
    • 0:43:02column and a password column.
    • 0:43:03Though, in practice you probably wouldn't
    • 0:43:04want to store passwords and clear text, let's
    • 0:43:06imagine here for example that you are storing usernames and passwords inside
    • 0:43:10of a table.
    • 0:43:12We have a log in form for a website that looks
    • 0:43:14like this, where you get to type in your username and your password.
    • 0:43:17So if someone types in their username and password,
    • 0:43:19what might happen is that the web application might look SELECT * FROM
    • 0:43:23users, where username= this particular user name highlighted here.
    • 0:43:28We're just going to substitute the user name right there.
    • 0:43:30And password= and we'll substitute the password over there.
    • 0:43:34So if someone tries to log into our site,
    • 0:43:36like Harry logs in with a password of 12345, what we might do
    • 0:43:40is run this SELECT query.
    • 0:43:42Say SELECT * FROM users where username is "Harry"
    • 0:43:45and where the password is 12345.
    • 0:43:48Our logic might be if we get results back,
    • 0:43:50then that means there is a user whose username is Harry and password is 12345
    • 0:43:55and we can go ahead and sign that user in.
    • 0:43:59But imagine now what might happen if instead,
    • 0:44:02the user who typed in a username of hacker"--.
    • 0:44:07Seems like a bit of a strange username to type in.
    • 0:44:10It doesn't matter what they put in their password here now.
    • 0:44:12The result might be that what they would plug into the username is where
    • 0:44:16username="hacker" and then the - -.
    • 0:44:19It turns out --
    • 0:44:20in SQL stands for a comment in SQL.
    • 0:44:22It just means ignore everything that comes after it in the same way that
    • 0:44:25in Python you can use the hashtag symbol to mean the rest of this line is
    • 0:44:29a comment, and the compiler-- whoever is running the program--
    • 0:44:31should just ignore it.
    • 0:44:33So everything after the - - kind of gets ignored.
    • 0:44:36We've effectively been able to bypass the password check.
    • 0:44:39Someone could bypass a password check and log into an account
    • 0:44:43even if they were unauthorized to do so.
    • 0:44:45So here is a vulnerability within the SQL syntax.
    • 0:44:48If we're not careful about when we're running SQL syntax,
    • 0:44:51we could be running untrusted SQL commands
    • 0:44:54that some hacker or some adversary has been able to plug-in to our program.
    • 0:44:58So how do we solve this sort of problem?
    • 0:45:00One strategy is to escape these characters.
    • 0:45:03Escaping just meaning add some back slashes
    • 0:45:06that just makes sure that SQL knows to treat these as a literal quotation
    • 0:45:09mark and a literal dash, and not special SQL syntax of any sort.
    • 0:45:13Another strategy is to use an abstraction layer on top of SQL
    • 0:45:17so that we don't have to write the SQL queries at all.
    • 0:45:19That's in fact what we're about to do as we transition to the world of Django
    • 0:45:23to take a look at how when we begin to use a web framework like Django,
    • 0:45:27we now have the ability to not worry about the nuances of the syntax of SQL
    • 0:45:31and just deal a little more high level with what our models are.
    • 0:45:34What the types of objects are that we're dealing with and interacting
    • 0:45:37with inside of this application.
    • 0:45:40One other concern worth noting about with regards to SQL
    • 0:45:43is the possibility of race conditions.
    • 0:45:45A race condition is something that might happen
    • 0:45:47anytime you have multiple events that are happening in parallel threads,
    • 0:45:51so to speak.
    • 0:45:52That you have one thing happening and another thing happening simultaneously.
    • 0:45:56You might imagine that in the case of social media sites
    • 0:45:59where you can like a post, like an image on Instagram or a tweet on Twitter,
    • 0:46:02for example.
    • 0:46:03What would happen if two people tried to like the same post at the same time?
    • 0:46:08If we're not careful about how we run those particular SQL queries
    • 0:46:11there's a potential for us to be able to get race condition problems,
    • 0:46:16where we end up trying to query for the number of likes that a post has
    • 0:46:20and then another person tries to do the same thing
    • 0:46:22and there are conflicts when we try and update it
    • 0:46:24where the result might not be what we would expect it to be.
    • 0:46:27There are a number of unexpected results that
    • 0:46:29can happen when we deal with problems related to race conditions
    • 0:46:32where multiple things are happening simultaneously.
    • 0:46:35How do we solve those problems?
    • 0:46:36Well, one strategy is to sort of place a lock on the database.
    • 0:46:40To say, while I'm working on this database nobody else
    • 0:46:43can touch this data.
    • 0:46:44Let me finish this transaction, so to speak.
    • 0:46:46Finish working on this particular transaction and making all the changes
    • 0:46:49I need to make to the database.
    • 0:46:51Only after I'm done I can release the lock and let someone else go ahead
    • 0:46:55and modify the database as well.
    • 0:46:57So a number of concerns to be aware of as we
    • 0:47:00begin dealing in this world of SQL and trying to work with databases.
    • 0:47:05So now that we've taken a look at the syntax of SQL,
    • 0:47:07understanding how these tables work, how they're structured,
    • 0:47:10and what it is that we can add to those tables,
    • 0:47:13let's go ahead and turn our attention in particular to Django
    • 0:47:16models, which are a way of representing data inside of a Django application.
    • 0:47:20Because where Django is really going to get powerful in designing our web
    • 0:47:23applications is the ability to represent data in terms of these models.
    • 0:47:27So we're going to go ahead and try to create a web application that
    • 0:47:30is going to represent what an airline might want to store inside
    • 0:47:34of its own web application.
    • 0:47:37All right.
    • 0:47:37So the first thing I want to do is create a Django project.
    • 0:47:40So I'll go ahead and type Django-admin START PROJECT
    • 0:47:43and the name of my project will just be called "airline."
    • 0:47:46I'm creating a project for an airline's website for example.
    • 0:47:49I'll go ahead and go into the airline directory,
    • 0:47:51open that up in my code editor.
    • 0:47:53Before I actually begin editing any code,
    • 0:47:55remember that every Django project needs to have one or more apps within it.
    • 0:48:00So the first app that I'll create for this airline
    • 0:48:02is an app for keeping track of flights.
    • 0:48:05So keeping track of flight related information,
    • 0:48:08like origins and destinations and durations
    • 0:48:10and what passengers are on those flights.
    • 0:48:13When I create a new app, first thing I'll need to do
    • 0:48:16is go into settings.py inside of airline,
    • 0:48:19and go ahead and add this app as an installed app.
    • 0:48:22So "flights" is now an app that I have installed.
    • 0:48:26Then what I'll want to do is say, go ahead and go into urls.py, which is,
    • 0:48:32again, that table of contents for all the
    • 0:48:34URLs I can get to for this particular web application.
    • 0:48:36I'll IMPORT include, because I want to do is when someone visits the path
    • 0:48:41of flights/ something, I want to take them to flights.urls,
    • 0:48:46mapping them to the urls.py file that will be inside of my "flights"
    • 0:48:51application.
    • 0:48:52Of course, now I need a urls.py file inside of my "flights" application.
    • 0:48:56So I go into "flights" and create a new file that I'll call urls.py.
    • 0:49:03We can do from FROM.django.urls IMPORT path FROM .IMPORT views.
    • 0:49:09And then my URL patterns are going to go inside of this list right here.
    • 0:49:14But before I begin dealing with actual URLs,
    • 0:49:16the first thing I'm going to want to do is create some models.
    • 0:49:19Models are going to be a way of creating a Python class that
    • 0:49:22is going to represent data that I want Django to store inside of a database.
    • 0:49:27So when I create a model, Django is going
    • 0:49:28to figure out what SQL syntax it needs to use it to A-- create that table
    • 0:49:32but then B-- manipulate that table.
    • 0:49:34Selecting and updating and inserting anytime I make changes to those models.
    • 0:49:39So here what I can do is inside of every app that gets created--
    • 0:49:42in this case called "flights"-- there is a models.py file.
    • 0:49:46We haven't looked at this before, but this
    • 0:49:48is going to be the place where we get to define what models
    • 0:49:51are going to exist for our application.
    • 0:49:53Every model is going to be a Python class.
    • 0:49:56You can think of this as having one model for each of the main tables
    • 0:49:59we care about storing information about.
    • 0:50:02So let me define a new class called "flight"
    • 0:50:04that is going to inherit from models dot model some creating a new class
    • 0:50:08called "flight" that is going to be a model.
    • 0:50:11Then I need to provide inside of this class all of the parameters
    • 0:50:15that a flight has.
    • 0:50:16What properties does a flight have that I might want to keep track of?
    • 0:50:20Well, a flight has an origin.
    • 0:50:22And the origin is going to be a models.charfield.
    • 0:50:25This is all documented on Django's website
    • 0:50:27in terms of the various different types of fields that exist that I
    • 0:50:30can include inside of a Django model.
    • 0:50:33Where here I'm saying here is a character field
    • 0:50:35whose max length is going to be 64.
    • 0:50:39I assume that most city names are not going to go longer than 64 characters.
    • 0:50:42That seems like a reasonable maximum length for the origin of the flight,
    • 0:50:46for example.
    • 0:50:47Every flight will also have a destination,
    • 0:50:49which will be a character field whose max length is also 64.
    • 0:50:53Every flight will have a duration, which will just be an integer field.
    • 0:50:58So now this is my very first Django model.
    • 0:51:01It is a class called "flight" where I've defined all of the properties
    • 0:51:05that a flight has, and then using Django syntax to find
    • 0:51:08what type they should have as well.
    • 0:51:09Every flight has an origin, has a destination, and has a duration.
    • 0:51:15But of course nothing here is actually modified
    • 0:51:17the database the Django is using in order to store information
    • 0:51:21about my web application.
    • 0:51:22And we can see if we, in fact go back to airline,
    • 0:51:24and I type LS, what you see here is that there isn't yet a database that exists.
    • 0:51:30I just have an airline directory, a flights directory,
    • 0:51:33and a manage.py file.
    • 0:51:35So what I'd like to do is somehow tell Django
    • 0:51:38that you should update the database to include information about the models
    • 0:51:42that I have just created.
    • 0:51:44This is a process that we refer to in Django and more generally as
    • 0:51:47migrations.
    • 0:51:48I create a migration to say, here are some changes
    • 0:51:51that I would like to apply to the database.
    • 0:51:54Then I migrate them to tell Django to take those changes
    • 0:51:57and actually apply them to the database.
    • 0:51:59So it's a 2-step process.
    • 0:52:01One is creating the migration, the instructions for how to actually go
    • 0:52:05about manipulating the database.
    • 0:52:06And then one to take that migration step of saying now take those instructions
    • 0:52:10and actually apply them to the underlying database.
    • 0:52:14We can make the migrations via command.
    • 0:52:17Again we'll use the manage.py script that has a number of different commands
    • 0:52:20that allow us to control various parts of the application.
    • 0:52:23I'll use python manage .py and then MAKE migrations.
    • 0:52:29Now what we see is we've created a migration inside of 0001_initial.py
    • 0:52:35where in this migration it's created a model called "flight."
    • 0:52:40So if I go ahead and look at the migrations directory,
    • 0:52:43I see this file has been created for me.
    • 0:52:46I didn't have to create it myself.
    • 0:52:48This file has instructions to Django for how
    • 0:52:52to manipulate the database to reflect the changes I have made to the model.
    • 0:52:56Here is an instruction to Django to create a new model called
    • 0:53:00"flight" that has these particular fields inside of it.
    • 0:53:04It's basing this off of the changes that I made to models.py.
    • 0:53:07The model that I added is now reflected in this migration.
    • 0:53:12Now if I want to apply the migration, actually apply it to Django's database,
    • 0:53:16I can run python manage.py MIGRATE to go ahead and apply these migrations.
    • 0:53:22There are a bunch of default migrations that get applied as well,
    • 0:53:25but notice that one of the migrations that gets applied is this one here.
    • 0:53:28Applying flights.0001_initial to say let's go ahead and apply that
    • 0:53:33migration, create that table that is going to represent flights.
    • 0:53:38If I type LS now, you'll see that now I have a db.sqlite3 file.
    • 0:53:43A SQLite database that is going to contain a table that
    • 0:53:47is going to store all of my flights.
    • 0:53:50And so how can I actually begin to manipulate this data?
    • 0:53:52How can I interact with these sorts of models?
    • 0:53:56I could use direct SQL syntax by opening up this database file
    • 0:53:59and running commands, but Django provides some nice abstraction layers
    • 0:54:03on top of it so that I don't actually need to execute those commands myself.
    • 0:54:07I can begin to work more generally with Python classes and variables and things
    • 0:54:11that I'm used to inside the Python language.
    • 0:54:14So I can enter Django's shell where I can just run Python commands
    • 0:54:18by running python manage.py shell.
    • 0:54:22What this does is open up a shell or a console
    • 0:54:24where I can begin to write Python commands that
    • 0:54:27get executed on this web application.
    • 0:54:30The first thing I'd like to do is from flights.models
    • 0:54:34let me just import flight.
    • 0:54:37So "flights" is the name of my app.
    • 0:54:38"Models" is the name of that file.
    • 0:54:40I'm importing the flight class from that models file that I've just created.
    • 0:54:44Now what I can do is I can create a new flight.
    • 0:54:48I can say something like f= a flight whose origin is "New York"
    • 0:54:54and whose destination is "London" and whose duration= 415 minutes.
    • 0:55:00And then I can say f.save to save that new flight that I have created.
    • 0:55:05This syntax-- I'll go ahead and make it a little bit bigger so you can see it
    • 0:55:08a little easier--
    • 0:55:09is my way of inserting data into this table.
    • 0:55:13I don't need to use an INSERT query in SQL.
    • 0:55:15I just have to write a Python command, and Django knows that when
    • 0:55:18I create a new flight and save it, that it should run an instant command
    • 0:55:22on the underlying SQL tables.
    • 0:55:24Where here I've created a new flight with this particular origin
    • 0:55:27and destination and duration and I've gone ahead and saved that flight as
    • 0:55:32well.
    • 0:55:33If I want to query that flight, get information about that flight,
    • 0:55:36I can say something like, flight.objects.all
    • 0:55:40is the equivalent of a Select-All.
    • 0:55:42Get me all of the flights that exist inside of my database.
    • 0:55:46Here I see I get back a query set, which is just a set of results.
    • 0:55:50Here I have one flight that came back, flight object 1.
    • 0:55:54So a flight has been created for me with ID 1.
    • 0:55:56Now "flight object 1" probably not all that helpful of a name.
    • 0:55:59It'd be nicer if this model had a cleaner
    • 0:56:02way of seeing the name of a particular flight, for example.
    • 0:56:06And it turns out we can do that.
    • 0:56:08Any model-- I'll go back to the code inside of models.py--
    • 0:56:11any model can implement double underscore str
    • 0:56:15function, which returns a string representation
    • 0:56:19of that particular object.
    • 0:56:21This applies not just to Django models but to Python classes more generally.
    • 0:56:25That if this function returns a string representation of the object,
    • 0:56:28let's go ahead and return a formatted string that is self.id
    • 0:56:33I'll say self.origin to self.destination.
    • 0:56:37So here what I've said is that the string representation of any flight
    • 0:56:41is going to be a string that gives its ID and then says origin to destination.
    • 0:56:46Just a nice clean name that is going to represent this particular flight.
    • 0:56:50So now if I go back to the shell by running python manage.py shell,
    • 0:56:57I can say from flights.models import Flight.
    • 0:57:00I can say, all right, let's let a variable called
    • 0:57:02flights be equal to flight.objects.all.
    • 0:57:06And now flights is going to be this flight, flight 1, New York to London.
    • 0:57:11It now has a much nicer, string representation
    • 0:57:13of the name, which just makes it a little bit easier to interact with.
    • 0:57:17If I wanted to get just that one flight, I can say flight equals flights.first.
    • 0:57:23Flights is a query set.
    • 0:57:24First gets me that first flight.
    • 0:57:26And so now, I have this flight from New York to London.
    • 0:57:30And just as in any Python object, I can begin
    • 0:57:32to access properties of that object.
    • 0:57:34I can say, all right, flight, what is your ID?
    • 0:57:36Flight, what is your origin?
    • 0:57:38Flight, what is your destination?
    • 0:57:40Flight, what is your duration?
    • 0:57:41And I can access, as values, all of the properties of this flight
    • 0:57:45that I ultimately care about.
    • 0:57:46And if I want to delete the flight, I can say something like flight.delete.
    • 0:57:51Now, ultimately though, this is not the model
    • 0:57:54that I actually want to represent my flight.
    • 0:57:56Because here again, I'm using a character field, a char field,
    • 0:58:00for things like origin and destination.
    • 0:58:02When in reality, I'd probably like to use something like another table
    • 0:58:06for representing airports, and then some relationship
    • 0:58:09between every flight and an airport.
    • 0:58:12So let's go ahead and try and implement that idea now,
    • 0:58:14that I can go back into models.py and create a new class.
    • 0:58:18I'll create a class called airport.
    • 0:58:21That is also a model.
    • 0:58:23And I'd like for this airport class to have
    • 0:58:25a code, which is a character field with a max length of 3,
    • 0:58:30for the airports code.
    • 0:58:32As well as a city, which would be a character field
    • 0:58:34with a max length of 64.
    • 0:58:37And let's also give this airport a string representation.
    • 0:58:41We'll say that the string representation of an airport
    • 0:58:43will just be the city of the airport, and then in parentheses,
    • 0:58:48the code of the airport.
    • 0:58:49So it will be something like New York, and then in parentheses
    • 0:58:51JFK to represent a particular airport.
    • 0:58:56And now, our flight model needs to change a little bit.
    • 0:58:59No longer will origin and destination be character fields
    • 0:59:02that are just storing text.
    • 0:59:04But instead, origin is going to be a foreign key.
    • 0:59:08A foreign key that references another table, like the airport table.
    • 0:59:14And then, I can provide some additional arguments.
    • 0:59:16So this alone would be enough.
    • 0:59:17But I can add some additional arguments like on delete equals models.cascade.
    • 0:59:23So what does this mean?
    • 0:59:24Well, when I have tables that are related to each other,
    • 0:59:27SQL needs some way of knowing what should
    • 0:59:29happen if you ever delete something.
    • 0:59:30If I have a flight from JFK to London, and later in time
    • 0:59:35decide to delete JFK airport from my database,
    • 0:59:38what should happen to that flight?
    • 0:59:40What happens to flights when the thing that it is referencing gets deleted?
    • 0:59:44What models.cascade means is if I were to ever delete
    • 0:59:47an airport from the airports table, it's going to also delete
    • 0:59:51any of the corresponding flights.
    • 0:59:53And there are other on delete parameters you
    • 0:59:54can set for saying like, don't even let me delete an airport
    • 0:59:57if there are flights that are leaving from
    • 0:59:59or going to that airport, that's called models.protect.
    • 1:00:02But there are other ways of implementing similar types of constraints.
    • 1:00:06And the other argument that I'm going to provide
    • 1:00:08is what's called a related name.
    • 1:00:11And a related name, as we'll see in just a moment,
    • 1:00:13is going to be a way of me accessing a relationship in the reverse order.
    • 1:00:18That from a flight, I can take a flight and say .origin to get
    • 1:00:22the flight's origin in airport.
    • 1:00:23But the other question I might want to ask is in the reverse order.
    • 1:00:26If I have an airport, how do I get all of the flights that
    • 1:00:30have that airport as an origin?
    • 1:00:32And so here, if I give a related name to this foreign key,
    • 1:00:35Django will automatically set up the relationship
    • 1:00:38going in that opposite direction.
    • 1:00:40And so here, well, if we have an airport,
    • 1:00:42and I want to know all of the flights that have that airport as their origin,
    • 1:00:45the reasonable name for a related name here is something like departures.
    • 1:00:50So if I have an airport, I can access all
    • 1:00:53of the departures, which gets me all of the flights that
    • 1:00:55are leaving from that airport.
    • 1:00:58And I'll likewise do the same thing here for destination.
    • 1:01:00Instead of a character field it's going to be a foreign key.
    • 1:01:04It's going to reference airport.
    • 1:01:06When we delete it, we'll go ahead and cascade it.
    • 1:01:08And the related name will be arrivals.
    • 1:01:11Because if I have an airport, I might want
    • 1:01:13to access all of the arrivals, all of the flights
    • 1:01:15that correspond to flights that are arriving
    • 1:01:18at that particular destination.
    • 1:01:21And so now, I've done two things.
    • 1:01:22I've added a new class called airport, and I've
    • 1:01:25modified my existing flight model.
    • 1:01:28So this has changed in my Python code, but it hasn't yet
    • 1:01:31changed in my database.
    • 1:01:32So in order to make the change in the database, again, it's a 2-step process.
    • 1:01:36Step one, python manage.py, make migrations,
    • 1:01:40to say look for any new changes that have been made to models.py,
    • 1:01:45and go ahead and create a migration instruction
    • 1:01:48for how to make those changes to the database.
    • 1:01:50And here, we see that we've created a new migration file.
    • 1:01:55And this migration is going to create a model called airport.
    • 1:01:58And it's also going to alter the destination field
    • 1:02:01and alter the origin field on my flight model.
    • 1:02:04Because as we know, we've changed destination and origin
    • 1:02:07to no longer be character fields, but to instead
    • 1:02:09be references to a particular airport.
    • 1:02:11So that's something that's going to need to change in the database.
    • 1:02:15And to make that change, I can run something like python manage.py
    • 1:02:19migrate to go ahead and apply those changes.
    • 1:02:22We've now applied this migration that we just created,
    • 1:02:25and our database is now up to date.
    • 1:02:28So what can we do?
    • 1:02:30Well, now I can go ahead and go back into the shell.
    • 1:02:33And I'll just go ahead and import from flights.models import
    • 1:02:36star, import everything.
    • 1:02:38And I can now create an airport.
    • 1:02:40I can say something like JFK equals an airport whose code is JFK
    • 1:02:46and whose city is New York, for example.
    • 1:02:49And then save that.
    • 1:02:50I can create a London one.
    • 1:02:52So LHR is an airport whose code is LHR and whose city is London.
    • 1:02:57And I can save that.
    • 1:02:59You could create more.
    • 1:02:59I could say CDG equals an airport whose code is CDG and city is Paris.
    • 1:03:06And maybe we'll do one more.
    • 1:03:07We'll say NRT is the airport whose code is NRT and whose city is Tokyo,
    • 1:03:13for example.
    • 1:03:14So I've created and saved four airports that get added to my airport table.
    • 1:03:18And now, I can add a flight.
    • 1:03:20F equals flight whose origin equals JFK whose destination equals
    • 1:03:26London Heathrow and whose duration equals 415 minutes.
    • 1:03:31And I'll go ahead and save that as well.
    • 1:03:35So I've now created four airports.
    • 1:03:36I've created a flight and saved it.
    • 1:03:38If I type F just for my flight, I see that, all right,
    • 1:03:42this is a flight from New York to London.
    • 1:03:45But I can also say what is f.origin, and to write f.origin,
    • 1:03:50that is now an airport object.
    • 1:03:52It's JFK, in particular.
    • 1:03:53And I can do f.origin.city to get the city of the origin, which is New York.
    • 1:03:58f.origin.code to get the code of that airport, which is JFK.
    • 1:04:02And if I start with an origin, something like JFK or London Heathrow,
    • 1:04:06I can say LHR.arrivals.all to get all of the arrivals, all
    • 1:04:13of the flights arriving in London Heathrow.
    • 1:04:15And it looks like there's just one of them, which
    • 1:04:17is this flight that I've just created from New York that
    • 1:04:20is going to London as well.
    • 1:04:23And so this now gives us the ability to manipulate SQL
    • 1:04:26just by using these Python models.
    • 1:04:28And I now have Python classes that represent
    • 1:04:30all of these various different types of data.
    • 1:04:32And now, instead of running SQL queries like select star
    • 1:04:35from flights or from airports, I can just
    • 1:04:37interact with these classes and these properties on the classes,
    • 1:04:41and Django takes care of the process for me
    • 1:04:43of figuring out what the underlying SQL queries should be,
    • 1:04:46executing those queries, and just giving those results back to me.
    • 1:04:50And we can begin now to design a web application around this idea.
    • 1:04:54That I can go into urls.py, and lets add a url pattern that says,
    • 1:04:58the default route.
    • 1:04:59We'll go ahead and load the index view.
    • 1:05:02Give it a name of index.
    • 1:05:03Same as similar things we've seen from last time.
    • 1:05:06And now, what should we do in the index view?
    • 1:05:09Well, the index view, let's go ahead and say, what I would like to do
    • 1:05:13is just display a list of all the flights.
    • 1:05:16So I might from.models import flight and airport.
    • 1:05:22Or maybe I just need flight.
    • 1:05:23I just want a list of all the flights.
    • 1:05:25So I'm going to import flight from all of my models.
    • 1:05:28And now, what I'd like to do is return--
    • 1:05:30let's go ahead and render a template called flight/index.html,
    • 1:05:35and give index.html access to a variable called flights.
    • 1:05:39And what is that variable going to be equal to?
    • 1:05:42It's going to be equal to flight.objects.all
    • 1:05:45to get me all of the flights that I would like to put right here.
    • 1:05:50All right, so what can I do from now?
    • 1:05:52Now, what I need to do is actually create those individual templates.
    • 1:05:55So inside of flights, I'll create a new folder called templates.
    • 1:06:00Inside of which I'll create a new folder called flights.
    • 1:06:03Inside of which I'll go ahead and create a layout.html,
    • 1:06:06much as we've done before, where that layout
    • 1:06:08is going to contain the basic structure of our HTML page.
    • 1:06:12So a head section whose title is flights, and a body section that
    • 1:06:18is going to have a block body, and the end of the block.
    • 1:06:24Much as before, this is the default layout for this particular page.
    • 1:06:28And then, I'll add a new template called index.html that is going to extend
    • 1:06:33flight/layout.html.
    • 1:06:36And then, inside the body of the page, I'm
    • 1:06:40going to display an H1 that just says flights.
    • 1:06:43And let's now create an unordered list where I can now
    • 1:06:46loop over for flight in flights.
    • 1:06:50endfor to end the loop.
    • 1:06:51But inside the loop, let me create a list item
    • 1:06:54where I just print out a flight--
    • 1:06:57maybe I'll print out the flight and then flight.id
    • 1:07:01to print out flight 1, flight 2, flight 3.
    • 1:07:03And then, I'll print flight.origin to flight.destination.
    • 1:07:10So what I've done here is create a template
    • 1:07:13that I'm going to give access to a variable called flights, where flights
    • 1:07:16is going to be a variable that represents all of the flights
    • 1:07:20that I queried by running flight.objects.all, that is my way
    • 1:07:24using Django's API, using the functions that it has given me access to,
    • 1:07:27to say, take the flight and get all of the flights that are stored inside
    • 1:07:32of Django's database.
    • 1:07:33Then here in the template, I'm looping over each one of those flights.
    • 1:07:36For each one, printing out a list item where
    • 1:07:39I can access properties of that flight.
    • 1:07:41Say flight this ID from origin to a particular destination.
    • 1:07:48So now, I'll go ahead and go into my terminal.
    • 1:07:51Run python manage.py run server, which again,
    • 1:07:54is how we run a Django web application.
    • 1:07:56And now, if I go to that URL, flash flights this time,
    • 1:07:59because that's the URL.
    • 1:08:01What I see is exactly what I'd expect to see.
    • 1:08:03An unordered list that just so happens to have flight one New York to London
    • 1:08:08displayed there.
    • 1:08:09It is taking data from my database, and now displaying it inside
    • 1:08:13of this template.
    • 1:08:14And if I were to add new flights, it would also update on this page as well.
    • 1:08:18So if I go ahead and go back, go into the shell, python manage.py shell.
    • 1:08:22I'll go from flights.models import star.
    • 1:08:26Let's go ahead and--
    • 1:08:28well, all right, let's add a flight from Shanghai to Paris, for example.
    • 1:08:31Well, how do I get the airports for Shanghai and Paris?
    • 1:08:34Well, it turns out that if I want to get Shanghai,
    • 1:08:37I can say Shanghai equals airport.objects.
    • 1:08:42and then I can say--
    • 1:08:43if I do airport.objects.all, that gets me all of the airports, for example.
    • 1:08:48Oh, and it seems I don't actually have a Shanghai one,
    • 1:08:51but I can add one if I wanted to.
    • 1:08:53But if I do airport.objects.all, that, again, gives me all of them.
    • 1:08:57But if I want to filter my airports list, not get all of the airports
    • 1:09:00but just get some of them, I can say airport.objects.filter, and I can say,
    • 1:09:06get me all the airports where the city is New York, for example.
    • 1:09:12And that is going to go ahead and give me a query set that only
    • 1:09:15contains the results that I care about.
    • 1:09:17So again, airport.objects.filter lets me constrain the results that come back.
    • 1:09:22Not get me all of the airports, but only get me
    • 1:09:24airports whose city is New York, for example.
    • 1:09:27And it is only giving back one, so I could say .filter city equals New
    • 1:09:30York.first, to say, take that query set, and just get me the first and only
    • 1:09:35thing in that query set.
    • 1:09:36And that gives me airport New York.
    • 1:09:38A simplified way of doing the same thing.
    • 1:09:40If you know you're only going to get one result back, is I
    • 1:09:43can say something like airport.objects.get,
    • 1:09:45which will only get one result if it knows that there's only going to be
    • 1:09:50one airport with the city of New York.
    • 1:09:52That too will return to me New York JFK airport.
    • 1:09:56But it will throw an error if ever there's more than one,
    • 1:09:59or if there's none, for example.
    • 1:10:00So we'll go in and save that inside of JFK,
    • 1:10:02and we'll go ahead and create a flight that is going
    • 1:10:05from New York to Paris, for example.
    • 1:10:07I can do CDG equals airport.objects.get.
    • 1:10:11City equals Paris.
    • 1:10:13And now, I have this variable CDG, which represents the airport Paris.
    • 1:10:17And if I want to create a new flight that goes from New York to Paris,
    • 1:10:21I can say F is going to be a flight whose
    • 1:10:23origin is JFK whose destination equals CDG and whose duration equals 435.
    • 1:10:30And I can save that flight as well.
    • 1:10:33And so I've added a new flight.
    • 1:10:34And so now, if I run the server, python manage.py,
    • 1:10:38run server, refresh the page, I now see that I have two flights.
    • 1:10:43One flight that's going from New York to London, one flight that's
    • 1:10:46going from New York to Paris.
    • 1:10:48But of course, it's going to be pretty annoying if every time I
    • 1:10:51want to update the data, adding new data, manipulating the data,
    • 1:10:54I need to go into the shell in order to run
    • 1:10:56direct commands that are able to add new flights,
    • 1:10:58add new airport, so on and so forth.
    • 1:11:00What I'd really like to be able to do is just very simply
    • 1:11:03to add it via a web interface.
    • 1:11:05Via the web, be able to say, all right, let
    • 1:11:07me add a new flight that goes from location 1 to location 2.
    • 1:11:11And it's possible, using the information we know now,
    • 1:11:13to build a web page that does just this.
    • 1:11:16But Django is built on this idea that it doesn't want you, the programmer,
    • 1:11:20to have to repeat work that other people have already done.
    • 1:11:23And this process of trying to define models and very quickly be
    • 1:11:27able to create and edit and manipulate models
    • 1:11:29is so common that Django has already built for us an entire app that is just
    • 1:11:34designed for the manipulation of these models,
    • 1:11:36and it's known as the Django admin app.
    • 1:11:40And this is an app that we've seen traces of already,
    • 1:11:42that if we remember that urls.py file from inside of our application.
    • 1:11:48We saw that we added a path for our own app,
    • 1:11:51but there was already a path given to us by default, /admin,
    • 1:11:55that takes us to the admin app as well.
    • 1:11:58And so in order to use the admin app, we need
    • 1:12:01to create an administrative account inside of our Django web application.
    • 1:12:05And the way to do that is via the command line.
    • 1:12:08I can run python manage.py create super user.
    • 1:12:12It's going to ask me for my name.
    • 1:12:14I'll go in and type in my user name, my email address,
    • 1:12:18and it's also going to ask for a password.
    • 1:12:19I can just make up a password that I would like to use.
    • 1:12:22Retype it in just to confirm it.
    • 1:12:24And now, Django has created a super user account for me in this web application
    • 1:12:30so that I, using these credentials, have the ability
    • 1:12:32to visit the web interface for the admin app
    • 1:12:35and actually manipulate some of these underlying models.
    • 1:12:39So in order to do this, the first thing I need to do is take my models
    • 1:12:43and add those models to the admin app.
    • 1:12:45So inside of models.py, I have a class called airport and a class
    • 1:12:49called flight.
    • 1:12:51And if we look at the files I have, there's
    • 1:12:52another file we haven't really looked at yet called admin.py inside of my app.
    • 1:12:57And inside of admin.py, I'll first from my models import flight and airport.
    • 1:13:03And now, I'm going to say, admin.site.register airport.
    • 1:13:08And admin.site.register flight.
    • 1:13:13And what this is going to do is it is going
    • 1:13:15to tell Django's admin app that I would like to use the admin
    • 1:13:18app to be able to manipulate airports and to be able to manipulate flights
    • 1:13:23as well.
    • 1:13:25So let's take a look at this admin app and see how it actually works.
    • 1:13:28I can run python manage.py run server.
    • 1:13:33That will start up the web server.
    • 1:13:34I'll now visit this URL.
    • 1:13:36Instead of going to /flights I'll go /admin.
    • 1:13:39And this opens up this Django administration app
    • 1:13:43that is not written by me.
    • 1:13:44Django has written this, and it's asking me to log in.
    • 1:13:47I'll go ahead and log in using those credentials I used a moment ago, typing
    • 1:13:50in my username and password.
    • 1:13:52And what I get here is Django's site administration interface.
    • 1:13:56Built for me by Django, where I didn't need to design this at all.
    • 1:14:00But importantly, if we noticed down here,
    • 1:14:02I now have the ability to add and manipulate
    • 1:14:04airports and flights via this web interface, this Django
    • 1:14:08administrative interface.
    • 1:14:09So now, using this interface, I have the ability
    • 1:14:11to manipulate the underlying database.
    • 1:14:13To manipulate my models to add and modify data that already exists.
    • 1:14:18So if I click on airports, for example, I see here,
    • 1:14:21here are all of the airports that I've already added to my database.
    • 1:14:24Tokyo, Paris, London, and New York.
    • 1:14:26And I can add a new one.
    • 1:14:27I can say, let's go ahead and add PVG which is Shanghai.
    • 1:14:31And I can either save it, save and continue editing, save and add another.
    • 1:14:34I'm going to add a couple, so I'll go ahead and save and add another.
    • 1:14:37Let's go ahead and add Istanbul airport as well.
    • 1:14:40Let's add Moscow as an airport two, and maybe one more, we'll add Lima as well.
    • 1:14:47And I'll just go ahead and click save.
    • 1:14:49And now, I've added a whole bunch of airports all via this web interface.
    • 1:14:52Django was originally created for news organization that
    • 1:14:55very quickly wanted to be able to post articles and post
    • 1:14:58new posts on their website.
    • 1:14:59And it made it very easy via an interface like this
    • 1:15:02to very quickly just say, here, add a new article
    • 1:15:04and here's the content of the article, to be able to display on a page.
    • 1:15:08And now, we've been able to very quickly add new airports to our website
    • 1:15:11as well.
    • 1:15:12And so if we want to add flights, well, we can go ahead and go back home.
    • 1:15:18Click on flights.
    • 1:15:19I see that I already have two flights inside of my database.
    • 1:15:21I have New York to London and New York to Paris.
    • 1:15:24I'll add a new one.
    • 1:15:25It's letting me choose an origin, destination, and duration.
    • 1:15:28And Django knows that the origin must be an airport,
    • 1:15:31so it's going to give me the opportunity to just choose an airport.
    • 1:15:34Where I can say, OK, Shanghai is the origin.
    • 1:15:36The destination is going to be Paris, and the duration
    • 1:15:39is going to be 760 minutes, for example.
    • 1:15:42So now, using Django's admin interface, I've
    • 1:15:44been able to add a number of different flights
    • 1:15:46and a number of different airports.
    • 1:15:48And if I go back, not to the admin app, but to my flights app,
    • 1:15:52the app that I wrote myself, and go back to /flights.
    • 1:15:54Now, I actually see all of the new flights
    • 1:15:57that I have added to my database via Django's admin interface.
    • 1:16:01I added them to the admin interface, and now I see this flight from Shanghai
    • 1:16:04to Paris.
    • 1:16:05I see this flight from Paris to New York as well.
    • 1:16:09And so now, what I might like to do is begin
    • 1:16:11to add some more pages to this web application.
    • 1:16:14Make this web application a little more sophisticated
    • 1:16:17by maybe giving me the ability to click on a particular flight
    • 1:16:20to view details about that flight.
    • 1:16:22What I'd like is for every flight to have its own page,
    • 1:16:25not just /flights for all the flights, but /flight/one for flight ID one.
    • 1:16:30/flight/two for ID two, so on and so forth.
    • 1:16:34What I can do in order to do that is go back into urls.py
    • 1:16:38and create a new path.
    • 1:16:40We'll create a path where I'm going to specify
    • 1:16:42a flight ID, which would be an integer.
    • 1:16:45When I do, let's go ahead and load the flight view, whose name will be flight.
    • 1:16:49And now, I need to just go to views.py and add a function called flight.
    • 1:16:54So I go back, go into views.py.
    • 1:16:57In addition to an index function, we'll define a flight function that
    • 1:17:01accepts as an argument a flight ID.
    • 1:17:04So now, what is the flight function going to do?
    • 1:17:06Well, the first thing I need to do is actually get that flight.
    • 1:17:09I can say flight equals flight.objects.get.
    • 1:17:12Get me the flight whose idea is equal to flight ID, for example.
    • 1:17:18Or alternatively, Django also let's you say pk instead of ID.
    • 1:17:22It's a much more generic way of referencing
    • 1:17:24the primary key, for whatever the primary key happens to be called.
    • 1:17:27The pk in this case is just the ID.
    • 1:17:30But then what I can do is render a template, like flight/flight.html,
    • 1:17:36and pass as input to that the flight.
    • 1:17:40So we're passing this flight to flight.html.
    • 1:17:44And now, I can create a template--
    • 1:17:46create a new file called flight.html which is going to also extend
    • 1:17:52flight/layout.html using that same HTML layout.
    • 1:17:56And inside the body of the page, let's just
    • 1:17:59say something like in big we'll say flight ID.
    • 1:18:04And then, maybe an unordered list where I can say something like the origin
    • 1:18:08is flight.origin.
    • 1:18:11The destination is flight.destination.
    • 1:18:15And the duration is flight.duration.
    • 1:18:21So now, I have a page that displays flight information
    • 1:18:24about any particular flight.
    • 1:18:26And if I go ahead and load not /flights in my web browser, but /flights/one,
    • 1:18:31for example.
    • 1:18:32Well, now I have information about flight number one.
    • 1:18:34And /flight/two gets me information about flight number two.
    • 1:18:38Querying for that particular flight, then printing out
    • 1:18:40its origin, destination, and duration.
    • 1:18:43Now, there is some error checking that we probably should do here.
    • 1:18:46If I try and access a flight that doesn't exist,
    • 1:18:48something like flight 28, for example.
    • 1:18:50I'm going to get some sort of error that does not exist error.
    • 1:18:53Flight matching query does not exist.
    • 1:18:55I might like to control what happens in that situation a little better.
    • 1:18:58So you might imagine adding some additional error checking
    • 1:19:01to handle those cases as well.
    • 1:19:02But we'll leave it at this just for now.
    • 1:19:05But now, let's go ahead and add the ability,
    • 1:19:07not only to have flights that have airports associated with them,
    • 1:19:11but let's also add passengers to our flights
    • 1:19:13as well to be able to represent passengers that might actually
    • 1:19:16be on these flights, too.
    • 1:19:18So go ahead and go back into models.py.
    • 1:19:22And in models.py, in addition to an airport class and a flight class,
    • 1:19:27let me create a new class called passenger.
    • 1:19:31Also going to be a model.
    • 1:19:32And what properties does a passenger have?
    • 1:19:35Well, a passenger has a first name, which
    • 1:19:37we'll go ahead and make a models.CharField whose max length
    • 1:19:42we'll put at 64.
    • 1:19:44And the last name.
    • 1:19:47Max length equals 64.
    • 1:19:49And passengers also, as we described before,
    • 1:19:52they have a many to many relationship with flights.
    • 1:19:55That a flight could have multiple passengers,
    • 1:19:57a passenger could be on multiple flights,
    • 1:19:59and ultimately, we need an additional table to keep track of this.
    • 1:20:02But we can think a little bit more abstractly here in Django
    • 1:20:05and just say that every passenger has flights associated with them, which are
    • 1:20:09a models.manytomanyfield with flight.
    • 1:20:14So every passenger could be associated with many flights.
    • 1:20:18We'll say blank equals true to allow the possibility that a passenger has
    • 1:20:22no flights.
    • 1:20:22Maybe if they're not registered for any flights at all.
    • 1:20:26And we'll also give this a related name of passengers,
    • 1:20:30meaning if I have a passenger, I can use the flights attribute
    • 1:20:33to access all of their flights.
    • 1:20:35And likewise, if I have a flight, I can use this passenger's related name
    • 1:20:39to access all of the passengers who are on that flight.
    • 1:20:42And we'll see how that'll be useful in a moment, too.
    • 1:20:45The string representation of a passenger will just
    • 1:20:48go ahead and be their first name space their last name,
    • 1:20:52which feels like a reasonable way of representing a particular passenger.
    • 1:20:56And now, I need to apply these changes.
    • 1:20:58I need to say, python manage.py.
    • 1:21:01Make migrations because I've made new changes to my model.
    • 1:21:04I've created a model passenger, in particular.
    • 1:21:07And now, if I do python manage.py migrate,
    • 1:21:10now I've applied those changes to my actual database.
    • 1:21:13And if I go into admin.py, so we'll go into admin.py,
    • 1:21:18and register not only flight and airport but passenger,
    • 1:21:23admin.site.register passenger, then now via the admin interface,
    • 1:21:28I can manipulate passengers as well.
    • 1:21:30I can say python manage.py run server to run my web server.
    • 1:21:35Go to my web servers admin view by going to /admin.
    • 1:21:39Go down to passengers, and let's go ahead and add a passenger.
    • 1:21:44Where I can say, all right, first name Harry,
    • 1:21:46last name Potter, and we'll go ahead and put him on flight 1 and flight 3,
    • 1:21:51maybe.
    • 1:21:51He's on two different flights, for example.
    • 1:21:53And you can hold down command or control to be able to select multiple flights.
    • 1:21:57And we'll go ahead and save that.
    • 1:21:58Harry Potter has been added successfully.
    • 1:22:00And let's add a couple of other passengers.
    • 1:22:02We'll add Ron Weasley.
    • 1:22:04And we'll add another.
    • 1:22:05We'll add Hermione Granger.
    • 1:22:08And we'll add Ginny Weasley as well.
    • 1:22:11So we've added a number of different passengers
    • 1:22:13that now all exist in Django's admin interface.
    • 1:22:16And now, what I'd like to do is on the flight page,
    • 1:22:18display information about which passengers
    • 1:22:21happened to be on any given flight.
    • 1:22:23So the way I might do that is by going into views.py.
    • 1:22:29And on the flight page, in addition to giving access to the flight,
    • 1:22:33let me also give it access to passengers.
    • 1:22:35So passengers this template is going to get access to.
    • 1:22:39And we get passengers by saying flight.passengers.all.
    • 1:22:43And the reason we can do this is, again, because passengers
    • 1:22:46is that related name.
    • 1:22:48It is our way of taking a flight and getting all of the passengers that
    • 1:22:52happened to be on that flight.
    • 1:22:54And so now, inside of flight.html I can add something like,
    • 1:23:01let's add an H2 called passengers.
    • 1:23:04Where here, I'm going to loop for passenger in passengers.
    • 1:23:10Go ahead and display that passenger.
    • 1:23:14Just print out that passenger inside of a list item.
    • 1:23:18And in Django, I can say, if the list is empty,
    • 1:23:20let's just have a list item that says, no passengers.
    • 1:23:23Meaning nobody is currently on this flight.
    • 1:23:27So now, my web server is still running.
    • 1:23:29I can go back to /flights.
    • 1:23:31Here are all of the flights.
    • 1:23:34And if I go to /flight/one I now see that I'm flight one.
    • 1:23:37Harry Potter is a passenger on that flight.
    • 1:23:40But if I go to flight two, all right, no passengers are on that flight either.
    • 1:23:44And now, it's been a little annoying that I've
    • 1:23:46had to do everything by using the URL here to be able to go
    • 1:23:49back and forth between pages.
    • 1:23:51I could link to those pages if I want to.
    • 1:23:53And the way I might do that is, let's on the flight page,
    • 1:23:57add a link that goes to the URL index that says something
    • 1:24:03like, back to flight list, maybe.
    • 1:24:06So here is now a link that takes me to the index view.
    • 1:24:09And likewise, I can go into index.html.
    • 1:24:12And for each of these list items, each of these list items
    • 1:24:16is really going to be a link that links to it's a url to a particular flight.
    • 1:24:23And the flight route takes as a parameter a flight ID.
    • 1:24:27And so inside this url a substitution here.
    • 1:24:30I can specify use flight.id as the ID of the flight
    • 1:24:35that I would like to use here.
    • 1:24:37And so now, I've put every single flight inside
    • 1:24:40of a link that takes me to the flight route.
    • 1:24:43But because the flight route requires as an argument the flight ID,
    • 1:24:46I can specify the flight ID here.
    • 1:24:50And so now, if I go back to /flights, I now see a list of flights where every
    • 1:24:54flight is in fact a link that can take me somewhere else.
    • 1:24:57And so now, I can click on any one of those links, like New York to Paris,
    • 1:25:02and that takes me to the flight page.
    • 1:25:03I can click back to flight lists, that takes me back to the flight list.
    • 1:25:06Click on another flight and go to that flight as well.
    • 1:25:09So I've now been able to come up with this way of linking
    • 1:25:12these pages together by having links in each of the various different pages
    • 1:25:16that take me to some other route as well.
    • 1:25:20And so now what I might like to do is, in addition
    • 1:25:22to displaying all the passengers on any particular flight,
    • 1:25:25also give myself the ability to add passengers to a flight
    • 1:25:28as well, which feels like a reasonable thing
    • 1:25:30that I might want to do inside of this web application.
    • 1:25:33And so how can I go about doing that?
    • 1:25:35Well, in order to do that, I'm going to need
    • 1:25:37some new route that lets me book a flight for a particular passenger.
    • 1:25:42And so I'll go ahead and go back to urls.py.
    • 1:25:45And inside of urls.py I'll add a new path that will be int flightid/book.
    • 1:25:54And int flightid/book is going to let me book
    • 1:25:56a flight for this particular flight ID.
    • 1:25:59For flight one or flight two or flight three, or so forth.
    • 1:26:03When I do, we'll go ahead and go to the book view, and we'll name that book.
    • 1:26:08And so now, I need to implement the book view.
    • 1:26:12So how is this view going to work?
    • 1:26:13I'm going to define a function called book
    • 1:26:17that is going to take as its argument, not only the request,
    • 1:26:21but also a flight ID.
    • 1:26:22The first thing, as with before, is I want to get the flight ID.
    • 1:26:26But remember from before, that there are multiple ways
    • 1:26:28that I can request a web page.
    • 1:26:30I can request a web page via the get request method,
    • 1:26:33which means I would just like to get this page.
    • 1:26:35Or I can request the method via post, meaning
    • 1:26:38I would like to send data to the page.
    • 1:26:40And generally speaking, anytime you want to manipulate the state of something,
    • 1:26:44especially manipulating our database, that
    • 1:26:46should be inside of a post request.
    • 1:26:49I'm submitting some form, some data.
    • 1:26:51And in response to that post submission, you
    • 1:26:53should manipulate what's going on inside of the database.
    • 1:26:57So we're going to check when this book route is called upon.
    • 1:27:01If request method is post, then we want to perform some sort of action.
    • 1:27:06The flight in question is just going to be flight.objects.get.
    • 1:27:11Get the flight whose primary key is that flight ID.
    • 1:27:15And then, what I'd also like to do is associated with the form.
    • 1:27:18When someone submits this form to book a new passenger on the flight,
    • 1:27:22they should tell me what the ID is of the passenger.
    • 1:27:25What passenger should I book on the flight?
    • 1:27:27Because those are the two pieces of information
    • 1:27:29you need to know in order to actually book a flight.
    • 1:27:31You need the flight and the passenger information.
    • 1:27:35So let's assume for now that the information is
    • 1:27:37going to be in request.post and then in square brackets passenger.
    • 1:27:42What this means is that the data about which passenger
    • 1:27:45ID we want to register on this flight is going
    • 1:27:49to be passed in via a form with an input field whose name is passenger.
    • 1:27:54The name on any particular input field dictates what name we get--
    • 1:27:58is received when a route like this book route
    • 1:28:01is able to process the request from the user.
    • 1:28:04So we'll go ahead and take that information.
    • 1:28:06And because by default this might be a string, let's go ahead
    • 1:28:09and convert it into an integer just to make sure
    • 1:28:11we're dealing with an integer.
    • 1:28:13And let me say that the passenger in question
    • 1:28:16is going to be passenger.objects.get pk equals this whole thing.
    • 1:28:24So now what I've done is, if the request method
    • 1:28:26is post, meaning someone submitted this form via the post request method,
    • 1:28:30I'm first thing flights.objects.get. to get a particular flight,
    • 1:28:34get me the flight with that flight ID.
    • 1:28:37And then, I'm getting a passenger.
    • 1:28:39Which passenger am I getting?
    • 1:28:40The one who's pk, their primary key, otherwise known as ID,
    • 1:28:43is equal to whatever was submitted via this post form
    • 1:28:47with a name of passenger.
    • 1:28:49And we haven't yet created that form, but we'll do so in just a moment.
    • 1:28:53Now ultimately, we'll want to add some more error checking to this as well,
    • 1:28:56like what if someone requests a passenger that doesn't exist,
    • 1:28:59or a flight that doesn't exist either.
    • 1:29:01So there is definitely some error checking
    • 1:29:03that we probably should be doing here.
    • 1:29:05But for simplicity, let's just assume for now that we're able to get a flight
    • 1:29:09and get a passenger.
    • 1:29:10Well, how do we access a passenger's flights?
    • 1:29:13I can just say passenger.flights.
    • 1:29:16And in order to add a new item to some set like flights,
    • 1:29:20I can just say passenger.flights.add flight.
    • 1:29:24And this will do the equivalent of adding
    • 1:29:25a new row into a table of keeping track that the passengers on that flight.
    • 1:29:29But the nice thing about Django's abstractions
    • 1:29:31is that I don't have to worry about those underlying details.
    • 1:29:34I don't have to worry about what the structures of the tables are.
    • 1:29:37I can think at a much higher level and just say take this passenger,
    • 1:29:41take their set of flights, and go ahead and add a new flight
    • 1:29:45to that set of flights.
    • 1:29:47And when all that's said and done, what I probably want to do
    • 1:29:51is return some sort of redirect that redirects the user back
    • 1:29:55to the flight page.
    • 1:29:56So we'll go ahead and return an HTTP response redirect.
    • 1:30:01What you URL would I like to take them to?
    • 1:30:03Well, I'd like to take them to the flight route.
    • 1:30:06And reverse, again, takes the name of a particular view,
    • 1:30:09and gets me what the URL is, and we saw that last time.
    • 1:30:13And the flight route takes an argument.
    • 1:30:14So I need to pass as an argument the flight's ID.
    • 1:30:18So I need to provide it to the flight route
    • 1:30:20what the flight's ID is, structured it as a tuple,
    • 1:30:23and that is going to redirect me back to the flight route
    • 1:30:27so that I can see that flight page again.
    • 1:30:30And what I need to add up at the top here is from django.http.
    • 1:30:35Import HttpResponseRedirect in addition to from django.urls import reverse.
    • 1:30:47And so those I'll need to add as well so that I
    • 1:30:49can redirect the user back to the flight page
    • 1:30:52after they're done submitting the form.
    • 1:30:53And reverse takes the name of a particular view,
    • 1:30:57as defined in urls.py, something like index or flight or book,
    • 1:31:01and gets me what the actual URL path should be.
    • 1:31:04And as we talked about last time, that's helpful
    • 1:31:06so that I don't have to hard code URLs into my Django web application.
    • 1:31:10I can just reference URLs by their name.
    • 1:31:12And if ever I need to change a URL, I can just
    • 1:31:14change it in one place in urls.py, and that change
    • 1:31:18is going to reflect everywhere else as well.
    • 1:31:22So now, the next thing I need to do is actually create this form.
    • 1:31:26That what I have so far is just a function
    • 1:31:28called book that is waiting for a post request to be made to it.
    • 1:31:32And when a post request is made to it, then we're
    • 1:31:34going to go ahead and submit this form and go ahead and add the flight
    • 1:31:37for this particular passenger.
    • 1:31:39But what I'd like to do now is actually add that form.
    • 1:31:42So I'll go back into templates, go into flight.html,
    • 1:31:46and what I'd like to add here is a form.
    • 1:31:49I'll go ahead and label it with an H2 called add passenger.
    • 1:31:53And we'll create a form whose action is going to be URL of book.
    • 1:32:00So we're going to go to the book route.
    • 1:32:01And again, if we recall the book route in urls.py,
    • 1:32:04the route with name book, this view, requires as a parameter some flight ID.
    • 1:32:10So I need to provide the flight ID as an argument for what flight
    • 1:32:14I'm booking the passenger on.
    • 1:32:15And it just so happens to be flight.id because this template has
    • 1:32:18access to a variable called flight.
    • 1:32:21The method of this submission is, again, going to be post.
    • 1:32:25And recall from before that whenever I have a form in Django,
    • 1:32:28I need to give it the CSRF token just for security to make sure
    • 1:32:31that Django knows it's really this application that
    • 1:32:34is submitting this form.
    • 1:32:35We'll go ahead and add a dropdown list, which you can
    • 1:32:38create in HTML using a select field.
    • 1:32:41The name of this select field is going to be passenger.
    • 1:32:45And the reason for that is inside of views.py, when I get the passenger,
    • 1:32:49I'm looking for inside the post data, inside of request.post,
    • 1:32:53for a field whose name is passenger.
    • 1:32:55And so that is what I would like the name of this dropdown to be.
    • 1:32:59And inside of a select dropdown, we have a whole bunch of options.
    • 1:33:03Options that we can choose from.
    • 1:33:04And there's going to be one option for everyone who
    • 1:33:07isn't a passenger on this flight.
    • 1:33:10And so how do I get everyone who isn't a passenger on the flight?
    • 1:33:13Well, it seems that right now, the flight page only has access
    • 1:33:17to actual passengers, and doesn't yet have access to people
    • 1:33:21that are not passengers on the flight.
    • 1:33:23So it sounds like I need to add some additional context to this template.
    • 1:33:26Additional information that we want access to.
    • 1:33:29So I'll go ahead and give this flight access to additional information
    • 1:33:33that we'll call non-passengers.
    • 1:33:35For people that are not on the flight.
    • 1:33:38And how do we I non-passengers?
    • 1:33:39Well, just as I could say passenger.objects.filter
    • 1:33:43to only get passengers that match a particular query,
    • 1:33:47there's also a way in Django to say passenger.objects.exclude to say
    • 1:33:51exclude passengers that satisfy a particular query.
    • 1:33:55So I want to exclude passengers who, among their flights,
    • 1:33:59have this as one of their flights.
    • 1:34:02And so what does this actually mean?
    • 1:34:04Well, it means that when I render flight.html,
    • 1:34:06there's a couple pieces of information that it should have.
    • 1:34:09It needs to know what flight is being rendered.
    • 1:34:11It needs to know who is on the flight, who are the passengers.
    • 1:34:15But if I want a dropdown where I can choose from all the people
    • 1:34:17who aren't already on the flight, like I would
    • 1:34:19like to register you for the flight, well, I also
    • 1:34:22need all of the non-passengers.
    • 1:34:23Passengers except, excluding the ones who are already on the flight,
    • 1:34:29and get me all of them is what that .all is ultimately saying.
    • 1:34:33And so using that, I now have access to this variable
    • 1:34:36called non-passengers that I can use as I'm constructing this page.
    • 1:34:41So back on flight.html, I can say, for every passenger in non-passengers,
    • 1:34:49let me create a option that I can choose from.
    • 1:34:54And the options value is going to be the passenger's ID.
    • 1:34:59Because ultimately, when I submit the form, what I care about getting
    • 1:35:02is what is the ID of this passenger that I've chosen from this dropdown.
    • 1:35:06But of course, the user who's looking at this page,
    • 1:35:08they don't want to see people's IDs.
    • 1:35:10They want to see people's names.
    • 1:35:12So inside of the option tag, we'll go ahead
    • 1:35:14and just print out the passenger's name.
    • 1:35:17And we'll see in a moment what all of this
    • 1:35:19actually looks like in terms of HTML.
    • 1:35:21So now that I've created this form, I'll also
    • 1:35:24need at the bottom to add an input whose type is submit
    • 1:35:27to let myself submit this form to.
    • 1:35:30Let's now try running this application.
    • 1:35:33It looks like there's a slight error where
    • 1:35:34I said view.book instead of views.book.
    • 1:35:36Views is the name of the module, since it's in a file called views.py.
    • 1:35:39And now, it looks like my server is running OK.
    • 1:35:43I can go back to /flights.
    • 1:35:45Let me get one of the flights, like New York to London, flight number one.
    • 1:35:48And all right.
    • 1:35:49Name error.
    • 1:35:50Name passenger is not defined.
    • 1:35:52This is how Django renders to me errors that are occurring in my Python code.
    • 1:35:55Looks like it just means inside of veiws.py I'm referencing passenger,
    • 1:36:00but I never imported it.
    • 1:36:01So up at the top, I'll go ahead and import passenger as well.
    • 1:36:05Now, it seems that my web application is working OK.
    • 1:36:07So now, hopefully, I refresh this page, I see flight information.
    • 1:36:11I see passengers.
    • 1:36:12And I also down at the bottom now see an add passenger section
    • 1:36:16with a dropdown list.
    • 1:36:17Where I can click on it and see, all right, here
    • 1:36:19are the three people that are not already on this flight.
    • 1:36:23And so if I want to add Ginny Weasley to this flight,
    • 1:36:26I can click Ginny Weasley, click submit, and that submits the form.
    • 1:36:30And I'm redirected back to the same flight page.
    • 1:36:32Now, Harry and Ginny are both on the flight.
    • 1:36:35And in the add passenger list, I see Ron and Hermione as the options for me
    • 1:36:40there.
    • 1:36:41And so using Django's models, I've been able to very quickly build up
    • 1:36:44a reasonably sophisticated application.
    • 1:36:46An application that has models, that displays that information to me,
    • 1:36:50and lets me manipulate that data.
    • 1:36:52And that data is ultimately stored inside of a SQL database.
    • 1:36:56And one of the big powers of Django that it really gives to me
    • 1:36:59is this admin interface that I ordinarily
    • 1:37:02might have had to spend a lot of time designing a web interface that just
    • 1:37:05lets me do things like take some person and go ahead and update
    • 1:37:09what is their name, what flights are they on,
    • 1:37:11and the ability to very quickly add and delete and edit the models
    • 1:37:15is something that in a web application could take quite a lot of time
    • 1:37:18to be able to build from scratch.
    • 1:37:20But Django, very fortunately, gives all of that right to me.
    • 1:37:24And this admin interface, even though it is designed by Django,
    • 1:37:27it's very customizable in terms of things
    • 1:37:29that I can do on this admin interface if I want to manipulate it in certain ways
    • 1:37:33in order to add additional features to it as well.
    • 1:37:36So we'll see a couple of brief examples of this.
    • 1:37:39If I go into admin.py, here's my configuration
    • 1:37:43for Django's admin interface.
    • 1:37:45And I can say, I would like to configure the admin
    • 1:37:47interface in a particular way.
    • 1:37:49That in a flight, for example, by default all I saw
    • 1:37:52was the flight's origin and destination.
    • 1:37:55If I want to be able to see more information about a flight,
    • 1:37:57I can say, go ahead and give me a class called flight admin, which is
    • 1:38:01going to be a subclass of model admin.
    • 1:38:05Where I can specify any particular settings
    • 1:38:08that I want to apply to how the flight admin page is displayed.
    • 1:38:12So I can-- and all of this is documented on Django's website.
    • 1:38:15And you just have to read it to be able to know what configuration options are
    • 1:38:18available to you.
    • 1:38:19But I can say, in the list display, when you list all the flights
    • 1:38:22and display them all to me, what field should I have access to?
    • 1:38:26Well, go ahead and show me something like the origin
    • 1:38:29and the destination and the duration and maybe also show me the ID, for example.
    • 1:38:35So I want to see all of this information when you load a flight.
    • 1:38:38And when I register the flight, I'll say register this flight,
    • 1:38:42but use the flight admin settings when you do so.
    • 1:38:44So I can specify, I would like to use these particular settings
    • 1:38:48when you view the admin interface.
    • 1:38:51And so now, if I go back and go ahead and click on flights,
    • 1:38:55then now in this list display, whereas before I only
    • 1:38:58saw IDs and origins and destinations, now I
    • 1:39:00can configure it to show me all of the IDs and origins and destinations
    • 1:39:04and durations as well.
    • 1:39:05I've been able to configure this display to work the way I would like it to.
    • 1:39:09And there are other configurations you can do as well.
    • 1:39:12One that I quite like to use is if I want to update my passenger admin,
    • 1:39:16so when I'm editing a passenger, you can have
    • 1:39:19a special way of manipulating many to many relationships inside
    • 1:39:22of an attribute called filter horizontal.
    • 1:39:25And if I use a horizontal filter on flights,
    • 1:39:29this will just make it a little bit nicer for manipulating
    • 1:39:31the flights that a passenger is on.
    • 1:39:33And again, the specific syntax of this not as important as the idea
    • 1:39:36that these are all just configurable settings that Django has documented.
    • 1:39:39That you can look at to see how to configure the Admin interface to work
    • 1:39:42exactly the way you want it to work.
    • 1:39:45And so now, if I go back home and go to passengers,
    • 1:39:48and maybe click on a passenger like Harry Potter,
    • 1:39:51I now see this horizontal filter, which is
    • 1:39:53a very nice way of being able to manipulate flights
    • 1:39:56that the person is on.
    • 1:39:57I see on the left a list of their available flights
    • 1:39:59that I could add them to.
    • 1:40:01On the right, a list of their chosen flights.
    • 1:40:03Flights that they're already on.
    • 1:40:04And it becomes very easy for me to just take a flight and double click on it
    • 1:40:08to move it from an available flight to a flight that they're on and vise versa.
    • 1:40:12Just very quickly being able to control and manipulate these models.
    • 1:40:16And this is all stuff that Django just gives to you right out of the box.
    • 1:40:20So Django now has given us a lot of features.
    • 1:40:23The ability to represent models very succinctly.
    • 1:40:25A migration method for being able to very quickly apply
    • 1:40:28those changes to our database.
    • 1:40:30And the last thing we'll take a look at is this idea of authentication.
    • 1:40:34That on many websites, we want some method of authentication.
    • 1:40:36Some ability for users to be able to log in and log out.
    • 1:40:39For Django to remember who a particular user happens to be.
    • 1:40:43And what we're going to do now is introduce
    • 1:40:45an application that lets us interact with this authentication method.
    • 1:40:48Because Django has a whole bunch of authentication features
    • 1:40:51built right into the framework that we can take advantage of so
    • 1:40:54that we don't need to rewrite all the logic for how do you log someone in,
    • 1:40:58and what does it mean to represent the user.
    • 1:40:59Django has done a whole lot of that for us.
    • 1:41:02So we'll go ahead and create an application to do that now.
    • 1:41:05All right, so let's go back into my terminal now.
    • 1:41:07And now, I have this airline project inside
    • 1:41:09of which is one app called flights.
    • 1:41:11And I'd like to now create another app that's going to maintain users inside
    • 1:41:15of this application.
    • 1:41:16So I'll go ahead and run python manage.py start app users.
    • 1:41:20Which will just be an app that's going to allow me to represent users.
    • 1:41:23As before, when I create a new application,
    • 1:41:27I'll need to go into settings.py.
    • 1:41:29Add users as one of the installed apps inside of this project.
    • 1:41:35And I'll go into urls.py to say I'd also like when I go to users,
    • 1:41:41we'll go ahead and include users.urls.
    • 1:41:45So all the URLs that are associated with my user's application.
    • 1:41:49Now, I'll need to actually create those URLs.
    • 1:41:52So I'll go ahead and go down into my users application,
    • 1:41:55create a new file called urls.py, inside of which
    • 1:41:58is the same as what we've normally see inside of these URLs files.
    • 1:42:02I need to import path, import my views, and then define some URL patterns.
    • 1:42:08Where here what I'd like to do is define one path that takes me to views.index.
    • 1:42:16And we'll call this one index.
    • 1:42:18Then I'll create another path that takes me to log in, called the log in view.
    • 1:42:24And the name will be log in.
    • 1:42:26And we'll have another path called log out for a function called log out
    • 1:42:31view that will be associated with it.
    • 1:42:33So we'll effectively have three different routes.
    • 1:42:35One main index route that's just going to display information about the
    • 1:42:38currently signed in user.
    • 1:42:40One route for logging someone in, a form that
    • 1:42:42will display the place where they can type in a user name
    • 1:42:44and password to log in.
    • 1:42:45And then, one route to allow users to be able to log out from this application
    • 1:42:50as well.
    • 1:42:51So let's go ahead now and actually write these functions.
    • 1:42:53We need one function called index, one function
    • 1:42:55called log in view, one function called log out view.
    • 1:42:59So we'll go into views.py, and we'll start with index.
    • 1:43:03And so what does the index function need to do?
    • 1:43:05It's going to display information about the currently signed in user.
    • 1:43:09That I sign into this website, and then I'm presented with the index page.
    • 1:43:13Because if we think about this programmatically,
    • 1:43:15we first need to think about what should happen
    • 1:43:17if someone tries to access this page but they're not authenticated.
    • 1:43:21How would we even find that out, and what do we do in that situation?
    • 1:43:24Well, let's say, if not request.user.is_authenticated.
    • 1:43:30The request object that gets passed in as part of the request
    • 1:43:33to every user in Django automatically has
    • 1:43:36a user attribute associated with it.
    • 1:43:38And that user object has an is authenticated attribute that tells us
    • 1:43:42if the user is signed in or not.
    • 1:43:44If they're not signed in, we'll go ahead and HTTP response
    • 1:43:48redirect them to the log in view.
    • 1:43:53And in order to make this work, I'm going
    • 1:43:54to need to-- from django.http import HttpResponseRedirect.
    • 1:44:00And likewise, from django.urls, let's go ahead and import reverse as well.
    • 1:44:06So if the user is not authenticated, then
    • 1:44:08we're going to redirect them to the log in view, where what is the log in view
    • 1:44:12going to do?
    • 1:44:13Well, the log in view for now, let's just go ahead and render
    • 1:44:18users/login.html.
    • 1:44:21Some form where the user can log themselves in.
    • 1:44:24We'll need to create some templates.
    • 1:44:26I'll create a templates folder inside of which is a user's folder.
    • 1:44:30Inside of which we'll just create a basic layout,
    • 1:44:32as we've done multiple times now.
    • 1:44:36This is, again, going to be the general structure for pages in this app.
    • 1:44:40Title will be users, and the body will just
    • 1:44:42have a block called body that I can later fill in with other content.
    • 1:44:48And now that I have this HTML layout, I can go ahead and create a new file
    • 1:44:52called login.html, where login.html will extend user/layout.html.
    • 1:45:00And inside the body block, I can just display in HTML form.
    • 1:45:06So I can say something like, I would like
    • 1:45:08for there to be a form, whose action, when I submit the forum--
    • 1:45:11let's go ahead and still go to the log in URL,
    • 1:45:14but let's do so using the post request method.
    • 1:45:17Again, I'm logging in, I'm submitting a form.
    • 1:45:20Generally, when you're doing that, you want
    • 1:45:21to submit form data via post, especially in the case of user name and password.
    • 1:45:25Because if you do this sort of thing, you
    • 1:45:27don't want the user name and password to be passed in as get parameters
    • 1:45:30because those show up in the URL.
    • 1:45:32Our form will have our CSRF token for security as before.
    • 1:45:36And input whose type is text, whose name is username, and just
    • 1:45:41for user friendliness, let's give it a placeholder also of user name
    • 1:45:44so the user knows to type in their user name here.
    • 1:45:47We'll also have an input whose type is password, whose name is also password,
    • 1:45:52and when an input's type is password, that just
    • 1:45:54means our HTML will know in the browser that chrome or Safari or whatnot
    • 1:45:58will know to show the password as dots instead of as characters.
    • 1:46:02And we'll give that a placeholder of password.
    • 1:46:05And then an input of type submit whose value is log in.
    • 1:46:10So we now have the ability to log in.
    • 1:46:13So if we go ahead and run this program, python manage.py run server,
    • 1:46:17we should see users.views has no attribute log in view.
    • 1:46:21All right, it looks like I called this function log in request.
    • 1:46:25It should actually be called log in view.
    • 1:46:27And I'll also need a function called log out view.
    • 1:46:30But I haven't implemented that yet.
    • 1:46:31So I'll just go ahead and say pass for now,
    • 1:46:33but I'll come back to that later to implement the log out view.
    • 1:46:37All right, so it looks like my web server is running now.
    • 1:46:39And before I actually go to the login page,
    • 1:46:41let me first go back to the admin page and actually just create some users.
    • 1:46:45I can go to users and then add, and let's add a user.
    • 1:46:49The user name will be Harry, for example.
    • 1:46:51And we'll go ahead and give Harry a password.
    • 1:46:54And we'll go ahead and save and add another.
    • 1:46:56Let's add maybe Ron as well.
    • 1:46:59Go ahead and add him.
    • 1:47:01We'll go ahead and save that.
    • 1:47:02And these users, they can have additional information
    • 1:47:04associated with them.
    • 1:47:05I can give Ron a name like Ron Weasley, RonWeasley@example.com
    • 1:47:09is his email address.
    • 1:47:10There are a bunch of default fields that Django gives you
    • 1:47:13for manipulating users.
    • 1:47:14And you can take these users and go ahead and add to those fields
    • 1:47:17if I want to.
    • 1:47:18Giving them a first name, last name, email address, and whatnot.
    • 1:47:21And you can also customize these fields as well.
    • 1:47:23If you'd like to add custom fields that you would like to keep track of with
    • 1:47:26regards to your individual users.
    • 1:47:29And I will go ahead and log out from Django admin
    • 1:47:31now because I don't need it anymore.
    • 1:47:33But now, if I go to /users, I'm not authenticated.
    • 1:47:38So what I see is a log in form that just looks like this.
    • 1:47:40A place for me to type in a user name and a password.
    • 1:47:44And of course, I could type in Harry's username and password.
    • 1:47:46But I haven't yet implemented the processing of that data yet.
    • 1:47:49So let's go ahead and do that now.
    • 1:47:52We'll go ahead and go back to views.py.
    • 1:47:55In the log in view, there are two ways the log in view function
    • 1:47:57could be called.
    • 1:47:58One is via the get request method, meaning just show me the log in form.
    • 1:48:02And one is via post, submit data to the log in form as well.
    • 1:48:05So if the request method is post, well then,
    • 1:48:10let me first get the user name which will be inside of the post
    • 1:48:13data in a field called user name.
    • 1:48:15And let me get the password, which will be
    • 1:48:17in a field in the request.post inside of password.
    • 1:48:22And now what I'd like to do is try to authenticate this user.
    • 1:48:26And how do I go about doing that?
    • 1:48:28Well, it turns out there are a couple of functions that Django
    • 1:48:30has given to me that I can import.
    • 1:48:31So from django.contrib.auth, auth for authentication.
    • 1:48:36I'm going to import three functions we're ultimately going to use.
    • 1:48:38One is authenticate that checks if user name and password are correct.
    • 1:48:42One is called log in, one is called log out.
    • 1:48:47And I can now use those functions inside of this log in view here.
    • 1:48:50After I've gotten the username and password,
    • 1:48:52I'd like to authenticate the user.
    • 1:48:54Check if the user name and password are correct.
    • 1:48:57So I'll go ahead and say user is equal to authenticate request username is
    • 1:49:02the username password equals password.
    • 1:49:05And so authenticate is a function.
    • 1:49:06Just takes the request, takes a user name, takes a password,
    • 1:49:10and if the user name and password are valid,
    • 1:49:12they give me back who the user actually is.
    • 1:49:16And as long as the user is not none, that
    • 1:49:20means the authentication was successful, and I can go ahead and log the user in.
    • 1:49:24How do I log the user in?
    • 1:49:26I use the log in function the Django gives me.
    • 1:49:28Logging in with this request, this user.
    • 1:49:31And now I can go ahead and redirect them.
    • 1:49:34HttpResponseRedirect the user back to the index route.
    • 1:49:38Back to the original route that the user started out as.
    • 1:49:41And so that's if the user is not none.
    • 1:49:42If the authentication was successful.
    • 1:49:44But otherwise, if the authentication failed, what should I do?
    • 1:49:48Well, let me go ahead and render the same users login page again.
    • 1:49:53But let me add some additional context.
    • 1:49:56The context will be a message that says invalid credentials.
    • 1:50:02And now, inside of login.html, I can just
    • 1:50:05add some logic that says, if there's a message,
    • 1:50:08then go ahead and display that message inside of a div.
    • 1:50:12And then endif to end that.
    • 1:50:14So if there is a message, we'll see the message printed.
    • 1:50:16Otherwise, we won't see it at all.
    • 1:50:19So now, if I go ahead and refresh the login page,
    • 1:50:22nothing seems to have changed.
    • 1:50:23But let's say I type in a user name that doesn't exist.
    • 1:50:25Hermione and some password and I log in.
    • 1:50:28Well, then I get this error message.
    • 1:50:30Invalid credentials.
    • 1:50:31We were not able to log the user in.
    • 1:50:33So what happens if we do successfully logged in?
    • 1:50:36Well, then the user is going to be taken to this index route.
    • 1:50:40And it looks like now we need to finish off this index route.
    • 1:50:43What does the index route do?
    • 1:50:44Well, let's go ahead and return render a template called users/user.html.
    • 1:50:52And inside of user.html, we'll go ahead and display
    • 1:50:56some information about the user.
    • 1:50:59We'll still extend user/layout because we're
    • 1:51:02going to use the same basic layout.
    • 1:51:04But in the body of this page, the information I want to show--
    • 1:51:08if I want to say welcome and then like Harry or welcome Ron or whoever
    • 1:51:13the user happens to be.
    • 1:51:15And it turns out inside of Django templates,
    • 1:51:17I have access to the request that was used to make this HTTP request, which
    • 1:51:21means I also have access to request.user who is the user associated
    • 1:51:26with that request.
    • 1:51:27And if the user has a first name, I can access request.user.firstname.
    • 1:51:33And in addition to that, I can display other information.
    • 1:51:35Maybe their user name is request.user.username.
    • 1:51:39And maybe their email address is request.user.email.
    • 1:51:43And so I can show the user information about them.
    • 1:51:47Such that if Harry logs in, for example, I sign in as Harry,
    • 1:51:51sign in with Harry's credentials, click log in.
    • 1:51:56Well then, Harry sees a page that says welcome Harry.
    • 1:51:58Harry is logged in.
    • 1:52:00They are request.user.
    • 1:52:02And using that information, we can access first name, username, and email
    • 1:52:07as well just by accessing properties of request.user.
    • 1:52:11Now, last thing we need to add, which still doesn't yet exist,
    • 1:52:15is a way to actually log the user out.
    • 1:52:17And it turns out that just as Django has a log in function,
    • 1:52:21Django also has a log out function that handles log out for us so we
    • 1:52:24don't need to implement it ourselves.
    • 1:52:26So all our log out view needs to do is make a call to this log out function.
    • 1:52:31And then, figure out where should the user go after they've been logged out.
    • 1:52:34And you know what, let's go ahead and take them back to the login page
    • 1:52:40with a message of logged out to indicate that the user has now been logged out.
    • 1:52:46Then in user.html, we'll go ahead and add a link that will go to the log
    • 1:52:54out route that just says, log out, for example.
    • 1:52:59So now, when Harry goes back to Harry's page,
    • 1:53:01Harry sees a URL that says log out.
    • 1:53:04If Harry clicks log out, Harry gets logged out is brought back to this page
    • 1:53:08because now request.user.isauthenticated is going to be false.
    • 1:53:12There is no authenticated user.
    • 1:53:14And so they now see just the default login page.
    • 1:53:17And if now Ron were to log in, for example, using Ron's username
    • 1:53:21and Ron's password logging in, then Ron now
    • 1:53:24sees information associated with him as well.
    • 1:53:27So Django gives us a lot out of the box.
    • 1:53:30Gives us the ability to represent these models in admin interface,
    • 1:53:33to be able to manipulate them.
    • 1:53:34A migration system that allows us to very quickly make changes to our models
    • 1:53:38and apply them to our database.
    • 1:53:40And also, a built in user authentication system.
    • 1:53:42A system that allows us to very quickly enable
    • 1:53:45users to be able to log in, log out, from our web application as well.
    • 1:53:49So all of this are features that just helps
    • 1:53:51to make it so that we can very quickly take advantage of things like SQL
    • 1:53:54and models and migrations to build dynamic, interesting web
    • 1:53:57applications with data to back them up.
    • 1:54:00This was web programming with Python and JavaScript.
    • 1:54:02We will see you next time.
  • CS50.ai
Shortcuts
Before using a shortcut, click at least once on the video itself (to give it "focus") after closing this window.
Play/Pause spacebar or k
Rewind 10 seconds left arrow or j
Fast forward 10 seconds right arrow or l
Previous frame (while paused) ,
Next frame (while paused) .
Decrease playback rate <
Increase playback rate >
Toggle captions on/off c
Toggle mute m
Toggle full screen f or double-click video