WEBVTT

1
00:00:00.250 --> 00:00:01.700
- [Jose] Hi guys and welcome back.

2
00:00:01.700 --> 00:00:02.533
In this video,

3
00:00:02.533 --> 00:00:05.500
we're going to be writing
our database.py file.

4
00:00:05.500 --> 00:00:08.260
So we've got here some of the
main queries we'll be using,

5
00:00:08.260 --> 00:00:10.450
we're gonna be adding more as we go along,

6
00:00:10.450 --> 00:00:14.030
but here we are and we need
to import datetime module,

7
00:00:14.030 --> 00:00:15.180
we're gonna need that later on,

8
00:00:15.180 --> 00:00:17.590
and we will cover exactly how to use it.

9
00:00:17.590 --> 00:00:20.900
And we'll also need to import sqlite3.

10
00:00:20.900 --> 00:00:23.230
Then as we know, in order
to connect to SQLite,

11
00:00:23.230 --> 00:00:25.030
we need to create a connection,

12
00:00:25.030 --> 00:00:27.310
so we're gonna do sqlite3.connect,

13
00:00:27.310 --> 00:00:31.260
and we're going to use data.db
as our connection place.

14
00:00:31.260 --> 00:00:34.484
So we're gonna create data.db
file in our workspace,

15
00:00:34.484 --> 00:00:36.800
and we're going to use
that as our database.

16
00:00:36.800 --> 00:00:39.520
Then, I like creating
functions for everything

17
00:00:39.520 --> 00:00:42.500
that our database is going to
do with our Python application

18
00:00:42.500 --> 00:00:44.680
so we're gonna start at that.

19
00:00:44.680 --> 00:00:47.340
So I've gone ahead and
created my five functions

20
00:00:47.340 --> 00:00:49.860
that are going to do the
main things in this file.

21
00:00:49.860 --> 00:00:51.450
I've got create_tables,

22
00:00:51.450 --> 00:00:53.860
and I know that at the
moment we only have one table

23
00:00:53.860 --> 00:00:57.050
in our application, but it's
uncommon to have a single table

24
00:00:57.050 --> 00:00:59.050
and later on, our
application is going to grow

25
00:00:59.050 --> 00:01:00.470
and have more tables

26
00:01:00.470 --> 00:01:02.680
so that's why I've
called it create_tables.

27
00:01:02.680 --> 00:01:03.880
Then we've got add_movie,

28
00:01:03.880 --> 00:01:06.780
that takes in a movie title
and the release_timestamp,

29
00:01:06.780 --> 00:01:08.980
and it's going to
essentially add the movie

30
00:01:08.980 --> 00:01:10.150
to the database.

31
00:01:10.150 --> 00:01:14.670
We've got get_movies which has
a parameter upcoming=False,

32
00:01:14.670 --> 00:01:17.840
so that will be used to get
the movies that are upcoming

33
00:01:17.840 --> 00:01:21.430
or all the movies if
we pass false in here.

34
00:01:21.430 --> 00:01:23.740
We've got watch_movie which
take in the movie title

35
00:01:23.740 --> 00:01:25.960
and it's gonna mark it as watched.

36
00:01:25.960 --> 00:01:27.890
And we've got get_watched_movies
that is gonna go

37
00:01:27.890 --> 00:01:31.020
and find all the movies that
have already been watched.

38
00:01:31.020 --> 00:01:33.140
So let's go ahead and
implement these functions.

39
00:01:33.140 --> 00:01:36.970
In create_table, all we have
to do is do with connection,

40
00:01:36.970 --> 00:01:39.000
and then connection.execute,

41
00:01:39.000 --> 00:01:43.540
and we're gonna pass in the
create_movies_table query,

42
00:01:43.540 --> 00:01:46.800
so that we will create that
when we run this function.

43
00:01:46.800 --> 00:01:47.980
That's really it.

44
00:01:47.980 --> 00:01:49.690
Remember, we don't have to create cursors

45
00:01:49.690 --> 00:01:50.820
or anything in here,

46
00:01:50.820 --> 00:01:53.520
because we are not actually
selecting any information,

47
00:01:53.520 --> 00:01:55.580
and we don't really need
the overhead of creating

48
00:01:55.580 --> 00:01:57.820
our own cursor as well
as the lines of code

49
00:01:57.820 --> 00:01:58.920
that come with that,

50
00:01:58.920 --> 00:02:01.950
so we're just using
connection.execute here.

51
00:02:01.950 --> 00:02:04.660
To add a movie, we need to simply insert,

52
00:02:04.660 --> 00:02:08.000
so we need to call this query
with those two parameters.

53
00:02:08.000 --> 00:02:11.790
So again, we will do
with connection and then,

54
00:02:11.790 --> 00:02:14.013
we are going to do connection.execute,

55
00:02:15.040 --> 00:02:19.350
and we will call the
insert_movies query in that,

56
00:02:19.350 --> 00:02:21.650
and then passing the two parameters,

57
00:02:21.650 --> 00:02:24.220
title and release_timestamp.

58
00:02:24.220 --> 00:02:26.710
Remember that this has to
be a topple of arguments

59
00:02:26.710 --> 00:02:29.450
and we are passing them into insert_movies

60
00:02:29.450 --> 00:02:31.280
where the question marks are.

61
00:02:31.280 --> 00:02:33.330
So we're creating a new
movie with this title,

62
00:02:33.330 --> 00:02:36.820
release_timestamp, and
zero for the watched value.

63
00:02:36.820 --> 00:02:39.680
So by default, it's not
going to be watched.

64
00:02:39.680 --> 00:02:40.640
To get movies,

65
00:02:40.640 --> 00:02:42.590
things start to get a
little more interesting.

66
00:02:42.590 --> 00:02:46.680
Let's assume that we always
pass in false for upcoming,

67
00:02:46.680 --> 00:02:49.160
so at the moment, we are going
to implement this function,

68
00:02:49.160 --> 00:02:51.900
assuming that we never get upcoming=True,

69
00:02:51.900 --> 00:02:54.150
and all they have to do
is get all the movies.

70
00:02:54.150 --> 00:02:56.913
So, we can do with connection,

71
00:02:58.060 --> 00:03:00.350
then we create our cursor,

72
00:03:00.350 --> 00:03:03.330
so we'll say cursor=connection.cursor,

73
00:03:03.330 --> 00:03:05.850
and notice that this is optional in a way

74
00:03:05.850 --> 00:03:07.220
as we've discussed in the past,

75
00:03:07.220 --> 00:03:10.430
but I like creating my cursor
here so that it's really clear

76
00:03:10.430 --> 00:03:12.280
to anybody who's reading this function

77
00:03:12.280 --> 00:03:15.810
that we are using a cursor to get results.

78
00:03:15.810 --> 00:03:18.167
Then we'll do
cursor.execute(select_all_movies),

79
00:03:19.610 --> 00:03:22.433
and finally we will
return cursor.fetchall.

80
00:03:23.796 --> 00:03:27.170
This is going to get all
the rows from the result set

81
00:03:27.170 --> 00:03:30.160
and return them as something like a list.

82
00:03:30.160 --> 00:03:33.630
Remember that you can still
do this if you prefer,

83
00:03:33.630 --> 00:03:38.180
so you
can
cursor=connection.execute(select_all_movies)

84
00:03:38.180 --> 00:03:39.150
and then return cursor.fetchall.

85
00:03:39.150 --> 00:03:41.260
This is exactly the same thing

86
00:03:41.260 --> 00:03:44.540
because connection.execute
creates a cursor for you.

87
00:03:44.540 --> 00:03:47.860
However, I just like the
explicitness of this code

88
00:03:47.860 --> 00:03:49.540
so I'm gonna leave it as this.

89
00:03:49.540 --> 00:03:51.990
So what happens if upcoming=True,

90
00:03:51.990 --> 00:03:54.870
well that means that instead
of selecting all movies,

91
00:03:54.870 --> 00:03:57.410
we need to call select_upcoming_movies.

92
00:03:57.410 --> 00:03:59.950
And so, we are going to
have an if statement here,

93
00:03:59.950 --> 00:04:03.653
if upcoming, and here we will put pass,

94
00:04:05.310 --> 00:04:08.383
else we will call cursor.execute
with select_all_movies.

95
00:04:09.370 --> 00:04:11.130
This here is another benefit

96
00:04:11.130 --> 00:04:14.110
of creating the cursor
beforehand, it already exists,

97
00:04:14.110 --> 00:04:16.490
we can use it in both
branches of the if statement,

98
00:04:16.490 --> 00:04:17.780
as you'll see in the moment.

99
00:04:17.780 --> 00:04:20.170
If you created using connection.execute,

100
00:04:20.170 --> 00:04:22.120
then you're gonna have
two assignment operations

101
00:04:22.120 --> 00:04:23.420
in either if statements.

102
00:04:23.420 --> 00:04:24.700
So I like this better.

103
00:04:24.700 --> 00:04:27.540
What we're gonna do here is
we're going to first of all,

104
00:04:27.540 --> 00:04:31.700
drag the timestamp of right
now, right this moment.

105
00:04:31.700 --> 00:04:35.170
That is what we need to pass
in to select upcoming movies.

106
00:04:35.170 --> 00:04:37.440
When we have today's timestamp,

107
00:04:37.440 --> 00:04:39.160
we can pass it in, and this query

108
00:04:39.160 --> 00:04:41.630
will give us the movies
whose release_timestamp

109
00:04:41.630 --> 00:04:44.490
is greater than the timestamp right now.

110
00:04:44.490 --> 00:04:47.020
So in order to get today's
timestamp, we're going to create

111
00:04:47.020 --> 00:04:49.310
a variable today_timestamp for example,

112
00:04:49.310 --> 00:04:51.750
and this is going to be
using the datetime module,

113
00:04:51.750 --> 00:04:56.290
datetime.datetime.today.timestamp.,

114
00:04:56.290 --> 00:04:58.010
so this is a little bit long here,

115
00:04:58.010 --> 00:04:59.930
we are accessing the datetime module,

116
00:04:59.930 --> 00:05:00.950
inside the datetime module,

117
00:05:00.950 --> 00:05:03.560
we're accessing the datetime class,

118
00:05:03.560 --> 00:05:05.970
that has a method called today

119
00:05:05.970 --> 00:05:10.230
that gives us a new datetime
object with today's date in it,

120
00:05:10.230 --> 00:05:12.880
and datetime objects have
the timestamp method in them

121
00:05:12.880 --> 00:05:16.530
that allow us to get the
time and date of that object

122
00:05:16.530 --> 00:05:18.760
as a number of seconds.

123
00:05:18.760 --> 00:05:20.100
So now that we've got that,

124
00:05:20.100 --> 00:05:24.330
we can do
cursor.execute(select_upcoming_movies)

125
00:05:24.330 --> 00:05:26.640
with today_timestamp as an argument,

126
00:05:26.640 --> 00:05:28.920
remembering to make this a topple

127
00:05:28.920 --> 00:05:30.790
so we need that comma there.

128
00:05:30.790 --> 00:05:33.270
We are going to look
into the datetime module

129
00:05:33.270 --> 00:05:35.740
in much more detail
later on in the course,

130
00:05:35.740 --> 00:05:38.140
we're actually going to have
a whole section dedicated

131
00:05:38.140 --> 00:05:40.420
to the datetime module,
how you can use it,

132
00:05:40.420 --> 00:05:44.230
how PostgreSQL works with
datetime internally et cetera,

133
00:05:44.230 --> 00:05:46.280
but for now, this is everything we need

134
00:05:46.280 --> 00:05:49.780
in order to get the current timestamp.

135
00:05:49.780 --> 00:05:52.300
I'll also link a block post
in the resources section

136
00:05:52.300 --> 00:05:53.470
of this lecture,

137
00:05:53.470 --> 00:05:55.640
to tell you a little bit
more about datetimes,

138
00:05:55.640 --> 00:05:58.100
but again, everything that is
covered in that block post,

139
00:05:58.100 --> 00:06:00.340
we will cover later on
in the course as well.

140
00:06:00.340 --> 00:06:01.173
So now that we've got this,

141
00:06:01.173 --> 00:06:03.560
we have the ability to
select upcoming movies

142
00:06:03.560 --> 00:06:04.720
or all movies.

143
00:06:04.720 --> 00:06:06.410
And then we're returning everything

144
00:06:06.410 --> 00:06:08.430
so this function is complete.

145
00:06:08.430 --> 00:06:10.670
Let's skip the watch_movie function

146
00:06:10.670 --> 00:06:12.730
and go down to get_watched_movies,

147
00:06:12.730 --> 00:06:14.620
here we're gonna do with connection,

148
00:06:14.620 --> 00:06:16.800
and then we're simply
going to get our cursor,

149
00:06:16.800 --> 00:06:19.690
again the cause, I like the explicit code

150
00:06:19.690 --> 00:06:21.930
that tells me that a
cursor is being generated.

151
00:06:21.930 --> 00:06:23.927
We'll do
cursor.execute(select_watched_movies),

152
00:06:25.130 --> 00:06:27.403
and then, return cursor.fetchall.

153
00:06:28.270 --> 00:06:31.110
So nothing new there,
just running that query.

154
00:06:31.110 --> 00:06:33.210
Finally, we have the watch_movie function.

155
00:06:33.210 --> 00:06:35.720
And for this one, we
need to go into the table

156
00:06:35.720 --> 00:06:39.600
and change a row, or
as SQL likes to put it,

157
00:06:39.600 --> 00:06:42.150
we need to update a row.

158
00:06:42.150 --> 00:06:45.230
And so we need to go and
into this table here,

159
00:06:45.230 --> 00:06:48.540
change the watched column
of a particular row

160
00:06:48.540 --> 00:06:49.740
or particular movie.

161
00:06:49.740 --> 00:06:53.410
So we're going to learn how
to use the update SQL command

162
00:06:53.410 --> 00:06:55.310
in the next video, and
then we will come back

163
00:06:55.310 --> 00:06:57.470
and add that query and function.

164
00:06:57.470 --> 00:06:59.050
All right, thank you for watching,

165
00:06:59.050 --> 00:07:00.613
I'll see you in the next video.