-: Hi guys and welcome back.
In this video we're going to be implementing
the stage three database.py.
And as you know, the most important change that
we're making is the change to our data model.
So first of all what changes are we making
to our tables?
Well, let's copy in here
the new create table for movies.
Movies is now gonna have an I-D property
which is gonna be the primary key
a number used to identify uniquely each movie.
Every movie is then gonna have their title
and the release timestamp as it had before.
Then we're gonna have a user's table.
And I'm gonna bring it in here as well.
The user's table
is just gonna have a user's name for now.
Which is gonna be the primary key.
This is use to uniquely identify people
using our application.
And remember you can use more columns here
to add more information about specific users
if you wanted to.
Finally, we're gonna have the watched table.
And the watched table that I'm gonna chain here.
Create watched table.
Is gonna be a little more complicated.
Firstly, we have the create table if not exists
as normal, and it's gonna be called, watched.
But, then, inside it we have the user_username
which is text,
and that is gonna reference this column here.
We've got the movie ID which is gonna be an integer
referencing this column up here.
So, this table is simply references to other columns.
And how do we tell SQlite or PostgreSQL later on
that these columns here are referencing other columns
with a foreign key constraint.
So, we'll do foreign key user_username
references the user stable on specifically the username.
Similarly, we will have another foreign key constraint
for movie ID that references the movie stable
specifically the ID column.
Then, at the end, we have our semicolon as usual,
and that's our table created.
So, again, when we add the new record
to the watched table that is going to mean
that the user that we added watched
the movie that we added.
Then with some smart querying,
We can go through and find more information
about either of those.
Remember to use these new tables and variables
in here, in order to create the tables themselves.
And it's important that we create them in order.
So, the foreign key must appear
after the other tables have been created.
So, here we're referencing the user's table.
Therefore the user's table must be created first.
Here, we're referencing the movie's table.
So, therefore that must be created first.
So, the order that I'm gonna go for it,
is movies, users, and then watched.
Also, remember to change the name of this variable
like I just did, if you are gonna change that.
Next up to insert a new movie.
Is there anything we have to change?
Well, not really. We've got insert into movies.
We're putting in here the title and the timestamp,
so we're not adding a value to the ID column.
Therefore, SQlite is gonna automatically add that for us
using that primary key. Which is, as we know,
an alias for the row ID column.
So, nothing to change here at all.
That's great.
When we get and display movies from the movies table,
we do have to make a small change in app.py.
That's because we've added a new column.
So, when we go to app.py,
we no longer want to access movie one and movies zero.
We really want to access movie two and movie one.
However, it's gonna be useful
to print out the movie ID as well,
since we might need it for other things.
So, we're gonna do movie zero as well in here.
So, it'll be something like one, The Matrix,
on this date, two, Gone Girl, on that date.
This might be later on for users
to be able to refer to the movies by their IDs.
Something interesting to note in here,
is that we could use tuple destructing.
I'm gonna leave a reference to that
in the resources section of this lecture.
To simplify this code a little bit.
We can do for ID title and release date in movies
and now we can refer to these things separately
instead of movie two, zero, and one.
So, we can refer here to release date.
Here, we can refer to underscore ID.
And here, we can refer title.
And notice that I'm using underscore ID here
instead of just ID,
because ID is a built in function in Python,
and it's generally a bad idea to shadow it or override it.
When we come to marking movies as watched,
that's when the differences start popping up.
To mark a movie as watched,
we need to insert a row into the watched table.
And, that requires username and movie ID.
So, we're going to go over to that watch movie
in database.py down here and we now
instead of title, need to take in movie ID.
We no longer need to delete anything.
That was really the purpose of the stage three really.
To not have to delete movies when we insert them.
So, here we're gonna insert watch movie,
but we're gonna pass in the user name and the movie ID.
What that's gonna do,
when we go to insert watched movie,
is we're now going to insert data into this watched table.
Notice that watched, has kept the same name,
but we do now need to insert into watcher name,
and movie ID, but of course this is user_username.
You can see that the watch movie function is now
simpler because we've only got a single insert
statement that tells us that his happened.
This person watched this movie.
That's now a row in our table.
If we go over to app.py, we do also have to
make a couple of changes in here.
In prompt watch movie, we're asking for the movie title,
now we need to ask for the movie ID.
And, I'm gonna add here movie ID.
This is the reason why we were printing _id up here.
We need that of course to be able to
use it down here when we ask.
In order to be able to watch a movie,
we need to have a user be created first.
Otherwise the foreign key here the username
won't be able to reference any specific value.
That's gonna give us an error.
So we need to have a query
for inserting a new user into the user's table.
So, here we're gonna do insert, user,
and that's gonna be insert into users username
the values is gonna be just a question make there,
and we also need of course the add user function
that we are going to make for example down here.
And def add user and it's gonna take in a username,
and all it's gonna do is use the connection
to execute the insert user. So, execute
insert user with the username.
Now we're gonna wanna use that in app.py
with a new menu option to let the user
create a new user in the table.
So going up to the menu, we're going to change
exit to seven, six to add user to the app.
Then, going back to the while loop,
we're gonna change the exit condition to seven.
And we're going to add and new elif branch
at the bottom for user input equal six.
And that is gonna do prompt add user.
A function which doesn't exist yet,
that we're gonna add.
So, going up here, we'll do prompt, add, user,
and that's just gonna take in a username.
Input a username, and do database.add
user with that username.
So, that's all the changes that
we're gonna make in this video.
But, the more keen eyed amongst you
will have noticed that there is a problem.
We go over to database.py; you can see that
when we select watched movies we're selecting
star from the watched table.
Which has given us the username and the movie ID.
But, it's not giving us the movie title.
So, how do we do this?
Well, we're selecting watched movies in here,
when we ask them for the username.
Then, we get the watched movies,
and then we're printing them out.
Which takes us to here, and then, we're printing
the username, and for each movie
we're printing movie one. Movie one is the movie ID.
So, how do we get the title in there,
instead of the ID. Well, in order to do that,
we need to use a new sequel construct.
Which is the join.
Joins are a little bit more complicated.
They involve selecting data from multiple tables
at the same time. And, they're really cool.
They're really useful. They're really
one of the main things in SQL
that you really need to know about.
So, we're going to learn about them
in the next couple of videos.
Then we will come back and be able to add them
to out application to actually display
the movie title in here.
Thank you guys for watching! Thanks for joining me
in this video and I'll see you in the next one!