1
00:00:00,000 --> 00:00:03,990
As you build applications in Python, you are probably going to find that you use

2
00:00:04,000 --> 00:00:08,990
databases quite a bit and so you will probably find some value in having a

3
00:00:09,000 --> 00:00:12,990
normalized interface for working with your databases.

4
00:00:13,000 --> 00:00:15,990
I have built such a normalized interface and you are welcome to use mine but I

5
00:00:16,000 --> 00:00:19,990
would suggest that you simply use this as a model and build your own.

6
00:00:20,000 --> 00:00:23,990
Build one that works for you, that works for the way that you like to work with databases.

7
00:00:24,000 --> 00:00:26,990
So I will show you mine as an example.

8
00:00:27,000 --> 00:00:32,990
It's called bwDB.py and it's in the lib folder, in the 19 Projects folder in

9
00:00:33,000 --> 00:00:38,990
your Exercise Files. So go ahead and open that up and we will maximize this so

10
00:00:39,000 --> 00:00:39,990
that we can take a look.

11
00:00:40,000 --> 00:00:47,990
Of course it imports SQLite3. This is a library for using SQLite3, like I said.

12
00:00:48,000 --> 00:00:53,990
For most of my web applications these days I am using SQLite3, because it's

13
00:00:54,000 --> 00:00:58,990
small, it's fast, it's self-contained, it's easy and it's very robust and

14
00:00:59,000 --> 00:01:02,990
reliable. I've never had any problems with it.

15
00:01:03,000 --> 00:01:07,990
So there is a class here called bwDB and the first thing in it is the

16
00:01:08,000 --> 00:01:12,990
constructor, and the constructor simply takes this keyword arguments and it

17
00:01:13,000 --> 00:01:14,990
looks for the file name and the table name.

18
00:01:15,000 --> 00:01:18,990
You will notice that in each of these methods I have what's called a docstring.

19
00:01:19,000 --> 00:01:24,990
If the first line in a function or a method is just a string by itself this is

20
00:01:25,000 --> 00:01:28,990
picked up by Python's documentation protocol and it's called a docstring.

21
00:01:29,000 --> 00:01:32,990
I use it to describe how the function works.

22
00:01:33,000 --> 00:01:36,990
So this is the constructor method, the table is for the CRUD methods and you don't

23
00:01:37,000 --> 00:01:39,990
have to use that. I will get to that in a moment.

24
00:01:40,000 --> 00:01:44,990
And the file name is for connecting to the database file.

25
00:01:45,000 --> 00:01:46,990
Here is one of my workhorse methods.

26
00:01:47,000 --> 00:01:51,990
It's called sql_do. This is for non-selective type queries.

27
00:01:52,000 --> 00:01:56,990
You just pass it in some SQL, you pass it in some parameters, and it does its job.

28
00:01:57,000 --> 00:02:01,990
This next one, sql_query, is the same thing except that it also works as a

29
00:02:02,000 --> 00:02:07,990
generator and it will iterate through set of results, and of course each

30
00:02:08,000 --> 00:02:09,990
result is a row factory.

31
00:02:10,000 --> 00:02:15,990
You will notice that the constructor assigns file name here and file name is

32
00:02:16,000 --> 00:02:20,990
actually a property, which is defined down below, and we have seen this technique

33
00:02:21,000 --> 00:02:26,990
already. And so in the setter when a file name gets assigned it sets the

34
00:02:27,000 --> 00:02:28,990
attribute in the object.

35
00:02:29,000 --> 00:02:32,990
It also connects to the database and it sets up the row query.

36
00:02:33,000 --> 00:02:38,990
So this is actually a constructor of sorts but it allows you to change files if

37
00:02:39,000 --> 00:02:42,990
you want to. If you are using the object and you decide that you need to assign

38
00:02:43,000 --> 00:02:47,990
this object to a different database file, first of course, it will call the

39
00:02:48,000 --> 00:02:51,990
Destructor which closes the old database, and then it'll call this constructor

40
00:02:52,000 --> 00:02:54,990
for the file name and it will connect to the new database.

41
00:02:55,000 --> 00:03:01,990
This works really, really well.

42
00:03:02,000 --> 00:03:05,990
We have and sql_query method for returning a single row, and we have an

43
00:03:06,000 --> 00:03:09,990
sql_query method for returning a single value.

44
00:03:10,000 --> 00:03:11,990
I find these very useful and I use them a lot.

45
00:03:12,000 --> 00:03:15,990
Again depending on how your pattern of dealing with database is, you might

46
00:03:16,000 --> 00:03:19,990
find different things useful and I would suggest that you use those.

47
00:03:20,000 --> 00:03:24,990
Now we get into the CRUD methods. CRUD stands for create, retrieve, update and delete.

48
00:03:25,000 --> 00:03:28,990
These are the basic four functions of a database.

49
00:03:29,000 --> 00:03:33,990
So this is the getrec that just would be the retrieve part of CRUD, and for my

50
00:03:34,000 --> 00:03:41,990
CRUD methods I depend on their being a column in the table that's called id.

51
00:03:42,000 --> 00:03:45,990
So all of my tables and all of my applications that are going to use these

52
00:03:46,000 --> 00:03:48,990
methods must have a column called id.

53
00:03:49,000 --> 00:03:53,990
I typically create this column in SQLite using the integer primary key feature

54
00:03:54,000 --> 00:03:59,990
which makes that column an equivalent for SQLite's internal row id.

55
00:04:00,000 --> 00:04:03,990
This all just works very nicely together and you will see examples of this in

56
00:04:04,000 --> 00:04:10,990
the projects that we are going to look at in this chapter.

57
00:04:11,000 --> 00:04:14,990
Getrecs is a method that returns all of the rows in the table and it returns it

58
00:04:15,000 --> 00:04:18,990
as a generator with row factories.

59
00:04:19,000 --> 00:04:23,990
Insert inserts a record and this uses a dictionary for the record.

60
00:04:24,000 --> 00:04:25,990
This is actually very interesting.

61
00:04:26,000 --> 00:04:30,990
This method constructs the SQL based on the names of the keys in the dictionary

62
00:04:31,000 --> 00:04:32,990
object that gets passed.

63
00:04:33,000 --> 00:04:36,990
So it takes a little bit of care to work with it but when you use it with care

64
00:04:37,000 --> 00:04:41,990
it works very, very well.

65
00:04:42,000 --> 00:04:46,990
This next method works the same way. Again it constructs the sql_query based on

66
00:04:47,000 --> 00:04:52,990
the keys in the dictionary object that gets passed and this will update a

67
00:04:53,000 --> 00:04:58,990
particular record based on an id that's passed.

68
00:04:59,000 --> 00:05:04,990
Finally we have the delete method where it deletes based on the id, deletes the

69
00:05:05,000 --> 00:05:11,990
row from the table based on the id and a countrecs method that simply gives us a

70
00:05:12,000 --> 00:05:13,990
count of all the records in the table.

71
00:05:14,000 --> 00:05:17,990
This is a very fast operation to do in SQL.

72
00:05:18,000 --> 00:05:25,990
Most database engines including SQLite are very highly optimized for count operations.

73
00:05:26,000 --> 00:05:29,990
So this method offloads that work to the database engine and allows it to

74
00:05:30,000 --> 00:05:31,990
happen very quickly.

75
00:05:32,000 --> 00:05:36,990
Finally we have the property accessors for the filename property and a close

76
00:05:37,000 --> 00:05:39,990
method for closing the database.

77
00:05:40,000 --> 00:05:49,990
Then we have a test method for testing and we will go ahead and run that.

78
00:05:50,000 --> 00:05:52,990
And that creates a database in memory.

79
00:05:53,000 --> 00:05:59,990
Sqlite has a feature where if you give the filename as this with colons

80
00:06:00,000 --> 00:06:02,990
on either side of it, that whole thing is the filename then it will

81
00:06:03,000 --> 00:06:04,990
create a database in memory.

82
00:06:05,000 --> 00:06:07,990
Creates a table, inserts into the table, reads from the table, updates a table.

83
00:06:08,000 --> 00:06:12,990
Exercises all of the CRUD methods.

84
00:06:13,000 --> 00:06:18,990
So that is my example of a normalized database interface. I find this very

85
00:06:19,000 --> 00:06:23,990
useful and you'll see an example of it here in this chapter of how I use this.

86
00:06:24,000 --> 00:06:28,990
It makes it that much easier for me to work with databases in my applications,

87
00:06:29,000 --> 00:06:32,990
and these days in the little web applications that I might be working on it

88
00:06:33,000 --> 00:06:43,000
saves me a lot of time in work and allows me to focus on the logic of the code.