MySQL for Python Developers
Sequence
Query a single booking
Sequence

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:

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

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

Python
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.

Python
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:

Python
cursor.fetchone()

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

Python
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:

Python
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!

About this lesson

Query the database and return data for a single booking

03:04
Closed captioned

Meet your instructor, Anthony Herbert

Software Developer

I'm a software developer and educator focused on helping others build their dream software projects.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.