GP5: Database Updates

Due: Monday, April 22nd

Image source: pexels.com

Instructions

The goal of this assignment is to extend your application to perform various transactions. For each query (and web form) you have created, you will need to add support for INSERT/UPDATE statements.

Define additional routes in web.py that implement the transactions described below. Modify the Jinja templates to gather necessary user input and display transaction results. Finally, add code to db.py for executing the transactions in the database.

Recall that in GP4 you ran complex SELECT statements via SQL functions. For GP5, you can run simple INSERT/UPDATE statements directly from db.py or from additional SQL functions that you write.

Requirements

Your web application must support the following transactions. Determine whether data will need to be inserted or updated, depending on your database design.

Query 1: Reservations

After the guest selects the room type(s) they want to reserve, a reservation(s) should be created in the system. The guest should be able to reserve multiple rooms for the same dates.

Query 2: Checking In

After the clerk selects a specific room or rooms for the guest who is checking in, the room(s) should be assigned to the guests, and the other room occupants' information should be stored.

Query 3: Checking Out

The database should be updated showing that the guest has checked out and the room or rooms are ready to be cleaned. An itemized bill should be generated and displayed, and any related data should be inserted/updated.

Query 4: Special Feature

The insert(s)/update(s) required when a guest (or occupants of the guest's room(s)) uses the special service should be made, including inserting/updating the information needed to generate the bill when the guest checks out.

Example Files

The flight_search() example from GP4 has been extended to support flight reservations. Notice the templates files have been renamed to .jinja, which is the default extension for Better Jinja in VS Code.

• flight_reserve.jinja
• flight_search.jinja
• app.py
• db.py

Documentation

Refer to the following documentation sites as needed:

• app.py
  • Flask: Quickstart
• db.py
  • Psycopg: Passing Parameters to SQL
  • Psycopg: Transactions Management
• *.html
  • Jinja: Template Designer Documentation
  • Google Charts: API Reference