Retrieving and displaying a single booking with Flask

In the previous lesson, we learned how to create new bookings in our database. Now, we will write the code to return the information for one particular booking using the reference number from the URL. This will allow us to eventually update the booking information on a detail page.

Prepare the database connection and cursor

First, just like in the previous tutorial, we need to set up our database connection and cursor:

db = get_db()
cursor = db.cursor(dictionary=True)

Don't forget to close the cursor at the end of your code:

cursor.close()

Write the query for a single booking

Next, we need to write the query to get a single booking based on the reference number. This will be similar to the query used for retrieving all bookings, but with a WHERE clause specific to the reference number.

cursor.execute(
  """
    SELECT
        booking.reference_number,
        guest.name,
        guest.email,
        booking.check_in,
        booking.check_out,
        room_type.code,
        booking.airport_pickup_time,
        booking.breakfast
    FROM
        booking
    JOIN
        guest ON booking.guest_id = guest.id
    JOIN
        room_type ON booking.room_type_id = room_type.id
    WHERE booking.referenceNumber = %s;"
    """,
    [reference_number]
)

Fetch and display the booking details

After executing the query, we need to fetch the booking details:

cursor.fetchone()

If the booking is not found, we want to return a 404 error and close the cursor:

if not booking:
    cursor.close()
    abort(404)

Now that we have the booking details, we can generate the template for the single view and render it. Assuming your template is named booking.html, you can do the following:

return render_template('booking.html', booking=booking)

Conclusion and Next Steps

In this lesson, we learned how to retrieve and display information for a single booking using Flask. We created a database connection, wrote a query to get a single booking based on the reference number, fetched and displayed the booking details, and updated the template to show additional information.

In the next lesson, we will modify this code to allow users to update a booking's information by changing the displayed values and clicking a "Submit" button. This will involve adding a form and handling form submission in our Flask app. Stay tuned!