Create an OrderInfo table and an OrderItems table in the Books database to store or- ders placed by customers. The OrderInfo table should store an OrderID, an OrderDate and the Email address of the customer who placed the order. [Note: You will need to modify the form to include the customer’s e-mail address.] The OrderItems table should store the OrderID, IS- BN, Price and Quantity of each

Question

book in the order. Modify process.py so that it stores the order information in the OrderInfo and OrderItems tables.

What will be an ideal response?

Answer 1

```
# Exercise 23.7: process.py
# Display thank you page to customer and delete session

import cgi
import time
import sys
import Session
import MySQLdb

# load session
try:
session = Session.Session()
except Session.SessionError, message: # invalid/no session ID
Session.redirect( "error.py?message=%s" % message )
sys.exit()

form = cgi.FieldStorage()
fields = [ "firstname", "lastname", "city", "state", "creditcard",
"zipcode", "phone", "street", "expires", "expires2",
"email" ]

# test for required fields
for field in fields:

if not form.has_key( field ):
Session.redirect( "order.py?ID=%s" % session.data[ "ID" ] )
sys.exit()

# test for expired credit card
month = int( time.strftime( "%m" ) )
year = int( time.strftime( "%Y" ) )
cardMonth = int( form[ "expires" ].value )
cardYear = int( form[ "expires2" ].value )

if ( cardYear < year ) or ( ( cardYear == year ) and \
( cardMonth <= month ) ):
Session.redirect( "error.py?message=Expired+credit+card." )
sys.exit()

# update Books database with latest purchase
try:

# connect to the database, retrieve a cursor and execute queries
connection = MySQLdb.connect( db = "Books" )
cursor = connection.cursor()

# store OrderInfo
cursor.execute( "insert into OrderInfo (OrderDate, Email) " + \
"values (’%s’, ’%s’)" % ( time.strftime( "%B %d %Y" ),
form[ "email" ].value ) )

# obtain current OrderID
cursor.execute( "select OrderID from OrderInfo" )
orderID = len( cursor.fetchall() )

cart = session.data[ "cart" ]

# store OrderItems for each item in cart
for item in cart.values():
book = item.item
ISBN = book.isbn
price = book.price
quantity = item.quantity

cursor.execute( "insert into OrderItems (OrderID, ISBN, " + \
"Price, Quantity) values (%d, ’%s’, %f, %d)" % \
( orderID, ISBN, price, quantity ) )

# close database connection
cursor.close()
connection.close()
except MySQLdb.OperationalError, message:
Session.redirect( "error.py?message=%s" % message )
sys.exit()

# display content type and thankYou for specific client-type
content = open( "%s/thankYou.%s" % ( session.data[ "agent" ],
session.data[ "extension" ] ) )
pageData = session.data[ "content type" ] + content.read() % \
session.data[ "total" ]
content.close()

# delete session and display thank you page
session.deleteSession()
print pageData
```