10.3 Applied DB-API

only for RuBoard - do not distribute or recompile

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 sys, os; import traceback; import MySQLdb;

Much of the script defines Python objects that encapsulate the business objects in the database. Example 10-4 contains the code for these business objects.

Example 10-4. Business objects for the XML generator

 class Address:     def __init_  _(self, l1, l2, cty, st, ctry, zip):         self.line1 = l1;         self.line2 = l2;         self.city = cty;         self.state = st;         self.country = ctry;         self.postalCode = zip;     def toXML(self, ind):         xml = ('%s<address>\r\n' % ind);         xml = ('%s%s  <lines>\r\n' % (xml, ind));         if self.line1:             xml = ('%s%s    <line>\r\n%s      %s\r\n%s    </line>\r\n' %                    (xml, ind, ind, self.line1, ind));         if self.line2:             xml = ('%s%s    <line>\r\n%s      %s\r\n%s    </line>\r\n' %                    (xml, ind, ind, self.line2, ind));         xml = ('%s%s  </lines>\r\n' % (xml, ind));         if self.city:             xml = ('%s%s  <city>%s</city>\r\n' % (xml, ind, self.city));         if self.state:             xml = ('%s%s  <state>%s</state>\r\n' % (xml, ind, self.state));         if self.country:             xml = ('%s%s  <country>%s</country>\r\n' % (xml,ind,self.country));         if self.postalCode:             xml = ('%s%s  <postalCode>%s</postalCode>\r\n' %                    (xml, ind, self.postalCode));         xml = ('%s%s</address>\r\n' % (xml, ind));         return xml;      class Customer:     def __init__(self, cid, nom, addr):         self.customerID = cid;         self.name = nom;         self.address = addr;     def toXML(self, ind):         xml = ('%s<customer customerID="%s">\r\n' % (ind, self.customerID));         if self.name:             xml = ('%s%s  <name>%s</name>\r\n' % (xml, ind, self.name));         if self.address:             xml = ('%s%s' % (xml, self.address.toXML(ind + '  ')));         xml = ('%s%s</customer>\r\n' % (xml, ind));         return xml; class LineItem:     def __init__(self, prd, qty, cost):         self.product = prd;         self.quantity = qty;         self.unitCost = cost;     def toXML(self, ind):         xml = ('%s<lineItem quantity="%s">\r\n' % (ind, self.quantity));         xml = ('%s%s  <unitCost currency="USD">%s</unitCost>\r\n' %                (xml, ind, self.unitCost));         xml = ('%s%s' % (xml, self.product.toXML(ind + '  ')));         xml = ('%s%s</lineItem>\r\n' % (xml, ind));         return xml;                 class Order:     def __init__(self, oid, date, rep, cust):         self.orderID = oid;         self.orderDate = date;         self.salesRep = rep;         self.customer = cust;         self.items = [];     def toXML(self, ind):         xml = ('%s<order orderID="%s" date="%s" salesRepID="%s">\r\n' %                (ind, self.orderID, self.orderDate, self.salesRep));         xml = ('%s%s' % (xml, self.customer.toXML(ind + '  ')));         for item in self.items:             xml = ('%s%s' % (xml, item.toXML(ind + '  ')));         xml = ('%s%s</order>\r\n' % (xml, ind));         return xml; class Product:     def __init__(self, pid, nom):         self.productID = pid;         self.name = nom;     def toXML(self, ind):         xml = ('%s<product productID="%s">\r\n' % (ind, self.productID));         xml = ('%s%s  <name>%s</name>\r\n' % (xml, ind, self.name));         xml = ('%s%s</product>\r\n' % (xml, ind));         return xml;

Each business object defines two basic methods . The first, the constructor, does nothing more than assign values to the object's attributes. The second method, toXML( ) , converts the business object to XML. So far, we have kept all database access separate from our business objects. This is a very critical design element of good database programming.

All database access comes in a module method called executeBatch( ) . The purpose of this method is to find out which orders need XML generated and load them from the database into business objects. It then takes these loaded orders and sends the return value of toXML( ) to an XML file. Example 10-5 shows the executeBatch( ) method.

Example 10-5. Database access for the XML generator

 def executeBatch(conn):     try:         cursor = conn.cursor(  );         cursor.execute("SELECT ORDER_ID FROM ORDER_EXPORT " +                        "WHERE LAST_EXPORT <> CURRENT_DATE(  )");         orders = cursor.fetchall(  );         cursor.close(  );     except:         print "Error retrieving orders.";         traceback.print_exc(  );         conn.close(  );         exit(0);                  for row in orders:         oid = row[0];         try:             cursor = conn.cursor(  );             cursor.execute("SELECT CUST_ORDER.ORDER_DATE, " +                            "CUST_ORDER.SALES_REP_ID, " +                            "CUSTOMER.CUSTOMER_ID, " +                            "CUSTOMER.NAME, " +                            "CUSTOMER.ADDRESS1, " +                            "CUSTOMER.ADDRESS2, " +                            "CUSTOMER.CITY, " +                            "CUSTOMER.STATE, " +                            "CUSTOMER.COUNTRY, " +                            "CUSTOMER.POSTAL_CODE " +                            "FROM CUST_ORDER, CUSTOMER " +                            "WHERE CUST_ORDER.ORDER_ID = %s " +                            "AND CUST_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID",                            ( oid ) );             row = cursor.fetchone(  );             cursor.close(  );             addr = Address(row[4], row[5], row[6], row[7], row[8], row[9]);             cust = Customer(row[2], row[3], addr);             order = Order(oid, row[0], row[1], cust);             cursor = conn.cursor(  );             cursor.execute("SELECT LINE_ITEM.PRODUCT_ID, " +                            "LINE_ITEM.QUANTITY, " +                            "LINE_ITEM.UNIT_COST, " +                            "PRODUCT.NAME " +                            "FROM LINE_ITEM, PRODUCT " +                            "WHERE LINE_ITEM.ORDER_ID = %s " +                            "AND LINE_ITEM.PRODUCT_ID = PRODUCT.PRODUCT_ID",                            oid);             for row in cursor.fetchall(  ):                 prd = Product(row[0], row[3]);                 order.items.append(LineItem(prd, row[1], row[2]));         except:             print "Failed to load order: ", oid;             traceback.print_exc(  );             exit(0);                      try:             cursor.close(  );         except:             print "Error closing cursor, continuing...";             traceback.print_exc(  );         try:             fname = ('%d.xml' % oid);             xmlfile = open(fname, "w");             xmlfile.write('<?xml version="1.0"?>\r\n\r\n');             xmlfile.write(order.toXML(''));             xmlfile.close(  );         except:             print ("Failed to write XML file: %s" % fname);             traceback.print_exc(  );                      try:             cursor = conn.cursor(  );             cursor.execute("UPDATE ORDER_EXPORT " +                            "SET LAST_EXPORT = CURRENT_DATE(  ) " +                            "WHERE ORDER_ID = %s", ( oid ));         except:             print "Failed to update ORDER_EXPORT table, continuing";             traceback.print_exc(  );

The first try / except block looks in the ORDER_EXPORT table for all orders that have not had XML generated in the previous day. If that fails for any reason, the script bails completely.

Each row returned from fetchall( ) represents an order in need of exporting. The script therefore loops through each of the rows and loads all the data for the order represented by the row. Inside the for loop, the script executes SQL to pull order and customer data from the ORDER and CUSTOMER tables. With those columns in hand, it can construct Order , Customer , and Address objects for the order, its associated customer, and the customer's address. Because ORDER to LINE_ITEM is a one-to-many relationship, we need a separate query to load the LineItem objects. The next query looks for all the line items associated with the current order ID and loads business objects for them.

With all the data loaded into business objects, the script opens a file and writes out its XML conversion to that file. Once the write is successful, the script goes back to the database to note that the XML is now up to date with the database. The script then goes to the next order and continues processing until there are no more orders.

The last part missing from the script is the functionality to call executeBatch( ) :

 if __name__ == '__main__':     try:         conn = MySQLdb.connect(host='carthage', user='test', passwd='test',                                db='Test');     except:         print "Error connecting to MySQL:";         traceback.print_exc(  );         exit(0);     executeBatch(conn);

This script, along with the SQL to generate the tables and data behind, it are available with the rest of the examples from this book at the O'Reilly web site.

only for RuBoard - do not distribute or recompile