Enter the following code and name your file dbmcreate.py:

import dbm

db = dbm.open('websites', 'c')

# Add an item.
db['www.python.org'] = 'Python home page'

print(db['www.python.org'])

# Close and save to disk.
db.close()

When you run this script, you'll see output like the following:

$ python dbmcreate.py
b 'Python home page'

How It Works

This example uses the recommended dbm module.

The open function requires the name of the dictionary to create. This name gets translated into the name of the data file or files that may already be on the disk. (The dbm module may — though not always — create more than one file, usually a file for the data and one for the index of the keys.) The name of the dictionary is treated as a base file name, including the path. Usually, the underlying dbm library will append a suffix such as .dat for data. You can find the file yourself by looking for the file named websites, most likely in your current working directory.

You should also pass the optional flag. The following table lists the available flags.

You can also pass another optional parameter, the mode. The mode holds a set of UNIX file permissions. See Chapter 8 for more on opening files.

db['www.python.org'] = 'Python home page'

Enter the following script and name the file dbmaccess.py:

import dbm

# Open existing file.
db = dbm.open('websites', 'w')

# Add another item.
db['www.wrox.com'] = 'Wrox home page'

# Verify the previous item remains.
if db['www.python.org'] != None:
    print('Found www.python.org')
else:
    print('Error: Missing item')

# Iterate over the keys. May be slow.
# May use a lot of memory.
for key in db.keys():
    print("Key =",key," value =",db[key])

del db['www.wrox.com']
print("After deleting www.wrox.com, we have:")

for key in db.keys():

print("Key =",key," value =",db[key])

# Close and save to disk.
db.close()

When you run this script, you'll see output similar to the following:

$ python dbmaccess.py
Type of dbmfile = dbhash
Found www.python.org
Key = www.wrox.com  value = Wrox home page
Key = www.python.org  value = Python home page
After deleting www.wrox.com, we have:
Key = www.python.org  value = Python home page

How It Works

This script works with a small database of website URLs and descriptions. You need to first run the dbmcreate.py example, shown previously. That example creates the dbm file and stores data in the file. The dbmaccess.py script then opens the preexisting dbm file.

The dbmaccess.py script opens the persistent dictionary websites in read/write mode. The call to the open function will generate an error if the necessary data file or files do not exist on disk in the current directory.

From the previous example, dbmcreate.py, there should be one value in the dictionary, under the key www.python.org. This example adds the Wrox website, www.wrox.com, as another key.

The script verifies that the www.python.org key exists in the dictionary, using the following code:

if db['www.python.org'] != None:
    print('Found www.python.org')
else:
    print('Error: Missing item')

Next, the script prints out all of the keys and values in the dictionary:

for key in db.keys():
    print("Key =",key," value =",db[key])

Note that there should be only these two entries.

After printing out all of the entries, the script removes one using del:

del db['www.wrox.com']

The script then prints all of the keys and values again, which should result in just one entry, as shown in the output.

Finally, the close method closes the dictionary, which involves saving all the changes to disk, so the next time the file is opened it will be in the state you left it.

As you can see, the API for working with persistent dictionaries is incredibly simple because it works like files and like dictionaries, which you're already familiar with.

Enter the following script and name the file createdb.py:

import os
import sqlite3
conn=sqlite3.connect('sample_database')
cursor=conn.cursor()
# Create tables.
cursor.execute("""
create table employee

(empid integer,
firstname varchar,
lastname varchar,
dept integer,
manager integer,
phone varchar)
""")
cursor.execute("""
create table department
    (departmentid integer,
    name varchar,
    manager integer)
""")
cursor.execute("""
create table user
    (userid integer,
    username varchar,
    employeeid integer)
""")
# Create indices.
cursor.execute("""create index userid on user (userid)""")
cursor.execute("""create index empid on employee (empid)""")
cursor.execute("""create index deptid on department (departmentid)""")
cursor.execute("""create index deptfk on employee (dept)""")
cursor.execute("""create index mgr on employee (manager)""")
cursor.execute("""create index emplid on user (employeeid)""")
cursor.execute("""create index deptmgr on department (manager)""")
conn.commit()
cursor.close()
conn.close()

When you run this script, you should see no output unless the script raised an error.

How It Works

Sqlite has its own API along with the standard Python DB API. This script uses the Sqlite API, but you'll notice that this API is very similar to the DB API covered in the following section, "Using the Python Database APIs." This section briefly describes the Sqlite specific code in the creatdb.py script.

Following is the code used to create a Connection object using the Sqlite module:

conn=sqlite3.connect('sample_database')

From there, the script gets a Cursor object, covered in the section "Working with Cursors." The Cursor object is used to create three tables and define indexes on these tables.

The script calls the commit method on the connection to save all the changes to disk.

Sqlite stores all of its data in the file sample_database. After running the createdb.py script, you should see the file in your Python30 directory.

You are now ready to start working with the Python database APIs.

Enter the following script and name the file insertdata.py:

import os
import sqlite3

conn=sqlite3.connect('sample_database')
cursor = conn.cursor()

# Create employees.
cursor.execute("""
insert into employee (empid,firstname,lastname,manager,dept,phone)
values (1,'Eric','Foster-Johnson',1,1,'555-5555')""")

cursor.execute("""
insert into employee (empid,firstname,lastname,manager,dept,phone)
values (2,'Peter','Tosh',2,3,'555-5554')""")

cursor.execute("""
insert into employee (empid,firstname,lastname,manager,dept,phone)
values (3,'Bunny','Wailer',2,2,'555-5553')""")

# Create departments.
cursor.execute("""
insert into department (departmentid,name,manager)
values (1,'development',1)""")

cursor.execute("""
insert into department (departmentid,name,manager)
values (2,'qa',2)""")

cursor.execute("""
insert into department (departmentid,name,manager)
values (3,'operations',2)""")

# Create users.
cursor.execute("""
insert into user (userid,username,employeeid)
values (1,'ericfj',1)""")

cursor.execute("""
insert into user (userid,username,employeeid)
values (2,'tosh',2)""")

cursor.execute("""
insert into user (userid,username,employeeid)
values (3,'bunny',3)""")

conn.commit()

cursor.close()

conn.close()

When you run this script, you will see no output unless the script raises an error.

How It Works

The first few lines of this script set up the database connection and create a cursor object:

import os
import sqlite3
conn=sqlite3.connect('sample_database')
cursor = conn.cursor()

Note how we connect to an Sqlite database. To connect to a different database, replace this with your database-specific module, and modify the call to use the connect function from that database module, as needed.

The next several lines execute a number of SQL statements to insert rows into the three tables set up earlier: employee, department, and user. The execute method on the cursor object executes the SQL statement:

cursor.execute("""
insert into employee (empid,firstname,lastname,manager,dept,phone)
values (2,'Peter','Tosh',2,3,'555-5554')""")

This example uses a triple-quoted string to cross a number of lines as needed. You'll find that SQL commands, especially those embedded within Python scripts, are easier to understand if you can format the commands over a number of lines. This becomes more important with complex queries covered in examples later in this chapter.

To save your changes to the database, you must commit the transaction:

conn.commit()

Note that this method is called on the connection, not the cursor.

When you are done with the script, close the cursor and then the connection to free up resources. In short scripts like this, it may not seem important, but this helps the database program free its resources, as well as your Python script:

cursor.close()

conn.close()

You now have a very small amount of sample data to work with using other parts of the DB API, such as querying for data.

The following script implements a simple query that performs a join on the employee and department tables:

import os
import sqlite3
conn=sqlite3.connect('sample_database')
cursor = conn.cursor()
cursor.execute("""
select employee.firstname, employee.lastname, department.name
from employee, department
where employee.dept = department.departmentid
order by employee.lastname desc
""")
for row in cursor.fetchall():
    print(row)
cursor.close()
conn.close()

Save this script under the name simplequery.py. When you run this script, you will see output like the following:

('Bunny', 'Wailer', 'qa')
('Peter', 'Tosh', 'operations')
('Eric', 'Foster-Johnson', 'development')

How It Works

This script initializes the connection and cursor in the same manner as the previous script. This script, though, passes a simple join query to the cursor execute method. This query selects two columns from the employee table and one from the department table.

When working with user interfaces, you will often need to expand IDs stored in the database to human-readable values. In this case, for example, the query expands the department ID, querying for the department name. You simply cannot expect people to remember the meaning of strange numeric IDs.

The query also orders the results by the employees' last names, in descending order. (This means that it starts at the beginning of the alphabet, which is what you'd normally expect. However, you can reverse this and have them sorted in ascending order.)

After calling the execute method, the data, if any was found, is stored in the cursor object. You can use the fetchall method to extract the data.

Note how the data appears as Python tuples:

('Bunny', 'Wailer', 'qa')
('Peter', 'Tosh', 'operations')
('Eric', 'Foster-Johnson', 'development')

You can use this example as a template to create other queries, such as the more complex join shown in the following Try It Out.

Enter this script and name the file finduser.py:

import sqlite3
conn=sqlite3.connect('sample_database')
cursor = conn.cursor()
username = 'bunny'
query = """
select u.username,e.firstname,e.lastname,m.firstname,m.lastname, d.name
from user u, employee e, employee m, department d where username=?
and u.employeeid = e.empid
and e.manager = m.empid
and e.dept = d.departmentid
"""
cursor.execute(query, (username,))
for row in cursor.fetchall():
    (username,firstname,lastname,mgr_firstname,mgr_lastname,dept) = row
    name=firstname + " " + lastname
    manager=mgr_firstname + " " + mgr_lastname
    print(username,":",name,"managed by",manager,"in",dept)
cursor.close()
conn.close()

When you run this script, you will see results like the following:

bunny : Bunny Wailer managed by Peter Tosh in qa

You need to pass the user name of a person to query from the database. This must be a valid user name of a person in the database. In this example, bunny is a user name previously inserted into the database.

How It Works

This script performs a join on all three example tables, using table-name aliases to create a shorter query. The purpose is to find a given user in the database by searching for that user name. This script also shows an example of expanding both the manager's ID to the manager's name and the department's ID to the department's name. All of this makes for more readable output.

This example also shows how you can extract data from each row into Python variables. For example:

(username,firstname,lastname,mgr_firstname,mgr_lastname,dept) = row

Note that this is really nothing new. See Chapter 3 for more on Python tuples, which is all row is.

An important new feature of this script, though, is the use of a question mark to enable you to build a query using dynamic data. When you call the execute method on the Cursor, you can pass a tuple of dynamic data, which the execute method will fill in for the question marks in the SQL statement. (This example uses a tuple of one element.) Each element in the tuple is used, in order, to replace the question marks. Thus, it is very important to have as many dynamic values as you do question marks in the SQL statement, as shown in the following example:

query = """
select u.username,e.firstname,e.lastname,m.firstname,m.lastname, d.name
from user u, employee e, employee m, department d where username=?
and u.employeeid = e.empid
and e.manager = m.empid
and e.dept = d.departmentid
"""
cursor.execute(query, (username,))

The query used in this example is very helpful when you want to start updating rows in the tables. That's because users will want to enter meaningful values. It is up to you, with your SQL statements, to translate the user input into the necessary IDs.

For example, the following script enables you to change the manager for an employee:

Enter the following script and name the file updatemgr.py:

import sqlite3
import sys
conn=sqlite3.connect('sample_database')
cursor = conn.cursor()
newmgr   = sys.argv[2]
employee = sys.argv[1]
# Query to find the employee ID.
query = """
select e.empid
from user u, employee e
where username=? and u.employeeid = e.empid
"""
cursor.execute(query,(newmgr,));
for row in cursor.fetchone():
    if (row != None):
        mgrid = row
# Note how we use the same query, but with a different name.
cursor.execute(query,(employee,));
for row in cursor.fetchone():
    if (row != None):

empid = row
# Now, modify the employee.
cursor.execute("update employee set manager=? where empid=?", (mgrid,empid))
conn.commit()
cursor.close()
conn.close()

When you run this script, you need to pass the name of the user to update, as well as the name of the manager. Both names are user names from the user table. For example:

$ python finduser.py bunny
bunny : Bunny Wailer managed by Peter Tosh in qa
$ python updatemgr.py bunny ericfj
$ python finduser.py bunny
bunny : Bunny Wailer managed by Eric Foster-Johnson in qa

How It Works

The example output shows the before and after picture of the employee row, verifying that the updatemgr.py script worked.

The updatemgr.py script expects two values from the user: the user name of the employee to update and the user name of the new manager. Both of these names must be user names stored in the database. Both names are converted into IDs using a simple query. This is not very efficient, because it involves two extra round-trips to the database. A more efficient means would be to perform an inner select statement on the update statement. For simplicity, though, the separate queries are far easier to understand.

This example also shows the use of the fetchone method on the Cursor. The final SQL statement then updates the employee row for the given user to have a new manager.

The next example uses a similar technique to terminate an employee. You can really have fun with this one (terminate your friends, your enemies, and so on).

Enter the following script and name the file terminate.py:

import sqlite3
import sys
conn=sqlite3.connect('sample_database')
cursor = conn.cursor()
employee=sys.argv[1]
# Query to find the employee ID.
query = """
select e.empid
from user u, employee e
where username=? and u.employeeid = e.empid
"""

cursor.execute(query,(employee,));
for row in cursor.fetchone():
    if (row != None):
        empid = row
# Now, modify the employee.
cursor.execute("delete from employee where empid=?", (empid,))
conn.commit()
cursor.close()
conn.close()

When you run this script, you need to pass the user name of the person to terminate. You should see no output unless the script raises an error:

$ python finduser.py bunny
bunny : Bunny Wailer managed by Eric Foster-Johnson in qa
$ python terminate.py bunny
$ python finduser.py bunny

How It Works

This script uses the same techniques as the updatemgr.py script by performing an initial query to get the employee ID for the given user name and then using this ID in a later SQL statement. With the final SQL statement, the script deletes the employee from the employee table.