- [Jose] Hi guys and welcome back.
In this video,
we're going to be writing our database.py file.
So we've got here some of the main queries we'll be using,
we're gonna be adding more as we go along,
but here we are and we need to import datetime module,
we're gonna need that later on,
and we will cover exactly how to use it.
And we'll also need to import sqlite3.
Then as we know, in order to connect to SQLite,
we need to create a connection,
so we're gonna do sqlite3.connect,
and we're going to use data.db as our connection place.
So we're gonna create data.db file in our workspace,
and we're going to use that as our database.
Then, I like creating functions for everything
that our database is going to do with our Python application
so we're gonna start at that.
So I've gone ahead and created my five functions
that are going to do the main things in this file.
I've got create_tables,
and I know that at the moment we only have one table
in our application, but it's uncommon to have a single table
and later on, our application is going to grow
and have more tables
so that's why I've called it create_tables.
Then we've got add_movie,
that takes in a movie title and the release_timestamp,
and it's going to essentially add the movie
to the database.
We've got get_movies which has a parameter upcoming=False,
so that will be used to get the movies that are upcoming
or all the movies if we pass false in here.
We've got watch_movie which take in the movie title
and it's gonna mark it as watched.
And we've got get_watched_movies that is gonna go
and find all the movies that have already been watched.
So let's go ahead and implement these functions.
In create_table, all we have to do is do with connection,
and then connection.execute,
and we're gonna pass in the create_movies_table query,
so that we will create that when we run this function.
That's really it.
Remember, we don't have to create cursors
or anything in here,
because we are not actually selecting any information,
and we don't really need the overhead of creating
our own cursor as well as the lines of code
that come with that,
so we're just using connection.execute here.
To add a movie, we need to simply insert,
so we need to call this query with those two parameters.
So again, we will do with connection and then,
we are going to do connection.execute,
and we will call the insert_movies query in that,
and then passing the two parameters,
title and release_timestamp.
Remember that this has to be a topple of arguments
and we are passing them into insert_movies
where the question marks are.
So we're creating a new movie with this title,
release_timestamp, and zero for the watched value.
So by default, it's not going to be watched.
To get movies,
things start to get a little more interesting.
Let's assume that we always pass in false for upcoming,
so at the moment, we are going to implement this function,
assuming that we never get upcoming=True,
and all they have to do is get all the movies.
So, we can do with connection,
then we create our cursor,
so we'll say cursor=connection.cursor,
and notice that this is optional in a way
as we've discussed in the past,
but I like creating my cursor here so that it's really clear
to anybody who's reading this function
that we are using a cursor to get results.
Then we'll do cursor.execute(select_all_movies),
and finally we will return cursor.fetchall.
This is going to get all the rows from the result set
and return them as something like a list.
Remember that you can still do this if you prefer,
so you can cursor=connection.execute(select_all_movies)
and then return cursor.fetchall.
This is exactly the same thing
because connection.execute creates a cursor for you.
However, I just like the explicitness of this code
so I'm gonna leave it as this.
So what happens if upcoming=True,
well that means that instead of selecting all movies,
we need to call select_upcoming_movies.
And so, we are going to have an if statement here,
if upcoming, and here we will put pass,
else we will call cursor.execute with select_all_movies.
This here is another benefit
of creating the cursor beforehand, it already exists,
we can use it in both branches of the if statement,
as you'll see in the moment.
If you created using connection.execute,
then you're gonna have two assignment operations
in either if statements.
So I like this better.
What we're gonna do here is we're going to first of all,
drag the timestamp of right now, right this moment.
That is what we need to pass in to select upcoming movies.
When we have today's timestamp,
we can pass it in, and this query
will give us the movies whose release_timestamp
is greater than the timestamp right now.
So in order to get today's timestamp, we're going to create
a variable today_timestamp for example,
and this is going to be using the datetime module,
datetime.datetime.today.timestamp.,
so this is a little bit long here,
we are accessing the datetime module,
inside the datetime module,
we're accessing the datetime class,
that has a method called today
that gives us a new datetime object with today's date in it,
and datetime objects have the timestamp method in them
that allow us to get the time and date of that object
as a number of seconds.
So now that we've got that,
we can do cursor.execute(select_upcoming_movies)
with today_timestamp as an argument,
remembering to make this a topple
so we need that comma there.
We are going to look into the datetime module
in much more detail later on in the course,
we're actually going to have a whole section dedicated
to the datetime module, how you can use it,
how PostgreSQL works with datetime internally et cetera,
but for now, this is everything we need
in order to get the current timestamp.
I'll also link a block post in the resources section
of this lecture,
to tell you a little bit more about datetimes,
but again, everything that is covered in that block post,
we will cover later on in the course as well.
So now that we've got this,
we have the ability to select upcoming movies
or all movies.
And then we're returning everything
so this function is complete.
Let's skip the watch_movie function
and go down to get_watched_movies,
here we're gonna do with connection,
and then we're simply going to get our cursor,
again the cause, I like the explicit code
that tells me that a cursor is being generated.
We'll do cursor.execute(select_watched_movies),
and then, return cursor.fetchall.
So nothing new there, just running that query.
Finally, we have the watch_movie function.
And for this one, we need to go into the table
and change a row, or as SQL likes to put it,
we need to update a row.
And so we need to go and into this table here,
change the watched column of a particular row
or particular movie.
So we're going to learn how to use the update SQL command
in the next video, and then we will come back
and add that query and function.
All right, thank you for watching,
I'll see you in the next video.