1
00:00:00,000 --> 00:00:05,990
The basic four functions of a database are considered create, retrieve, update

2
00:00:06,000 --> 00:00:08,990
and delete, which conveniently spells the word CRUD.

3
00:00:09,000 --> 00:00:14,990
In the Exercise Files, you'll notice file called sqlite3-crud.py.

4
00:00:15,000 --> 00:00:15,990
We're going to use that as a starting point here.

5
00:00:16,000 --> 00:00:18,990
So we'll just make a working copy of it and we'll call it

6
00:00:19,000 --> 00:00:26,990
sqlite3-crud-working.py. If you like, you can name it something simpler and

7
00:00:27,000 --> 00:00:32,990
we'll open that up and you'll notice that this is a rather complete file.

8
00:00:33,000 --> 00:00:37,990
Rather than type all this stuff, I just give it to you and we'll take a tour

9
00:00:38,000 --> 00:00:38,990
of it and we'll see how it works.

10
00:00:39,000 --> 00:00:44,990
You'll notice that at the top of the file here in imports sqlite3 and down here

11
00:00:45,000 --> 00:00:45,990
we have the main function.

12
00:00:46,000 --> 00:00:50,990
Normally I would put this at the top, but really the object of our exercise here

13
00:00:51,000 --> 00:00:52,990
is those functions at the top.

14
00:00:53,000 --> 00:00:55,990
So, we'll get to those in a moment and you'll notice a few little print

15
00:00:56,000 --> 00:00:56,990
statements here as comments.

16
00:00:57,000 --> 00:01:01,990
So, we create the table and it's called test and this is the same as in the

17
00:01:02,000 --> 00:01:03,990
creating a database lesson.

18
00:01:04,000 --> 00:01:07,990
You'll notice that we also have our row factory in here and that's also

19
00:01:08,000 --> 00:01:11,990
described in the creating a database lesson, and then we create the rows and

20
00:01:12,000 --> 00:01:13,990
we're using this insert function that's defined above.

21
00:01:14,000 --> 00:01:16,990
I don't call it create because I think of create as being creating the table or

22
00:01:17,000 --> 00:01:17,990
creating the database.

23
00:01:18,000 --> 00:01:22,990
I call it insert. CRUD doesn't work as well if there is an I instead of the C,

24
00:01:23,000 --> 00:01:26,990
but this works for me to call it insert and actually it matches the

25
00:01:27,000 --> 00:01:31,990
corresponding SQL also, which is insert. And then we retrieve a row based on a key.

26
00:01:32,000 --> 00:01:36,990
There's the key and there is the key, we update rows and changing their values

27
00:01:37,000 --> 00:01:43,990
and we delete rows and if we run this, go ahead and run it, it creates a table,

28
00:01:44,000 --> 00:01:47,990
it creates rows and then it shows them to you and it retrieves rows and it

29
00:01:48,000 --> 00:01:51,990
retrieves them based on those keys and there is the dictionary objects and it

30
00:01:52,000 --> 00:01:57,990
updates rows, and there it changed these two, and it deletes rows and this is all

31
00:01:58,000 --> 00:01:58,990
that's left after it's deleted them.

32
00:01:59,000 --> 00:02:02,990
So, this just shows us that it's working and now we can look at exactly how

33
00:02:03,000 --> 00:02:04,990
all this stuff works.

34
00:02:05,000 --> 00:02:08,990
When you're building a specific application and your specific application has

35
00:02:09,000 --> 00:02:13,990
specific database tables and specific schemas, a lot of times it's convenient

36
00:02:14,000 --> 00:02:19,990
to create specific functionality in your program that deals with those objects

37
00:02:20,000 --> 00:02:22,990
and normally you're going to do it in an object-oriented method and we'll look

38
00:02:23,000 --> 00:02:27,990
at that in another lesson in this chapter, but for now, here's how you can do

39
00:02:28,000 --> 00:02:28,990
it in a simple way.

40
00:02:29,000 --> 00:02:31,990
I would not write production code this way.

41
00:02:32,000 --> 00:02:36,990
There's no error checking. Using functions I'm passing the database handle around.

42
00:02:37,000 --> 00:02:40,990
The way I would create actual applications is going to be in more

43
00:02:41,000 --> 00:02:42,990
object-oriented manner. We'll look at that in another lesson.

44
00:02:43,000 --> 00:02:46,990
For now, our purpose here is just to look at how can we do these individual

45
00:02:47,000 --> 00:02:52,990
tasks in very simple way. And so, for the insert function, I simply pass it a

46
00:02:53,000 --> 00:02:56,990
dictionary and the dictionary has the data that's going to be inserted and then

47
00:02:57,000 --> 00:03:02,990
up here we can use this little outline feature in the Eclipse workflow and I

48
00:03:03,000 --> 00:03:06,990
look at the insert function. And you'll see it takes that row which is a

49
00:03:07,000 --> 00:03:13,990
dictionary object and it passes the individual elements into the placeholders in

50
00:03:14,000 --> 00:03:16,990
the SQLite3 execute method.

51
00:03:17,000 --> 00:03:21,990
So, the execute method takes as its first argument a string of SQL and in

52
00:03:22,000 --> 00:03:24,990
that argument you can put in placeholders and there's actually a couple of ways to do that.

53
00:03:25,000 --> 00:03:27,990
I'm showing you the simple way here with the question mark and these are

54
00:03:28,000 --> 00:03:32,990
positional and then it takes a list as the second argument and so this has to

55
00:03:33,000 --> 00:03:37,990
be a list or a tuple. In this case a tuple, and so it need to be as one argument

56
00:03:38,000 --> 00:03:40,990
which is why we're passing it a tuple with parentheses around it so that it's

57
00:03:41,000 --> 00:03:45,990
grouped as one argument and then that has positional ordered parameters.

58
00:03:46,000 --> 00:03:49,990
The first one is going to correspond with the first question mark and the second one

59
00:03:50,000 --> 00:03:53,990
is going to correspond with the second question mark and each of these is simply

60
00:03:54,000 --> 00:03:56,990
dereferencing the dictionary object that's passed into the function.

61
00:03:57,000 --> 00:03:58,990
So, this very simple.

62
00:03:59,000 --> 00:04:02,990
in the main, all we need to do is we call it like this, insert and the first

63
00:04:03,000 --> 00:04:06,990
parameter here is DB, which is the database object, so that the insert function

64
00:04:07,000 --> 00:04:11,990
can access the database and the second parameter is this dictionary object and

65
00:04:12,000 --> 00:04:14,990
obviously, in your code, you could create that separately.

66
00:04:15,000 --> 00:04:18,990
It could be derived from all sorts of sources and for our purposes, we're simply

67
00:04:19,000 --> 00:04:20,990
creating the dictionary object on the fly here.

68
00:04:21,000 --> 00:04:24,990
And so we call that four times and that inserts the data and we'll see that

69
00:04:25,000 --> 00:04:27,990
right here at the top of our results, Create rows.

70
00:04:28,000 --> 00:04:32,990
Notice that I have this little display rows function and that looks like this.

71
00:04:33,000 --> 00:04:37,990
It basically executes a select and it prints out the results from the dictionary

72
00:04:38,000 --> 00:04:39,990
object, because we're using the row factory.

73
00:04:40,000 --> 00:04:41,990
So, that's our insert function.

74
00:04:42,000 --> 00:04:43,990
Our retrieve function is like this.

75
00:04:44,000 --> 00:04:49,990
It simply passes a key and it gets a row object in return and so it prints that

76
00:04:50,000 --> 00:04:53,990
out as a dictionary and so that looks like this here in our results.

77
00:04:54,000 --> 00:04:57,990
These are the dictionary objects that are returned by those two calls to retrieve.

78
00:04:58,000 --> 00:05:02,990
So, retrieve is really just this part here and when we look at it up in our code,

79
00:05:03,000 --> 00:05:06,990
it takes a key and I'm calling that key t1, I could just name it key if I

80
00:05:07,000 --> 00:05:12,990
want and it calls execute with this SQL, which is basically select an entire row

81
00:05:13,000 --> 00:05:17,990
from the test table where t1 equals question mark, and then it passes this one

82
00:05:18,000 --> 00:05:18,990
positional argument out.

83
00:05:19,000 --> 00:05:23,990
Now this has to be a list or a tuple, and so I create a tuple here and remember

84
00:05:24,000 --> 00:05:25,990
it's the comma that creates a tuple.

85
00:05:26,000 --> 00:05:27,990
It is not the parentheses.

86
00:05:28,000 --> 00:05:29,990
The parentheses are just for grouping.

87
00:05:30,000 --> 00:05:34,990
So, if you want a tuple with one element, it needs to be like this parentheses

88
00:05:35,000 --> 00:05:38,990
and then the object and then a comma and then the closed parentheses and this is

89
00:05:39,000 --> 00:05:41,990
a case where I need a tuple with just one object.

90
00:05:42,000 --> 00:05:46,990
It calls db.execute with this SQL and that returns a cursor and then I use the

91
00:05:47,000 --> 00:05:52,990
cursor with the fetchone method in the cursor object from SQLite3, and that'll

92
00:05:53,000 --> 00:05:56,990
simply fetch one result because that's what I'm asking for with this

93
00:05:57,000 --> 00:05:57,990
particular retrieve.

94
00:05:58,000 --> 00:06:00,990
Obviously, if you want a method that retrieves more than one object, you can

95
00:06:01,000 --> 00:06:05,990
do that and we'll look at an example of that in the database object lesson in this chapter.

96
00:06:06,000 --> 00:06:08,990
Now, returning to our main function, we update rows and it's really very

97
00:06:09,000 --> 00:06:12,990
similar. We're passing a dictionary to an update function and the update

98
00:06:13,000 --> 00:06:18,990
function simply has an execute statement with the SQL here, update test, set

99
00:06:19,000 --> 00:06:23,990
this value, set that value and it passes it a tuple with those elements from the

100
00:06:24,000 --> 00:06:25,990
dictionary that we're passing here.

101
00:06:26,000 --> 00:06:26,990
You see the pattern here?

102
00:06:27,000 --> 00:06:28,990
Delete works the same way.

103
00:06:29,000 --> 00:06:31,990
It gets a key and it has the SQL.

104
00:06:32,000 --> 00:06:36,990
It says delete from test where t1 equals question mark and then it's this one

105
00:06:37,000 --> 00:06:38,990
element tuple and it calls commit.

106
00:06:39,000 --> 00:06:43,990
We have to call commit every time we do anything that can change the database.

107
00:06:44,000 --> 00:06:48,990
So, for update, we call commit, for delete, we call commit and also for insert

108
00:06:49,000 --> 00:06:54,990
we're calling commit and so there is our retrieve. There is our update and

109
00:06:55,000 --> 00:06:57,990
after update, we display the rows and after delete, we display the rows and

110
00:06:58,000 --> 00:07:02,990
there is our result, update, change those two, and there they are changing the

111
00:07:03,000 --> 00:07:08,990
two up here and Delete rows, we're deleting two rows, we delete one and we

112
00:07:09,000 --> 00:07:12,990
delete three with these keys and all we have left is two and four.

113
00:07:13,000 --> 00:07:16,990
So, those are the major four functions of a database.

114
00:07:17,000 --> 00:07:22,990
Insert, retrieve, update and delete and you can see with SQLite and Python,

115
00:07:23,000 --> 00:07:23,990
it's incredibly simple.

116
00:07:24,000 --> 00:07:28,990
It's really just passing the SQL and getting the right parameters into the

117
00:07:29,000 --> 00:07:39,000
SQL and it just works.