MySQL for Python Developers
Sequence
Updates for a single booking
Sequence

Update booking information in your Flask app

In the previous lesson, we worked on the endpoint to display the information for a single booking. In this lesson, we'll work on the code to save an updated booking. We'll be using the Flask web framework to build our Python application.

Getting started

As always, we'll start by getting the database connection and cursor. We'll be using the get_db() function to get our database connection and then setting up a cursor with dictionary support.

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

Retrieve the guest_id

Next, we want to get the ID of the guest associated with the selected booking, in case we need to update the guest's information. We can do this by executing a SQL query to fetch the guest_id.

Python
cursor.execute(
"SELECT guest_id FROM booking WHERE reference_number = %s",
(reference_number,),
)
booking = cursor.fetchone()

Make sure to check if the booking exists and return an appropriate error if it does not.

Python
if not booking:
abort(404)

Update guest information

Now it's time to update the guest information. We'll be executing another SQL query to update the guest's name and email address.

Python
cursor.execute(
"UPDATE guest SET name = %s, email = %s WHERE id = %s",
(request.form["name"], request.form["email"], booking["guest_id"]),
)

Update booking information

Finally, we'll update the booking information in the database. We'll execute a more complex UPDATE query for this purpose, which involves joining the room_type table to get the correct room_type_id.

Python
cursor.execute(
"""
UPDATE booking
JOIN room_type ON room_type.code = %s
SET check_in = %s, check_out = %s, room_type_id = room_type.id,
breakfast = %s, airport_pickup_time = %s
WHERE reference_number = %s
""",
(
request.form["room_type"],
request.form["check_in"],
request.form["check_out"],
request.form.get("breakfast"),
request.form["airport_pickup_time"],
reference_number,
),
)

After updating the information, commit the changes to the database and close the cursor.

Python
db.commit()
cursor.close()

Now, if we test our application by altering some booking information, we can see that the updated values are saved in the database.

Recap and next steps

In this lesson, we covered how to write the code for updating booking and guest information using Flask and SQL queries. Specifically, we inserted the guest_id and then used UPDATE statements to revise the contents of the guest and booking tables.

In the next lesson, we'll continue our journey by writing code for the delete button in our hotel booking application to remove bookings from the database.

About this lesson

Make updates to a single booking in the hotel management app.

06:12
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.