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.
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.
db = get_db()
cursor = db.cursor(dictionary=True)
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.
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.
if not booking:
abort(404)
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.
cursor.execute(
"UPDATE guest SET name = %s, email = %s WHERE id = %s",
(request.form["name"], request.form["email"], booking["guest_id"]),
)
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.
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.
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.
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.