1 00:00:00,000 --> 00:00:04,990 The four basic functions of a database are create, retrieve, update and delete, 2 00:00:05,000 --> 00:00:06,990 often pronounced as CRUD. 3 00:00:07,000 --> 00:00:11,990 This is an example of an application that does those basic four functions. 4 00:00:12,000 --> 00:00:13,990 Of course there is a lot of functions that people perform with database, there is a 5 00:00:14,000 --> 00:00:17,990 lot of things that they are used for, and as you are writing code over the years 6 00:00:18,000 --> 00:00:20,990 you will be writing databases for a lot different purposes. 7 00:00:21,000 --> 00:00:23,990 But they all basically break down to those four functions. 8 00:00:24,000 --> 00:00:27,990 So those are the things that if you pay attention to those things, then building the 9 00:00:28,000 --> 00:00:30,990 more complex applications becomes a little bit easier. 10 00:00:31,000 --> 00:00:33,990 So this is an application that just does that. 11 00:00:34,000 --> 00:00:37,990 It manages as a very simple database and it simply allows you to add records, 12 00:00:38,000 --> 00:00:41,990 edit records, and delete records, and view records. 13 00:00:42,000 --> 00:00:45,990 So those are the basic four functions create, retrieve, update and delete. 14 00:00:46,000 --> 00:00:50,990 What this application does as it manages a Testimonials database that I use in 15 00:00:51,000 --> 00:00:53,990 my web site and you will see if you look at my web site, you will see little boxes 16 00:00:54,000 --> 00:00:56,990 like this one over here that have testimonials. 17 00:00:57,000 --> 00:01:00,990 For our purposes I have replaced the testimonials with some witty little quotes 18 00:01:01,000 --> 00:01:05,990 that I have collected over the years and here is the database itself. 19 00:01:06,000 --> 00:01:14,990 This has got 14 records in the database and if you wanted to add something and 20 00:01:15,000 --> 00:01:23,990 print a byline and click Add, you will see that it adds it to the database right 21 00:01:24,000 --> 00:01:29,990 here, and then I can edit it, if I want to. 22 00:01:30,000 --> 00:01:33,990 Update it and there it is updated in the database. 23 00:01:34,000 --> 00:01:37,990 And if I want to delete it, I can delete it. 24 00:01:38,000 --> 00:01:44,990 It's all very simple to operate and very clear, and this comes from it being a very 25 00:01:45,000 --> 00:01:49,990 simple application and also from it being designed clearly. 26 00:01:50,000 --> 00:01:55,990 So, here in the Example files, under Projects and under testimonials, you will 27 00:01:56,000 --> 00:02:00,990 find db.py and that is that script right there. 28 00:02:01,000 --> 00:02:04,990 See down here,it says db.py version 1.13. 29 00:02:05,000 --> 00:02:06,990 So that is this script right here. 30 00:02:07,000 --> 00:02:11,990 So we are going to take a little tour through this script and see how it works. 31 00:02:12,000 --> 00:02:16,990 At the top here you see that I imported a few libraries that start with bw. 32 00:02:17,000 --> 00:02:22,990 These libraries are available in the lib folder under Projects and these are 33 00:02:23,000 --> 00:02:31,990 libraries that I have built over the years, and there is this global namespace container. 34 00:02:32,000 --> 00:02:36,990 I tend to do this in all of my web scripts because it helps to keep state in 35 00:02:37,000 --> 00:02:42,990 otherwise stateless environment, and it helps me to keep my global name space cleaned up. 36 00:02:43,000 --> 00:02:49,990 So I use a dictionary for that in Python, and I have here a call to init, which 37 00:02:50,000 --> 00:02:54,990 initializes these variables, again helping to keep state and to set up my various 38 00:02:55,000 --> 00:02:59,990 objects and to read my configuration file and things like that, and then I look 39 00:03:00,000 --> 00:03:02,990 for a variable a in the CGI variables. 40 00:03:03,000 --> 00:03:08,990 If I find it, I run something called dispatch. Otherwise I load the first main page. 41 00:03:09,000 --> 00:03:13,990 So what dispatch does is it simply looks for what is the action, a stands 42 00:03:14,000 --> 00:03:16,990 for action, what is the action that's been taken and it will dispatch the 43 00:03:17,000 --> 00:03:19,990 proper function for that. 44 00:03:20,000 --> 00:03:23,990 If these get any bigger I actually have a library that runs a jump table that 45 00:03:24,000 --> 00:03:24,990 will do something like this. 46 00:03:25,000 --> 00:03:27,990 So I tend to do a lot of work on the web. 47 00:03:28,000 --> 00:03:29,990 I tend to use CGI a lot. 48 00:03:30,000 --> 00:03:34,990 CGI being stateless the way that it is, the web and HTTP being stateless, that 49 00:03:35,000 --> 00:03:41,990 helps to have things like this to manage a little bit of a state machine. 50 00:03:42,000 --> 00:03:44,990 The main part of the application lists these records. 51 00:03:45,000 --> 00:03:49,990 So this is the retrieve part of the four functions of the database. 52 00:03:50,000 --> 00:03:53,990 So the first thing this does is it grabs a count of the records in the 53 00:03:54,000 --> 00:03:58,990 database using the countrecs method in the database library and that very 54 00:03:59,000 --> 00:04:01,990 quickly and efficiently returns a count all of the records and that allows 55 00:04:02,000 --> 00:04:05,990 us to do some math here and figure out how many we are going to display on the page 56 00:04:06,000 --> 00:04:09,990 and how many pages there is going to be and then we set up this little 57 00:04:10,000 --> 00:04:13,990 menu here which is this part here that tells us, we can jump to a specific 58 00:04:14,000 --> 00:04:16,990 page or we can go back and forward. 59 00:04:17,000 --> 00:04:20,990 So that's very useful. 60 00:04:21,000 --> 00:04:24,990 Then we have our little bit of SQL and this actually does the retrieval. 61 00:04:25,000 --> 00:04:30,990 SELECT From ORDER BY byline and LIMIT and OFFSET. 62 00:04:31,000 --> 00:04:38,990 And in SQLite, limit and offset allow us to only get the first so many records 63 00:04:39,000 --> 00:04:44,990 and if it's not on the first page, then to skip forward to an offset first and 64 00:04:45,000 --> 00:04:47,990 then get that many records, and it will do this on any query. 65 00:04:48,000 --> 00:04:50,990 Most modern database engines have some way of doing this. 66 00:04:51,000 --> 00:04:55,990 It's not a part of the SQL standard so they all tend to be a little bit different. 67 00:04:56,000 --> 00:05:00,990 This one, SQLite actually, uses a very similar syntax, I don't think it's exact, 68 00:05:01,000 --> 00:05:05,990 but a very similar syntax to how it's done in MySQL and those are the two really 69 00:05:06,000 --> 00:05:08,990 popular ones for web applications these days. 70 00:05:09,000 --> 00:05:13,990 So that's how we do the paging and we make that list at the bottom of the page. 71 00:05:14,000 --> 00:05:22,990 The pagebar itself with the various links is there and displaying pages is here 72 00:05:23,000 --> 00:05:26,990 and then we get down to the actual actions. 73 00:05:27,000 --> 00:05:31,990 Adding a record to the database, it simply builds this dictionary object and 74 00:05:32,000 --> 00:05:36,990 passes it off to the insert CRUD function in my database library, which I explain 75 00:05:37,000 --> 00:05:39,990 in another movie in this chapter. 76 00:05:40,000 --> 00:05:49,990 Likewise, the delete function calls the delete method in the database library 77 00:05:50,000 --> 00:05:56,990 and the update function calls the update method in the database library. 78 00:05:57,000 --> 00:06:01,990 Using these CRUD methods in my normalized database library allows me to keep 79 00:06:02,000 --> 00:06:04,990 this code very small. 80 00:06:05,000 --> 00:06:08,990 Each of these function uses exactly the same data structure. 81 00:06:09,000 --> 00:06:10,990 It uses this dictionary. 82 00:06:11,000 --> 00:06:15,990 It uses an id field that's called id, and this allows the methods in the 83 00:06:16,000 --> 00:06:19,990 database library to do there job. 84 00:06:20,000 --> 00:06:30,990 So, let's take a quick look at those methods and this is the database library, bwDB. 85 00:06:31,000 --> 00:06:35,990 So the insert method here, it takes the dictionary object and it actually goes 86 00:06:36,000 --> 00:06:39,990 through the dictionary object. 87 00:06:40,000 --> 00:06:45,990 It grabs all of its keys and then uses this generator expression to create a 88 00:06:46,000 --> 00:06:47,990 list of all the values. 89 00:06:48,000 --> 00:06:52,990 And so it has got a list of the keys and it's got a list of the values. 90 00:06:53,000 --> 00:07:00,990 And then it uses those two lists to build the SQL using the strings format 91 00:07:01,000 --> 00:07:05,990 method and then it passes that SQL query off to the database. 92 00:07:06,000 --> 00:07:11,990 So this allows us to use a dictionary object and to build our query based on 93 00:07:12,000 --> 00:07:16,990 the names of the keys in that dictionary object and to use that so that we 94 00:07:17,000 --> 00:07:22,990 can use the same code for different applications that have different database schemas. 95 00:07:23,000 --> 00:07:28,990 So, the same technique is also used for update. 96 00:07:29,000 --> 00:07:35,990 Where again, we build a query based on a list of keys and values. 97 00:07:36,000 --> 00:07:40,990 Keys and values. 98 00:07:41,000 --> 00:07:43,990 The other methods like delete(), they only need the id. 99 00:07:44,000 --> 00:07:46,990 So that makes that a lot easier. 100 00:07:47,000 --> 00:07:50,990 So this is a very useful technique where you can use normalized code for a 101 00:07:51,000 --> 00:07:53,990 number of different applications with different database schemas. 102 00:07:54,000 --> 00:07:58,990 And by using that normalized code and some simple coding conventions, you can 103 00:07:59,000 --> 00:08:03,990 keep an application like this, down to about 250 lines and still have it look 104 00:08:04,000 --> 00:08:14,000 and operated like something a lot bigger and a lot more complex.