Introduction To Python Programming: Assignment 1

Introduction to Python Programming

Final Project

#!/usr/bin/env python

# nwindA2X.py

# Description: list records from North Wind ODBC database using SQL

# and output xml file

# Author: Suzanne Berger, tangle1@mindspring.com

# Company: UBC Continuing Ed

# Date: August 2, 2006

import sys,os

import string

import getopt

import mx.ODBC.Windows

#----------------------------------------------------------

# ampersand has special meaning in xml

# so delete ampersand to end of string

# disclaimer: this assumes only one ampersand

def delampersand(foo):

fix = -1

fix = foo.find('&')

if (fix > -1):

foo = foo[:fix]

return foo

#-------------------------------------------------------

# methods to create the xml tags we need

def opentag(tagname, level, *tagattrs):

tagattr_str = ''

if tagattrs:

for i in range(0,len(tagattrs)):

tagattr_str = '%s %s' % (tagattr_str,tagattrs[i])

tagstr = '<%s%s>' % (tagname,tagattr_str)

tagwidth = (2 * level) + len(tagstr)

line = tagstr.rjust(tagwidth) + '\n'

return line

def closetag(tagname, level):

tagstr = '</%s>' % tagname

tagwidth = (2 * level) + len(tagstr)

line = tagstr.rjust(tagwidth) + '\n'

return line

def openclosetag(tagname, level, tagvalue):

if (type(tagvalue) == float):

tagstr = '<%s>%.1f</%s>' % (tagname, tagvalue, tagname)

tagstr = '<%s>%s</%s>' % (tagname, tagvalue, tagname)

tagwidth = (2 * level) + len(tagstr)

line = tagstr.rjust(tagwidth) + '\n'

return line

#-------------------------------------------------------

# usage

def usage(name):

print """

Usage: %s [-h|--help] [-v|--verbose] [<xml file path>]

Arguments:

<nwind xml file path> xml file, if ommitted then write to nwind.xml in current directory

Options:

-h|--help

-r|--verbose verbose mode

"""%name

#-------------------------------------------------------

def main():

# process command line arguments

try:

opts, args = getopt.getopt(sys.argv[1:], 'hv', ['help','verbose'])

except getopt.GetoptError:

print 'Error: invalid argument'

usage(sys.argv[0])

sys.exit(1)

# parse options

verbose = False # verbose off by default

for o, a in opts:

if o in ('-h', '--help'):

usage(sys.argv[0])

sys.exit()

if o in ('-v', '--verbose'):

verbose = True

# get the xml file path argument or set default

if len(args) > 0:

xmlFile = args[0]

else:

xmlFile = "nwind.xml"

# make sure the xml file can be opened for write

try:

outXml = open(xmlFile,"w")

except:

print 'Error: unable to open %s for write' % xmlFile

sys.exit(2)

#-------------------------------------------------------

# create data structures for SQL query

selItems = ['O.OrderID',

'OD.UnitPrice',

'OD.Quantity',

'C.CustomerID',

'C.CompanyName',

'P.ProductID',

'P.ProductName',

'S.SupplierID',

'S.CompanyName'

]

colItems = string.join(selItems,',') # make select string for SQL query

tableItems = 'Orders O,[Order Details] OD,Customers C,Products P,Suppliers S'

cond = ['O.OrderID = OD.OrderID',

'O.CustomerID = C.CustomerID',

'OD.ProductID = P.ProductID',

'P.SupplierID = S.SupplierID']

condItems = string.join(cond, ' AND ')

queryItems = {'OrderID':'',

'UnitPrice':'',

'Quantity':'',

'CustomerID':'',

'CompanyName':'',

'ProductID':'',

'ProductName':'',

'SupplierID':'',

'SupplierName':''}

# initialize dictionaries to hold order info and product info

dbDict = {}

prDict = {}

#-------------------------------------------------------

# set up to connect to db

database = 'nwind'

dbConstr = 'DSN=%s;UID=;PWD=' % database

# be sure there is valid database connection

try:

db = mx.ODBC.Windows.DriverConnect(dbConstr)

c = db.cursor()

except:

print 'Error: cannot connect to database'

sys.exit(2)

# make the sql SELECT query string and then do the query

sqlSelect = 'SELECT %s FROM %s WHERE %s' % (colItems,tableItems,condItems)

# but make sure its a good query

try:

c.execute(sqlSelect)

except:

print 'Error: invalid query'

sys.exit(3)

#-------------------------------------------------------

# start to iterate through the data base

numberOfOrders = 0

numberOfRows = 0

# list the record items

while(1):

row = c.fetchone()

if row == None: break

queryItems['OrderID'] = row[0]

queryItems['UnitPrice'] = row[1]

queryItems['Quantity'] = row[2]

queryItems['CustomerID'] = row[3]

queryItems['CompanyName'] = delampersand(row[4])

queryItems['ProductID'] = row[5]

queryItems['ProductName'] = delampersand(row[6])

queryItems['SupplierID'] = row[7]

queryItems['SupplierName'] = delampersand(row[8])

# if productID is not in product dictionary then add it and

# assign tuple with productName, SupplierID, and SupllierName

if not (prDict.has_key(queryItems['ProductID'])):

prDict[queryItems['ProductID']] = (queryItems['ProductName'],queryItems['SupplierID'],queryItems['SupplierName'])

# add each orderID to orderID dictionary,

# then assign customerID and companyname and start list of tuples containing productID, unit price, and quatity

# if orderID is already in the dictionary, just append new product tuple

if dbDict.has_key(queryItems['OrderID']):

orderList = dbDict[row[0]][2]

orderList.append((queryItems['ProductID'],queryItems['UnitPrice'],queryItems['Quantity']))

dbDict[queryItems['OrderID']][2] = orderList

else:

dbDict[queryItems['OrderID']] = [queryItems['CustomerID'],queryItems['CompanyName'],

[(queryItems['ProductID'],queryItems['UnitPrice'],queryItems['Quantity'])]]

if verbose:

keys = queryItems.keys()

for key in keys:

print '%s %s' % (key.ljust(20),queryItems[key])

print '-------------------------------------------'

numberOfOrders = numberOfOrders + 1

numberOfRows = numberOfRows + 9

print 'Number of Orders = %s and Number of Rows = %s' % (numberOfOrders, numberOfRows)

#-------------------------------------------------------

# now write to xml file using order and product info in dictionaries

outXml.write('<?xml version="1.0" encoding="ISO-8859-1"?>\n')

outXml.write(opentag('nwind',0))

keys = dbDict.keys()

for key in keys:

outXml.write(opentag('orders',1,'orderid = "%s"' % key))

outXml.write(opentag('customers',2))

companyname = dbDict[key][1]

outXml.write(openclosetag('companyname',3,dbDict[key][1]))

outXml.write(openclosetag('customerid',3,dbDict[key][0]))

outXml.write(closetag('customers',2))

orderlist = dbDict[key][2]

for i in range(0, len(orderlist)):

outXml.write(opentag('orderdetails',2))

outXml.write(opentag('products',3))

productid = orderlist[i][0]

productname = prDict[productid][0]

outXml.write(openclosetag('productid',4,productid))

outXml.write(openclosetag('productname',4,productname))

outXml.write(closetag('products',3))

unitprice = orderlist[i][1]

quantity = orderlist[i][2]

outXml.write(openclosetag('unitprice',3,unitprice))

outXml.write(openclosetag('quantity',3,quantity))

outXml.write(opentag('suppliers',3))

supplierid = prDict[productid][1]

companyname = prDict[productid][2]

outXml.write(openclosetag('supplierid',4,supplierid))

outXml.write(openclosetag('companyname',5,companyname))

outXml.write(closetag('suppliers',3))

outXml.write(closetag('orderdetails',2))

outXml.write(closetag('orders',1))

outXml.write(closetag('nwind',0))

outXml.close()

if __name__ == '__main__':

main()

#!/usr/bin/env python

# parseNwindXml.py

# Description: parse North Wind data base XML file and print statistics

# Author: Suzanne Berger, tangle1@mindspring.com

# Company: UBC Continuing Ed

# Date: August 2, 2006

import sys,os

import string

import getopt

from elementtree.ElementTree import ElementTree,Element,SubElement

#-------------------------------------------------------

# usage

def usage(name):

print """

Usage: %s [-h|--help] [-v|--verbose] [<nwind xml file path>]

Arguments:

<input nwind xml file path> xml file, if ommitted then parse nwind.xml from current directory

Options:

-h|--help

-r|--verbose verbose mode

"""%name

#-------------------------------------------------------

def main():

# process command line arguments

try:

opts, args = getopt.getopt(sys.argv[1:], 'hv', ['help','verbose'])

except getopt.GetoptError:

print 'Error: invalid argument'

usage(sys.argv[0])

sys.exit(1)

# parse options

verbose = False # verbose off by default

for o, a in opts:

if o in ('-h', '--help'):

usage(sys.argv[0])

sys.exit()

if o in ('-v', '--verbose'):

verbose = True

# get the xml file path argument or set default

if len(args) > 0:

xmlFile = args[0]

else:

xmlFile = "nwind.xml"

# initialize data

avgOrderPrice = 0.0

maxOrderPrice = 0.0

avgNumProducts = 0

avgQuantityProducts = 0

maxProductOrder = 0

maxCompanyName = ''

maxCustomerId = ''

maxSupplierName = ''

maxSupplierId = ''

numOrders = 0

numProductOrders = 0

totalPrice = 0.0

totalQuantity = 0

prDict = {}

try:

tree = ElementTree(file=xmlFile)

except:

print 'Error: ElementTree is unable to parse %s' % xmlFile

sys.exit(2)

elem = tree.getroot()

orderlist = elem.getiterator("orders")

for element in orderlist:

if (verbose and element.keys()):

for attr, value in element.items():

print "************************************************************"

print "Orders: '%s = %s'"%(attr, value)

if element.getchildren():

for child in element:

if (child.tag == "customers"):

companyname = child.findtext("companyname")

customerid = child.findtext("customerid")

orderTotal = 0.0

numOrders += 1

if verbose:

print "Customerid = %s Companyname = %s" % (customerid,companyname)

print "-----------------------------------------------"

elif (child.tag == "orderdetails"):

productid = supplierid = suppliername = ""

numProductOrders += 1

# get product info

prodelem = child.getiterator("products")

products = prodelem[0].getchildren()

for item in products:

if (item.tag == "productid"):

productid = item.text

elif (item.tag == "productname"):

productname = item.text

unitprice = float(child.findtext("unitprice"))

quantity = int(child.findtext("quantity"))

totalQuantity += quantity

price = quantity * unitprice

# get supplier info

suppelem = child.getiterator("suppliers")

suppliers = suppelem[0].getchildren()

for item in suppliers:

if (item.tag == "supplierid"):

supplierid = item.text

elif (item.tag == "companyname"):

suppliername = item.text

if (prDict.has_key(productid)):

prDict[productid] += quantity

else:

prDict[productid] = quantity

# print if verbose

if verbose:

print "\tProductID = %s ProductName = %s" % (productid,productname)

print "\tUnitprice = %s Quantity = %s Totalprice = %.2f" % (unitprice,quantity, price)

print "---------------------------------------------------"

# compare with running max product total and max order price

if (prDict[productid] > maxProductOrder):

maxProductOrder = prDict[productid]

maxProductName = productname

maxSupplierId = supplierid

maxSupplierName = suppliername

totalPrice += price

orderTotal += price

if (orderTotal > maxOrderPrice):

maxOrderPrice = orderTotal

maxCompanyName = companyname

maxCustomerId = customerid

# compute averages and print statistics

avgOrderPrice = float(totalPrice/numOrders)

avgNumProducts = float(numProductOrders/numOrders)

avgQuantityProducts = float(totalQuantity/numProductOrders)

print "********************************************************************"

print "%s: Order Statistics" % xmlFile

print "\tAverage total price per customer order = %.2f" % avgOrderPrice

print "\tAverage number of products per customer order = %.f" % avgNumProducts

print "\tAverage quantity per product order = %.f" % avgQuantityProducts

print "\tLargest total order = %d made by Company: %s %s" % (maxOrderPrice, maxCompanyName, maxCustomerId)

print "\tLargest number of products sold = %d" % maxProductOrder

print "\t\tProduct: %s" % maxProductName

print "\t\tSupplier: %s %s" % (maxSupplierId,maxSupplierName)

print "********************************************************************"

if __name__ == '__main__':

main()

> python nwindA2X.py -v nwind9.xml > outnwindA2X.py

************************************************************

Orders: 'orderid = 10248'

Customerid = VINET

Companyname = Vins et alcools Chevalier

-----------------------------------------------

ProductID = 72 ProductName = Mozzarella di Giovanni

Unitprice = 34.8 Quantity = 5 Totalprice = 174.00

---------------------------------------------------

ProductID = 11 ProductName = Queso Cabrales

Unitprice = 14.0 Quantity = 12 Totalprice = 168.00

---------------------------------------------------

ProductID = 42 ProductName = Singaporean Hokkien Fried Mee

Unitprice = 9.8 Quantity = 10 Totalprice = 98.00

---------------------------------------------------

************************************************************

********************************************************************

nwind9.xml: Order Statistics

Average total price per customer order = 1631.88

Average number of products per customer order = 2

Average quantity per product order = 23

Largest total order = 17250 made by Company: QUICK-Stop QUICK

Largest number of products sold = 1577

Product: Camembert Pierrot

Supplier: 28 Gai pâturage

********************************************************************

<?xml version="1.0" encoding="ISO-8859-1"?>

<nwind>

<companyname>Vins et alcools Chevalier</companyname>

<customerid>VINET</customerid>

</customers>

<productname>Mozzarella di Giovanni</productname>

</products>

<companyname>Formaggi Fortini s.r.l.</companyname>

</suppliers>

</orderdetails>

<productname>Queso Cabrales</productname>

</products>

<companyname>Cooperativa de Quesos 'Las Cabras'</companyname>

</suppliers>

</orderdetails>

<productname>Singaporean Hokkien Fried Mee</productname>

</products>

<companyname>Leka Trading</companyname>

</suppliers>

</orderdetails>

</orders>

<companyname>Hanari Carnes</companyname>

<customerid>HANAR</customerid>

</customers>

<productname>Louisiana Fiery Hot Pepper Sauce</productname>

</products>

<companyname>New Orleans Cajun Delights</companyname>

</suppliers>

</orderdetails>

<productname>Jack's New England Clam Chowder</productname>

</products>

<companyname>New England Seafood Cannery</companyname>

</suppliers>

</orderdetails>

<productname>Manjimup Dried Apples</productname>

</products>

</suppliers>

</orderdetails>

</orders>

<companyname>Victuailles en stock</companyname>

<customerid>VICTE</customerid>

</customers>

<productname>Ravioli Angelo</productname>

</products>

<companyname>Pasta Buttini s.r.l.</companyname>

</suppliers>

</orderdetails>

<productname>Louisiana Fiery Hot Pepper Sauce</productname>

</products>

<companyname>New Orleans Cajun Delights</companyname>

</suppliers>

</orderdetails>

<productname>Gustaf's Knäckebröd</productname>

</products>

<companyname>PB Knäckebröd AB</companyname>

</suppliers>

</orderdetails>

</orders>

<companyname>Suprêmes délices</companyname>

<customerid>SUPRD</customerid>

</customers>

<productname>Camembert Pierrot</productname>

</products>

<companyname>Gai pâturage</companyname>

</suppliers>

</orderdetails>

<productname>Sir Rodney's Marmalade</productname>

</products>

<companyname>Specialty Biscuits, Ltd.</companyname>

</suppliers>

</orderdetails>

<productname>Geitost</productname>

</products>

<companyname>Norske Meierier</companyname>

</suppliers>

</orderdetails>

</orders>

<companyname>Hanari Carnes</companyname>

<customerid>HANAR</customerid>

</customers>

<productname>Gorgonzola Telino</productname>

</products>

<companyname>Formaggi Fortini s.r.l.</companyname>

</suppliers>

</orderdetails>

<productname>Chartreuse verte</productname>

</products>

<companyname>Aux joyeux ecclésiastiques</companyname>

</suppliers>

</orderdetails>

<productname>Maxilaku</productname>

</products>

<companyname>Karkki Oy</companyname>

</suppliers>

</orderdetails>

</orders>

<companyname>Chop-suey Chinese</companyname>

<customerid>CHOPS</customerid>

</customers>

<productname>Pâté chinois</productname>

</products>

<companyname>Ma Maison</companyname>

</suppliers>

</orderdetails>

<productname>Longlifes Tofu</productname>

</products>

<companyname>Tokyo Traders</companyname>

</suppliers>

</orderdetails>

<productname>Guaraná Fantástica</productname>

</products>

<companyname>Refrescos Americanas LTDA</companyname>

</suppliers>

</orderdetails>

</orders>

<companyname>Richter Supermarkt</companyname>

<customerid>RICSU</customerid>

</customers>

<productname>Raclette Courdavault</productname>

</products>

<companyname>Gai pâturage</companyname>

</suppliers>

</orderdetails>

<productname>Chang</productname>

</products>

<companyname>Formaggi Fortini s.r.l.</companyname>

</suppliers>

</orderdetails>

<productname>Pavlova</productname>

</products>

<companyname>Pavlova, Ltd.</companyname>

</suppliers>

</orderdetails>

<productname>Inlagd Sill</productname>

</products>

<companyname>Svensk Sjöföda AB</companyname>

</suppliers>

</orderdetails>

</orders>

<companyname>Wellington Importadora</companyname>

<customerid>WELLI</customerid>

</customers>

<productname>Original Frankfurter grüne Soße</productname>

</products>

<companyname>Plutzer Lebensmittelgroßmärkte AG</companyname>

</suppliers>

</orderdetails>

<productname>Perth Pasties</productname>

</products>

</suppliers>

</orderdetails>

</orders>

<companyname>Ernst Handel</companyname>

<customerid>ERNSH</customerid>

</customers>

<productname>Chang</productname>

</products>

<companyname>Formaggi Fortini s.r.l.</companyname>

</suppliers>

</orderdetails>

<productname>Chef Anton's Gumbo Mix</productname>

</products>

<companyname>New Orleans Cajun Delights</companyname>

</suppliers>

</orderdetails>

<productname>Mascarpone Fabioli</productname>

</products>

<companyname>Formaggi Fortini s.r.l.</companyname>

</suppliers>

</orderdetails>

</orders>

<customerid>BONAP</customerid>

</customers>

<productname>Grandma's Boysenberry Spread</productname>

</products>

<companyname>Grandma Kelly's Homestead</companyname>

</suppliers>

</orderdetails>

<productname>Tofus</productname>

</products>

<companyname>Mayumi's</companyname>

</suppliers>

</orderdetails>

<productname>Teatime Chocolate Biscuits</productname>

</products>

<companyname>Specialty Biscuits, Ltd.</companyname>

</suppliers>

</orderdetails>

</orders>

</nwind>