While the Python db-api is completely functional, it is not always well-suited for a particular application. This is an example of a wrapper module that the author uses for many projects.
- [Instructor] For many purposes the Python DB API is all you'll need. If you're doing a significant amount of database coding, you'll want something more robust. Here in Komodo I've opened a working copy of bwDB.py from chapter 15 of the exercise files. This is a database interface that I've created and used over many years and in several different languages. It's not exhaustive but it provides sufficient functionality for most uses and it's extensible.
This interface uses SQLite which I use a lot. I find it both powerful and convenient and in my cases a more reliable and robust choice than a client server system like MySQL. So let's take a look at this interface down here on line eight I import sqlite3 and here's my constructor, and you'll notice that in each of my functions I have what's called a doc string. This is a Python paradigm I may have mentioned it before, where if you put a string as the first line, just a bare literal string as the first line of any function, method, or class, that serves as documentation for that function, method, or class.
It's traditional to use the triple quoted strings like this and so that's what I've done. So, this is the constructor and this doc string talks about how to call the constructor and it describes it so that I'll always know how to use this when I come back to it years later perhaps after not using it for a while. The filename uses property decorators and this is something I haven't talked about because in actuality I find them rather counterintuitive and they're a little bit useful because properties are a useful thing, but I don't really care for the implementation too much, but I left it in here because originally it was an experiment and I left it in because it's good to show you how it works.
So you'll notice way down here towards the bottom of my class I have methods for dealing with the filename property and these methods use the property decorator and setter and deleter decorators. And so the first one is just the getter, it's for reading the filename and it simply returns the filename. The setter is for assigning a value to the filename, and when I assign a value to this filename I actually do a fresh connect to the database and I setup the row factory which we'll talk about a little bit later.
And then the filename deleter I actually just close the database. And so this can be considered useful because all of this happens just by setting or reading the filename property. In practice it's not fully implemented. The deleter doesn't always get called under all circumstances and it's a bit of a mess. And this is one of the areas where Python still needs to do some work and I expect that they will in a future version. But for right now that's how it works and I left it in here so I could demonstrate it.
Then I set the table, and the table is used for the CRUD methods and we'll talk about that a little bit later. First, I'm just going to show you the SQL methods, and so I have SQL do which is for non-select queries. Queries that do not return any values and you see it's just very simple. You pass it SQL, you pass it parameters, it passes those directly to the DB API. And then we have do with no commit. You'll notice that I have a commit after that do, and I have no commit after this one.
In many database languages commits are required, in some they're not, and in any case the whole point of commits is to be able to do several database commands and then commit them all at once so that it creates efficiency and you can reduce the number of disk IO operations. And so I have both with commit and without commit for all of my write operations. SQL query, it passes an SQL statement to execute and then it returns a generator so that you can use this with a for loop.
I use yield here for this. You can query a single row and it calls fetchone, or you can query a single value and it returns just the first column from the first row. And then there's a commit statement and now we have the CRUD section. So CRUD is C-R-U-D and it stands for create read update delete, it's the basic four functions of any database system. And this is one of the most useful things I feel about having this library, is that I have these CRUD methods, and you'll see in my unit test down below, how convenient these are to use.
And so I have a record, a single record, get multiple records with a yield that returns a generator, I have a insert without commit and here I take this record, these are all record based so you pass these a dictionary and they return dictionaries and these dictionaries have all the column names in the keys. And so I pass a dictionary to this insert and it introspects the dictionary, gets all of the keys, builds the SQL query, and again this is relatively simple in Python and then it executes it, and it returns the inserted row id and the SQL is called lastrowid, rather in the SQLite database driver it's called lastrowid.
And then I have a version of it with a commit, so that was the no commit version above. And so this is the with commit version. It simply calls the no commit version and then it does a commit, and returns the lastrowid. And just like insert I have an update that also is dictionary based and it builds our SQL based on the properties of the dictionary and it calls execute and then I have the version down below here with commit.
And then I have a delete and likewise it builds the SQL and executes the delete and then I have the version with commit. I have countrecs which does a select count and returns one value. And then I have these properties and close. So that's really it. It's a relatively simple database interface, and here's our test so you can see how this is typically implemented. I have a list of dictionaries for the records.
Oh, you'll notice here that my filename, this is specific to SQLite, the filename is this special string, the word memory surrounded by colons and this creates an in memory database so we don't actually have to create a file. And this is great for testing stuff. If you want to do a file based one then you need to un-comment this stuff so that it can create the file and remove the file. So we print out the version, we create the database, and this is done by constructing the DB object, the bwDB object, and then we have do for creating the table, that's sql_do, and we use SQL insert to insert records into the table.
And again here I'm just looping over our list of dictionary entries and calling insert, very convenient. I don't have to write any SQL for that. I count the number of records in the table with countrecs, I read from the table again with a for loop getrecs that returns a generator, so this is really easy. And I can update the table, I can change the value of one of the rows in the table, I can insert an extra row using db.insert and a dictionary entry.
Again, this is all just really straight forward. And I can then delete the new entry and I can retrieve the records either using the CRUD method or the SQL query method. And now I'm going to run this and I'm going to show you a couple of interesting things about this. I'm going to go ahead and bring this up so we see a lot more of it, there we go. I don't think that's everything. Is that everything? Yeah, that's everything. So we print out the version, we create the database file in memory. Create the table. Insert three rows into the table, there's now three rows.
Then we read from the table and the entries look like this. Update the table, changing two to a capital two, capital T-W-O, insert an extra row, delete the row, there's now four rows. Now we delete it, and now there's three rows, and then we print out the rows. You notice I printed out in two different ways and I want to show you about this. You recall that when I construct the database up here, I set the row factory to the built in row factory it allows you to read the rows in several different ways.
It adds one small layer of complexity that I actually have to tell it what type I want for those rows. And so you notice that I use dict here, I use dict there to tell it I want a dictionary back. In this case I'm just giving you the basic row so you can see this, and so we get these row objects down here. If instead I tell it I want it to be a dictionary, then we'll get dictionary entries. I'm going to run it here and you see we get dictionary entries.
Or if I tell it, say that I want tuple and I run it and I get tuples, or lists. And this is because it's a factory and it's not an actual dictionary row that it's being returned in. Sometimes and well want it in different types and so that's why I decided to do it this way. So I know this is a lot to absorb, and please you know, go back over this, take your time, read it, feel free to email me with questions. But, I wanted to show you this and then in our next lesson I'm going to show you a practical application where I actually use this.
So, I find this sort of database wrapper very useful, and like I said I've implemented it in most of the languages that I use on a daily basis including Python and C++. And so that I can write code based on these libraries and it all makes sense to me. And as time goes on I'll refine them as I did when I started this class. So, as I said in the next lesson I'll show you a practical application where I've used this module.
- Python anatomy
- Types and values
- Conditionals and operators
- Building loops
- Defining functions
- Python data structures: lists, tuples, sets, and more
- Creating classes
- Handling exceptions
- Working with strings
- File input/output (I/O)
- Creating modules
- Integrating a database with Python db-api