Lab 10 - MySQL and Python

Introduction

This lab should get you familiarized with accessing MySQL databases over the web using Python. In this lab, you will add a database back-end for your online store and allow a user to buy products using the shopping cart functionality you created previously.
For this lab you will be using your MySQL database on mope.academy.usna.edu and the webserver on mope.academy.usna.edu .

SETUP - DO THIS FIRST!

You must create a folder on you Web drive called "Lab10" (without the quotes) and store your work in that directory.
Copy your files from Lab07 to Lab10. If your Lab 7 is not working properly (create shopping cart, checkout), contact the instructor

VERY IMPORTANT: To allow the webserver to later create files when running a Python script, the webserver user needs to have extra permissions on your Lab10 directory. To enable this, open a terminal window on your Unix machine (or ssh into mich300csdYYu.academy.usna.edu. you can use 01 to 20 for YY). Type the following (replace XXXXXX with your alpha) in the terminal window that appears:

cd public_html

setfacl -R -m u:www-data:rwx Lab10
setfacl -R -dm u:www-data:rwx Lab10
setfacl -R -m u:mXXXXXX:rwx Lab10
setfacl -R -dm u:mXXXXXX:rwx Lab10

ADVICE:

Look through the song.py and songPage.py available on the course webpage. A lot of the code required from this lab is similar to the code provided.

PRELIMINARY

You should have the files products.html, shoppingCart.js, shoppingCart.html, shoppingCart.py from your lab 7 and the tables PRODUCT, SALE, SALEITEM from your lab 9. The tables should be appropriate to store the data for your products and sales. The primary and foreign keys must be defined as specified in Lab 9.

PRODUCT(ProductID, PName, PDescription, Price) - this table records information about each product. Modify the table as needed to fit your products. The table must have a numeric primary key. If you have images for your products, you should store the image name/path in the database (whatever is the value for the src attribute in the img tag). You should NOT store the image files in the database (while it is possible to do so, it is not efficient). Choose appropriate data types for the columns. See http://dev.mysql.com/doc/refman/5.6/en/data-types.html for more information on MySQL data types if needed.

SALE(SaleID, DeliveryAddress, CreditCard) - this table records information about each sale/transaction. Modify the table as needed to fit your sale information. SaleID must be a surrogate key - created by MySQL. Hint: Declare SaleID int AUTO_INCREMENT in the column declaration for SaleID

SALEITEM(SaleID, ProductID, Quantity) - this table records information about which products were sold in each sale/transaction. SaleID and ProductID should be declared as foreign keys. The primary key is the combination of SaleID and ProductID

In this lab we will store the product information in the database and we will dynamically generate the products page, based on the products in the database. We will also store the sales data into the database

REQUIREMENTS - THE ACTUAL LAB:

Read the entire lab so you see the requirements and know what is coming.

Display products:

  1. Insert the information about the products you have in your products.html page into the database. Write and execute SQL INSERT statements for that. You should have at least 5 products in your Products table.
  2. Write the Python script(s) to dynamically generate the products.html, including the "add to cart" buttons based on the data in your Products table. Follow these steps:
    • Write a module or class product.py with one method, printProducts(cursor) that prints or returns a string with all the products in the Product table as a nice HTML table very similar with the one you had for your products.html page. This should include the code for the "add to cart" buttons.
    • Write a script productPage.py that when executed, will produce an output similar with products.html. Your productsPage.py should invoke the printProducts() from the product.py module to print the products. Test the functionality using the URL http://mope.academy.usna.edu/~mXXXXXX/Lab10/productsPage.py

Can still shop

  1. Ensure that your shoppingCart.js script from Lab 7 is used by the new productPage.py and that addToCart and viewCart functionality works as expected. Note that if you hard-coded some product information in JavaScript, that information must now be generated dynamically from the database, or must be removed completely.
  2. Ensure that the checkout button/link to the shoppingCart.html is visible when productsPage.py is loaded by the browser
  3. Ensure that shoppingCart.html displays the list of products in the shopping cart and the check-out form with action shoppingCart.py

Additional Steps

  • Documentation: Ensure you have appropriate comments in your Python script.
  • Important final steps: Create two links in your top-level default.html page under the heading "Lab10":
    1. Under the name "Products", make a link to your Lab10/productPage.py page
    2. Under the name "shoppingCart.js" make a link to your Lab10/shoppingCart.js file

EXTRA CREDIT: Buy products - database style

For a nominal amount of extra credit, do the following:

  1. You should have the SALE and SALEITEM tables from Lab 9. Check that the tables exist, that the SaleID is the primary key and AUTO_INCREMENT in SALE table, and that the foreign key constraints between SALEITEM and Product and SALE are in place. If needed, modify the tables (add extra columns) such that the information from your checkout form and shopping cart can be stored in these tables.
  2. Modify your shoppingCart.py script to save the information into the SALE and SALEITEM tables instead of the files, and if everything OK, delete the shopping cart cookie and display a confirmation message, or error message if errors occurred. Here are the steps
    • Write a module or class sale.py with at least one method addSale(cursor, any extra information neeed) that inserts the data in the SALE and SALEITEM tables and returns either a SaleID as the confirmation number or 0 otherwise. The function should
      • Insert general order data into the SALE table in your database. Note that the SaleID is a surrogate key. The surrogate keys were created using AUTO_INCREMENT keyword, and the value for the surrogate key is generated by the database system. When writing the INSERT statement to insert into the table, you should not insert into the SaleID column (see the addSong in song.py on the course calendar for an example). Since you need the generated SaleID value for subsequent inserts, you should use cursor.lastrowid -this represents the ID created by the previous insert statement.
      • Insert each product from the shopping cart in the SALEITEM table. Note that the sale id should correspond to the sale id previously created (see above)
      • If data was saved in the database, return the new SaleID
      • If there was any error, return 0
    • Modify the shoppingCart.py script to invoke the addSale function with the values posted by the user during checkout. If everything is OK, delete the shopping cart cookie and display the confirmation message, including the transaction number (SaleID) and list of products purchased. If errors, display an error message to the user.

Deliverables

  1. New files: product.py, productPage.py, andfor extra credit, sale.py
  2. Modified files: shoppingCart.py
  3. Files that could have been modified or stayed the same: shoppingCart.html, shoppingCart.js
  4. You should have all the pieces working as described in "Requirements" above.
  5. You should have the two links in default.html that are described above.
  6. All of your files should be in a folder called "Lab10" (without the quotes) in your public_html. Your instructor will assume that your web pages are viewable at http://mope.academy.usna.edu/~mXXXXXX/Lab10/productPage.py where XXXXXX is your alpha number.
  7. Turn in (due before lab on Friday):
    1. Paper submission: turn in the following hardcopy at the beginning of class on the due date, stapled together in the following order (coversheet on top):
      1. A completed assignment coversheet. Your comments will help us improve the course.
      2. A printout of the source of your product.py file.
      3. A printout of the source of your productPage.py file
      4. If extra credit done, a printout of sale.py and shoppingCart.py
      5. A screen-shot of your browser window showing the productPage.py loaded in the browser window
    2. Electronic submission: zip your Lab10 folder with all its contents and upload the zip file to Lab10 assignment on Blackboard.