-: Hi guys, and welcome back.
In this video we're going to be looking at the first
database queries that we'll need
for stage one of our project.
The stage one of a project should replicate
what you would do
when you're starting a much larger project.
And that is picking one feature that will be useful
to users and going and implementing it
in the simplest way possible.
A lot of people, many developers, start designing
and planning a lot of things,
and that can lead to
over complicating and over developing your programmes.
It's good to keep things simple
that's really one of the most important things
in programming,
so that's what we're gonna start with stage one.
All right, let's get to it.
I've gone ahead and created the database.py file,
so I'm gonna go and open that.
And now we're going to close the file explorer there,
so we can focus on the code.
And by the way, for this stage, the feature is
to store and retrieve watched movies.
And you may think that we should start
with a different feature,
but that's fine, everybody does things differently.
The important thing is that we try to simplify things
before we work on them.
So like we've discussed,
we're going to have single table
with title, release date,
and a watched property.
If we add movies to a table with this structure,
we'll know the title and release date,
and then we're going to have a one watched
and zero for not watched.
In release date, we're going to have a timestamp,
or the number of seconds since first of January 1970.
I'm gonna leave a resource in the resources section
of this lecture with information as to why
the first of January 1970, and not any other date.
So for a table like this one,
we're going to need queries to create the table,
insert a movie, select all movies,
select upcoming movies, select watched movies,
and also to set a movie to watched.
We're going to code some of those right now.
For the create movies table query,
we're going to have this multi-line query,
because it's a bit longer.
And here we're going to put our create table,
so we'll do create table,
we're gonna add here an if not exists,
just in case we wanna run this multiple times,
and we're gonna create the table movies.
Now I'm opening the brackets here for the columns,
and then I'm going into a new line just to give me
a bit more room.
And usually when you do that,
you indent with four spaces
when you're going to this new line.
And so here we're going to put the columns
that we wanna create,
which is title, which is a text column.
The release timestamp,
which is gonna be
a real column for a number.
And watched, which is going to be an integer.
Then we put the semicolon at the end and we're gonna
close the string right there.
So this is the create table query.
Create table if not exists,
Movies is the table name,
and it's got three columns at the moment.
Next we're going to insert movies.
So we're going to do insert into movies,
and then the columns that we're going to insert into,
so that's title, release timestamp, and watched.
The values of question mark, question mark, zero.
So here we're going to put two arguments
that are gonna be passed into the query.
Zero is just going to be the initial value
of the watched column.
We're going to assume that users are inserting movies
they haven't watched yet.
This here, by the way, is optional,
because we're passing in all of the columns,
so we could just remove it and keep the values,
but I do like putting it there.
I think it helps with readability a little bit.
Then we're gonna select all movies.
Here we're gonna do select star from movies,
and we know how to do that.
To select the upcoming movies,
we are going to use a where clause
to filter my release timestamp.
That's one of the benefits of the real timestamp there.
We can do something like select upcoming movies
is gonna be equal to select star from movies,
where the release timestamp is greater than question mark.
Because this is going to be a number,
the number of seconds since the first of January 1970,
we can pass in another number,
and then this is just number to number comparison.
It's really straightforward.
So when we do the where clause there,
that's going to be really easy to do.
And the argument we're going to pass in here
is gonna be the number of seconds since
the first of January 1970 right now.
And that's going to mean that the release timestamp must be
greater than the time right now,
that's gonna give us upcoming movies.
Finally, for the select watched movies,
we're gonna have something identical.
Select star from movies where watched equal one.
Notice that we're not gonna have any arguments here
because watched can either be zero or one,
we're defining that here.
The watched property must be one,
that tells us the movie has been watched.
All right, so up to here there's nothing new.
We've learned how to do all of this before,
we've written our queries,
now the next step is to write the functions
that app.py will use to interact with the database
by using these queries.
Let's go and write those functions in the next video.
I'll see you there.