Managing & Using MySQL, 2nd Edition

Applied DB-API

So far, we have walked you through the DB-API and showed you its basic functionality. Now we will go through a practical example of a Python database application using the DB-API.

Our example is a batch routine that pulls stale orders from an order database and builds an XML file. Business partners can then download this XML file and import the order information into their databases. Example 10-3 shows a sample generated XML file.

Example 10-3. An XML file containing order information for a fictitious manufacturer

<?xml version="1.0"?>

<order orderID="101" date="2000" salesRepID="102">
  <customer customerID="100">
    <name>Wibble Retail</name>
    <address>
      <lines>
        <line>
          1818 Harmonika Rd.
        </line>
      </lines>
      <city>Osseo</city>
      <state>MN</state>
      <country>US</country>
      <postalCode>55369</postalCode>
    </address>
  </customer>
  <lineItem quantity="2">
    <unitCost currency="USD">12.99</unitCost>
    <product productID="104">
      <name>Wibble Scarf</name>
    </product>
  </lineItem>
  <lineItem quantity="1">
    <unitCost currency="USD">24.95</unitCost>
    <product productID="105">
      <name>Wibble Hat</name>
    </product>
  </lineItem>
</order>

The XML enables the business partners to trade information about orders without having to know anything about our data model. Every night, a Python script runs to look for orders that have not been converted to XML in the previous day. Any such orders are then read from the database and converted to XML.

The Python script, xmlgen.py, starts with a few simple imports:

import ...