1
00:00:00,000 --> 00:00:02,990
When you are writing an application that uses a database, sometimes it's a good

2
00:00:03,000 --> 00:00:06,990
idea to create a class that handles that particular schema.

3
00:00:07,000 --> 00:00:10,990
Let's take a look at an example of how you can do that in Python.

4
00:00:11,000 --> 00:00:15,990
We'll start by making a working copy of sqlite3-class.py, and we will call it

5
00:00:16,000 --> 00:00:22,990
sqlite3-class-working.py, and we'll go ahead and open up that working copy.

6
00:00:23,000 --> 00:00:27,990
And here we have a complete working example of a class. It's very simple.

7
00:00:28,000 --> 00:00:32,990
It's just a few lines of code, and how we access that class through this interface.

8
00:00:33,000 --> 00:00:37,990
We'll go ahead and run it and you can see what it does.

9
00:00:38,000 --> 00:00:42,990
This is just an example of testing this particular class, so it creates a table

10
00:00:43,000 --> 00:00:45,990
test and it creates some rows and retrieves the rows.

11
00:00:46,000 --> 00:00:48,990
It tests these four functions of the database.

12
00:00:49,000 --> 00:00:53,990
So if we look at our interface, we create our object by passing in a file name

13
00:00:54,000 --> 00:00:57,990
and a table name, and that will actually initialize the database.

14
00:00:58,000 --> 00:01:01,990
And then we create the table using some sql.

15
00:01:02,000 --> 00:01:05,990
Then we insert some rows using dictionary objects.

16
00:01:06,000 --> 00:01:10,990
Then we retrieve rows, just using keys, and it will retrieve dictionary objects.

17
00:01:11,000 --> 00:01:14,990
And retrieve test, there is our dictionary objects.

18
00:01:15,000 --> 00:01:19,990
We update a couple of rows, again, using dictionary objects, and there they are.

19
00:01:20,000 --> 00:01:23,990
These dictionary objects could instead be specific classes that you've created

20
00:01:24,000 --> 00:01:25,990
for your database application.

21
00:01:26,000 --> 00:01:29,990
And we delete rows with keys. And you'll notice that every time we do one of

22
00:01:30,000 --> 00:01:34,990
these operations, we're printing out all the rows in a database using the

23
00:01:35,000 --> 00:01:36,990
database handle itself as an iterator.

24
00:01:37,000 --> 00:01:39,990
So this is a nice little convenient interface.

25
00:01:40,000 --> 00:01:43,990
First of all, the constructor takes these two arguments as named arguments

26
00:01:44,000 --> 00:01:46,990
using the kwargs pattern.

27
00:01:47,000 --> 00:01:52,990
And the first one is the filename and it assigns it to a filename property.

28
00:01:53,000 --> 00:01:55,990
And the second one is the table and it assigns it to a table property.

29
00:01:56,000 --> 00:01:58,990
And you'll notice that these do not have underscores, because these are meant to

30
00:01:59,000 --> 00:02:01,990
be accessible to the outside world.

31
00:02:02,000 --> 00:02:06,990
Down here, I use the property decorator to allow the filename to be assigned

32
00:02:07,000 --> 00:02:11,990
like that, and when it gets assigned it actually connects to the database and it

33
00:02:12,000 --> 00:02:13,990
sets up the row_factory.

34
00:02:14,000 --> 00:02:18,990
And when you delete the file name, it actually closes the database.

35
00:02:19,000 --> 00:02:23,990
And so this allows you to use this kind of a pattern for assigning the filename,

36
00:02:24,000 --> 00:02:27,990
and you can even do this on the object level, using the object, and it will go

37
00:02:28,000 --> 00:02:30,990
ahead and initialize the database like that.

38
00:02:31,000 --> 00:02:37,990
Likewise, with the table, it sets the table name and that one is just really

39
00:02:38,000 --> 00:02:42,990
simply setting this _table variable, and if you delete it, it defaults

40
00:02:43,000 --> 00:02:47,990
to test, so that there is always a table name. Because it will kind of break the

41
00:02:48,000 --> 00:02:51,990
code here if there isn't a table name, if the table name is blank, and you'll

42
00:02:52,000 --> 00:02:53,990
see that as we go through.

43
00:02:54,000 --> 00:02:57,990
So the insert function is very simple, and you'll notice it goes off the end

44
00:02:58,000 --> 00:02:58,990
of the screen here.

45
00:02:59,000 --> 00:03:01,990
So I'll just go ahead and reformat that.

46
00:03:02,000 --> 00:03:06,990
So the sql, insert into it, and you'll notice that we have to use a

47
00:03:07,000 --> 00:03:11,990
replacement in the string using format, because the question mark pattern

48
00:03:12,000 --> 00:03:14,990
doesn't work for the table name.

49
00:03:15,000 --> 00:03:18,990
That's true across the board in every database entry that I have ever used.

50
00:03:19,000 --> 00:03:22,990
If you want to use the positional parameters, you cannot do that for the table name,

51
00:03:23,000 --> 00:03:27,990
and so I allow the table name to be set using the format here and it uses

52
00:03:28,000 --> 00:03:29,990
the underscore table.

53
00:03:30,000 --> 00:03:34,990
And then, the row is passed in as a dictionary object and here we make a tuple

54
00:03:35,000 --> 00:03:39,990
out of the two parameters that we are inserting, the values t1 and i1, and they

55
00:03:40,000 --> 00:03:41,990
are positional like that in order.

56
00:03:42,000 --> 00:03:44,990
So insert looks like that. Retrieve looks like this.

57
00:03:45,000 --> 00:03:50,990
It returns a dictionary object using the fetch 1 method of the database cursor

58
00:03:51,000 --> 00:03:56,990
and the sql is just a simple select * from the table name.

59
00:03:57,000 --> 00:03:59,990
We are replacing the table name using format and we are using positional

60
00:04:00,000 --> 00:04:03,990
arguments over here for the key, and again, this is single element tuple and

61
00:04:04,000 --> 00:04:08,990
so it has to have that comma. Because the comma is what creates the tuple, not the parenthesis.

62
00:04:09,000 --> 00:04:14,990
Update, it takes a row as a dictionary object and it passes the menu using that

63
00:04:15,000 --> 00:04:17,990
tuple, and the same pattern here.

64
00:04:18,000 --> 00:04:23,990
We're replacing the table name using the string format method, and delete uses a key

65
00:04:24,000 --> 00:04:29,990
and there is the single element tuple there, and all of these methods that

66
00:04:30,000 --> 00:04:32,990
change the database, they all have this db.commit.

67
00:04:33,000 --> 00:04:36,990
There is a display rows method that we are not actually using, which does

68
00:04:37,000 --> 00:04:41,990
what we were doing in our other examples. It simply prints them out using a print function.

69
00:04:42,000 --> 00:04:46,990
But here is how we are looking at the data in this example. We are using this iter method.

70
00:04:47,000 --> 00:04:48,990
That's a special method in Python.

71
00:04:49,000 --> 00:04:53,990
If you put this in your class, it allows your object to be used as an iterator.

72
00:04:54,000 --> 00:05:00,990
So it has two underscores __iter__ for its name, and other than that, it works

73
00:05:01,000 --> 00:05:01,990
just like any method.

74
00:05:02,000 --> 00:05:08,990
And it's a generator because it uses yield, and this allows it to operate as an iterator.

75
00:05:09,000 --> 00:05:12,990
So it simply yields a dictionary of the row, and that allows us to call it like

76
00:05:13,000 --> 00:05:17,990
this, for row in db:print(row), and we get these results.

77
00:05:18,000 --> 00:05:22,990
So we'll go ahead and we'll save this and we'll run it again, and we see there

78
00:05:23,000 --> 00:05:25,990
we have our results.

79
00:05:26,000 --> 00:05:29,990
Finally, it's worth noting that because of this pattern down here at the bottom,

80
00:05:30,000 --> 00:05:32,990
if __name__="__main__": main(),

81
00:05:33,000 --> 00:05:38,990
this entire file will work just fine as a module in Python.

82
00:05:39,000 --> 00:05:44,990
You could simply say import sqlite3-class-working and you would have access to

83
00:05:45,000 --> 00:05:49,990
this class and be able to use this in another file.

84
00:05:50,000 --> 00:05:54,990
And this main function down here will be completely ignored, because when you

85
00:05:55,000 --> 00:06:00,990
import it that way, this is no longer true and so main will not be called.

86
00:06:01,000 --> 00:06:05,990
So it's a useful pattern to be able to write a module like this and to have a

87
00:06:06,000 --> 00:06:09,990
class that you are going to use throughout a project or even to distribute it to the world.

88
00:06:10,000 --> 00:06:16,990
And to test it using a main function that is really just there for testing purposes.

89
00:06:17,000 --> 00:06:20,990
So this is a useful pattern and it's one that you'll probably use.

90
00:06:21,000 --> 00:06:27,990
Here we have a custom class for working with a specific database, and a lot

91
00:06:28,000 --> 00:06:31,990
of different techniques that you can use in building a class like that for yourself.

92
00:06:32,000 --> 00:06:35,990
It does the major four functions and it does a couple of other things.

93
00:06:36,000 --> 00:06:41,990
The object itself is useable as an iterator, because we included an iterator method.

94
00:06:42,000 --> 00:06:48,990
It uses properties for setting the filename that make it easy to change files if you want to.

95
00:06:49,000 --> 00:06:52,990
So here we have an example of how you can create a custom class for your own

96
00:06:53,000 --> 00:06:56,990
database schema, for your own project, using Python's object-oriented features

97
00:06:57,000 --> 00:07:07,000
to make your programming task a lot easier.