Explaining the installation of a specific database is outside the scope of this book. However, if you are completely new to databases, we provide a quick overview of how this particular database was set up and used in this example. This overview broadly applies to any database systems, including SQL Server, Postgres, and MySQL. As a Python XML developer gluing together applications, understanding at least the fundamentals of working with databases will serve you well.
We used Microsoft SQL Server during the creation of this example as our relational database. However, only one simple table is created, and any database that supports SQL queries should be fine. A SQL table creation script is provided that should work on just about any SQL platform. However, the Python connectivity code presented here uses ODBC for access. If you choose to use a different database than SQL Server, you may need to download a Python API to access it. For example, the MySQLdb API is available at http://sourceforge.net/projects/mysql-python/ to provide access from Python to MySQL databases. Regardless of your connectivity API, the SQL calls shown in this chapter should be identical.
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.