Jose: Hi guys, and welcome back.
In this video, we're going to be completing
our database.py file, so let's get started.
We've got most of the code already,
so what we're going to change is mostly
the create tables and watch movie functions.
Create tables now has to create the two tables that we need
instead of just one, and watched movie now has to delete
from one table and insert into the other.
Some of your other queries are also gonna have to change,
but most of our Python code is going to remain the same.
So here we've got the queries that we had
at the end of stage one.
We're creating our movies table,
then we can insert movies, select movies, upcoming
and watched, set movies to be watched by updating the table,
and finally, we've got this delete movie
that we added just recently.
Let's modify our existing queries
to match the new data model.
The first thing we wanna do is remove the watched property
from the movies table.
We're not going to need that anymore.
Similarly, when we go to insert movies,
we no longer need the watched property,
so we can remove that column,
as well as the value we're inserting.
When we go to select watched movies,
now we're no longer going to select star from movies,
we're going to select star from the watched table
that we're gonna create,
and we're going to set the where
to where watcher name equals the argument,
so we're going to change that,
and then we're going to get everything
that a certain person has watched.
We also need a few new queries as well, of course.
We have to create the watch list table,
and that is going to be another of these multi-line queries
in here, and I'm just gonna copy it in
to save you some time.
What we've got is create table if not exists,
the watched table, and it has the watcher name,
or which user watched a thing,
and the thing they watched is in here,
the title of the movie.
Okay, I'm just gonna move the delete movie up here
below insert.
I think that's a slightly better place for it,
and then we've got the insert watched movie, as well.
So we need insert watched movie,
and that is gonna be insert into the watched table,
and we have the watcher name and the title,
and the values are the two arguments.
All right, this is everything we need.
I'm sure that with this data,
you can go and fill in the rest of the blanks
in our database.py file,
so I would recommend that you do that first of all,
because as you know, coding is the best way
to practise coding,
but we're gonna do that just now, as well.
So in order to create the tables,
the first thing that we have to do is, of course,
to duplicate this and make sure to create the watched table.
We'll create the watch list table in there as well
in order to have everything that we need.
Something to remember though is that we should delete
data.db in order to make sure that we recreate the table,
because at the moment, the movies table exists,
but it has the old columns.
We have to get rid of that in order to be able
to recreate it, so I'm gonna go ahead and delete that
and also delete the DB journal.
This is created by the DB Browser to keep track
of the old changes before we actually save the file.
To mark a movie as watched,
we're now going to have to delete the movie
from the movies table
and insert it into the watched movies table.
So we're gonna do connection.execute delete movie,
and that takes in the movie title,
and we also have to do connection.execute,
and we insert a watched movie, passing in the username
of the person that watched the movie, as well as the title.
Of course, we do need the username to be passed
into this function in order to have access to it here.
Because we need the username, let's go back to app.py
and make sure to pass that in when we watch a movie.
Here we can see in prompt watch movie.
We get the movie title.
We're now going to get the username as well,
or who watched the movie.
So it's a username, it's that, and pass it in here
just like that.
To get the watched movies of a person,
we're also going to need that person's username.
Otherwise, we won't be able to find specifically
that person's watched movies,
and so we have to go ahead and, in database.py,
make sure that get watched movies accepts a username,
and then when we execute select watched movies,
pass that in as the argument.
Make sure to make it a tuple.
That's because when we changed
select watched movies earlier,
we now have the watcher name that we need.
Of course, just like before,
because we have the username here,
we now need to go back into app.py
and make sure to pass that in.
So right here, before calling get watched movies,
we'll do username equal input of username,
just as we have up here, and we're gonna pass it in there.
You may start to believe that this could be a function,
and I would probably agree with you.
You can make this a function if you want,
but I think it's just short enough and just clear enough
and just similar enough to previous pieces of code up here
that if I make this a function,
I would start to feel like I should probably
make this a function too, and this,
so for now, I'm just gonna leave it as that,
but again, it's totally your choice.
Something important to note is that if we go over to,
for example, database.get_movies, which is here,
that is selecting movies from the movies table,
which has a title and a release timestamp.
Something to take into account is that
database.get_watched_movies, if we go over to database.py,
is actually retrieving data from the watched table
instead of from the movies table,
so therefore, getting data from the watched table
is gonna give us watcher name and title
and not title and timestamp as it did before.
So when we get that, then pass it to print movie list,
what we're gonna end up with is trying to print out
the movie title as a timestamp,
which is not gonna make any sense,
as well as the person's name.
So clearly, we can't get data from get watched movies
and pass it directly to print movie list.
So instead, we're gonna create a new function
similar to this one,
just for printing the watched movie data.
We're gonna do print watched movie list,
and that is gonna take the username and the movies
of the person,
and then we're gonna print out something like, for example,
usernames
watched movies,
like that, and then at the end,
we're gonna print, again, the four dashes
with the backslash, and then for movie in movies,
we're going to print the movie name that they have watched,
so movie one.
Just like that.
Now, what this is gonna do is it's going to
print out the person's name,
and then going through the movies,
it's gonna get the movie title for each movie.
Notice that movie zero for each movie watched
is the watcher name, so we don't really want to print that
because that should be the same as the username.
Now, down here, instead of print movie list,
we wanna do, of course, print watched movie list,
passing in the username that they gave us
as well as the movies that they have watched.
I hope all of this makes sense.
Remember, you've got this all written down over in the ebook
if you prefer reading it or if you want another way
of explaining things, you can read that and also see
all the code changes that we've made
written down there, as well.
Although this has been a bit of a long video,
the actual code changes that we've made are not that many.
Just think about it.
We've changed a couple of things here when we prompt users
and print the watched movie list, and then in database.py,
we've added a couple of queries
or changed a couple of queries,
but most of the other stuff has remained the same.
That's because we are separating our replication
into different parts, and changing one part does not mean
that we have to change everything else.
Thank you for watching, guys.
I hope you've enjoyed this video. I'll see you in the next one.