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.
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()
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]
)
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)
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!