Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The basic four functions of a database are considered create, retrieve, update and delete, which conveniently spells the word CRUD. In the Exercise Files, you'll notice file called sqlite3-crud.py. We're going to use that as a starting point here. So we'll just make a working copy of it and we'll call it sqlite3-crud-working.py. If you like, you can name it something simpler and we'll open that up and you'll notice that this is a rather complete file.
Rather than type all this stuff, I just give it to you and we'll take a tour of it and we'll see how it works. You'll notice that at the top of the file here in imports sqlite3 and down here we have the main function. Normally I would put this at the top, but really the object of our exercise here is those functions at the top. So, we'll get to those in a moment and you'll notice a few little print statements here as comments. So, we create the table and it's called test and this is the same as in the creating a database lesson.
You'll notice that we also have our row factory in here and that's also described in the creating a database lesson, and then we create the rows and we're using this insert function that's defined above. I don't call it create because I think of create as being creating the table or creating the database. I call it insert. CRUD doesn't work as well if there is an I instead of the C, but this works for me to call it insert and actually it matches the corresponding SQL also, which is insert. And then we retrieve a row based on a key. There's the key and there is the key, we update rows and changing their values and we delete rows and if we run this, go ahead and run it, it creates a table, it creates rows and then it shows them to you and it retrieves rows and it retrieves them based on those keys and there is the dictionary objects and it updates rows, and there it changed these two, and it deletes rows and this is all that's left after it's deleted them.
So, this just shows us that it's working and now we can look at exactly how all this stuff works. When you're building a specific application and your specific application has specific database tables and specific schemas, a lot of times it's convenient to create specific functionality in your program that deals with those objects and normally you're going to do it in an object-oriented method and we'll look at that in another lesson in this chapter, but for now, here's how you can do it in a simple way.
I would not write production code this way. There's no error checking. Using functions I'm passing the database handle around. The way I would create actual applications is going to be in more object-oriented manner. We'll look at that in another lesson. For now, our purpose here is just to look at how can we do these individual tasks in very simple way. And so, for the insert function, I simply pass it a dictionary and the dictionary has the data that's going to be inserted and then up here we can use this little outline feature in the Eclipse workflow and I look at the insert function. And you'll see it takes that row which is a dictionary object and it passes the individual elements into the placeholders in the SQLite3 execute method.
So, the execute method takes as its first argument a string of SQL and in that argument you can put in placeholders and there's actually a couple of ways to do that. I'm showing you the simple way here with the question mark and these are positional and then it takes a list as the second argument and so this has to be a list or a tuple. In this case a tuple, and so it need to be as one argument which is why we're passing it a tuple with parentheses around it so that it's grouped as one argument and then that has positional ordered parameters. The first one is going to correspond with the first question mark and the second one is going to correspond with the second question mark and each of these is simply dereferencing the dictionary object that's passed into the function.
So, this very simple. in the main, all we need to do is we call it like this, insert and the first parameter here is DB, which is the database object, so that the insert function can access the database and the second parameter is this dictionary object and obviously, in your code, you could create that separately. It could be derived from all sorts of sources and for our purposes, we're simply creating the dictionary object on the fly here. And so we call that four times and that inserts the data and we'll see that right here at the top of our results, Create rows.
Notice that I have this little display rows function and that looks like this. It basically executes a select and it prints out the results from the dictionary object, because we're using the row factory. So, that's our insert function. Our retrieve function is like this. It simply passes a key and it gets a row object in return and so it prints that out as a dictionary and so that looks like this here in our results. These are the dictionary objects that are returned by those two calls to retrieve.
So, retrieve is really just this part here and when we look at it up in our code, it takes a key and I'm calling that key t1, I could just name it key if I want and it calls execute with this SQL, which is basically select an entire row from the test table where t1 equals question mark, and then it passes this one positional argument out. Now this has to be a list or a tuple, and so I create a tuple here and remember it's the comma that creates a tuple. It is not the parentheses. The parentheses are just for grouping.
So, if you want a tuple with one element, it needs to be like this parentheses and then the object and then a comma and then the closed parentheses and this is a case where I need a tuple with just one object. It calls db.execute with this SQL and that returns a cursor and then I use the cursor with the fetchone method in the cursor object from SQLite3, and that'll simply fetch one result because that's what I'm asking for with this particular retrieve. Obviously, if you want a method that retrieves more than one object, you can do that and we'll look at an example of that in the database object lesson in this chapter.
Now, returning to our main function, we update rows and it's really very similar. We're passing a dictionary to an update function and the update function simply has an execute statement with the SQL here, update test, set this value, set that value and it passes it a tuple with those elements from the dictionary that we're passing here. You see the pattern here? Delete works the same way. It gets a key and it has the SQL. It says delete from test where t1 equals question mark and then it's this one element tuple and it calls commit.
We have to call commit every time we do anything that can change the database. So, for update, we call commit, for delete, we call commit and also for insert we're calling commit and so there is our retrieve. There is our update and after update, we display the rows and after delete, we display the rows and there is our result, update, change those two, and there they are changing the two up here and Delete rows, we're deleting two rows, we delete one and we delete three with these keys and all we have left is two and four.
So, those are the major four functions of a database. Insert, retrieve, update and delete and you can see with SQLite and Python, it's incredibly simple. It's really just passing the SQL and getting the right parameters into the SQL and it just works.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104602 Viewers
56 Video lessons · 116518 Viewers
71 Video lessons · 85710 Viewers
131 Video lessons · 41016 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.