Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
As you build applications in Python, you are probably going to find that you use databases quite a bit and so you will probably find some value in having a normalized interface for working with your databases. I have built such a normalized interface and you are welcome to use mine but I would suggest that you simply use this as a model and build your own. Build one that works for you, that works for the way that you like to work with databases. So I will show you mine as an example. It's called bwDB.py and it's in the lib folder, in the 19 Projects folder in your Exercise Files. So go ahead and open that up and we will maximize this so that we can take a look.
Of course it imports SQLite3. This is a library for using SQLite3, like I said. For most of my web applications these days I am using SQLite3, because it's small, it's fast, it's self-contained, it's easy and it's very robust and reliable. I've never had any problems with it. So there is a class here called bwDB and the first thing in it is the constructor, and the constructor simply takes this keyword arguments and it looks for the file name and the table name.
You will notice that in each of these methods I have what's called a docstring. If the first line in a function or a method is just a string by itself this is picked up by Python's documentation protocol and it's called a docstring. I use it to describe how the function works. So this is the constructor method, the table is for the CRUD methods and you don't have to use that. I will get to that in a moment. And the file name is for connecting to the database file.
Here is one of my workhorse methods. It's called sql_do. This is for non-selective type queries. You just pass it in some SQL, you pass it in some parameters, and it does its job. This next one, sql_query, is the same thing except that it also works as a generator and it will iterate through set of results, and of course each result is a row factory. You will notice that the constructor assigns file name here and file name is actually a property, which is defined down below, and we have seen this technique already. And so in the setter when a file name gets assigned it sets the attribute in the object.
It also connects to the database and it sets up the row query. So this is actually a constructor of sorts but it allows you to change files if you want to. If you are using the object and you decide that you need to assign this object to a different database file, first of course, it will call the Destructor which closes the old database, and then it'll call this constructor for the file name and it will connect to the new database. This works really, really well.
We have and sql_query method for returning a single row, and we have an sql_query method for returning a single value. I find these very useful and I use them a lot. Again depending on how your pattern of dealing with database is, you might find different things useful and I would suggest that you use those. Now we get into the CRUD methods. CRUD stands for create, retrieve, update and delete. These are the basic four functions of a database. So this is the getrec that just would be the retrieve part of CRUD, and for my CRUD methods I depend on their being a column in the table that's called id.
So all of my tables and all of my applications that are going to use these methods must have a column called id. I typically create this column in SQLite using the integer primary key feature which makes that column an equivalent for SQLite's internal row id. This all just works very nicely together and you will see examples of this in the projects that we are going to look at in this chapter. Getrecs is a method that returns all of the rows in the table and it returns it as a generator with row factories.
Insert inserts a record and this uses a dictionary for the record. This is actually very interesting. This method constructs the SQL based on the names of the keys in the dictionary object that gets passed. So it takes a little bit of care to work with it but when you use it with care it works very, very well. This next method works the same way. Again it constructs the sql_query based on the keys in the dictionary object that gets passed and this will update a particular record based on an id that's passed.
Finally we have the delete method where it deletes based on the id, deletes the row from the table based on the id and a countrecs method that simply gives us a count of all the records in the table. This is a very fast operation to do in SQL. Most database engines including SQLite are very highly optimized for count operations. So this method offloads that work to the database engine and allows it to happen very quickly.
Finally we have the property accessors for the filename property and a close method for closing the database. Then we have a test method for testing and we will go ahead and run that. And that creates a database in memory. Sqlite has a feature where if you give the filename as this with colons on either side of it, that whole thing is the filename then it will create a database in memory. Creates a table, inserts into the table, reads from the table, updates a table.
Exercises all of the CRUD methods. So that is my example of a normalized database interface. I find this very useful and you'll see an example of it here in this chapter of how I use this. It makes it that much easier for me to work with databases in my applications, and these days in the little web applications that I might be working on it saves me a lot of time in work and allows me to focus on the logic of the code.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 105484 Viewers
56 Video lessons · 117150 Viewers
71 Video lessons · 86382 Viewers
131 Video lessons · 41287 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.