Using DBM as a Persistent Dictionary

DBM files originated in UNIX but have been developed over the years for other platforms as well. Python supports several variations. These variations are hidden by the dbm module that automatically determines the best solution based on which libraries are supported by the OS installation at hand. If no native DBM library can be found, a basic, pure Python version is used.

The DBM system is a simplified version of a dictionary in that both the keys and values must be strings, which means that some data conversion and string formatting is necessary if you are using non-string data. The advantages of a DBM database are that it is simple to use, fast, and fairly compact.

You can see how DBM works by revisiting the tool-hire example from Chapter 2. When you last looked at it you were working from a spreadsheet as the master data source. Suppose you decided to migrate the solution to a pure Python application? You would need a storage mechanism for the various data elements.

Recall that the spreadsheet had two sheets, one representing the tools for hire and the other the actual loans by the members. The record formats are shown in Table 3.1.

That design is fine for a human working with a spreadsheet, but if you want to convert it into a full-blown data application you need to overcome a number of issues with it:

• First, there is a lot of duplication between the two entities. The Name, Description, and Owner fields are all duplicated, and therefore need to be changed in two places whenever they are edited.
• Both entities use the ItemID as a key, which suggests the ItemID represents both a Tool and a Loan which is confusing.
• Several fields store names of subscribers to the service, but it would be better to have a separate entity to describe those members and reference that member entity from the other entities.
• Finally, although this started out as a tool-hire application, there is no reason to limit it to tools. The members could just as well borrow books or DVDs or anything else. So rather than restrict it to tools, you can rename the Tool entity as Item. And in keeping with that, you can rename the application to reflect its more generic approach. Call it LendyDB.

With very little effort, you can rearrange things to overcome the issues with the spreadsheet. Table 3.2 shows the resulting database design.

You now have three entities, so you need to store the data in three data files. You can use the DBM format for this because each entity now has a unique identifier field, which, in string format, works well as a DBM key. You need to populate these files with data, and that means reformatting the data from the spreadsheet. You could write a Python program to do that but, because the sample data set is small, it’s easier to just cut and paste the data into the new format. (Or you can extract the files from the LendyDB folder of the Chapter3.zip file from the download site.) Once you have the data you can save it into DBM files quite easily, as shown in the following Try It Out.

Having created your database, you can now use it to read or edit the contents. This is best demonstrated from an interactive session at the Python prompt, so fire up the Python interpreter from the folder where you saved the data files and type the following:

  >>> import dbm
  >>> items = dbm.open('itemdb')
  >>> members = dbm.open('memberdb')
  >>> loans = dbm.open('loandb','w')
  >>> loan2 = loans['2'].decode()
  >>> loan2
  '2,2,5,9/5/2012,1/5/2013'
  >>> loan2 = loan2.split(',')
  >>> loan2
  ['2', '2', '5', '9/5/2012', '1/5/2013']
  >>> item2 = items[loan2[1]].decode().split(',')
  >>> item2
  ['2', 'Lawnmower', 'Tool', '2', '$370', 'Fair', '2012-04-01']
  >>> member2 = members[loan2[2]].decode().split(',')
  >>> member2
  ['5', 'Anne', '[email protected]']
  >>> print('{} borrowed a {} on {}'.format(
  ... member2[1],item2[1],loan2[3]))
  Anne borrowed a Lawnmower on 9/5/2012

With the preceding commands, you opened the three databases, extracted loan number 2 (using decode() to convert from the dbm bytes format into a normal Python string), and split it into its separate fields. You then extracted the corresponding member and item records by using the loan record values as keys. Finally, you printed a message reporting the data in human-readable form.

Of course, you can create new records in the data set, too. Here is how you create a new loan record:

  >>> max(loans.keys()).decode()
  '5'
  >>> key = int(max(loans.keys()).decode()) + 1
  >>> newloan = [str(key),'2','1','4/5/2014']
  >>> loans[str(key)] = ','.join(newloan)
  >>> loans[str(key)]
  b'6,2,1,4/5/2014'

With the preceding code, you used the built-in max() function to find the highest existing key value in the loans database. You then created a new key by converting that maximum value to an integer and adding one. Next, you used the string version of the new key value to create a new loan record. You then wrote that record out to the database using the new key field. Finally, you checked that the new record existed by using the new key value to read the record back.

You can see that DBM files can be used as a database, even with multiple entities. However, if the data is not naturally string-based, or has many fields, extracting the fields and converting to the appropriate format becomes tedious. You can write helper functions or methods to do that conversion for you, but there is an easier way. Python has a module that can store arbitrary Python objects to files and read them back without you having to do any data conversion. It’s time to meet the pickle module.

Using Pickle to Store and Retrieve Objects

The pickle module is designed to convert Python objects into sequences of binary bytes. The object types converted include the basic data types, such as integers and boolean values, as well as system- and user-defined classes and even collections such as lists, tuples, and functions (except those defined using lambda). A few restrictions exist on objects that can be pickled; these are described in the module documentation.

pickle is not of itself a data management solution; it merely converts objects into binary sequences. These sequences can be stored in binary files and read back again so they can be used as a form of data persistence. But pickle does not provide any means to search the stored objects or retrieve a single object from among many stored items. You must read the entire stored inventory back into memory and access the objects that way. pickle is ideal when you just want to save the state of a program so that you can start it up and continue from the same position as before (for example, if you were playing a game).

The pickle module provides several functions and classes, but you normally only use the dump() and load() functions. The dump() function dumps an object (or objects) to a file and the load() function reads an object from a file (usually an object previously written with dump).

To see how this works, you can use the interactive prompt and experiment with the Item data definition from LendyDB in the previous section. You start off by creating a single item and this time, instead of using a single string for all the values, you use a tuple, like this:

  >>> import pickle
  >>> anItem = ['1','Lawnmower','Tool','1','$150','Excellent','2012-01-05']
  >>> with open('item.pickle','wb') as pf:
  ...	pickle.dump(anItem,pf)
  ...
  >>> with open('item.pickle','rb') as pf:
  ...	itemCopy = pickle.load(pf)
  ...
  >>> print(itemCopy)
  ['1', 'Lawnmower', 'Tool', '1', '$150', 'Excellent', '2012-01-05']
  >>>

Notice that you have to use binary file modes for the pickle file. Most importantly, notice that you got a list back from the file, not just a string. Of course, these elements are all strings, so just for fun try pickling some different data types:

  >>> funData = ('a string', True, 42, 3.14159, ['embedded', 'list'])
  >>> with open('data.pickle','wb') as pf:
  ...	pickle.dump(funData, pf)
  ...
  >>> with open('data.pickle','rb') as pf:
  ...	copyData = pickle.load(pf)
  ...
  >>> print (copyData)
  ('a string', True, 42, 3.14159, ['embedded', 'list'])
  >>>

That all worked as expected, and you got back the same data that you put in. The only other thing you need to know about pickle is that it is not secure. It potentially executes objects that get unpickled, so you should never use pickle to read data received from untrusted sources. But for local object persistence in a controlled environment, it does a great job very simply. If you are using pickle in your own projects you should be aware that you can get some pickle-specific exceptions raised so you might want to wrap your code inside a try/except construct.

For your LendyDB project, the big problem with pickle is that you can only access the data by reading the whole file into memory. Wouldn’t it be great if you could have an indexed file of arbitrary Python objects by combining the features of pickle and dbm? It turns out that you can, and the work has been done for you in the shelve module.

Accessing Objects with shelve

The shelve module combines the dbm module’s ability to provide random access to files with pickle’s ability to serialize Python objects. It is not a perfect solution in that the key field must still be a string and the security issue with pickle also applies to shelve, so you must ensure your data sources are safe. Also, like dbm files the module cannot tell if you modify data read into memory, so you must explicitly write any changes back to the file by reassigning to the same key. Finally, dbm files impose some limits around the size of objects they can store and are not designed for concurrent access from, for example, multiple threads or users. However, for many projects, shelve provides a simple, lightweight, and fairly fast solution for storing and accessing data.

So as far as you are concerned, shelve acts just like a dictionary. Almost everything you do with a dictionary you can also do with shelve instances. The only difference is that the data remains on the disk rather than being in memory. This has obvious speed implications, but on the other hand, it means you can work with very large dictionaries even when memory is limited.

Before you build LendyDB with shelve, you’ll experiment with some dummy data that includes a bigger selection of data types, including a user-defined class. The first thing you do is create the shelve database file (or, as they are sometimes known, a shelf):

  >>> shelf = shelve.open('fundata.shelve','c')

The open function takes the same arguments as the dbm version discussed earlier. Because you are creating a new shelf, you use mode c for create. Now you can start adding items to the shelf:

  >>> shelf['tuple'] = (1,2,'a','b',True,False)
  >>> shelf['lists'] = [[1,2,3],[True,False],[3.14159, -66]]

With these commands, you saved two items, each of which contains a mix of Python data types, and shelve happily stored them without any data conversion required by you. You can check that shelve saved the items by reading the values back:

  >>> shelf['tuple']
  (1, 2, 'a', 'b', True, False)
  >>> shelf['lists']
  [[1, 2, 3], [True, False], [3.14159, -66]]

To make the data changes permanent you need to call close (normally you would use a try/finally construct or, unlike dbm, you can use a context manager style):

  >>> shelf.close()
  >>> shelf['tuple']
  Traceback (most recent call last):
 	File "C:Python33libshelve.py", line 111, in __getitem__
 		value = self.cache[key]
  KeyError: 'tuple'

  During handling of the above exception, another exception occurred:

  Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:Python33libshelve.py", line 113, in __getitem__
 	f = BytesIO(self.dict[key.encode(self.keyencoding)])
  File "C:Python33libshelve.py", line 70, in closed
 	raise ValueError('invalid operation on closed shelf')
  ValueError: invalid operation on closed shelf
  >>>

You can see that after closing the shelf you can no longer access the data; you need to reopen the shelf.

Now you can try something slightly more complex. First, you define a class, create some instances, and then store them to a shelf:

  >>> class Test:
  ...	def __init__(self,x,y):
  ...		self.x = x
  ...		self.y = y
  ... def show(self):
  ...	print(self.x, self.y)
  ...
  >>> shelf = shelve.open('test.shelve','c')
  >>> a = Test(1,2)
  >>> a.show()
  1 2
  >>> b = Test('a','b')
  >>> b.show()
  a b
  >>> shelf['12'] = a
  >>> shelf['ab'] = b

So far, so good. You have saved two instances of the class. Getting them back is just as easy:

  >>> shelf['12']
  <__main__.Test object at 0x01BD1570>
  >>> shelf['ab']
  <__main__.Test object at 0x01BD1650>
  >>> c = shelf['12']
  >>> c.show()
  1 2
  >>> d = shelf['ab']
  >>> d.show()
  a b
  >>> shelf.close()

Notice that the object returned was reported as a __main__.Test object. That raises one very important caveat about saving and restoring user-defined classes. You must make sure that the very same class definition used by shelf for the save is also available to the module that reads the class back from the shelf, and the class definitions must be the same. If the class definition changes between writing the data and reading it back, the result is unpredictable. The usual way to make the class visible is to put it into its own module. That module can then be imported, and used in the code that writes, as well as the code that reads, the shelf.

It’s time to revisit your lending library, LendyDB. This time you replicate what you did with the dbm database, but use the shelve module instead.

You’ve now seen the various options Python offers for storing objects and retrieving them. The shelve module, in particular, offers a persistence mechanism that is compact, fairly fast, and simple to use. If you have a solution that uses Python dictionaries in memory, switching to a shelve solution is almost a trivial task. However, this is still a long way short of what is needed for complex data handling. Operations like finding a set of records based on non-key values or sorting the data essentially still require a complete read of the data into memory. The only way to avoid that is to move to a full-blown database solution. However, before you look at that you should consider some aids that Python provides to make data analysis of in-memory data sets easier.

Analyzing Data Using Built-In Features of Python

When you analyze data, it is important to select the right data structure. For example, Python includes a set data type that automatically eliminates duplicates. If you care only about unique values, converting (or extracting) the data to a set can simplify the process considerably. Similarly, using Python dictionaries to provide keyword access rather than numeric indices often improves code readability, and thus reliability (you saw an example of that in Chapter 2 that compared the CSV dictionary-based reader with the standard tuple-based reader). If you are finding that your code is getting complicated, it’s often worthwhile to stop and consider whether a different data structure would help.

In addition to the wide variety of data structures, Python also offers many built-in and standard library functions that you can use, such as any, all, map, sorted, and slicing. (Slicing isn’t technically a function but an operation, however it does return a value in a similar way that a function would.) When you combine these functions with Python generator expressions and list comprehensions, you have a powerful toolkit for slicing and dicing your data.

You can apply these techniques to your LendyDB data to answer the questions raised in the opening paragraph of this “Analyzing Data with Python” section. You can try that now.

In the preceding Try It Out you saw that you can use the built-in functions and data structures combined with loops and generators to answer most questions about data. The problem is that for volumes of data this technique requires storing large lists in memory and may involve looping over those lists many times. This can become very slow and resource-intensive. The Python itertools module provides several functions that can reduce the load significantly.

Analyzing Data with itertools

The itertools module of the standard Python library provides a set of tools that utilize functional programming principles to consume iterable objects and produce other iterables as results. This means that the functions can be combined to build sophisticated data filters.

Before looking at how itertools can be used on the LendyDB data, you should look at some of the functions provided using simpler data sets. These functions are powerful, but operate in slightly different ways than most of the functions you have dealt with in the past. In particular, they are all geared around processing iterators. You should recall that all the standard Python collections, as well as objects such as files, are iterators. You can also create your own custom iterators by defining some methods that adhere to the Python iterator protocol. The simplest iterators are strings, so that’s mainly what the documentation uses to demonstrate the itertools functions, but remember that these functions work with any kind of iterator, not just strings.

  >>> import itertools as it
  >>> for n in it.count(15,2):
  ...	if n < 40: print(n, end=' ')
  ...	else: break
  ...
  15 17 19 21 23 25 27 29 31 33 35 37 39

  >>> for n in range(7):
  ...	print(next(it.repeat('yes ')), end='')
  ...
  yes yes yes yes yes yes yes >>>
  >>> list(it.repeat(6,3))
  [6, 6, 6]
  >>>

  >>> res1 = []
  >>> res2 = []
  >>> res3 = []
  >>> resources = it.cycle([res1,res2,res3])
  >>> for n in range(30):
  ...	res = next(resources)
  ...	res.append(n)
  ...
  >>> res1
  [0, 3, 6, 9, 12, 15, 18, 21, 24, 27]
  >>> res2
  [1, 4, 7, 10, 13, 16, 19, 22, 25, 28]
  >>> res3
  [2, 5, 8, 11, 14, 17, 20, 23, 26, 29]
  >>>

  >>> items = it.chain([1,2,3],'astring',{'a','set','of','strings'})
  >>> for item in items:
  ...	print(item)
  ...
  1
  2
  3
  a
  s
  t
  r
  i
  n
  g
  a
  of
  set
  strings

  >>> data = list(range(20))
  >>> data[3:12:2]
  [3, 5, 7, 9, 11]
  >>> for d in it.islice(data,3,12,2): print(d, end=' ')
  ...
  3 5 7 9 11

  >>> for item in it.compress([1,2,3,4,5],[False,True,False,0,1]):
  ...	 print (item)
  ...
  2
  5

  >>> def singleDigit(n): return n < 10
  ...
  >>> for n in it.dropwhile(singleDigit,range(20)): print(n,end=' ')
  ...
  10 11 12 13 14 15 16 17 18 19
  >>> for n in it.takewhile(singleDigit,range(20)): print(n,end=' ')
  ...
  0 1 2 3 4 5 6 7 8 9

  >>> for n in it.dropwhile(singleDigit,[1,2,12,4,20,7,999]): print(n,end=' ')
  ...
  12 4 20 7 999

  >>> for n in it.accumulate([1,2,3,4,]): print(n, end=' ')
  ...
  1 3 6 10

  >>> data = [[1,2,3,4,5],[6,7,8,9,0],[0,2,4,6,8],[1,3,5,7,9]]
  >>> for d in data: print(all(d))
  ...
  True
  False
  False
  True

  >>> for ky,grp in it.groupby(data,key=all):
  ...	print(ky, grp)
  ...
  True <itertools._grouper object at 0x7fd3ee2c>
  False <itertools._grouper object at 0x7fd3ee8c>
  True <itertools._grouper object at 0x7fd3ee2c>

  >>> for ky,grp in it.groupby(sorted(data,key=all), key=all):
  ...	print(ky, grp)
  ...
  False <itertools._grouper object at 0x7fd3ef4c>
  True <itertools._grouper object at 0x7fd3ee2c>

  >>> for ky,grp in it.groupby(sorted(data,key=all), key=all):
  ...	if ky: trueset = grp
  ...	else: falseset=grp
  ...
  >>> for item in falseset: print(item)
  ...
  >>>

  >>> groups = {True:[], False:[]}
  >>> for ky,grp in it.groupby(sorted(data,key=all), key=all):
  ...	groups[ky].append(list(grp))
  ...
  >>> groups
  {False: [[[6, 7, 8, 9, 0], [0, 2, 4, 6, 8]]],
 	True: [[[1, 2, 3, 4, 5], [1, 3, 5, 7, 9]]]}
  >>>

Using itertools to Analyze LendyDB Data

You’ve seen what itertools has to offer, so now it’s time to try using it with your LendyDB data. You want to repeat the analysis that you did using the standard tools, but see how the itertools functions can be brought to bear, too. Remember, the real point of the itertools module is not so much that it gives you new features, but rather that it lets you process large volumes of data more efficiently. Given the tiny amount of data you are using in this chapter, you won’t actually see any efficiency improvements, but as you scale the data volumes up, it does make a difference.

In this section you have seen how a mix of the conventional Python data structures, functions, and operators, combined with the functional techniques of the itertools module, enable you to perform complex analysis of quite large data sets. However, there comes a point when the volume and complexity of the data call for another approach, and that means introducing a new technology: relational databases powered by the Structured Query Language (SQL).

Relational Database Concepts

The basic principle of a relational database is very simple. It’s simply a set of two-dimensional tables. Columns are known as fields and rows as records. Field values can refer to other records, either in the same table, or in another table—that’s the “relational” part.

A table holding data about employees might look like Table 3.3.

Notice a couple of conventions demonstrated by this data:

• You have an identifier (ID) field to uniquely identify each row; this ID is known as the primary key. It is possible to have other keys too, but conventionally, there is nearly always an ID field to uniquely identify a record. This helps should an employee decide to change her name, for example.
• You can link one row to another by having a field that holds the primary key value for another row. Thus an employee’s manager is identified by the ManagerID field, which is simply a reference to another EmpID entry in the same table. Looking at your data, you see that both Fred and Anne are managed by John who is, in turn, managed by someone else, whose details are not visible in this section of the table.

You are not restricted to linking data within a single table. You can create another table for Salary. A salary can be related to Grade, and so you get a second table like Table 3.4.

To determine employee John Brown’s salary, you would first look up John’s grade in the main employee data table. You would then consult the Salary table to learn what an employee of that grade is paid. Thus you can see that John, a foreman, is paid $60,000.

Relational databases take their name from this ability to link table rows together in relationships. Other database types include network databases, hierarchical databases, and flat-file databases (which includes the DBM databases you looked at earlier in the chapter). For large volumes of data, relational databases are by far the most common.

You can do much more sophisticated queries, too, and you look at how to do this in the next few sections. But before you can query anything, you need to create a database and insert some data.

Structured Query Language

The Structured Query Language, or SQL (pronounced either as Sequel or as the letters S-Q-L), is the standard software tool for manipulating relational databases. In SQL an expression is often referred to as a query, regardless of whether it actually returns any data.

SQL is comprised of two parts. The first is the data definition language (DDL). This is the set of commands used to create and alter the shape of the database itself—its structure. DDL tends to be quite specific to each database, with each vendor’s DLL having a slightly different syntax.

The other part of SQL is the data manipulation language (DML). DML, used to manipulate database content rather than structure, is much more highly standardized between databases. You spend the majority of your time using DML rather than DDL.

You only look briefly at DDL, just enough to create (with the CREATE command) and destroy (with the DROP command) your database tables so that you can move on to filling them with data, retrieving that data in interesting ways, and even modifying it, using the DML commands (INSERT, SELECT, UPDATE, and DELETE).

  CREATE TABLE tablename (fieldName, fieldName,....);

  $ sqlite3 employee.db
  SQLite version 3.8.2 2013-12-06 14:53:30
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite>

  sqlite> create table Employee
 	...> (EmpID,Name,HireDate,Grade,ManagerID);
  sqlite> create table Salary
 	...> (SalaryID, Grade,Amount);
  sqlite> .tables
  Employee Salary
  sqlite>

  INSERT INTO Tablename ( column1, column2... ) VALUES ( value1, value2... );

  sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
  	...> values ('1020304','John Brown','20030623','Foreman','1020311'),
  sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
  	...> values ('1020305','Fred Smith','20040302','Laborer','1020304'),
  sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
  	...> values ('1020307','Anne Jones','19991125','Laborer','1020304'),

  sqlite> insert into Salary (SalaryID, Grade,Amount)
  	...> values('000010','Foreman',60000);
  sqlite> insert into Salary (SalaryID, Grade,Amount)
  	...> values('000011','Laborer',35000);

  SELECT column1, column2... FROM table1,table2...;

  sqlite> SELECT Name from Employee;
  John Brown
  Fred Smith
  Anne Jones

  SELECT col1,col2... FROM table1,table2... WHERE condition;

  sqlite> SELECT Name
  	...> FROM Employee
  	...> WHERE Employee.Grade = 'Laborer';
  Fred Smith
  Anne Jones

  sqlite> SELECT Name FROM Employee
  	...> WHERE lower(employee.grade) LIKE 'lab%';
  Fred Smith
  Anne Jones

  sqlite> SELECT Name, Amount
  	...> FROM Salary, Employee
  	...> WHERE Employee.Grade = Salary.Grade
  	...> AND Salary.Amount > 50000;
  John Brown|60000

  sqlite> SELECT Employee.Grade, Name, Amount
  	...> FROM Employee, Salary
 	etc/...

  SELECT columns FROM tables WHERE expression ORDER BY columns;

  sqlite> SELECT Name
  	...> FROM Employee
  	...> ORDER BY HireDate;
  Anne Jones
  John Brown
  Fred Smith

  UPDATE table SET column = value WHERE condition;

  sqlite> UPDATE Salary
  	...> SET Amount = 70000
  	...> WHERE Grade = 'Foreman';

  DELETE FROM Table WHERE condition

  sqlite> DELETE FROM Employee
  	...> WHERE Name = 'Anne Jones';

Linking Data Across Tables

The possibility of linking data between tables was mentioned earlier, in the section on SELECT. However, this is such a fundamental part of relational database theory that you consider it in more depth here. The links between tables represent the relationships between data entities that give a relational database such as SQLite its name. The database maintains not only the raw data about the entities, but also information about the relationships.

The information about the relationships is stored in the form of database constraints, applied when you define the database structure using the CREATE statement. Before you see how to use constraints to model relationships, you first need to look deeper into the kinds of constraints available in SQLite.

  CREATE Tablename (Column, Column,...);

  CREATE Tablename (
  ColumnName Type Constraints,
  ColumnName Type Constraints,
  ...);

  sqlite> CREATE table test
  	...> (Id INTEGER PRIMARY KEY,
  	...> Name NOT NULL,
  	...> Value INTEGER DEFAULT 42);
  sqlite> INSERT INTO test (Name, Value) VALUES ('Alan', 24);
  sqlite> INSERT INTO test (Name) VALUES ('Heather'),
  sqlite> INSERT INTO test (Name, Value) VALUES ('Laura', NULL);
  sqlite> SELECT * FROM test;
  1|Alan|24
  2|Heather|42
  3|Laura|

  sqlite> CREATE TABLE Employee (
  	...> EmpID INTEGER PRIMARY KEY,
  	...> Name NOT NULL,
  	...> HireDate NOT NULL,
  	...> Grade NOT NULL,
  	...> ManagerID INTEGER
  	...> );
  sqlite> CREATE TABLE Salary (
  	...> SalaryID INTEGER PRIMARY KEY,
  	...> Grade UNIQUE,
  	...> Amount INTEGER DEFAULT 20000
  	...> );

  DROP TABLE Employee;
  CREATE TABLE Employee (
  EmpID INTEGER PRIMARY KEY,
  Name NOT NULL,
  HireDate NOT NULL,
  Grade NOT NULL,
  ManagerID INTEGER
  );

  DROP TABLE Salary;
  CREATE TABLE Salary (
  SalaryID INTEGER PRIMARY KEY,
  Grade UNIQUE,
  Amount INTEGER DEFAULT 10000
  );

  INSERT INTO Employee (Name, HireDate, Grade, ManagerID)
  		 VALUES ('John Brown','20030623','Foreman', NULL);
  INSERT INTO Employee (Name, HireDate, Grade, ManagerID)
  		 VALUES ('Fred Smith','20040302','Labourer',NULL);
  INSERT INTO Employee (Name, HireDate, Grade, ManagerID)
  		 VALUES ('Anne Jones','19991125','Labourer',NULL);

  UPDATE Employee
  SET ManagerID = (SELECT EmpID
  		 From Employee
  		 WHERE Name = 'John Brown')
  WHERE Name IN ('Fred Smith','Anne Jones'),

  INSERT INTO Salary (Grade, Amount)
  		 VALUES('Foreman','60000'),
  INSERT INTO Salary (Grade, Amount)
  		 VALUES('Labourer','35000'),

  sqlite> .read employee.sql
  sqlite> .read load_employee.sql

  sqlite> SELECT Name
  	...> FROM Employee
  	...> WHERE Grade IN
  	...> (SELECT Grade FROM Salary WHERE amount >50000)
  	...> ;
  John Brown

  CREATE TABLE Employee (
  ...
  ManagerID INTEGER REFERENCES Employee(EmpID)
  );

  PRAGMA Foreign_Keys=True;

  PRAGMA Foreign_Keys=True;

  DROP TABLE Employee;
  CREATE TABLE Employee (
  EmpID INTEGER PRIMARY KEY,
  Name NOT NULL,
  HireDate NOT NULL,
  Grade NOT NULL,
  ManagerID INTEGER REFERENCES Employee(EmpID)
  );

  DROP TABLE Salary;
  CREATE TABLE Salary (
  SalaryID INTEGER PRIMARY KEY,
  Grade UNIQUE,
  Amount INTEGER DEFAULT 10000
  );

  sqlite> .read employee.sql
  sqlite> .read load_employee.sql
  sqlite> insert into Employee (Name,HireDate,Grade,ManagerID)
  	...> values('Fred', '20140602','Laborer',999);
  Error: FOREIGN KEY constraint failed
  sqlite>

Many-to-Many Relationships

One scenario you haven’t covered is where two tables are linked in a many-to-many relationship. That is, a row in one table can be linked to several rows in a second table and a row in the second table can at the same time be linked to many rows in the first table.

Consider an example. Imagine creating a database to support a book publishing company. It needs lists of authors and lists of books. Each author may write one or more books. Each book may have one or more authors. How do you represent that in a database? The solution is to represent the relationship between books and authors as a table in its own right. Such a table is often called an intersection table or a mapping table. Each row of this table represents a book/author relationship. Now each book has potentially many book/author relationships, but each relationship only has one book and one author, so you have converted a many-to-many relationship into two one-to-many relationships. And you already know how to build one-to-many relationships using IDs. It looks like this (you can find the code in the file books.sql in the SQL folder of the .zip file):

  PRAGMA Foreign_Keys=True;

  drop table author;
  create table author (
  ID Integer PRIMARY KEY,
  Name Text NOT NULL
  );

  drop table book;
  create table book (
  ID Integer PRIMARY KEY,
  Title Text NOT NULL
  );

  drop table book_author;
  create table book_author (
  bookID Integer NOT NULL REFERENCES book(ID),
  authorID Integer NOT NULL REFERENCES author(ID)
  );

  insert into author (Name) values ('Jane Austin'),
  insert into author (Name) values ('Grady Booch'),
  insert into author (Name) values ('Ivar Jacobson'),
  insert into author (Name) values ('James Rumbaugh'),

  insert into book (Title) values('Pride & Prejudice'),
  insert into book (Title) values('Emma'),
  insert into book (Title) values('Sense &; Sensibility'),
  insert into book (Title) values ('Object Oriented Design with Applications'),
  insert into book (Title) values ('The UML User Guide'),

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'Pride &; Prejudice'),
  (select ID from author where Name = 'Jane Austin')
  );

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'Emma'),
  (select ID from author where Name = 'Jane Austin')
  );

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'Sense & Sensibility'),
  (select ID from author where Name = 'Jane Austin')
  );

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'Object Oriented Design with Applications'),
  (select ID from author where Name = 'Grady Booch')
  );

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'The UML User Guide'),
  (select ID from author where Name = 'Grady Booch')
  );

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'The UML User Guide'),
  (select ID from author where Name = 'Ivar Jacobson')
  );

  insert into book_author (BookID,AuthorID) values (
  (select ID from book where title = 'The UML User Guide'),
  (select ID from author where Name = 'James Rumbaugh')
  );

If you look at the values inserted into the tables, you see that Jane Austin has three books to her credit, while the book The UML User Guide has three authors.

If you load that into SQLite in a database called books.db (or just use the file books.db found in the SQL folder of the Chapter3. zip file), you can try some queries to see how it works:

  $ sqlite3 books.db
  SQLite version 3.8.2 2013-12-06 14:53:30
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> .read books.sql
  Error: near line 3: no such table: author
  Error: near line 9: no such table: book
  Error: near line 15: no such table: book_author

  sqlite> .tables
  author	book	book_author

Notice the errors resulting from the DROP statements. You always get those the first time you run the script because the tables don’t exist yet. Now you can find out which Jane Austin books are published:

  sqlite> SELECT title FROM book, book_author
  	...> WHERE book_author.bookID = book.ID
  	...> AND book_author.authorID = (
  	...> SELECT ID from Author
  	...> WHERE name='Jane Austin'),
  Pride & Prejudice
  Emma
  Sense & Sensibility

Things are getting a bit more complex, but if you sit and work through it you’ll get the idea soon enough. Notice you need to include both of the referenced tables—book and book_author—in the table list after the SELECT. (The third table, author, is not listed there because it is listed against its own embedded SELECT statement.) Now try it the other way around—see who wrote The UML User Guide:

  sqlite> SELECT name FROM author, book_author
  	...> WHERE book_author.authorID = author.ID
  	...> AND book_author.bookID = (
  	...> SELECT ID FROM book
  	...> WHERE title='The UML User Guide'),
  Grady Booch
  Ivar Jacobson
  James Rumbaugh

If you look closely you see that the structure of the two queries is identical—you just swapped around the table and field names a little.

That’s enough for that example; you now return to your lending library. You now see how you convert it from file-based storage to a full SQL database. You then go on to build an accompanying Python module that enables application writers to ignore the SQL and just call Python functions.

Accessing SQL from Python

SQLite provides an application programming interface or API consisting of a number of standard functions that allow programmers to perform all the same operations that you have been doing without using the interactive SQL prompt. The SQLite API is written in C, but wrappers have been provided for other languages, including Python. Python has similar interfaces to many other databases, and they all provide a standard set of functions and provide very similar functionality. This interface is called the Python DBAPI, and its existence makes porting data between databases much easier than if each database had its own interface.

The DBAPI defines a couple of useful conceptual objects that form the core of the interface. These are connections and cursors.

  >>> import sqlite3
  >>> db = sqlite3.connect('D:/PythonCode/Chapter3/SQL/lendy.db')

Creating the LendyDB SQL Database

The database design is not significantly different from its previous incarnations. You just have to translate it into SQL syntax and add in some constraints to improve data integrity.

The initial database setup is usually easiest to do using raw SQL commands as you did in the earlier sections. Although it is possible to do it all from Python, occasionally another tool is better suited to the task at hand.

The code looks like this (and is available in the file lendydb.sql in the SQL folder of the .zip file):

  PRAGMA Foreign_Keys=True;

  drop table loan;
  drop table item;
  drop table member;

  create table member (
  ID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Email TEXT);

  create table item (
  ID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Description TEXT NOT NULL,
  OwnerID INTEGER NOT NULL REFERENCES member(ID),
  Price NUMERIC,
  Condition TEXT,
  DateRegistered TEXT);

  create table loan (
  ID INTEGER PRIMARY KEY,
  ItemID INTEGER NOT NULL REFERENCES item(ID),
  BorrowerID INTEGER NOT NULL REFERENCES member(ID),
  DateBorrowed TEXT NOT NULL,
  DateReturned TEXT);

You need to drop all the tables at the top of the script, and the order is important because otherwise the referential constraints fail, and an error results. Notice also that you used the NUMERIC type for the item price because this caters for both integer and floating-point values. It also means you don’t need to worry about those pesky dollar signs that you had to strip off in the previous incarnations of the data. Apart from the various key fields, the other fields are all declared as TEXT.

Now that the database is ready, you can insert the test data.

Inserting Test Data

The database design now includes some referential constraints so you need to think about the order in which you populate the data. The member data has no references, so it can be loaded first. The item data only references members, so it comes next. Finally, the loan data references both members and items, so it must come last.

For the data insertion, you are essentially repeating the same INSERT operation over and over with different data values. This time Python is the better solution because you can write the SQL once and execute it many times using either the executemany() method of the cursor object or by calling the execute() method from within a Python for loop. It looks like this (and the code is in the file lendydata-sql.py):

  import sqlite3
  members = [
  	['Fred', '[email protected]'],
  	['Mike', '[email protected]'],
  	['Joe', '[email protected]'],
  	['Rob', '[email protected]'],
  	['Anne', '[email protected]'],
  ]

  member_sql = '''insert into member (Name, Email) values (?, ?)'''
  items = [
  	['Lawnmower','Tool', 	0, 150,'Excellent', 	'2012-01-05'],
  	['Lawnmower','Tool', 	0, 370,'Fair',		'2012-04-01'],
  	['Bike', 'Vehicle', 	0, 200,'Good',		'2013-03-22'],
  	['Drill', 'Tool', 	0, 100,'Good',		'2013-10-28'],
  	['Scarifier','Tool', 	0, 200,'Average',	'2013-09-14'],
  	['Sprinkler','Tool', 	0, 80,'Good',		'2014-01-06']
  ]
  item_sql = '''
  insert into item
  (Name, Description, ownerID, Price, Condition, DateRegistered)
  values (?, ?, ?, ?, ?, date(?))'''
  set_owner_sql = '''
  update item
  set OwnerID = (SELECT ID from member where name = ?)
  where item.id = ?
  '''

  loans = [
  	[1,3,'2012-01-04','2012-04-26'],
  	[2,5,'2012-09-05','2013-01-05'],
  	[3,4,'2013-07-03','2013-07-22'],
  	[4,1,'2013-11-19','2013-11-29'],
  	[5,2,'2013-12-05', None]
  ]
  loan_sql = '''
  insert into loan
  (itemID, BorrowerID, DateBorrowed, DateReturned )
  values (?, ?, date(?), date(?))'''

  db = sqlite3.connect('lendy.db')
  cur = db.cursor()

  cur.executemany(member_sql, members)
  cur.executemany(item_sql, items)
  cur.executemany(loan_sql, loans)

  owners = ('Fred','Mike','Joe','Rob','Anne','Fred')
  for item in cur.execute("select id from item").fetchall():
 	itemID = item[0]
  cur.execute(set_owner_sql, (owners[itemID-1], itemID))

  cur.close()
  db.commit()
  db.close()

Several things are noteworthy in this script. The first point is the format of the dates. Although SQLite does not have a Date data type, it does have a number of functions that can be used to create standardized date strings and values that it then stores as text (or in some cases as floating-point numbers). The date() function used here requires the date string to be in the format shown, and an invalid date string will be stored as NULL. Using date() therefore improves data quality by ensuring only valid and consistently formatted dates get stored.

The item OwnerID field is specified as NOT NULL, so you filled it with a dummy value (0) that is then overwritten by the UPDATE code later in the script.

You could have used a for loop to process the INSERT statements, but instead used the executemany() method that takes the statement and a sequence (or iterator or generator) and repeatedly applies the statement until the iteration is completed.

The variable values are inserted into the query strings by using a question mark as a placeholder. This is very similar to the string formatting method that uses {} as a place marker.

At this point you have created the database and populated it with some test data. The next stage is to make that data accessible to applications via an application programming interface (API).

Creating a LendyDB API

When defining an API for a database, it’s normal to start off by thinking about the basic entities and providing functions to create, read, update, and delete the items. (This is often known as a CRUD interface after the initials of the operations.) The temptation is to provide these functions as a thin wrapper around the corresponding SQL commands. However, to the application programmer it is not particularly useful to just be given an ID of a member—for example, it would be much better in most cases to get the member name instead. Otherwise, the programmer must perform multiple reads of the member database to create a useful display for the user. On the other hand, there may be times when an ID is the best option because the programmer may want to get more specific details about the member. The skill in building a good API comes from being able to resolve these contradictions in a way that makes the application programmer effective while retaining full access to the data.

Although the CRUD operations are a good foundation, it is often more useful to the application programmer if some higher-level operations are also available that span entities. To do this you need to think about how your data is likely to be used in an application. What kinds of questions will the designer want to ask? If you are also the application programmer, this is relatively easy, but if you are providing the database as part of a bigger project, it gets more difficult.

For the lending library example, you focus only on the CRUD interface to items and members. The principles should be obvious, and you have the opportunity to extend the API to cover loans in Exercise 3.3 at the end of the chapter. (The code for items and members is in the lendydata.py file in the SQL folder of the .zip file.)

  '''
  Lending library database API

  Provides a CRUD interface to item and member entities
  and init and close functions for database control.
  '''

  import sqlite3 as sql

  db=None
  cursor = None

  ##### CRUD functions for items ######

  def insert_item(Name, Description, OwnerID, Price, Condition):
  	query = '''
  	insert into item
  	(Name, Description, OwnerID, Price, Condition, DateRegistered)
  	values (?,?,?,?,?, date('now'))'''
  	cursor.execute(query,(Name,Description,OwnerID,Price,Condition))

  def get_items():
  	query = '''
  	select ID, Name, Description, OwnerID, Price, Condition, DateRegistered
  	from item'''
  	return cursor.execute(query).fetchall()

  def get_item_details(id):
  	query = '''
  	select name, description, OwnerID, Price, Condition, DateRegistered
  	from item
  	where id = ?'''
  	return cursor.execute(query,(id,)).fetchall()[0]

  def get_item_name(id):
  	return get_item_details(id)[0]

  def update_item(id, Name=None, Description=None,
  		 OwnerID=None, Price=None, Condition=None):
  query = '''
  	update item
  	set Name=?, Description=?, OwnerID=?, Price=?, Condition=?
  	where id=?'''
  	data = get_item_details(id)
  	if not Name: Name = data[0]
  	if not Description: Description = data[1]
  	if not OwnerID: OwnerID = data[2]
  	if not Price: Price = data[3]
  	if not Condition: Condition = data[4]

  	cursor.execute(query, (Name,Description,OwnerID,Price,Condition,id))

  def delete_item(id):
  	query = '''
  	delete from item
  	where id = ?'''
  	cursor.execute(query,(id,))
  ##### CRUD functions for members ######

  def insert_member(name, email):
  	query = '''
  	insert into member (name, email)
  	values (?, ?)'''
  	cursor.execute(query, (name,email))

  def get_members():
  	query = '''
  	select id, name, email
  	from member'''
  	return cursor.execute(query).fetchall()

  def get_member_details(id):
  	query = '''
  	select name, email
  	from member
  	where id = ?'''
  	return cursor.execute(query, (id,)).fetchall()[0]

  def get_member_name(id):
  	return get_member_details(id)[0]

  def update_member(id, Name=None, Email=None):
  	query = '''
  	update member
  	set name=?, email=?
  	where id = ?'''
  	data = get_member_details(id)
  	if not Name: Name = data[0]
  	if not Email: Email = data[1]
  	cursor.execute(query, (Name, Email, id))

  def delete_member(id):
  	query = '''
  	delete from member
  	where id = ?''' 
  	cursor.execute(query,(id,))
  ##### Database init and close #######

  def initDB(filename = None):
  	global db, cursor
  	if not filename:
  		 filename = 'lendy.db'
  	try:
  		 db = sql.connect(filename)
  		 cursor = db.cursor()
  	except:
  		 print("Error connecting to", filename)
  		 cursor = None
  		 raise

  def closeDB():
  	try:
  		 cursor.close()
  		 db.commit()
  		 db.close()
  	except:
  		 print("problem closing database...")
  		 raise
  if __name__ == "__main__":
  	initDB() # use default file
  	print("Members:
", get_members())
  	print("Items:
",get_items())

In this module you create two global variables for the database connection and the cursor. The initDB() function initializes these variables, and the same cursor is used in each of the query functions within the module. The closeDB() function then closes these objects when you are finished using the module. This approach means that the initDB()/closeDB() functions must be called to initialize and finalize the database, which adds a little bit of complexity for the user, but it means that the individual queries are much simpler because you do not need to manage the database and cursor objects each time a query is called. Notice, too, that you set the Foreign_Keys pragma in the initDB() function to ensure that all the transactions are checked for referential integrity.

The query functions all follow the same pattern. A SQL query string is created, using triple quotes to allow multiple line layouts, and then that string is passed to the cursor.execute() method. The retrieved values are passed back where appropriate. The cursor.execute() parameter substitution mechanism is used throughout.

The two update methods have an extra twist in that they have defaulted input parameters. This means that the user can provide only those fields that are changing. The other fields are populated based on the existing values that are retrieved by using the appropriate get-details function.

The two get-name functions are provided for the convenience of the user to easily map from the database identifiers returned in the get-details queries to meaningful names. Typically, the application programmer should use these functions before displaying the results to the end user.

The final section of the module, inside the if test, is a very basic test function just to check that the connection and cursor objects are working as expected. It does not comprehensively test all of the API functions; you will do that in the Try It Out that follows.

You have now built a high-level API for application programmers to use and have seen that by making the underlying cursor object accessible you enable the user to issue arbitrary low-level SQL commands, too. This kind of flexibility is usually appreciated by application programmers, although if they find that they must use SQL extensively, they should request an addition to the functional API.

Of course SQLite is not the only tool available for working with large data volumes, and Python can provide support for these alternatives, too. In the next section, you look at some alternatives to SQLite and how Python works in these environments.

Client-Server Databases

The traditional SQL database is rather different from SQLite in the way it is constructed. In these databases you have a database server process accessed over a network by multiple clients. The Python DBAPI is designed so that you can work with these databases just as easily as you did with SQLite. Only minor changes in the way you initialize the database connection are usually all that is necessary. Occasionally you’ll see minor changes to the SQL syntax, and the parameter substitution symbol is sometimes different, too. But, in general, swapping from one SQL interface to another is a relatively painless experience.

Several reasons exist for wanting to adopt a client-server database or to migrate to one from SQLite. The first is capacity; most client-server databases can scale up to much larger volumes of data than SQLite. In addition, they can be configured to distribute their processing over several servers and disks, which greatly improves performance when multiple users are accessing the data concurrently. The second reason for migrating from SQLite is that larger databases usually come with a much richer set of SQL commands and query types as well as more data types. Many even support object-oriented techniques and have their own built-in programming languages that enable you to write stored procedures, effectively putting the API into the database itself. Usually, a much richer set of database constraints can be used to tighten up data integrity far beyond the simple foreign key checks that SQLite performs.

The biggest downside of selecting a client-server database is the extra complexity of administering the database. Usually a dedicated administrator is needed to set up users, adjust their access privileges, tune the SQL query engine performance, and do backups, data extracts, and loads.

Popular client-server databases include commercial offerings such as SQL Server, Oracle, and DB2, as well as open source projects such as MySQL, PostGres, and Firebird.

NoSQL

As data storage needs have expanded both in size and in variety, several projects have been exploring alternatives to SQL. Many of these projects are associated with what is called “Big Data,” which usually relates to the harvesting of large volumes of, often unstructured, data from places such as social media sites or from sensors in factories or hospitals and so on. One of the features of this kind of data is that most of it is of little significance to the database user, but amongst the detritus are gems to be had that can influence sales strategy or alert you to imminent failure of components or processes so that action can be taken in advance. The nature, volumes, and need for rapid access of such data means that traditional SQL databases are not well suited to the task.

The solution has been a variety of technologies that go under the collective term NoSQL. NoSQL does not mean that no SQL is used, but rather it stands for Not Only SQL. SQL may still be available for traditional queries on these new databases, but alternative query techniques are also used, especially for searching unstructured data. Some examples of NoSQL approaches (with typical implementations) are Document (MongoDB), Key-Value (Dynamo), Columnar (HBase), and Graphs (Allegro). All of these diverge to some degree or other from the traditional relational model of multiple, two-dimensional tables with cross-references. To get the best from these systems, you need to consider which architecture best suits the nature of your data. Many solutions are open source, but commercial offerings exist, too.

Although NoSQL databases do provide the potential for faster and more flexible access to a wider variety of data, they generally sacrifice qualities like data integrity, transactional control, and usability. The products are all evolving rapidly, although at the time of writing they require lower-level programming skills to utilize the data compared to traditional SQL databases that could change quite quickly. Most of the popular databases offer Python modules that facilitate programming them from Python.

The Cloud

Cloud computing has become popular in recent years with its promise of computing on-demand. This potentially offers lower costs, more flexibility, and lower risks than traditional data center–based solutions. It brings its own set of concerns, of course, especially for sensitive data or where network reliability is an issue. The biggest use of cloud technologies in the database world has been in combination with the NoSQL solutions, discussed in the previous section, and other Big Data solutions such as Hadoop. Many cloud storage providers offer these technologies on a software-as-a-service (SAAS) basis. This offers an attractive option for those just dipping a toe into the Big Data or NoSQL world.

The advantage of cloud computing for the application programmer is that the data is abstracted into a virtual database located at a single consistent network address. The physical location of the data may change over time, or the amount of storage available may grow or shrink, but to the programmer it doesn’t matter. (This does not mean that normal error handling can be ignored, but it does mean that the code can be largely decoupled from the physical design of the data storage.)

One of the biggest providers of cloud storage and database technology is the online retailer Amazon. It provides storage and an API (Amazon Web Services, or AWS) as well as a proprietary NoSQL database called SimpleDB, in addition to several other open source offerings. Other cloud suppliers are starting to offer similar products. Many providers offer small amounts of free storage and access as an incentive to try the product before committing to a significant investment.

Amazon AWS has Python support available, including a comprehensive tutorial to both Python and the AWS interface, at http://boto.readthedocs.org/en/latest/. Similar interfaces are available, or will likely soon become available, from the other providers.

Data Analysis with RPy

Though client-server SQL, NoSQL, and cloud computing all provide solutions for handling large data volumes or many users, you have other data management issues to consider. Often, the processing of large volumes of data is more important than the storage or retrieval. If that processing involves a high degree of statistical exploration or manipulation, Python offers a basic statistics module (introduced to the standard library in version 3.4). If that is not enough, there is the R programming language. R is a specialized language designed for statistical number crunching on a large scale. Like Python it has accumulated a large library of add-in modules, and many statistical researchers use R as their platform of choice, publishing their research using R.

The good news for Python programmers is that there is an interface from Python to R called rpy2 that opens up this processing power without having to become an expert in R. Knowing the basics of R, especially its data handling concepts, is pretty much essential, but much of your Python knowledge can be applied, too. You can find rpy2 on the Python Package Index and install it via pip.

Data management has many facets, and this chapter has reviewed how Python supports you, from small volumes to large, from simple data persistence through to complex data analysis. Once you have control of your data, you are in a much stronger position to create powerful, user-friendly applications whether on the desktop or on the web. That’s the subject of the next two chapters.