Adding a little database – about 15 minutes

We want to be able to have our application back up information from the host devices on our network. Devices, plural. And I think that if I had to list out those devices every time I needed to back them up I’d probably give up or forget something. Our solution so we don’t have to remember everything is to use a little database to act as our persistent memory.

Python comes with the SQLite database which is a great tool for our job at hand. We are going to start using SQLite in order to remember the details about our network devices.

We’ll start by adding some code to the top of our program. This is just a step along the way to a better program, but it will get us working through a database. Here’s the code to add:

It seems like a bunch of odd stuff to be adding to our little program. Some of the code is to create a database (a one-time task, but we’ll make sure the database is available by always [re-]creating it). Let’s break down the steps and take a look at them.

First we are going to assign the name of the file on disk that is our database to our variable database_name. We’ll have SQLite use this filename to store all of the data we give it.

Next we create an instance of the Path class that points to database_name. Using this Path instance we can then see if the database already exists. Since we’re only doing our development, we are going to remove the database if it already exists.

Then, by executing sqlite3.connect() we are creating a connection to our SQLite database. Using the connection we can perform actual database operations like inserting and updating data. Next we create a cursor object and assign it so our c variable. The cursor is the object that we can use to perform the actual database operations. You can think of the cursor as being almost like ssh-ing to the database. Once the cursor is established you can send commands through the cursor to the database.

This is an example of sending a command to the database. Note that we are using our cursor object to execute the command in the database. The command creates our devices table. We are going to store our list of network devices in the devices table.

The next statement can be a little confusing. The data structures used are kind of advanced programming concepts. But they’re handy so we’re gonna go ahead and use them.

This multiline statement is creating an array of information about our network devices (although it’s just one device in this case). Think of this array as your list of devices. The array itself is denoted with the [ ]square brackets. Within the array there is one element (all three literal strings inside the parentheses together are the one array element). Each element is actually a list of data elements. In this case the element contains a host name (er, IP address), user name, and password. Each list is denoted with the ( ) parentheses. In the next step we’ll see how these lists are used.

We saw how we can use the cursor’s execute() method to execute some DDL (Data Definition Language) statements like create table. Now we are going to dive into DML (Data Manipulation Language). The executemany() method expects us to give it an SQL statement (e.g., INSERT INTO...) and an array of data. The function is going to go through our one-item array, devices in this case, and it will execute the SQL statement for each element in the array.

The question marks inside the SQL statement seem a little odd at first. They are actually placeholders inside this parameterized query. Python will be replacing the question marks with the values from our array. In effect, Python will execute the following two SQL statements:

Executing the SQL statements with executemany() tells Python to write into the database. However, SQLite is a database engine that supports the idea of being able to rollback a transaction so that the database doesn’t get fouled up. As a result, nothing is permanently written to the database unless it is committed. In your Python code you can tell SQLite that everything is okay and that it should be saved permanently on disk by executing:

Note that the commit() method is in the connection object. You can infer from this that SQLite will be permanently applying all changes that have happened on that connection object. In our case, the commit() will be persisting the CREATE TABLE and the INSERT INTO statements.

At this point we have now created the database and we have permanently written some data into the database. Not too shabby for just a few lines of code.

Next we will be retrieving the list of devices from our devices table and we’ll display some basic info on the screen. To do that we need to execute() a SQL SELECT statement and then we need to loop through the results from that SQL.

First we’ll use the cursor to execute the SELECT:

We are using our cursor object, c, to ask SQLite to give us all of the data records that are in the devices table (it’s a basic SQL statement to get the contents of a data table). SQLite will then create a set of rows of data. We can access those rows of data using fetchone() or fetchall(). I prefer to use fetchone() because it is possible that you’ll be getting back a lot of rows and fetching everything at once could, potentially, cause memory problems. (It’s not very likely, but it’s wise to be safe now and simply not run into a problem later.)

We are going to put each row of data into our memory variable named row. Since the cursor will return None when it gets to the end of the data we’ll use that to stop our looping at the end of the data. To get us into the loop and accessing data we need our row to have an initial value. Assigning it an empty array will give it a value and then we’ll get looping through our vast list of network devices.

A note about Python and indentation. The level of indentation indicates to
Python some of the structure of your code. For example, on the line after
the while statement the row = c.fetchone() statement is indented with
four spaces.
The if statement on the next line also is indented
with four spaces. Python understands this to mean that both
statements are at the same level of logic. So they will each be
executed. In contrast, after the if statement there are some variable
assignment
statements that are indented with two tabs. Python will only execute these
statements when the if statement evaluates to True. They second tab
in the indentation indicates that the statements are subordinate to the
if.

And now we’re looping. Cruising through our list of devices and making sure we can identify each one. For now, we’re just going to print the device info on the screen as our own confirmation. Once we confirm that we are correctly accessing the data then we can start to make use of the data.

Let’s take a look at the details of the loop. We start our loop with a logical, true/false condition: is the row object equal to None? None is a special value in Python that indicates an empty variable. (Note that this is not the same as a variable that contains an empty string…an empty string is still a value, which is not the same as None.) Our loop is going to keep going, potentially forever, as long as row contains some values, meaning that our row does not contain Python’s special None value.

Now that we’re in our loop we’re going to get our first row of data out of our database. We do this by telling our c cursor object to fetchone() data row. We assign that value to our row variable. The fetchone() method is going to return None when there is no more data and that is what will terminate our loop.

If fetchone() does not return None then we are going to go into the if statement. In this ifstatement we are reading data out of row that contains an array of all of the columns from our SELECTstatement. Because we wrote SELECT * SQLite understood that we wanted to retrieve all of the data from each row with the data elements in the order we defined when we did CREATE TABLE. So each row will contain the host_name, user_name, and password in that order. Since our row is an array (and the first element in the array is item #0) we can access the data by using numeric subscripts. For example, when we want to access the first element in the row array we specify row[0]. Since we declared host_name as the first column in our table we can access it by referring to row[0] and then storing that data into our memory variable host_name. Then we do the same to move user_name and password from the cursor into memory variables.

At the end of our loop we use Python’s print() function to show off the fact that we have extracted some data. Note that we’re doing some fancy string formatting here too. We can use this feature to have Python substitute our data into our string nicely. In this case, we have string data (host_name and user_name). To merge and those values we call the Python string class’s format(). We put our format specifiers (e.g., {0}) in the format string and give format() a list of values that get merged into the format string. The {0} format specifier in the format string basically tells Python “put a string variable here”. Python goes through the format string from left-to-right and looking at the format specifiers and replacing them with the values from the list of values in order. You can do a lot more with format specifiers than this simple example shows.

Lastly we need to call conn.close() in order to close our connection to the database. This lets Python know that we’re finished using the conn variable to access our SQLite database. This is generally a good practice because you’re giving back resources to your operating system.

A further note about Python and indentation. conn.close() is
unindented to the same level as our while statement. Python understands
that after it finishes working its way through the while loop that it must
next execute the conn.close() call.

Wow! Super cool! We now have a program that can connect to an SSH host and execute commends plus it can remember the hosts by using a database that is persistent on our local hard drive. It seems like it might be nap time. Or, time to be…