In this topic we will look at how data can be stored over time and manipulated via a database package. We have already seen how to use files to store small quantities of data such as our personal address book but the complexity of using files increases dramatically as the complexity of the data increases, the volume of data increases, and the complexity of the operations (searching, sorting, filtering etc). To overcome this several database packages exist to take care of the underlying file management and expose the data in a more abstract form which is easy to manipulate. Some of these packages are simple code libraries that simplify the file operations we have already seen, examples include the pickle and shelve modules that come with Python. In this topic we will concentrate on more powerful packages such as those from commercial vendors that are designed to handle large volumes of complex data.

The specific package I will be looking at is SQLite, an open source freeware package that is simple to install and use but capable of handling the data needs of most beginning and intermediate programmers. Only if you have very large data sets - millions of records - do you need to consider a more powerful package and, even then, almost all you know from SQLite will transfer to the new package.

The SQLite package can be downloaded from here and you should fetch the command-line package appropriate for your platform. (There are some useful IDEs for SQLite that you might like, but they aren't necessary for this tutorial.) Follow the instructions on the web site to install the packages and you should be ready to start.

Relational Database Concepts

The basic principle of a relational database is very simple. It's simply a set of tables where a cell in one table can refer to a row in another table. Columns are known as fields and rows as records.

A table holding data about employees might look like:

Notice a couple of conventions here:

1. We have an ID field to uniquely identify each row, this is known as the Primary Key. It is possible to have other keys too, but conventionally there will always be an ID field to uniquely identify a record. This helps should an employee decide to change their name for example!
2. We can link one row to another by having a field that hold the Primary Key for another row. Thus an employee's manager is identified by the ManagerID field which is simply a reference to another EmpID entry. Looking at our data we see that both Fred and Anne are managed by John who is, in turn, managed by someone else whose details are not visible on our section of the table.

We are not restricted to linking data within a single table. We could create another table for Salary. These could be related to Grade and so we get a table like:

Now we can look up the grade of an Employee, such as John, and find that they are a Foreman, then by looking at the Salary table we can see that a Foreman is paid $60000.

It is this ability to link table rows together in relationships that gives relational databases their name. Other database types include network databases, hierarchical databases and flat-file databases. Relational databases are by far the most common.

We can do much more sophisticated queries too and we will look at how this is done in the next few sections. But before we can do that we had better create a database and insert some data.

Structured Query Language

The Structured Query Language or SQL (pronounced as either Sequel or 'S' 'Q' 'L') is the standard tool for manipulating relational databases. In SQL an expression is often referred to as a query.

SQL comprises two parts, the first is the Data Definition Language, or 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 database specific with each database supplier having a slightly different syntax for their DDL set of SQL commands.

The other part of SQL is the Data Manipulation Language or DML. DML is much more highly standardised between databases and is used to manipulate the data content of the database. You will spend the majority of your time using DML rather than DDL

We will only look briefly at DDL, just enough to create (with the CREATE command) and destroy (with the DROP command) our database tables so that we can move onto filling them with data and then retrieving that data in interesting ways using the DML commands (INSERT, SELECT, UPDATE, DELETE etc).

Creating Tables

To create a table in SQL we use the CREATE command. It is quite easy to use and takes the form:

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

Note that SQL statements are terminated with a semi-colon. Also SQL is not case-sensitive and, unlike Python, does not care about white-space or indentation levels. As you will see there is a certain style convention that is used but it is not rigidly adhered to and SQL itself cares not a jot!

Let's try creating our Employee and Salary tables in SQLite. The first thing to do is start the interpreter, which is simply a case of calling it with a filename as an argument. If the database exists it will be opened, if it doesn't it will be created. Thus to create an employee database we will start SQLite like so:

E:\PROJECTS\SQL> sqlite3 employee.db

That will create an empty database called employee.db and leave us at the sqlite> prompt ready to type SQL commands. So let's create some tables:

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

Note that I moved the list of fields into a separate line, that simply makes it easier to see them. The fields are listed by name but have no other defining information such as data type. This is a peculiarity of SqlLite and most databases require you to specify the type along with the name. We can do that in SqlLite too and we will look at this in more detail a little later in the tutorial.

Also note that I tested that the create statements had worked by using the .tables command to list all the tables in the database. SQLite has several of these dot commands that we will use to find out about our database. .help provides a list of them.

There are lots of other things we can do when we create a table. As well as declaring the types of data in each column, we can also specify constraints as to the values (for example NOT NULL means the value is mandatory and must be filled in - usually we would make the Primary Key field NOT NULL and UNIQUE.) We can also specify which field will be the PRIMARY KEY. We will look more closely at these more advanced creation options later on.

For now we will leave the basic table definition as it is and move on to the more interesting topic of manipulating the data itself.

Inserting Data

The first thing to do after creating the tables is fill them with data! This is done using the SQL INSERT statement. The basic structure is very simple:

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

There is an alternate form of INSERT that uses a query to select data from elsewhere in the database, but that's rather too advanced for us here so I recommend you read about that in the SQLite manual.

So now, to insert some rows into our employees table, we do the following:

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','Labourer','1020304');
sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
   ...> values ('1020307','Anne Jones','19991125','Labourer','1020304');

And for the Salary table:

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

And that's it done. We now have created two tables and populated them with data corresponding to the values described in the introduction above. Now we are ready to start experimenting with the data.

Extracting Data

Data is extracted from a database using the SELECT command of SQL. Select is the very heart of SQL and has the most complex structure. We will start with the most basic form and add additional features as we go along. The most basic Select statement looks like this:

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

So to select the names of all employees we could use:

sqlite> SELECT Name from Employee;

And we would be rewarded with a list of all of the names in the Employee table. In this case that's only three, but if we have a big database that's probably going to be more information than we want. To control the output we need to be able to refine our search somewhat and SQL allows us to do this by adding a WHERE clause to our Select statement, like this:

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

the condition is an arbitrarily complex boolean expression and, as we shall see, can include nested select statements within it.

Let's use a where clause to refine our search of names. We will only look for names of employees who are labourers:

sqlite> select Name 
   ...> from Employee
   ...> where Employee.Grade = 'Labourer';

Now we only get two names back. We could extend the condition using boolean operators such as AND, OR, NOT etc. Note that using the = condition the case of the string is important, testing for 'labourer' would not have worked! We'll see how to get round that limitation later on.

Notice that in the where clause we used dot notation to signify the Grade field. In this case it was not really needed since we are only working with a single table but where multiple tables are specified we need to make it clear which table the field belongs to. As an example let's change our query to find the names of all employees paid more than $50,000. To do that we will need to consider data in both tables:

sqlite> select Name, Amount from Employee, Salary
   ...> where Employee.Grade = Salary.Grade
   ...> and   Salary.Amount > '50000';

As expected we only get one name back - that of the foreman. But notice that we also got back the salary, because we added Amount to the list of columns selected. Also note that we have two parts to our where clause combined using an and boolean operator. The first part links the two tables together by ensuring that the common fields are equal, this is known as a join in SQL.

Note 1: Because the fields that we are selecting come from two tables we have to specify both of the tables from which the result will come. The order of the field names is the order in which we get the data back but the order of the tables doesn't matter so long as the specified fields appear in those tables.

Note 2: We specified two unique field names. If we had wanted to display the Grade as well, which appears in both tables, then we would have had to use dot notation to specify which table's Grade we wanted, like this:

sqlite> select Employee.Grade, Name, Amount 
   ...> from Employee, Salary
   etc/...

The final feature of Select that I want to cover (although there are several more which you can read about in the SQL documentation for SELECT) is the ability to sort the output. Databases generally hold data either in the order that makes it easiest to find things or in the order in which they are inserted, in either case that's not usually the order we want things displayed! To deal with that we can use the ORDER BY clause of the Select statement.

SELECT columns FROM tables WHERE expression ORDER BY columns;

Notice that the final ORDER BY clause can take multiple columns, this enables us to have primary, secondary, tertiary and so on sort orders.

Let's use this to get a list of names of employees sorted by HireDate:

sqlite> select Name from Employee
   ...> order by HireDate;

And that's really all there is to it, now you can't get much easier than that! The only thing worthy of mention is that we didn't use a where clause. If we had used one it would have had to come before the order by clause. So although SQL doesn't mind if you drop the clause, it does care about the sequence of the clauses within the statement.

That's enough about extracting data, let's now see how we can modify our data.

Changing Data

There are two ways that we can change the data in our database. We can alter the contents of a single record or, more drastically, we can delete a record or even a whole table. Changing the content of an existing record is the more common case and we do that using the UPDATE SQL command.

The basic format is:

UPDATE table SET column = value WHERE condition;

We can try it out on our sample database by changing the salary of a Foreman to $70,000.

sqlite> update Salary
   ...> set Amount ='70000'
   ...> where Grade = 'Foreman';

One thing to notice is that up until now all of the data we've inserted and selected has been string types. SQLite actually stores its data as strings but actually supports quite a few different types of data, including numbers. So we could have specified the salary in a numeric format which would make calculations easier. We'll see how to do that in the next section.

The other form of fairly drastic change we can make to our data is to delete a row, or set of rows. This uses the SQL DELETE FROM command, which looks like:

DELETE FROM Table WHERE condition

So if we wanted to delete Anne Jones from our Employee table we could do this:

sqlite> delete from Employee where Name = 'Anne Jones';

If more than one row matches our condition then all of the matching rows will be deleted. SQL always operates on all the rows that match our query, it's not like using a sequential search of a file or string using a regular expression.

To delete an entire table and its contents we would use the SQL DROP command, but we will see that in action a little later. Obviously destructive commands like Delete and Drop must be used with extreme caution!

Linking Data Across Tables

Data Constraints

We mentioned linking data across tables earlier, in the section on SELECT. However this is such a fundamental part of database theory that we will discuss 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 information about the relationships too.

The information about the relationships is stored in the form of database constraints which act as rules dictating what kind of data can be stored as well as the valid set of values. These constraints are applied when we define the database structure using the CREATE statement.

We normally express the constraints on a field by field basis so, within the CREATE statement, where we define our columns, we can expand the basic definition from:

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

To:

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

And the most common constraints are:

NOT NULL 
PRIMARY KEY [AUTOINCREMENT] 
UNIQUE 
DEFAULT value 

NOT NULL is fairly self explanatory, it indicates that the value must exist and not be NULL! And a NULL value is simply one that has no specified value. Thus NOT NULL means that a value must be given for that field, otherwise an error will result and the data will not be inserted.

PRIMARY KEY simply tells SQLite to use this column as the main key for lookups (in practice this means it will be optimized for faster searches). The AUTOINCREMENT means that an INTEGER type value will automatically be assigned on each INSERT and the value automatically incremented by one. This saves a lot of work for the programmer in maintaining separate counts. Note that the AUTOINCREMENT "keyword" is not actually used, rather it is implied from a type/constraint combination of INTEGER PRIMARY KEY. This is a not so obvious quirk of the SQLite documentation that trips up enough people for it to appear at the top of the SQLite FAQ list!

UNIQUE means that the value must be unique within the column. If we try to insert a value into a column with a UNIQUE constraint then an error results and the row will not be inserted. UNIQUE is often used for non INTEGER type PRIMARY KEY columns.

DEFAULT is always accompanied by a value. The value is what SqlLite will insert into that field if the user does not explicitly provide one. The effect of this is that columns with a DEFAULT constraint are in practice very rarely NULL, to create a NULL value you would need to explicitly set NULL as the value.

We can see a quick example showing the use of default here:

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 ('Linda', NULL);
sqlite> select * from test;
1|Alan|24
2|Heather|42
3|Linda|
sqlite>

Notice how the entry for Heather has the default value set? And also that the value for Linda is non existent, or NULL. That is an important difference between NOT NULL and DEFAULT. The former will not allow NULL values either by default or explicitly. The DEFAULT constraint prevents unspecified NULLs but does not prevent deliberate creation of NULL values.

There are also constraints that can be applied to the table itself but we will not be discussing those in any depth in this tutorial.

The other kind of constraint that we can apply, as already mentioned, is to specify the column Type. This is exactly like the concept of types in a programming language and the valid set of types in SQLite are:

• TEXT
• NUMERIC
• INTEGER
• None

These should be self evident with the possible exception of NUMERIC which allows the storage of floating point numbers as well as integers. None is not really a type but simply indicates that, as we did above, you don't need to specify a type at all. Most databases come with a much wider set of types including, crucially, a DATE type, however as we are about to see, SQLite has a somewhat unconventional approach to types which renders such niceties less relevant.

Most databases strictly apply the types specified. However SQLite employs a more dynamic scheme, where the type specified is more like a hint and any type of data can be stored in the table. When data of a different type is loaded into a field then SQLite will use the declared type to try and convert the data, but if it cannot be converted it will be stored in its original form. Thus if a field is declared as INTEGER but the TEXT value '123' is passed in, SQLite will convert the string '123' to the number 123. But if the TEXT value 'Freddy' is passed in the conversion will fail so SQLite will simply store the string 'Freddy' in the field! This can cause some strange behaviour if you are not aware of this foible. Most databases treat the type declaration as a strict constraint and will fail if an illegal value is passed.

Modelling Relationships with Constraints

So how do these constraints help us to model data and, in particular, relationships? Let's look again at our simple two-table database:

EmpID Name HireDate Grade ManagerID 1020304 John Brown 20030623 Foreman 1020311 1020305 Fred Smith 20040302 Labourer 1020304 1020307 Anne Jones 19991125 Labourer 1020304

SalaryID Grade Amount 000010 Foreman 60000 000011 Labourer 35000

Looking at the Employee table first we can see that the ID value should be of INTEGER type and have a PRIMARY KEY constraint, the other columns, with the possible exception of the ManagerID should be NOT NULL. ManagerID should also be of type INTEGER.

For the Salary table we see that again the SalaryID should be an INTEGER with PRIMARY KEY. The Amount column should also be an INTEGER and we will apply a DEFAULT value of 10000. Finally the Grade column will be constrained as Unique since we don't want more than one salary per grade! (Actually this is a bad idea since normally salary varies with things like length of service as well as grade, but we'll ignore such niceties! In fact, in the real world, we probably should call this a Grade table and not Salary...)

The modified SQL looks like this:

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 10000
   ...> );

You can try out these constraints by attempting to enter data that breaks them to see what happens. Hopefully you see an error message!

One thing to point out here is that the insert statements we used previously are no longer adequate. We previously inserted our own values for the ID fields but these are now autogenerated so we can (and should!) miss them out of the inserted data. But this gives rise to a new difficulty. How can we populate the managerID field if we don't know what the EmpID of the manager is? The answer is we can use a nested select statement. I've chosen to do this in two stages using NULL fields initially and then using an update statement after creating all the rows.

To avoid a lot of repeat typing I've put all of the commands in a couple of files which I called employee.sql for the table creation commands and employee.dat for the insert statements. (This is just the same as creating a python script file ending in .py to save typing everything at the >>> prompt.)

The employee.sql file looks like this:

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
);

Notice that I drop the tables before creating them. The DROP TABLE command, as mentioned earlier, simply deletes the table and any data within it. This ensures the database is in a nice clean state before we start creating our new table.

The employee.dat script looks like this:

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 = 'Fred Smith' OR 
      Name = 'Anne Jones';

insert into Salary (Grade, Amount)
       values('Foreman','60000');
insert into Salary (Grade, Amount)
       values('Labourer','35000');

Notice the use of the embedded select statement in the update command and also the fact that I've used a single update to modify both employee rows by using a boolean OR condition. By extending this OR I can easily add more employees with the same manager.

This is typical of the problems you can have when populating a database for the first time. You need to plan the order of the statements carefully to ensure that for every row that needs to contain a reference value to another table that you have already provided the data for it to reference! It's a bit like starting at the leaves of a tree and working back to the trunk. Always create/insert the data with no references first, then the data that references that data and so on. If you are adding data after the initial creation you will need to use queries to check the data you need already exists, and add it if it doesn't. At this point a scripting language like Python becomes invaluable!

Finally we can run these from the sqlite prompt like this:

sqlite> .read employee.sql
sqlite> .read employee.dat

Make sure you have the path issues sorted out though: either run sqlite from wherever the sql scripts live (as I've done above) or provide the full path to the script.

Now we'll try a query to check that everything is as it should be:

sqlite> select Name from Employee
   ...> where Grade in
   ...> (select Grade from Salary where amount >50000)
   ...> ;
John Brown

That seems to have worked, John Brown is the only employee earning over $50000. Notice that we used an IN condition combined with another embedded SELECT statement. This is a variation on a similar query that we performed above using a cross table join. Both techniques work but usually the join approach will be faster.

Many to many relationships

One scenario we haven't discussed 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 we are writing a database to support a book publishing company. They need lists of authors and lists of books. Each author will write one or more books. Each book will have one or more authors. How do we 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 only has potentially many book/author relationships but each relationship only has one book and one author, so we have converted a many to many relationship into two one to many relationships. And we already know how to build those using IDs. Let's see it in practice:

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

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

drop table book_author;
create table book_author (
bookID Integer NOT NULL,
authorID Integer NOT NULL
);

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')
);

Now we can try some queries to see how it works. Let's see which Jane Austin books we publish:

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");

It's getting a wee 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.) Let's try it the other way around, Let's 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");

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

That's enough for that example, I'm now going to return to our Address Book example that we last considered in the topic on file handling. You might want to review that before reading on to see how we convert it from file based storage to a full database.

The Address Book Revisited

In the file based version of the address book we used a dictionary with the contact name as key and the address as a single data item. That works fine if we always know the name we want or we always want the full address details. But what if we want all of our contacts in a particular town? Or all the people called 'John'? We could write specific Python code for each query but as the number of special queries rises the amount of effort gets to be a serious disincentive. This is where a database approach pays dividends with the ability to create queries dynamically using SQL.

So what does our address book look like as a database? Basically it is a single table. We could split the data into address and person and link them - after all you may have several friends living in the same house, but we will stick with our original design and use a simple table.

One thing that we will do is split the data into several fields. Rather than a simple name and address structure we will split the name into first and last names, and the address into its constituent parts. There has been a lot of study into the best way to do this and no definitive answer, but the one thing everyone agrees on is that single field addresses are a bad idea - they are just too inflexible. Let's list the fields of our database table and the constraints that we want to apply:

Field Name	Type	Constraint
First Name	String	Primary Key
Last Name	String	Primary Key
House Number	String	NOT NULL
Street	String	NOT NULL
District	String
Town	String	NOT NULL
Post Code	String	NOT NULL
Phone Number	String	NOT NULL

Some points to note:

1. We have two primary keys which is not allowed, we'll have to deal with that in a moment.
2. All of the data is type string even though the House Number might be expected to be an integer. Unfortunately house numbers often include letters too, so we have to use a string.
3. The district is the only optional field
4. Post or Zip codes are very specific in form but only within a single country. This means we have to, once again, leave the type as a string to cater for all eventualities.
5. The Phone Number might seem like a candidate for a UNIQUE constraint, but that wouldn't allow us to have two friends sharing the same phone.

Going back to the first point, that we have two primary keys. This is not allowed in SQL but what we can do is take two columns and combine them into what is called a composite key which allows them to be treated as a single value so far as identifying a row is concerned. Thus we could add a line at the end of our create table statement which combined FirstName and LastName as a single Primary Key. It would look something like this:

create table address (
FirstName NOT NULL,
LastName NOT NULL,
...
PhoneNumber NOT NULL,
PRIMARY KEY (FirstName,LastName)
);

Notice the last line which lists the columns we want to use as the composite key. (This is actually an example of a table based constraint.)

However, thinking about this, it isn't really such a good idea since, if we know two people with the same name, we could only store one of them. We'll deal with this by first of all defining an integer primary key field to uniquely identify our contacts, even though we will rarely if ever use it in a query.

We know how to declare an Integer Primary Key constraint, we did that for our employee example.

We can turn that straight into a SQLite data creation script, like this:

-- drop the tables if they exist and recreate them afresh
-- use constraints to improve data integrity
drop table address;
create table address (
ContactID Integer Primary Key,
First Not Null,
Last Not Null,
House Not Null,
Street Not Null,
District,
Town Not Null,
PostCode Not Null,
Phone Not Null
);

The first two lines are simply comments. Like the # symbol in Python, anything following a double dash (--) is considered a comment in SQL.

Notice that I have not defined the type because String is the default in SQLite, if we needed to convert, or port in computer speak, this schema, or table layout, to some other database we would probably need to go back and add the type information.

The next step is to load some data into the table ready to start performing queries. I'll leave that as an exercise for the reader, but I will be using the following data set in the following examples:

First	Last	House	Street	District	Town	PostCode	Phone
Anna	Smith	42	Any Street	SomePlace	MyTown	ABC123	01234 567890
Bob	Builder	17	Any Street	SomePlace	MyTown	ABC234	01234 543129
Clarke	Kennit	9	Crypt Drive	Hotspot	MyTown	ABC345	01234 456459
Dave	Smith	42	Any Street	SomePlace	MyTown	ABC123	01234 567890
Dave	Smith	12A	Double Street		AnyTown	DEF174	01394 784310
Debbie	Smith	12A	Double Street		AnyTown	DEF174	01394 784310

Now we have some data let's play with it and see how we can use the power of SQL to extract information in ways we couldn't even dream of with our simple file based Python dictionary.

Who lives in Any Street?

This is a fairly straightforward SQL query made simple by the fact that we have broken our address data into separate fields. If we had not done that we would have had to write string parsing code to extract the street data which is much more complex. The SQL query we need looks like this:

sqlite> SELECT First,Last FROM Address
   ...> WHERE Street = "Any Street";

Who is called Smith?

Again this is a fairly straightforward select/where SQL expression:

sqlite> SELECT First,Last FROM Address
   ...> WHERE Last = "Smith";

What is Dave's Phone number?

Again a straightforward query except that we get multiple results back.

sqlite> SELECT First,Last, Phone FROM Address
   ...> WHERE First Like "Dav%";

Notice we used Like in the where clause. This uses a wild card style comparison and ignores case. (Notice that the SQL wild card is a percent symbol (%) rather than the more common asterisk (*).) As a result it is a looser match than equality which requires an exact match. Notice that if we had only used D% as the wildcard pattern we would also have selected Debbie

What are the duplicated names?

This is a more complex query. We will need to select the entries which occur more than once. This is where the unique ContactID key comes into play:

sqlite> SELECT DISTINCT A.First, A.Last 
   ...> FROM Address as A, Address as B
   ...> WHERE A.First = B.First
   ...> AND A.Last = B.Last
   ...> AND NOT A.ContactID = B.ContactID;

We use a few new features here. The first thing is that we provide alias names, A and B, to the tables in the from clause. (We can also provide aliases for our result values too to save typing.) Also we use those aliases when referring to the result fields using the familiar dot notation. You can use an alias in any query but we are forced to do it here because we use the same table, Address, both times (thus joining it to itself!) so we need two aliases to distinguish the two instances in our where clause. We also introduce the DISTINCT keyword which results in us eliminating any duplicate results.

In summary, the query searches for rows with the same first and last names but different contactIDs, it then eliminates duplicate results prior to displaying them.

As with the Python interactive prompt the SqlLite interactive prompt is a powerful tool when developing more complex queries like this. You can start with a simple query and then build in the complexity as you go. For example the last part of the query that I added was the DISTINCT keyword, even though it's the second word in the final query!

Accessing SQL from Python

SQLite provides an Application Programmers Interface or API consisting of a number of standard functions which allow programmers to perform all the same operations that we have been doing using the interactive SQL prompt. The SQLite API is written in C but wrappers have been provided for other languages, including Python.

Cursors

When accessing a database from within a program one important consideration is how to access the multiple rows of data potentially returned by a select statement. The answer is to use what is known in SQL as a cursor. A cursor is like a Python sequence in that it can be accessed one row at a time. Thus by selecting into a cursor and then using a loop to access the cursor we can process large collections of data.

The DBAPI

The documentation for the latest version of the Python DB API is found in the Database Topic Guide on the Python website. You should read this carefully if you intend doing any significant database programming using Python.

Installing the SQLite drivers

   import sqlite3 as sqlite

If you don't have Python 2.5 installed yet then read on.

The Python DBAPI drivers for SQLite can be found here. Simply download the version corresponding to your version of Python and run the installer if you are using Windows, other OS users will need to follow the instructions on the web site! After it is installed you should be able to import the driver module like this:

from pysqlite2 import dbapi2 as sqlite

If no errors are reported then congratulations, the module is installed and usable.

Basic DBI usage

I am not going to cover all of the DBI features just enough for us to connect to our database and execute a few queries and process the results. We'll finish off by rewriting our address book program to use the address book database instead of a text file.

>>> db = sqlite.connect('D:/DOC/HomePage/Tutor2/sql/address.db')

>>> cur = db.cursor()
>>> cur.execute('Select * from address')

>>> print cur.fetchall()

And the results look like this:

[(1, u'Anna', u'Smith', u'42', u'Any Street', u'SomePlace', u'MyTown', u'ABC123', u'01234 567890'), 
 (2, u'Bob', u'Builder', u'17', u'Any Street', u'SomePlace', u'MyTown', u'ABC234', u'01234 543129'), 
 (3, u'Clarke', u'Kennit', u'9', u'Crypt Drive', u'Hotspot', u'MyTown', u'ABC345', u'01234 456459'),
 (4, u'Dave', u'Smith', u'42', u'Any Street', u'SomePlace', u'MyTown', u'ABC123', u'01234 567890'), 
 (5, u'Dave', u'Smith', u'12A', u'Double Street', u'', u'AnyTown', u'DEF174', u'01394 784310')]

As you can see the cursor returns a list of tuples. This is very similar to what we started off with back in the raw materials topic! And we could simply use this list in our program as if we had read it from a file, using the database merely as a persistence mechanism. However the real power of the database lies in its ability to perform sophisticated queries using select.

The Address Book - One last time

I'm now going to present our address book example for the final time. It's far from polished and is still command line based. You might like to add a GUI, remembering to refactor the code to separate function from presentation.

I won't explain every detail of the code, by now it should mostly be self evident if you read through it. I will however highlight a few points at the end.

###############################
# Addressbook.py
#
# Author: A J Gauld
#
# Build a simple addressbook using 
# the SQLite database and Python 
# DB-API.
###############################


# set up the database and cursor
dbpath = "D:/DOC/Homepage/Tutor2/sql/"
def initDB(path):
    from pysqlite2 import dbapi2 as sqlite
    try: 
        db = sqlite.connect(path)
        cursor = db.cursor()
    except : 
        print "Failed to connect to database:", path
        db,cursor = None,None
    return db,cursor

# Driver functions
def addEntry(book):
    first = raw_input('First name: ') 
    last =  raw_input('Last name: ') 
    house =  raw_input('House number: ') 
    street =  raw_input('Street name: ') 
    district =  raw_input('District name: ') 
    town =  raw_input('City name: ') 
    code =  raw_input('Postal Code: ') 
    phone =  raw_input('Phone Number: ') 
    query = '''INSERT INTO Address 
               (First,Last,House,Street,District,Town,PostCode,Phone)
               Values ("%s","%s","%s","%s","%s","%s","%s","%s")''' %\
               (first, last, house, street, district, town, code, phone)
    try:
       book.execute(query)
    except :  
       print "Insert failed"
       # raise
    return None

def removeEntry(book):
    name = raw_input("Enter a name: ")
    names = name.split()
    first = names[0]; last = names[-1]
    try:
       book.execute('''DELETE FROM Address 
                    WHERE First LIKE "%s" 
	            AND Last LIKE "%s"''' % (first,last))
    except : 
       print "Remove failed"
       # raise
    return None
    
def findEntry(book):
    field = raw_input("Enter a search field: ")
    value = raw_input("Enter a search value: ")
    query = '''SELECT first,last,house,street,district,town,postcode,phone 
               FROM Address WHERE %s LIKE "%s"''' % (field,value)
    try:
       book.execute(query)
    except : print "Sorry no matching data found"
    else: 
       for line in book.fetchall():
         print ' : '.join(line)
    return None

def testDB(database):
    database.execute("Select * from Address")
    print database.fetchall()
    return None

def closeDB(database, cursor):
    try:
       cursor.close()
       database.commit()
       database.close()
    except:
       print "problem closing database..."
       # raise
       
# User Interface functions
def getChoice(menu):
    print menu
    choice = raw_input("Select a choice(1-4): ")
    return choice
    
def main():
    theMenu = '''
    1) Add Entry
    2) Remove Entry
    3) Find Entry
    4) Test database connection
    9) Quit and save
    '''
    theDB, theBook = initDB(dbpath + 'address.db')
    choice = getChoice(theMenu)
    while choice != '9' and choice.upper() != 'Q':
        if choice == '1' or choice.upper() == 'A':
            addEntry(theBook)
        elif choice == '2' or choice.upper() == 'R':
            removeEntry(theBook)
        elif choice == '3' or choice.upper() == 'F':
            findEntry(theBook)
	elif choice == '4' or choice.upper() == 'T':
	    testDB(theBook)
        else: print "Invalid choice, try again"
        choice = getChoice(theMenu)
    else: closeDB(theDB,theBook)

if __name__ == '__main__': main()

Note that we have to put quotes around the strings within the SQL queries otherwise SQLite interprets them as field names. Also note that the closeDB function includes a call to commit. This forces the database to write all the changes in the current session back to the file, it can be thought of as being a little like the file.flush method. Finally note that the insert statement had to include all of the field names otherwise SQLite complained about a mismatch in the number of fields even though the ID field is declared as AUTOINCREMENT

One last point to note, which is unrelated to using databases but nonetheless is a useful debugging trick. Several of the functions have try/except constructs and in the except clause I've put in a raise statement which is now commented out. The raise is only there to aid debugging since, without it, the except clause would mask the full Python error report. However to restore user friendly reporting of errors it's easy to comment out this one line. This is much easier than trying to comment out the entire try/except construct during debugging.

A Word about Security

While the code above works and demonstrates how to call SQL from Python it does have one significant flaw. Because I used string formatting to construct the queries it is possible for a malicious user to enter some rogue SQL code as input. This rogue code then gets inserted into the query using the format string and is executed, potentially deleting vital data. To avoid that, the execute() API call has an extra trick up its sleeve. It can take the values that we use in a format string as an additional sequence parameter and insert them into the query for us. Unlike vanilla formatting however execute is smart enough to detect most rogue code and issue an error. It's not perfect and you do lose visibility of the actual SQL query submitted to the database (which can be useful when debugging) but if there is any possibility of users entering rogue data this is the best way to use execute. By way of an example here is our removeEntry() function with execute doing the formatting:

def removeEntry(book):
    name = raw_input("Enter a name: ")
    names = name.split()
    first = names[0]; last = names[-1]
    try:
       book.execute('''DELETE FROM Address 
                    WHERE First LIKE ? 
	            AND Last LIKE ?''', (first,last) )
    except : 
       print "Remove failed"
       # raise
    return None

Note the removal of the string formatting % operator and the introduction of the comma separating the query string argument from the value tuple argument. Also the formatting marker is now a question mark, however this value is specific to the database adaptor being used. Finally notice that we don't need to add quotes around the marker, execute is smart enough to fill those in itself.

A Final word

We have used SQLite for our examples because it is freely available, easy to install and use, and fairly forgiving of mistakes. However, this simplicity means that many of the more features found in more powerful packages are not available. In particular the text processing capabilities and the range of constraints available are quite limited. If you do find yourself confronted with a database like Oracle or IBM's DB2 it is well worth while taking the time to read the reference documentation, using the features of the database to best effect can significantly reduce the amount of custom code you need to write.

Some of the advanced features you should look out for are described in the box below:

Finally if you do want to explore some more sophisticated uses of SQLite there is an excellent tutorial by Mike Chirico which can be found in several places on the web, but the one I find easiest to read is found here. With the foundation material above imprinted on your mind you should have no problem following Mike's excellent tutor.