If you’ve installed your database of choice, your first task is to create a database inside of the system. If you are using SQL Server or your database offers an administrative GUI, this process may be as easy as typing a database name into a dialog box. For example, if using SQL Server, just browse to the databases folder using SQL Enterprise Manager. Once there, right-click and choose New Database. The name of the database should be Profiles. If you don’t have a GUI, a SQL statement as simple as the following should suffice:

CREATE DATABASE Profiles

Once created, you may want to enable an account that has read and write privileges to this database, but to no others. Consult your database’s documentation for details on creating specific user accounts. For the purposes of this example, in SQL Server the user webuser has been created, with a password of w3bus3r. The authentication information is required in the ODBC connectivity code.

Once you’ve created a database using either a GUI or SQL statements, create one simple table named Customer. This table represents some basic user information. It will be used by the different distributed applications as the one and only customer information record. While the fields in this table only cover the basics, you could easily expand them with other types of information related to the system.

The Customer table can be created with a GUI in SQL Enterprise Manager for SQL Server, or with the following SQL in any database:

CREATE TABLE Customer (
        firstname varchar (255) NULL,
        lastname varchar (255) NULL,
        address1 varchar (255) NULL,
        address2 varchar (255) NULL,
        city varchar (255) NULL,
        state varchar (2) NULL,
        zip varchar (10) NULL,
        customerId varchar (40) NULL)

The table is very simple. All of the data types are varchar and can easily be handled in Python as strings and integers. One thing to note about the Customer table is the varying length of the different fields. For example, most of the customer information may be zero to 256 characters in length. However, others in the table must conform to constraints such as two characters for a state abbreviation, and a 10-digit requirement on the zip code.

If you are using SQL Server, remember to expose your new database as an ODBC source on the machine you’re running any database clients on—in this example application, only the XML Switch, which loads the CustomerProfile class, needs database connectivity. To enable connectivity to SQL Server, use the ODBC manager in the Windows’ Control Panel to choose your database. Once this step is completed, the ODBC code presented here will work.

You can populate the fields in your new table with an SQL statement similar to the following:

insert into Customer values('John',
  'Smith',
  '123 Evergreen Terrace',
  '',
  'Podunk',
  'WA',
  '98072',
  '234-E838839')

This statement creates a new row in the database table with the corresponding values contained in quotes. If you want to fill your database with several rows, you can resort to good, old-fashioned data entry with the popdb.py script shown in Example 10-1. This simple script just reads input from the command line and inserts it into the database. It’s designed for use with the ODBC module and SQL Server, so if using another database, you need to adapt the connectivity code.

"""
popdb.py - populate the Profiles/Customer DB with ODBC calls
"""
import dbi, odbc
conn = odbc.odbc("Profiles/webuser/w3bus3r")
cmd = conn.cursor(  )

# loop to get input values.
while 1:
  firstname = raw_input("firstname:")
  lastname  = raw_input("lastname:")
  address1  = raw_input("address1:")
  address2  = raw_input("address2:")
  city      = raw_input("city:")
  state     = raw_input("state, 2 letter max:")
  zip       = raw_input("zip, 10 digit max:")
  customerId = raw_input("Customer ID, 40 character max length:")

  # execute SQL statement
  cmd.execute("insert into Customer values('"
              + firstname  + "', '"
              + lastname   + "', '"
              + address1   + "', '"
              + address2   + "', '"
              + city       + "', '"
              + state      + "', '"
              + zip        + "', '"
              + customerId + "')")

  # ask for additional entries
  finished = raw_input("another? [y/n]:")
  if (finished == "n"):
      break

There is no error checking in popdb.py, so if you violate one of the table constraints, you get an exception, and that particular row won’t be inserted.